Getting the next and the previous record in mysql

Question

I am querying a mysql database table by first creating a temporary table and filling it with data from a select statement.

To test it,i am using a known database id and test if it gets the next and previous records correctly.

This are my functions

function get_next($id){
    $db = new PDO('mysql:host=localhost;dbname=world', 'root', '');
    $db->query("CREATE TEMPORARY TABLE nr AS SELECT ID,Name,Population FROM city WHERE id > $id ORDER BY ID LIMIT 1;");
    $orm = $db->query("SELECT * FROM nr WHERE ID > $id ORDER BY ID LIMIT 1;");
    $id = $orm->fetchColumn(0);
    if ($id !== false) {
        return $id;
    }
}
function get_previous($id){
    $db = new PDO('mysql:host=localhost;dbname=world', 'root', '');
    $db->query("CREATE TEMPORARY TABLE pr AS SELECT ID,Name,Population FROM city WHERE ID > $id ORDER BY ID LIMIT 1;");
    $orm = $db->query("SELECT * FROM pr WHERE ID < $id ORDER BY ID DESC LIMIT 1;");
    $id = $orm->fetchColumn(0);
    if ($id !== false) {
        return $id;
    }
}

I can get the next record correctly but i get previous record wrong as it says the previous record is the current record.

Am i doing the previous query wrong?.

No comment on the rest of the question, but you should use bound parameters. — Daedalus, Jul 07 '13 at 07:39
get_previous - Where id < curent_id. Check record exist get_next - Where id > curent_id. Check record exist — d.danailov, Jul 07 '13 at 07:39

score 1 · Answer 1 · answered Jul 07 '13 at 07:42

1

Your temporary query is looking for IDs greater than your ID, so the next query won't find anything less than that.

answered Jul 07 '13 at 07:42

rybo111

12,240
4
61
70

score 1 · Answer 2 · answered Jul 07 '13 at 07:45

1

You need to remove the DESC from your get_previous function.Replace this line with mine:

$orm = $db->query("SELECT * FROM pr WHERE ID < $id ORDER BY ID LIMIT 1;");

answered Jul 07 '13 at 07:45

Moeed Farooqui

3,604
1
18
23

This worked `$db->query("CREATE TEMPORARY TABLE pr AS SELECT * FROM city"); $orm = $db->query("SELECT * FROM pr WHERE ID < $id ORDER BY ID DESC LIMIT 1;");` – You Know Nothing Jon Snow Jul 07 '13 at 07:48
yeah the same thing in `create table query` i.e: `WHERE ID < $id` – Moeed Farooqui Jul 07 '13 at 07:51

score 0 · Answer 3 · answered Jul 07 '13 at 08:00

0

I would not use a temp table, and since you're only interested in the id you don't need to retrieve other information

# To retrieve the previous id:
SELECT MAX(id) FROM city WHERE id < $id
# alternative:
SELECT id FROM city WHERE id < $id ORDER BY id DESC LIMIT 1

# To retrieve the next id:
SELECT MIN(id) FROM city WHERE id > $id
# alternative:
SELECT id FROM city WHERE id > $id ORDER BY id ASC LIMIT 1

answered Jul 07 '13 at 08:00

Arjan

9,784
1
31
41

Getting the next/prev records is easy on a an ordinary table.The problem comes in when i want to get the next and previous records in a dataset from a complicated select statement. – You Know Nothing Jon Snow Jul 07 '13 at 08:07
In that case you just need a more complex query. I don't see why you should use a temp table for that, even more so because you already have your answer when you populate the temp table. – Arjan Jul 07 '13 at 08:49
You are right.I did away with the temp table and it still works.Maybe you confirm this.When i do a select,the select data will be held in a container similar to a table and thus no need to place the data in a temp table?. – You Know Nothing Jon Snow Jul 07 '13 at 08:56

score 0 · Answer 4 · answered Jul 07 '13 at 08:15

0

Previus row

SELECT * FROM table WHERE id < 1 ORDER BY id DESC LIMIT 1;

Next row

SELECT * FROM table WHERE id > 1 ORDER BY id ASC LIMIT 1;

I hope this helps...

answered Jul 07 '13 at 08:15

Getting the next and the previous record in mysql

4 Answers4