I had considered that, but the problem is that the img_id's are sequential in the database -- not the album. In other words, if I wanted img_id #43, I could not just say "give me img_id #42, #43 and #44" - because #42 and #44 could possibly belong to another album_id for the same user - or another album_id for a completely different user.

Here is an example of the table:



img_id | album_id | uid |     name     | ext  
-------+----------+-----+--------------+------
    38 |      14  |  89 | mypetrat     | jpg  
    39 |      14  |  89 | mypetlemur   | jpg  
    40 |       2  |  12 | vacation_01  | jpg  
    41 |      14  |  89 | mypetvulture | jpg  
    42 |       2  |  12 | vacation_02  | jpg  
    43 |       2  |  12 | vacation_03  | jpg  
    44 |      14  |  89 | mypetcow     | jpg
[download]

What database? if Mysql you should be able to use a more generic query that limits to the user and album and then use it's INDEX statement to say only the Item N -> N+2.


-Waswas

I can't figure out how to pull the target record, plus the one before and after it in a single SQL query. I suppose that can't be done.

 select max(P.img_id), T.img_id, min(N.img_id)
 from images T, images P, images N
 where P.img_id(+) < T.img_id and P.uid(+) = T.uid and P.album_id(+) =
+ T.album_id
   and N.img_id(+) > T.img_id and N.uid(+) = T.uid and N.album_id(+) =
+ T.album_id
   and T.img_id = ? and T.uid = ? and T.album_id = ?
 group by T.img_id
[download]

my ($img_id) = $str =~ m/img_id=(\d+)/;
my $prev_id = $img_id - 1;
my $next_id = $img_id + 1;
[download]

"select WHATEVER from TABLE where img_id in ($prev_id, $img_id, $next_
+id)"
[download]

The problem is that this application will serve infinite users with infinite albums, so while img_id is unique and sequential, it doesn't gaurantee that any two images belong to the same person or album.

Ok, this may or may not be a good solution, depending you whether or not you want to mess with your database design, but it will work.

Somewhere in the table put another column for a sequence number. So, instead of


img_id | album_id | uid |     name     | ext  
-------+----------+-----+--------------+------
    38 |      14  |  89 | mypetrat     | jpg  
    39 |      14  |  89 | mypetlemur   | jpg  
    40 |       2  |  12 | vacation_01  | jpg  
    41 |      14  |  89 | mypetvulture | jpg  
    42 |       2  |  12 | vacation_02  | jpg  
    43 |       2  |  12 | vacation_03  | jpg  
    44 |      14  |  89 | mypetcow     | jpg
[download]

you have something like


img_id | album_id | seq_id | uid |     name     | ext  
-------+----------+--------+-----+--------------+------
    38 |      14  |      1 |  89 | mypetrat     | jpg  
    39 |      14  |      2 |  89 | mypetlemur   | jpg  
    40 |       2  |      1 |  12 | vacation_01  | jpg  
    41 |      14  |      3 |  89 | mypetvulture | jpg  
    42 |       2  |      2 |  12 | vacation_02  | jpg  
    43 |       2  |      3 |  12 | vacation_03  | jpg  
    44 |      14  |      4 |  89 | mypetcow     | jpg
[download]

You could then grab the seq_id to the corresponding img_id

select seq_id from TABLE where img_id = $img_id
[download]

Then 1) calculate prev and next seq_id and 2) grab the img_ids you want be referencing the seq_id

my $p_seq_id = $seq_id - 1;
my $n_seq_id = $seq_id + 1;

"select img_id from TABLE where seq_id in ($p_seq_id, $seq_id, $n_seq_
+id)"
[download]

You could easily keep the current album seq_id value in another table and reference it when adding new images. As I said, it may be a hassle modifying your database, but it may be worthwhile in the long run as it more easily does what you want than the other options.

Note: This may be a good "teachable moment" as educators like to call them.

Perhaps the most important thing in designing an application that uses a database is to know thoroughly what you want from the application before you even begin designing the database. Too often, people design a database based on a less than thorough examination of the application requirements and end up having to redo it (the database.) :)

davidj

# Elsewhere in the code, we get an img_id, do a SELECT to find what
# album_id it belongs to, then use the SELECT below to get all of
# the img_id's that belong to that album. We use flattenArray() to 
# turn the arrayref from DBI into a plain old array.
my @image_idx = flattenArray( @{$dbh->selectall_arrayref("SELECT img_i
+d FROM images WHERE album_id = $image_id")});

# Find what place our target img_id is in the array.
my $idx_loc = indexArray($img_id, @image_idx);

# Get img_id's from array that come before and after the target.
my $prev_img = $image_idx[$idx_loc - 1];
my $next_img = $image_idx[$idx_loc + 1];

# Thanks to merlyn, tilly and particle
# http://perlmonks.org/index.pl?node_id=151120
sub flattenArray {
  my @flattened; # Will be in reverse order
  while (@_) {
    my $last = pop;
    if (UNIVERSAL::isa($last, "ARRAY")) { push @_, @$last; }
    else { push @flattened, $last; }
  }
  return reverse @flattened;
}

# My apologies, but I took this from a golf thread on PM. I've lost
# the node number and apologize to the author. Please let me know
# so I can credit you for this.
sub indexArray(@) {        
    my $s=shift;
    $_ eq $s && return @_ while $_=pop;
    -1;
}
[download]

Your solution is quite general, but I don't like the idea of retrieving all the records every time I need to build next and previous links in a page; why don't you use the LIMIT/OFFSET clause available under PostgreSQL and MySQL?

Ciao, Valerio

what would be the quickest/shortest way to turn the $album_idx = $dbh->selectall_arrayref() into just an array?

@album_idx = @{$dbh->selectall_arrayref()}

Update: also, you may put album_id in the url, and eliminate the first SQL query