Previous/Next Rows in mySQL

Anon

Hello all. Is there a way to retrieve the previous and next row if your a given the middle row? I have script, and i need to be able to grab data from the row after the row specified [$rowNum] and from the row right before the specified row [$rowNum].

If someone could help me out, i'd appreciate it. I conisdered querying for each row by its num but when a row is deleted, the numbers arent rebuilt, so if i specify $rowNum as say 5, the previous row may not be 4, depending on whether or not it was deleted.

Thanks,
Bryan

Anon

Total guess but you could use your id column in a new query string using something like:
...select from table_name where id<='$id' limit 1 (for previous row)...Where you've grabbed the $id=row["id"] in a previously called "while" loop (don't forget globals) from a select query...Hope this helps...

Anon

Hang on...Sorry about the first post...

For the greater than (next row) the query will actually be like:

select * from table name where id>$id limit 1;

For the less than (previous row) stuff will be kind of like (presuming $id = 2):

select * from table_name where id< $id limit 0,1;

where in the limit part of the query you could assign the second number (here it is 1) to be 1 less than your $id and the first number of the limit (here it is 0) to be 1 less than the second number of the limit...

Anon

Thanks, im about to write the code in and see if it works. I'll let ya know how it goes =)

Anon

I was able to get the next row working, but whenever i try to get the previous row it always returns 1...

bah, remind me not to c0de at 1:00 half asleep =X. If anyone has any suggestions, let me know!

[deleted]

"select * from table name where id>$id limit 1;"

bad bad naughty bad, this just get's the first row where id>$id, which does not have to be the row you want. (the database does not have to store the ID's in ascending order on disk!)

Instead:

SELECT MIN(id) as nextid
FROM table
WHERE id>$id

and then
SELECT *
FROM table
WHERE id = nextid

"select * from table_name where id< $id limit 0,1;"

a total guess methinks? :-)

SELECT MAX(id) as previousid
FROM table
WHERE id<$id

and then

SELECT *
FROM table
WHERE id = previousid

Anon

Thanks Vince, your help was much appreciated =)