Prev, Current, Next - Fancy Query

[paulkissick]

Is it possible to construct a query which returns the rows before and after a certain row, identified with a specific ID, given a certain sort order.

Any help is appreciated,

Paul

[leatherback]

yes it is

[paulkissick]

Explanation or example code would be nice!

;-)

[leatherback]

Use a function.

Find the logic here (I actually found some small errors today, so check it before ising. e.g.: The returned colum nis fixed and stuff):

function getrecordid($table, $ordercol, $position, $thisid, $idcol, $limitclause)
  {
  switch($position)
    {
    case 0:
    // Next record
    $query = "select * from $table where ($ordercol > $thisid) $limitclause order by $ordercol asc limit 1";
    break;

case 1:
// Previous record
$query = "select * from $table where ($ordercol < $thisid) $limitclause order by $ordercol desc limit 1";
break;

case 2:
// First record
$limitclause2 = str_replace("and", "where", $limitclause);
$query = "select * from $table order by $ordercol asc limit 1";
break;

case 3:
// Last record
$limitclause2 = str_replace("and", "where", $limitclause);
$query = "select * from $table order by $ordercol desc limit 1";
break;
}


$result = mysql_query($query);
  while ( $row = mysql_fetch_array($result))
   {
   $id = $row[0];
   }

return $id;

}

[paulkissick]

Much appreciated.

Not the all-in-one query i was hoping, but useful all the same.

Thanks