Finding NEXT & PREVIOUS ID's in MySQL Results

[codeline]

I've got a page that displays all my blog entries on a single page. When you click on a specific entry, it pulls the post ID and loads the page specifically. From there, I wanted to have the option to click either onto the next or previous post by finding the next or previous ID in the database.. however, I know that sometimes posts are deleted, etc. so the ID's won't exactly be in order.

 

What direction should I take with this?

[Pikachu2000]

Timestamps, perhaps?

[PFMaBiSmAd]

You would find the next ID that is either greater-than or less-than the current id, LIMIT 1

[codeline]

I've got something like this.. however, the varible $next is returning "Resource id #8"..

 

$next = mysql_query("SELECT * FROM submissions WHERE id>'$curid' ORDER BY id DESC limit 1");

[Pikachu2000]

$query = "SELECT * FROM submissions WHERE id>'$curid' ORDER BY id DESC limit 1";

$next = mysql_query($query) or die( '<br />Query string ' . $query . '<br />Produced error: ' . mysql_error() . '<br />');

[codeline]

and i would also have to change ORDER BY to ASC for finding the next id correct? DESC for previous?

[Pikachu2000]

You'd need to change the comparison operator from greater than to less than for the previous record.

[codeline]

This is what I have.. a bit sloppy but it works! I did have the greater than AND less than for next and previous, but for some reason the lowest ID was sending me to the HIGHEST id when I was calling for the next one. I eventually changed DESC to ASC in the next query and it worked.

 

Any other suggestions on this? Maybe something I should avoid doing?

 

// variables for next submission link
	$next = mysql_query("SELECT * FROM submissions WHERE id>'$curid' ORDER BY id ASC limit 1");
	$nextr = mysql_fetch_array($next);
	$nextcount = mysql_num_rows($next);
	$nextid = $nextr['id'];

	// variables for previous submission link
	$prev = mysql_query("SELECT * FROM submissions WHERE id<'$curid' ORDER BY id DESC limit 1");
	$prevr = mysql_fetch_array($prev);
	$prevcount = mysql_num_rows($prev);
	$previd = $prevr['id'];