[SOLVED] Get next and previous records of the table

[Soleil]

Hi all,

 

I'm building a photo album that shows a photo (called current) and two links for the previous and next photos. I use ID field to identify photos ($IDnumber = $current_photo, then $previous_photo_IDnumber = $IDnumber - 1 and $next_photo = $IDnumber + 1).

If I have an ordered table (the IDnumbers are 1, 2, 3, 4, 5) everything is OK.

 

But when I delete a photo, eg, photo number 3, my the IDnumbers will be (1,2,4,5), I get into trouble.

For example, when I see the photo having the IDnumber 2, I can not get the link to the next photo, in that case, the photo having the IDnumber 4 because the IDnumber of the next photo is defined by $IDnumber + 1 equal to 3 but the photo with IDnumber 3 have been deleted off the table.

 

Could anyone have a solution ? Many thanks in advance and sorry for my bad English.

[simcoweb]

I don't have the specific syntax to provide but you should look into the next() and previous() functions of PHP that moves to the next item in the array regardless of the id number.

 

http://us2.php.net/manual/en/function.prev.php

 

http://us2.php.net/manual/en/function.next.php

[Soleil]

I don't have the specific syntax to provide but you should look into the next() and previous() functions of PHP that moves to the next item in the array regardless of the id number.

 

http://us2.php.net/manual/en/function.prev.php

 

http://us2.php.net/manual/en/function.next.php

 

Thank simcoweb ! I'll run to read it.

[Soleil]

Finally, I came out with simple solution using ORDER BY DESC LIMIT as follows :

 

$var = $IDnumber;

For the previous record IDnumber :

SELECT IDnumber WHERE IDnumber < $var ORDER BY IDnumber DESC LIMIT 1;

For the next record IDnumber :

SELECT IDnumber WHERE IDnumber > $var ORDER BY IDnumber LIMIT 1;

 

That's is !

 

I don't know if that is the good solution but at least it works for me. If anyone has a better solution, please share ! Thanks !