Calculating previous/next from database

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Suppose you have an image gallery stored in a mysql database, the field id is your index.

How do you calculate previous/next images from the database (assuming you can't just go ++ or -- as the admin can delete images and thus you could have image16.jpg and the next image in the database be image32.jpg).

My idea was to query the db first and store ALL the picture id's in that category into an array. That way I know what's available. Then query for the picture, then somehow see if there is a previous/next image.. and if so, what it is.

Can someone point me in the right direction?

# prequery to see if there IS a previous/next id
  my $data = qq(SELECT id, title FROM pictures WHERE category = $categ
+ory ORDER BY id ASC);
  my $sth = $dbh->prepare($data);
  $sth->execute() or die $dbh->errstr;

  my @pictures_in_category;

  my ($picid, $pictitle);
  $sth->bind_columns(\$id, \$title);
  
  while($sth->fetch)
  {
    push(@pictures_in_category, $id);
  }

# real query for the one image you're viewing
  my $data2 = qq(SELECT id, filename, title, category, description, da
+te, views, sensorisospeed, exposuretime, isospeedrating,
                lensaperture, flash, focallength, cameramodel, shutter
+speed FROM pictures WHERE id=?);
  my $sth2 = $dbh->prepare($data);
  $sth2->execute($picture) or die $dbh->errstr;
...
[download]

Comment on Calculating previous/next from database Download Code

Replies are listed 'Best First'.
Re: Calculating previous/next from database by dragonchild (Archbishop) on May 31, 2008 at 03:47 UTC
If you have the id as a separate column as opposed to embedded in the name, you can easily do this with: `SELECT * FROM images WHERE id > ? LIMIT 1` [download] That would be for the ++ scenario. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply] [d/l]
Re^2: Calculating previous/next from database by ikegami (Patriarch) on May 31, 2008 at 04:05 UTC
Not quite. What you have won't return all the records in the table when called repeatedly. For that you need `SELECT * FROM images WHERE id > ? ORDER BY id LIMIT 1` [download]	[reply] [d/l]
Re^2: Calculating previous/next from database by Anonymous Monk on May 31, 2008 at 05:21 UTC
A problem I see with this approach would be sending three queries to the database each time it loads. One for ++, one for --, and the one the OP is using for the real query.	[reply]
Re^3: Calculating previous/next from database by dragonchild (Archbishop) on May 31, 2008 at 17:09 UTC
Meh. If your requirement is a single query, then you need to implement a linked list in the database. This isn't that hard, but it requires that all inserts and deletes update 3 rows vs. just selects doing three queries. This probably is ok. `CREATE TABLE images ( id INT ,prev_id INT ,next_id INT ,other_columns_here );` [download] Then, to get everything in a single query, you do: `SELECT * FROM images WHERE ( (id=?) OR (next_id=?) OR (prev_id=?) );` [download] Not that hard. :-) My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply] [d/l] [select]
Re^3: Calculating previous/next from database by parv (Parson) on May 31, 2008 at 08:06 UTC
Only if this could work ... `-- changed "union" to "union all" select id from table where id > ? order by id asc limit 1 UNION ALL select id from table where id < ? order by id desc limit 1` [download] ... does not work in Sybase for (a) "order by" clause does not go with "union all" or in derived table creation; (b) there is no "limit" clause, just "rowcount" option per SQL session/transaction.	[reply] [d/l]
Re^4: Calculating previous/next from database by psini (Deacon) on May 31, 2008 at 12:10 UTC
Re: Calculating previous/next from database by igelkott (Priest) on May 31, 2008 at 09:51 UTC
Sounds like you want a Database Cursor. Some DB software makes this easy and some require something manual. What DB engine do you have?	[reply]
Re: Calculating previous/next from database by ides (Deacon) on May 31, 2008 at 15:05 UTC
I usually use DBIx::Class and it's paging ability via the Data::Page module. It makes this sort of thing very easy. Frank Wiles <frank@revsys.com> www.revsys.com	[reply]