PHP & MySQL

How can I get the next row?

[sned]

I have a table with several thousand records, and I want to be able to have previous and next link buttons as the user searches through.

However, the next id is not always id+1, they jump around quite a bit ... (1,2,3,5,8,9,10, ... ).

Is there a mysql command or php command to get the next row? Say if I had an ID of 5, how would I get the previous row and the next row?

The SQL statement I'm using just returns one row. (SELECT * FROM TABLE WHERE ID = 5)

I know I could do this by looping through the entire table, but that seems to be a lot of extra processing for something very simple.

Thanks for your info.

[brotherhood of LAN]

SELECT * FROM table WHERE ID > 5 LIMIT 1

?

[ergophobe]

$next_query = "SELECT * FROM table WHERE id>5 ORDER BY id LIMIT 1";

$prev_query = "SELECT * FROM table WHERE id<5 ORDER BY id DESC LIMIT 1";

Note the difference here - I added the ORDER BY statements. That's essential, otherwise you just get a record that is earlier or later. Note also that you probably want to sort by something other than id (name, size, date or something).

Tom

[sned]

Thanks Tom! That works like a charm!

(Now I wonder why I didn't think of that myself.)

-sned

[brucec]

I was wondering how to do this with the PHP, but the above statements work well.

[ergophobe]

You would not want to do it with PHP, especially not for a large result set.