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