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 


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 

and $input{next} will always be 10,15,20, etc.

Now from here:


> 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:


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.


PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to