Calculating previous/next from database

[Anonymous Monk]

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;
...

[dragonchild]

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

That would be for the ++ scenario.

[ikegami]

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

[Anonymous Monk]

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.

[dragonchild]

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
);

Then, to get everything in a single query, you do:

SELECT * FROM images WHERE ( (id=?) OR (next_id=?) OR (prev_id=?) );

Not that hard. :-)

[parv]

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

... 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.

[igelkott]

Sounds like you want a Database Cursor. Some DB software makes this easy and some require something manual. What DB engine do you have?

[ides]

I usually use DBIx::Class and it's paging ability via the Data::Page module. It makes this sort of thing very easy.