PHP mysql - Creating next and previous link (error with my solution)

This is a sequel of another question i asked: PHP/SQL creating links to next row in a database

I foun开发者_运维百科d this solution to make NEXT and PREVIOUS links. Now i need to tranform it into some useful PHP code.

So heres is the solution:

SELECT id 
  FROM database AS c 
 WHERE (id = (SELECT MAX(id) FROM database WHERE id < c.id AND language = 'en')
    OR  id = (SELECT MIN(id) FROM database WHERE id > c.id AND language = 'en'))

How do i make a next and a previous link from this?

What i tried:

$result = mysql_query("
SELECT id 
  FROM database AS c 
 WHERE (id = (SELECT MAX(id) FROM database WHERE id < c.id AND language = 'en')
    OR  id = (SELECT MIN(id) FROM database WHERE id > c.id AND language = 'en'))
");
while($row = mysql_fetch_array($result))
  {
   $nextlink = "<a href='http://www.domain.com/".$row['c.id'].".html'>Next</a>";
   echo $nextlink;
}

This shows nothing. Another problem is that i also need to extract another colum_name called permalink, so the next link can be complete.

Consider me as a PHP newbie, so you really have to make it clear. Thank you

Edit: i just realized that the current ID is not included anywhere in the solution?

I believe you want to look at something more like this:

SELECT c.id,
  (SELECT MAX(p.id) FROM database p WHERE p.id < c.id AND p.language = 'en') prev_id
  (SELECT MIN(n.id) FROM database n WHERE n.id > c.id AND n.language = 'en') next_id
FROM database AS c 
WHERE c.id = [current position]

So, your SQL will just address a single row:

while($row = mysql_fetch_array($result))
{
   $prevlink = "<a href='http://www.domain.com/".$row['c.prev_id'].".html'>Previous</a>";
   $nextlink = "<a href='http://www.domain.com/".$row['c.nexT_id'].".html'>Next</a>";
   echo $nextlink;
}

You need to add the current ID to the SQL statement

$sql = sprintf( "
SELECT id 
  FROM database AS c 
 WHERE (id = (SELECT MAX(id) FROM database WHERE id < %1$s AND language = 'en')
    OR  id = (SELECT MIN(id) FROM database WHERE id > %1$s AND language = 'en'))
", $current_id );