MySQL retrieve previous and next row

[mfaulkner]

I have a table which store details of uploaded images. The primary key is pic_id. If I upload five images, there will be 5 rows, with sequential pic_id's. If I delete the row where pic_id = 3, how can I implement navigation between pic_id = 2 and pic_id = 4?

Initially I have been using:

$pic_id = $_GET['pic_id'];

$next_pic = $pic_id+1;
$previous_pic = $pic_id-1;

But obviously this will only work if all of the pic_id's are sequential.

Many thanks

Mark

[bpat1434]

Well I guess two short queries would work:

Previous Image

SELECT pic_id FROM pictures
WHERE pic_id < '$pic_id'
ORDER BY pic_id DESC
LIMIT 0,1

Next Image

SELECT pic_id FROM pictures
WHERE pic_id > '$pic_id'
ORDER BY pic_id ASC
LIMIT 0,1

If you quickly execute those two, you'll get one result (or none if your pic_id is 1 😉) for each, and those will be your next and previous picture IDs.

The way the SQL works is this:
- We need the pic_id value, so we SELECT that
- We get the values from the those in the "pictures" table
- We only want values that are greater than (>) $pic_id for the next one, and less than (<) $pic_id for the previous one
- We want it ordered from highest to lowest for the previous one, and lowest to highest (ASC) for the next one.
- We only want the 1 row from the set of rows returned, starting at row 0 (the first row returned)

So if your database looked like this:

| pic_id |
+--------+
|    1   |
|    2   |
|    3   |
|    5   |
|    6   |
|    7   |
|    12  |
|    17  |
+--------+

and you're given the following pic_id, the results would be:

pic_id: 5
Previous: 3
Next: 6

pic_id 12
Previous: 7
Next: 17

Hope that helps.

[bdollproject]

This worked great for me, but how would you write it so that the first and last do not show previous and next links?

[laserlight]

The first and last would have the previous and next result sets be empty, respectively, so you can make use of that fact to decide whether or not to show those links.

[bdollproject]

Could you show me an example of what that would look like? I am new to PHP and MySQL.

[bdollproject]

I got it!

if(!is_null($newArray['wid'])){
print " <a target='main' href='gallery_showpic.php?wid=".$wid."'>Next</a>";
}