Cycling through rows, smartly?

[jayemvee]

I was wondering, what I would need to do the following. In this scenario, we have albums of photos.

2 Tables albums and photos.

So, if I were to pull a photo like so:

SELECT * FROM photos WHERE album_id='$album_id' AND id='$photo_id'

What is the most elegant way to determine the previous and next record in that set?

Do I need to number the photos within albums? or is there a way for it to be "smarter" than that.

Any help or direction on the best way to go would be greatly appreciated.

[guelphdad]

you should have three tables

table 1 is albums and has an albumid and album_name for each album

table 2 is photos and has a photoid and photo_name among other columns

table 3 is photosinalbums and has albumid and photoid as the only two columns

use a join to get your info out.

use an order by clause.

don't retrive just a single row with your query, retrieve all of your photos you want to display from an album at once. less trips to your database and back that way.

in your front end code you step through each of the photos, or switch albums or whatever.

do a search on pagination because essentially that is what you would want to do.

[jayemvee]

I have done typical pagination. But in this sense, I am not loading the album itself. Only the photo for a user to view. I wanted there to be a next / previous button and photos without any other information than the current "photo" or "row". Here was my solution:



// Current Photo
// I am using a DB abstraction layer so pay attention to the query part not so much the PHP
$photo = $_photo->find_first("id='$photo_id'");

$prevPhoto = $_photo->find_first("SELECT * FROM photos WHERE album_id='$photo->album_id' AND id > '$photo->id' ORDER BY id ASC");
$nextPhoto = $_photo->find_first("SELECT * FROM photos WHERE album_id='$photo->album_id' AND id < '$photo->id' ORDER BY id DESC");