Next/Previous Image by user id and photo_id

Question

I have created an next and prev image link which works great. But I'd like them both to loop back to the beginning or the end when the user hits their last uploaded image in their given album.

So I've clicked into my created album which has an album id of '22' and I have my uploaded images - image101, image 202 and image243 uploaded inside, once I hit image243. I'd like it to return to the albums first image image101.

I'd like to do the same with previous, when I hit Image101 I'd like the link to go back to image243. (Self explanitory).

Each User has their own albums which they create with their uploaded images inside with various photo_id's dependant on how many images the given user has uploaded. So its not in a 1,2,3,4 order. If I've just uploaded a new image to the website, it will create a photo_id of 244 as that is the next available photo_id and if another user in another account uploads an image straight after me under their account, their photo will have a photo_id of 245. The below code is what I have so far, and the next and prev jumps to the next users uploaded image missing out any uploaded images from other user accounts. I just need the next and previous not to hit a blank page after the last image.

I hope I've explained this clearly without giving anyone a headache. Happy to answer any questions if I can get abit of help/advise or guidence.

Thanks

Code used for selecting next/prev user image within the users set album

 <?php if(isset($_GET['pid'])){ ?>
<?php
//Now we'll get the list of the specified users photos

$sql = "SELECT * FROM userphotos WHERE photo_id=".$_GET['pid'];
$query = mysql_query($sql)or die(mysql_error());
while($photo = mysql_fetch_array($query)){
$user=rawfeeds_user_core::getuser($photo['photo_ownerid']);
?>
<p class="frontpage_description"><a href="profile.php?username=<?php echo $user['username']; ?>">
<?php 
$id=$_SESSION['id'];
if($id==$_SESSION['id']){
echo "<img border=\"0\" src=\"imgs/cropped".$id.".jpg\" onerror='this.src=\"img/no_profile_img.jpeg\"' width=\"40\" >";
}
            ?>

<?php echo $user['fullusersname'];?></a></p>
<?php
//Now we'll get the list of the specified users photos

    $sql = "SELECT id FROM albums WHERE user_id=".$user['id']." ORDER BY name ASC LIMIT 1 ";
    $query = mysql_query($sql)or die(mysql_error());


while($album = mysql_fetch_array($query)){ ?>

<?
$photo_sql = "SELECT photo_id FROM userphotos WHERE photo_ownerid = ".$user['id']." AND album_id=".$album['id']." ORDER BY photo_id ASC";
$photo_result = mysql_query($photo_sql) or die(mysql_error());
while($row=mysql_fetch_array($photo_result)) {
   $photos[]=$row[0];
}
$total = mysql_num_rows($photo_result);
print_r($photos);
// Testing example:
// $photos = array(200, 202, 206, 211);
// $total = count($photos);

$current = $_GET['pid']; // whatever your position is in the photo array

if($current > ($total-1) || $current < 0) {
    echo "Error: nonexistent photo ID.";
    exit;
}
echo "<div class='photo_captionholder'><b>Name</b> : ".$photo['photo_name']." | <b>Caption</b> : ".$photo['photo_caption']."</div>";
    echo "<img class='viewer_image' alt='".$photo['photo_name']."' width='60%'  src='";
    echo "include/media.userimage.php?pid=".$photos[$current];
    echo "'\">"; // display current photo

$next = ($current+1) % $total; // modulo
$prev = (($current-1) < 0) ? $total-1 : $current -1;

echo "<a href='photo.php?pid=".$next."'>Next</a>";
echo " | <a href='photo.php?pid=".$prev."'>Prev</a>";
?>
<?php   
}}}
?>

Answer 1

Explanation:

Your "next" query gets records after the current image. (ie record 3 will get 4,5,6)

Your code gets first record from that result. (ie 4)

Adding a UNION query to the PREVIOUS records would then put that record at the end of the query results. (ie 4,5,6, 1,2) NOTE: Ignoring the LIMIT for now

If NO records are returned from the first SELECT then the first record will actually be at the top of the results (ie for record 6 next is empty followed by 1,2 so next record is 1)

NEXT

$photo_sql = "(SELECT photo_id FROM userphotos WHERE photo_id > ".$_GET['pid']." AND photo_ownerid = ".$user['id']." AND album_id=".$album['id']." ORDER BY photo_id ASC LIMIT 1)";
$photo_sql.= " UNION (SELECT photo_id FROM userphotos WHERE photo_id < ".$_GET['pid']." AND photo_ownerid = ".$user['id']." AND album_id=".$album['id']." ORDER BY photo_id ASC LIMIT 1)";

PREVIOUS

$photo_sql = "(SELECT photo_id FROM userphotos WHERE photo_id < ".$_GET['pid']." AND photo_ownerid = ".$user['id']." AND album_id=".$album['id']." ORDER BY photo_id DESC LIMIT 1)";
$photo_sql.= " UNION (SELECT photo_id FROM userphotos WHERE photo_id > ".$_GET['pid']." AND photo_ownerid = ".$user['id']." AND album_id=".$album['id']." ORDER BY photo_id DESC LIMIT 1)";

I've added the second query to the first .= and reversed the photo_id checking > becomes <

Answer 2

Well for this purpose i wrote a query. See if you can change according to your requirements

select
  users_id as ID,
  (select  users_id from users where users_id > ID limit 1) as NextID,
  (select  users_id from users where users_id < ID ORDER BY users_id DESC limit 1) AS PreviousID
from users
where users_id = 1

Answer 3

It is not recommended to ask the same question twice, if nothing has changed in your situation. You have documented your question better here, however, so here is a clearer answer for you, with an example, and showing how you have to handle it:

$photo_sql = "SELECT photo_id FROM userphotos WHERE photo_ownerid = ".$user['id']." AND album_id=".$album['id']." ORDER BY photo_id ASC"
$photo_result = mysql_query($photo_sql) or die(mysql_error());
while($row=mysql_fetch_array($photo_result)) {
   $photos[]=$row[0];
}
$total = mysql_num_rows($photo_result);

// Testing example:
// $photos = array(200, 202, 206, 211);
// $total = count($photos);

$current = $_GET['pid']; // whatever your position is in the photo array

if($current > ($total-1) || $current < 0) {
    echo "Error: nonexistent photo ID.";
    exit;
}

echo '<img src="images/'.$photos[$current].'.png" alt="my image!" />'; // display current photo

$next = ($current+1) % $total; // modulo
$prev = (($current-1) < 0) ? $total-1 : $current -1;

echo "<a href='photo.php?pid=".$next."'>Next</a>";
echo " | <a href='photo.php?pid=".$prev."'>Prev</a>";