Get next row

[therealwesfoster]

I have links that allow you to view info from different rows.

 

Setup like this:

[PREV ROW]

Current row info

[NEXT ROW]

 

My question is, how do I link the prev/next buttons to the next row's id? (page.php?id=)

 

They are in an auto-increment field, but alot of rows have been deleted, so using a simple current_id+1 will not work.

 

How do i do this?

[themistral]

You could do 2 queries.

 

1 will be to find records less than current row id, order by id desc and limit 1

1 will be to find records greater than current row id, order by id asc and limit 1

 

Someone else may have a better solution though!

[therealwesfoster]

Nice I'll use that until I find another solution.

 

So please continue to post solutions

[The Little Guy]

You could Give this a try (may need to rename the variables, and table name though):

 

<?php
include 'db.php';
$currentID = $_GET['id'];
$sql = mysql_query("(SELECT id FROM snippets WHERE id = '$currentID' LIMIT 1)
UNION
(SELECT id FROM snippets WHERE id < '$currentID' ORDER BY id DESC LIMIT 1)
UNION
(SELECT id FROM snippets WHERE id > '$currentID' LIMIT 1)");
$i = 0;
while($row = mysql_fetch_array($sql)){
if($i == 1)
	echo 'Prev: '.$row['id'].'<br>';
if($i == 0)
	echo 'Curr: '.$row['id'].'<br>';
if($i == 2)
	echo 'Next: '.$row['id'].'<br>';
$i++;
}
?>

[therealwesfoster]

You could Give this a try (may need to rename the variables, and table name though):

 

<?php
include 'db.php';
$currentID = $_GET['id'];
$sql = mysql_query("(SELECT id FROM snippets WHERE id = '$currentID' LIMIT 1)
UNION
(SELECT id FROM snippets WHERE id < '$currentID' ORDER BY id DESC LIMIT 1)
UNION
(SELECT id FROM snippets WHERE id > '$currentID' LIMIT 1)");
$i = 0;
while($row = mysql_fetch_array($sql)){
if($i == 1)
	echo 'Prev: '.$row['id'].'<br>';
if($i == 0)
	echo 'Curr: '.$row['id'].'<br>';
if($i == 2)
	echo 'Next: '.$row['id'].'<br>';
$i++;
}
?>

 

Fancy.. I'll let you know how that goes later today