How to get previous and next entries in mysql query result

philosophology

I am querying a table and getting the result; the ID's would be:
1, 3, 6, 8, 65, 345, 566, ...
I want to put links on the page that shows the next and previous ID. How do I do that?

laserlight

Using :id as a placeholder for the current id, you can use something like this to retrieve the previous id:

SELECT id FROM tablename WHERE id<:id ORDER BY id DESC LIMIT 1

then something like this to retrieve the next id:

SELECT id FROM tablename WHERE id>:id ORDER BY id LIMIT 1

philosophology

ok, I'm a bit confused, I've never used :id before!

Here is what I have:

SELECT * FROM tblTABLE WHERE A='1' AND B='1' LIMIT 1

This gives me the first entry that A='1' and B='1'

How do I get the next entry? and the previous?

laserlight

philosophology wrote:
ok, I'm a bit confused, I've never used :id before!

It is just a placeholder, and it happens to be an actual valid placeholder if you use prepared statements with the PDO extension. If not, just substitute a variable or value as necessary.

philosophology

philosophology;10906342 wrote:
ok, I'm a bit confused, I've never used :id before!

Here is what I have:

SELECT * FROM tblTABLE WHERE A='1' AND B='1' LIMIT 1

This gives me the first entry that A='1' and B='1'

How do I get the next entry? and the previous?

So can I say for 'next' :

SELECT FROM (SELECT FROM tblTABLE WHERE A='1' AND B='1' LIMIT 1) WHERE id>:id ORDER BY id LIMIT 1

laserlight

More like:

SELECT * FROM tblTABLE WHERE A='1' AND B='1' AND id>:id ORDER BY id LIMIT 1

philosophology

laserlight;10906348 wrote:
More like:
SELECT * FROM tblTABLE WHERE A='1' AND B='1' AND id>:id ORDER BY id LIMIT 1

I tried it and the moment the ':' is in there mysql_fetch_row throws and exception.

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource

laserlight

philosophology wrote:
I tried it and the moment the ':' is in there mysql_fetch_row throws and exception.

Since you are not using prepared statements, you need to actually substitute the placeholder with some value or variable, e.g.,

$id = 123;
$sql = "SELECT * FROM tblTABLE WHERE A='1' AND B='1' AND id>$id ORDER BY id LIMIT 1";
$result = mysql_query($sql);

That said, why not use the PDO extension?

philosophology

laserlight;10906464 wrote:
Since you are not using prepared statements, you need to actually substitute the placeholder with some value or variable, e.g.,
$id = 123;
$sql = "SELECT * FROM tblTABLE WHERE A='1' AND B='1' AND id>$id ORDER BY id LIMIT 1";
$result = mysql_query($sql);
That said, why not use the PDO extension?

What is PDO ext. and what does it do? Why does it help me here?

laserlight

philosophology wrote:
What is PDO ext. and what does it do?

It is a database extension that can be used as a consistent PHP interface to several database management systems: PDO extension.

philosophology wrote:
Why does it help me here?

You would have access to several features not available with the legacy MySQL extension, one of which is prepared statements, which help to avoid SQL injection, and can be more efficient if the same SQL statement is run in the same script but with different values.