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.
Advertising
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
