query sql database to echo highest auto incremented number

Question

Im trying to make an sql function that displays the highest value however all the variations of the MAX function i use, still turn up with an empty image. What's going on here? how do i fix this?

//displays no image and doesn't give any errors

$result = mysql_query("SELECT MAX(id) AS id FROM people") or die (mysql_error());

//displays image 87

$result = mysql_query("SELECT  * FROM people WHERE id = 87") or die (mysql_error());

Answer 1

It shouldn't display an image, it should give a result of 87.

See MAX

You could do:

select * from people order by id desc limit 0, 1 

which should give you the latest image by ID.

To make this dynamic and allow a next button you would need to store the value of image being viewed. When the next button is clicked you could then do

select * from people order by id desc limit 1, 1 //Start at row 1, bring back 1.

See MySQL Limit

You would need to use PHP to assign the values in limit and your next link though. To do this you would need to have link like so:

<a href="www.mysite.com/page?imagecount=1">Next</a>

Then using PHP you could:

<?php
    if (isset($_GET["imagecount"]))
        $next = (int)$_GET["imagecount"]; //Don't forget the (int) cast to avoid SQL injection!!!
    else
        $next = 0;

   $result = mysql_query("select * from people order by id desc limit $next, 1") or die(mysql_error());
?>

TO expand on the link, you could then make your link dynamic:

<a href="www.mysite.com/page?imagecount=<?php echo $next+1; ?>">Next</a>