Page 1 of 1

Creating next and previous buttons for photo database

Posted: Sat Mar 14, 2009 3:09 am
by Hendeca
Hey,

I have a photo site that uses a database to generate thumbnails. When the thumbnails are clicked, the info for that record is retrieved and the photo is loaded.

I'm trying to create next and previous buttons that will load whatever the next photo in the series is. I would simply add one to the primary key of the photo and retrieve that record, but when a photo is deleted, it creates gaps in the primary keys.

Is there a way to send an sql query that just retrieves the next record? I'm just not sure how to tackle this problem. Here's how the site works so far:

Code: Select all

 
//retrieve all photos from one category
$dbName = 'dbName';
$dsn = 'dsn';
$user = 'user'
$pwd = 'password'
try {
    $db = new PDO($dsn, $user, $pwd);
    $st = $db->prepare('SELECT * FROM photos WHERE category = ? ORDER BY photo_id DESC');
    $st->bindParam(1, $dbName);
    $st->execute();
}
catch(PDOException $e)
{
    echo $e->getMessage();
}
 
 
//create thumbnails that load main photos
<div id="thumbnails">
            <?php while($row = $st->fetch(PDO::FETCH_ASSOC)) { ?>
                <a href="<?php echo $_SERVER['PHP_SELF']; ?>?image=<?php echo $row['file_name']; ?>"><img src="css/images/photos/thumbs/<?php echo $row['thumb_name']; ?>" /></a>
            <?php } ?>
 
 
//if thumbnail has been clicked, load the current image
if (isset($_GET['image'])) {
                $currentImage = $_GET['image'];
                $result = $db->prepare('SELECT * FROM photos WHERE file_name = ?');
                $result->bindParam(1, $currentImage);
                $result->execute();
                $currentEntry = $result->fetch(PDO::FETCH_ASSOC); 
 
These are the main pieces of the code that populate the photos from the database. Can anyone help me figure out a way to click "next" or "previous" and load the next photo in that photo's category? Thanks in advance!

Re: Creating next and previous buttons for photo database

Posted: Sat Mar 14, 2009 3:18 am
by Benjamin

Code: Select all

 
SELECT photo_id AS next FROM photos WHERE category = ? AND photo_id > n ORDER BY photo_id DESC LIMIT 1
SELECT photo_id AS prev FROM photos WHERE category = ? AND photo_id < n ORDER BY photo_id ASC LIMIT 1
 

Re: Creating next and previous buttons for photo database

Posted: Mon Mar 16, 2009 5:45 pm
by Hendeca
Hmm, I can't seem to get this to work. Does this use aliasing? I don't really understand how this works. Can you point me to some documentation or something? Thanks a lot!

Re: Creating next and previous buttons for photo database

Posted: Sat Mar 21, 2009 4:03 am
by Hendeca
Ah, now I've gotten it to work using your query. It was a problem elsewhere in the script. This also works well because if there is no record retrieved (aka it's reached the highest or lowest id number), a null variable is returned. Checking for the null variable will allow you to hide the "next" or "previous" link when the highest or lowest id number is reached. Thanks!

Re: Creating next and previous buttons for photo database

Posted: Sat Mar 21, 2009 4:17 am
by VladSun
If you need *only* the photo id field, you may use MIN/MAX functions to retrieve it. I believe it will be faster than ORDER BY/LIMIT.
[sql]SELECT MIN(photo_id) AS next FROM photos WHERE category = ? AND photo_id > n SELECT MAX(photo_id) AS prev FROM photos WHERE category = ? AND photo_id < n[/sql]

You may also combine these queries into a single query.