MYSQL - Get Next and Previous Record by ID - HTML for hyperlinks

Question

So I'm trying to add a little bit of convenience to a CRUD by adding next and previous links to navigate between records in my database.

Here are my queries:

$id=$_GET['id'];
$id = $currentid;
$prevquery= "SELECT * FROM inventory WHERE id < $currentid ORDER BY id DESC LIMIT 1"; 
$prevresult = mysql_query($prevquery);

$nextquery= "SELECT * FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1"; 
$nextresult = mysql_query($nextquery);
?>

Here is my HTML:

<a href="http://www.url.com/crud/edit.php?id=<?php echo $prevresult; ?> ">Previous</a>

<a href="http://www.url.com/crud/edit.php?id=<?php echo $nextresult; ?> ">Next</a>

Now I tested these queries in PHPMyAdmin and they produced the result I wanted, but I can't get my hyperlinks to actually be supplied with the correct IDs... they're just blank after the =. What am I doing wrong?

Answer 1

mysql_query() returns a result set (resource). To get the actual rows from the result set, you need to use a function like mysql_fetch_row().

Your code for the "next" link would look something like:

PHP

$nextquery= "SELECT * FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1"; 
$nextresult = mysql_query($nextquery);
if(mysql_num_rows($nextresult) > 0)
{
    $nextrow = mysql_fetch_row($nextresult);
    $nextid  = $nextrow['id'];
}

HTML

<a href="http://www.url.com/crud/edit.php?id=<?php echo $nextid; ?> ">Next</a>

and the previous link would be done similarly.

Obligatory note: For new code, you should seriously consider using PDO.

Advanced note: You could combine your queries into a single query like:

SELECT
  (
    SELECT id
    FROM inventory WHERE id < $currentid ORDER BY id DESC LIMIT 1
  ) AS previd,
  (
    SELECT id
    FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1
  ) AS nextid

And then adjust the logic accordingly.