mysql results split on a number of pages

[asdfkajsdflkjsadlfkjoewqifoeiwjf]

Hi

Does anyone have a swift solution on splitting up mysql results on a
number of pages given the results for the query exceeds a specific
number?

Example query: my $sth=$dbh->prepare("select CITY from MYDB where
COUNTRY='GB' desc");

If above would return more than X results I only display X with an
offset for page number (eg regukar search results solution).

Ive managed to do dodgy solutions where I first go through all results
to check total number, push them into an array and play with them.
Surely there must be a more efficient and server friendly way..
Thanks !
M

 

[Martin Kissner]

Hi

Does anyone have a swift solution on splitting up mysql results on a
number of pages given the results for the query exceeds a specific
number?

Example query: my $sth=$dbh->prepare("select CITY from MYDB where
COUNTRY='GB' desc");

If above would return more than X results I only display X with an
offset for page number (eg regukar search results solution).

Ive managed to do dodgy solutions where I first go through all results
to check total number, push them into an array and play with them.
Surely there must be a more efficient and server friendly way..

I guess this is more a mysql related question.
Nevertheless I'll try give you what I can.

my ($start, $step) = (0, 5);
my $sth=$dbh->prepare("select CITY from MYDB where COUNTRY='GB' desc
LIMIT $start, $step");

Now you can play with $start (number of first record beeing displayed) and
$step (number of records beeing displayed) in you script.
You could for example produce links on the result page like this:

[ next 5 >> ]
or
[ 6-10 ] [ 11 - 15 ] [ 16 - 20] and so on.

HTH
Martin

 

[xhoster]

Hi

Does anyone have a swift solution on splitting up mysql results on a
number of pages given the results for the query exceeds a specific
number?

Well, if you want a mysql solution, lookup the Mysql keyword "limit".
Be warned that this is method does not preserve integrity.

If you want a Perl solution, you will need to tell us more about the Perl
environment you are working in.

Example query: my $sth=$dbh->prepare("select CITY from MYDB where
COUNTRY='GB' desc");

If above would return more than X results I only display X with an
offset for page number (eg regukar search results solution).

Ive managed to do dodgy solutions where I first go through all results
to check total number, push them into an array and play with them.
Surely there must be a more efficient and server friendly way..

What do you find unefficient or server-hostile about doing it that way?

Xho

 

[Tore Aursand]

Does anyone have a swift solution on splitting up mysql results on a
number of pages given the results for the query exceeds a specific
number?

You could have a look at some of the modules found on CPAN;

Example query: my $sth=$dbh->prepare("select CITY from MYDB where
COUNTRY='GB' desc");

What you probably want - if you need to roll this on your own - is to
use LIMIT;

SELECT ... FROM ... WHERE ... LIMIT [offset], [limit]

Refer to the MySQL documentation for more details. You will also want to
have a look at the 'SQL_CALC_FOUND_ROWS' function, which I'm guessing is
MySQL specific.