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

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.