SQL: Find next row in a where clause given an ID

Question

I have a table called products in a MySQL database. products looks some what like this:

id   name     din        strength active deleted
1    APA TEST 00246374   25       1      0
4    APA BOB  00246375   50       1      0
5    APA TIRE 00246888   50       1      0
7    APA ROT  00521414   100      1      0
9    APA APA  01142124   100      1      0
6    APA CODE 00121212   150      1      0
8    APA SERV 00011145   600      1      0

Obviously I've left out several columns not important to my question. When I query this table, I will be sorting by one of several different columns (The user interface allows individual users to change the sorting column and order), and I may have a search clause in which case I'll do a LIKE clause on NAME and DIN.

What I want to know is, given the sorting info and search info, and the ID of a specific product (Say I searched for 004, which returned 3 results, and I am viewing one of them), how could I get the next and previous products?

I need to do this, because if a user clicks to edit/view one of the products after searching and sorting results, they want to be able to cycle through results without going to the previous page.

Is there a good and efficient way to do this in SQL, or am I best off using PHP? Any ideas are also welcome.

Currently using this SQL query, which is experiencing issues if I sort by the strength column as there are duplicate values

SELECT T.*
FROM `wp_products` T
INNER JOIN `wp_products` curr on curr.id = 38
   AND ((T.strength = curr.strength and T.id < curr.id)
    OR (T.strength > curr.strength))
WHERE T.active = 1 AND T.deleted = 0 AND (T.name LIKE '%%' OR T.din LIKE '%%')
ORDER BY T.strength ASC, T.id ASC
LIMIT 1

My PHP code (using WordPress) (Designed to get the next item)

    $sql = 'SELECT T.*
FROM `' . $wpdb->prefix . 'apsi_products` T
INNER JOIN `' . $wpdb->prefix . 'apsi_products` curr on curr.id = ' . $item->id . '
   AND ((T.' . $wpdb->escape( $query['orderby'] ) . ' = curr.' . $wpdb->escape( $query['orderby'] ) . ' and T.id > curr.id)
    OR (T.' . $wpdb->escape( $query['orderby'] ) . ' > curr.' . $wpdb->escape( $query['orderby'] ) . '))
WHERE T.active = 1 AND T.deleted = 0 AND (T.name LIKE \'%' . $query['where'] . '%\' OR T.din LIKE \'%' . $query['where'] . '%\')
ORDER BY T.' . $wpdb->escape( $query['orderby'] ) . ' ' . $query['order'] . ', T.id ASC
LIMIT 1;';

Answer 1

You need to have a reference to the current record, and then progressively look for the next record based on the sorted columns. The example below assumes it is sorted on

ORDER BY Active, DIN, NAME

First:

SELECT *
FROM TABLE
WHERE NAME LIKE '%X%' AND DIN LIKE '%%'
ORDER BY Active, DIN, Name
LIMIT 1;

Next: (make sure you separate the CURR.ID = 6 and the AND-ORs with proper brackets!)

SELECT *
FROM TABLE T
INNER JOIN TABLE CURR ON CURR.ID = 6 # the current ID being viewed
   AND ((T.Active = Curr.Active AND T.DIN = Curr.DIN AND T.NAME > Curr.Name)
     OR (T.Active = Curr.Active AND T.DIN > Curr.DIN)
     OR T.Active > Curr.Active)
WHERE T.NAME LIKE '%X%' AND T.DIN LIKE '%%'
ORDER BY T.Active, T.DIN, T.Name
LIMIT 1;

A working sample presented below

create table products
(ID int, SEED int, NAME varchar(20), DIN varchar(10), ACTIVE int, DELETED int);
insert products values
(1,  0,    'Product #1', '004812', 1,    0),
(2,  0,    'Product #2', '004942', 0,    0),
(3,  0,    'Product #3', '004966', 1,    0),
(4,  0,    'Product #4', '007437', 1,    1),
(5,  2,    'Product #2', '004944', 0,    0),
(6,  2,    'Product #2', '004944', 1,    0);

SELECT *
FROM products
WHERE active = 1 AND deleted = 0
ORDER BY din DESC, ID desc;

Output:
"ID";"SEED";"NAME";"DIN";"ACTIVE";"DELETED"
"3";"0";"Product #3";"004966";"1";"0"
"6";"2";"Product #2";"004944";"1";"0"
"1";"0";"Product #1";"004812";"1";"0"

If current is the row with ID=6, the next record can be retrieved using

SELECT T.*
FROM products T
INNER JOIN products curr on curr.ID = 6
   AND ((T.din = curr.din and T.ID > curr.ID)
    OR (T.din < curr.din))
WHERE T.active = 1 AND T.deleted = 0
ORDER BY T.din DESC, T.ID ASC
LIMIT 1;

Answer 2

Without a persistence cache layer like memcached, where you can store the results and get it without reissuing the query, a simple solution can be enabling the query cache in mysql. In this way, if the cache isn't invalidated from other querys, when you reissue the query the cost of pulling the result will be lowered