PHP & MySQL : Obtaining next and previous records

Hi,

I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query?

example: If I have ID 3, I want to find out if ID 2 and 4 exists, and then put them into a variable..

Hope I've explained ok.

 

select id, fields from table where (id = current_id -1 ) or (id = current_id +1)

Is it that simple or do I miss something?

 

what i would run a query to find the previous record id. Then - run a select to pull three records order by current_id where current_id >= your previously found record. then you have all three in 1 rs.

 

I found the solution using an array:

$query = "SELECT id FROM <table> ORDER BY date DESC";
		$result = mysql_query($query) or die("Unable to read table : $query");
		$list = array();
		while ($image = mysql_fetch_assoc($result)) {
			$list[] = $image["id"];
		}

		$current_item = array_search($id1,$list);
		$prev_link = $next_link = "";

		if ($current_item > 0) {
                  // do previous link with $list[$current_item - 1]
                }
		if ($current_item < sizeof($list)-1)
                  // do next link with $list[$current_item + 1]
                }

Code (markup):

TY

 

the problem with that solutions is that is listing all results in array
use something like:

SELECT id as prev FROM table WHERE id < 'id_value' ORDER by id DESC LIMIT 1;
and
SELECT id as next FROM table WHERE id > 'id_value' ORDER by id ASC LIMIT 1;

Code (markup):

this should return next and prev "id"