Need Help with some PHP/MySQL stuff - non-sequential navigation

PHP
1

Hi everyone,

I’m self-taught in PHP, MySQL, thanks to the Sitepoint book on creating dynamic websites of course! I’m working on another dynamic site and I’m stuck on something I hope someone can help with:

Basically, I’m trying to navigate from “video to video” and stay on the same page (video.php). Currently I do this by passing a videoID variable on the page refresh. My “next video” and “previous video” buttons are my question…currently all my video ID’s in my database are sequential, in increments of 1. So all I do for my next and previous buttons is get the value of the video ID and either add or subtract 1 to it. I can foresee a time, however, when a video gets deleted from the database and leaves a gap in my sequential ID’s. So my question is, how can I account for these gaps in establishing the previous/next navigation? Somehow I need to have it look for the next existing ID and make the link to that one… can somebody please help? Thanks!

Jarrod

2

most simple SQL:

select id from table where id < $id ORDER BY id LIMIT 1
select id from table where id > $id ORDER BY id DESC LIMIT 1

or you cold do:

select *
from table
where id = $id
    or id = ( select max(id) from table where id < $id)
    or id = ( select min(id) from table where id > $id)

this will select 3 rows the curent, the previous and the next;

only next and previous is:

select *
from table
where
       id = ( select max(id) from table where id < $id)
    or id = ( select min(id) from table where id > $id)