Creating next and previous buttons for photo database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Hendeca
Forum Commoner
Posts: 29
Joined: Tue Nov 18, 2008 1:27 pm

Creating next and previous buttons for photo database

Post 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!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Creating next and previous buttons for photo database

Post 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
 
Hendeca
Forum Commoner
Posts: 29
Joined: Tue Nov 18, 2008 1:27 pm

Re: Creating next and previous buttons for photo database

Post 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!
Hendeca
Forum Commoner
Posts: 29
Joined: Tue Nov 18, 2008 1:27 pm

Re: Creating next and previous buttons for photo database

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Creating next and previous buttons for photo database

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply