在不知道当前位置的情况下查找上一个/下一个ID（PHP和MySQL）

Question

This is my first time posting here so please bear with me.

I'm creating an image gallery using HTML & CSS, where I show one image on the screen and it has a prev/next button to toggle through the images (at no point does this take you to a new webpage). I'm using PHP & MySQL to populate the images.

So, I have a table like this:

    | ID | Name       | Genre      |
    --------------------------------
    | 1  | Apple      | Fruit      |
    --------------------------------
    | 2  | Cabbage    | Veg        |
    --------------------------------
    | 3  | Lettuce    | Veg        |
    --------------------------------
    | 4  | Pear       | Fruit      |
    --------------------------------
    | 5  | Banana     | Fruit      |
    --------------------------------
    | 6  | Leek       | Veg        |
    --------------------------------
    | 7  | Kiwi       | Fruit      |

I want to only images where genre=fruit, so now my table is like the following:

    | ID | Name       | Genre      |
    --------------------------------
    | 1  | Apple      | Fruit      |
    --------------------------------
    | 4  | Pear       | Fruit      |
    --------------------------------
    | 5  | Banana     | Fruit      |
    --------------------------------
    | 7  | Kiwi       | Fruit      |

Since the IDs are no longer incremental, I can no longer simply use $id-1 or $id+1 to get the prev/next photos.

This is what my PHP code looks like (simplified for this post):

 <?php
 $sql = "SELECT * FROM photo WHERE genre LIKE '%fruit%' ORDER BY id DESC";
 $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

 while ($rows = mysql_fetch_assoc($result)) {
     $id=$rows['id'];
     $name=$rows['name'];
     $genre=$rows['genre'];

     echo   "<div id=\"img$id\">".
            "<a href=\"#img".($id+1)."\">&lt;</a>".
            "<a href=\"#img".($id-1)."\">&gt;</a>".
            "<img src=\"img/full/$name.jpg\">".
            "</div>";
 } // end while 
 ?>

Since I'm loading all of the fruit photos, I don't know what is the current ID and I won't know how many rows there will be displayed in total. I'd like to find the ID of the row item that is before and after the current row/photo the user sees.

I've tried using current(), next(), and prev() but they all seem to select the first 3 rows only. Any help would be so greatly appreciated! Been researching this all day and haven't been able to solve this :( Thanks so much in advance.

Answer 1

Here's a sample code with comments:

$images = [];
while ($rows = mysql_fetch_assoc($result)) {
    // first we store images in an array
    $images[] = [
        'id' => $rows['id'],
        'name' => $rows['name'],
        'genre' => $rows['genre'],
    ];
}

// next we iterate over this array
foreach ($images as $index => $image) {
    echo '<div id="img' . $image['id'] . '">';
    // as array is numerically indexed 
    // we can get previous item's index as `$index - 1`
    // and check if it is set
    if (isset($images[$index - 1])) {
        // if it is set - we output it
        echo '<a href="#img' . $images[$index - 1]['id'] .'">&lt;</a>';
    }
    // as array is numerically indexed 
    // we can get next item's index as `$index + 1`
    // and check if it is set
    if (isset($images[$index + 1])) {
        // if it is set - we output it
        echo '<a href="#img' . $images[$index + 1]['id'] .'">&gt;</a>';
    }
    echo '<img src="img/full/' . $image['name'] . '.jpg">';
    echo '</div>';
}