On 16 Jul 2002 at 10:51, Mark McCulligh wrote:
I have newbie question.
I am building a search page that will return any number of records and
want to display only 30 at a time say. Then have one of though Page
1 of 3 [1] [2] [3] Next on top of the record list.
What is the best way to do this.
I was looking at using the LIMIT feature in MySQL, but the MySQL
manual said that if you use ORDER BY with LIMIT in the same SELECT it
does the LIMIT before the ORDER BY. Therefore it does the limit then
orders the limit list only, not order the entire list then returns the
limit from that. Is this true? -OR- This it better to retrieve the
entire record list, then jump to a starting position. Say on Page 2
start displaying at record 31.
If I use the second method, is there a PHP/MySQL function of move to a
certain record.
I know there is probably many different solution to this problem, but
what are some of them. I was going to use my last option but if I
have a 1000+ records loading each time the performance may be slow.
As common as this is I should have some template for it at least ... I did try that
once. Here is an example I recently used. It's in Perl but since it's primarily SQL
it's still
a good example I reckon.
(note, thank god, in Perl we don't have to quote literals in a hash, I hate doing that)
if($input{next}) {
$sql = qq|SELECT * FROM images WHERE location = ? AND image_id $input{next}
ORDER BY born DESC LIMIT $input{spread}|;
}elsif($input{previous}) {
$sql = qq|SELECT * FROM images WHERE location = ? AND image_id
$input{previous} ORDER BY born LIMIT $input{spread}|;
} else {
$sql = qq|SELECT * FROM images WHERE location = ? ORDER BY born DESC LIMIT
$input{spread}|;#
}
Explanation:
SELECT * FROM images WHERE location = ? AND image_id $input{next} ORDER BY born DESC
LIMIT $input{spread}
$input{next} is the ($input{spread} + current position) so if my spread is 5 I will
have
1-5
6-10
11-15
and $input{next} will always be 10,15,20, etc.
Now from here:
http://www.mysql.com/doc/L/I/LIMIT_optimisation.html
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon
as it has found the first # lines instead of sorting the whole table.
Umm ... my system worked. I don't think though that the above line indicates it would.
You can see the snippets I show from above here:
http://www.memphisart.com/superframes/index.cgi
I set the spread to 2 so you can see it working and I uploaded a few images. The guy
who wanted that wanted to sort by last in first up.
There are probably many more ways of doing this. Hope we see some more responses. I
never thought to use the LIMT X,Y option. I will test that later.
Peter
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
