George Andreev George Andreev - 1 year ago 78
MySQL Question

Why is my query always returning the path of the first image in my table?

I have an index page on which all images uploaded on the database are displayed. When you click a certain image, you're sent to a page that contains all the information about that image. Now I am trying to add a Previous/Next button on that information page that will send you to the Previous/Next image in the database. As you can see first I take the path of the image from the URL, then based on the path I get the Id and then I try to get the path of the previous/next image using the id of the current image. My next image button works, however, my previous button always returns the path of the first image in my database. So clicking next->next->next runs through all my images in the right order, however when I click previous on any image, I get sent to the first image. Any ideas why?

<?php

$realImagePath = $_GET['image'];
$sql = "SELECT * FROM image WHERE path='$realImagePath'";
$result = mysqli_query($conn, $sql);
$getResult = mysqli_fetch_assoc($result);

$currentId = $getResult['id'];

$sql4 = "SELECT * FROM image WHERE id > $currentId LIMIT 1";
$result4 = mysqli_query($conn, $sql4);
$getResult4 = mysqli_fetch_assoc($result4);
$nextImagePath = $getResult4['path'];

$sql5 = "SELECT * FROM image WHERE id < $currentId LIMIT 1";
$result5 = mysqli_query($conn, $sql5);
$getResult5 = mysqli_fetch_assoc($result5);
$previousImagePath = $getResult5['path'];

echo "<div class='imageContainer'>"
."<h1>".$getResult['name'].'</h1>'
.'<img class="uploadedRealImg" src="uploads/'.$realImagePath .'" alt="Random image" />'."<br><br>"
."<a href='imageInfo.php?image=".$previousImagePath."'>Previous</a>"
."<a href='imageInfo.php?image=".$nextImagePath."'>Next</a><br>"
.$getResult['description']."<br><br>"
.$getResult['date']."<br><br>"
.$getResult['author']."<br><br></div>";
?>

Answer Source

The first result for SELECT * FROM image WHERE id < $currentId LIMIT 1 is always going to be the first element in the db. This is your problem. Try this:

SELECT * FROM image WHERE id < $currentId ORDER BY id DESC LIMIT 1

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download