Next/Previous with MySQL

For one current project involving the presentation of a program for an arts festival, I needed a way to present ‘next’ and previous’ links within the results of a MySQL query. For example, the user might search for all artists who are sculptors and order the results by the artist’s last name. Ordinarily when building next/previous links I’m working without that level of sorting, and I ended up doing quite a bit of searching to find the best way to achieve this.

Of course I could load entire result sets into my program, and process them to find the results I need, but that could quickly eat memory, and I prefer whenever possible to do my processing within the database management system. What I really wanted to do was to return the initial search results with an extra column that represents the sequencing of the results, and then run a subsequent query to select particular results.

Eventually I found this posting in the MySQL forums which led me to the following pair of instructions:

DO @a := 0;
SELECT @a := @a + 1 AS sequence, *
FROM mytable
WHERE artist_type = 'clay'
ORDER BY artist_lastName;

That returns the sequence. Then, in order to get the result I’m looking for I need to do reset the increment and get my next/previous results. Say I have the third result already I’d issue:

DO @a := 0;
SELECT @a := @a + 1 AS sequence, *
FROM mytable
WHERE artist_type = 'clay'
ORDER BY artist_lastName
HAVING sequence = 2 OR sequence = 4;

For a low-demand application that will probably work, but for a higher-demand application there is a high likelihood that we will end up overwriting our sequence variables mid-query. In order to put this into production, therefore, we’ll probably want to come up with a way to generate our sequence variables’ labels (eg. ‘a’) in a way that avoids such conflicts.

Tags: ,

8 comments

  1. An easy and quick way in doing this is something like this:

    select * from mytable LIMIT 0,20

    The Limit 0,20 will give you a listing of 20 entries starting from 0. So, 1-20 records would be displayed. If you keep record of the number where you started in the header or somewhere else, then this would work pretty easy for you.

    select * from mytable LIMIT 30,20 – Start from 30 and given you the next 20 records.

  2. I made up the follwing:

    SELECT id, title, @a := title, ( SELECT id FROM table WHERE title @a ORDER BY title ASC LIMIT 1 ) AS next_id

    prev_id and next_id will hold the id of the previous and next record based on the sort criteria (title). If there is nothing before or after, then they will be empty…

    Hope this helps…

    ace

  3. Seems that the formatting did something to my statement, so here we go again:

    I made up the follwing:

    SELECT id, title, @a := title, ( SELECT id FROM table WHERE title < @a ORDER BY title DESC LIMIT 1 ) AS prev_id,
    ( SELECT id FROM table WHERE title > @a ORDER BY title ASC LIMIT 1 ) AS next_id

    prev_id and next_id will hold the id of the previous and next record based on the sort criteria (title). If there is nothing before or after, then they will be empty…

    Hope this helps…

  4. Ace, this query is awesome! I’m using it for a photo gallery in codeigniter and it’s perfect for getting the next/prev IDs for links.

    Here’s my code, I hope someone finds it useful:

    SELECT id, title, @a := id, ( SELECT id FROM art WHERE album = ‘$album’ AND id @a ORDER BY title ASC LIMIT 1 ) AS next_id FROM art WHERE album = ‘$album’ AND id = ‘$id’

  5. Glad it’s helpful, resist today art. I trust you’re actually using some sort of parameter substitution/prepared queries and aren’t really using string concatenation to construct your queries?

  6. Great! This query is great. However, I’ve run into a problem that I am hoping someone will be able to help me with.

    CREATE TABLE t ( id TINYINT, title VARCHAR(10) );
    INSERT INTO t (id, title) VALUES (1, ‘aaa’), (2, ‘bbb’), (3, ‘ccc’), (4, ‘ddd’), (5, ‘eee’);

    (a) DO @sn := 0;
    SELECT @sn:=@sn+1 sn, id, title FROM t GROUP BY id ORDER BY id desc;
    | sn | id | title |
    +——+——+——-+
    | 1 | 5 | eee |
    | 2 | 4 | ddd |
    | 3 | 3 | ccc |
    | 4 | 2 | bbb |
    | 5 | 1 | aaa |

    (b) DO @sn := 0;
    SELECT @sn:=@sn+1 sn, id, title FROM t GROUP BY id HAVING sn=2 ORDER BY id desc;
    | 2 | 2 | bbb |

    (c) DO @sn := 0;
    SELECT @sn:=@sn+1 sn, id, title FROM t HAVING sn=2 ORDER BY id desc;
    | 3 | 4 | ddd |

    I do not understand why (b) is not able to retain the same order of sn as in (a). I am assuming it is the ‘HAVING sn=2’ that is forcing (b) to reset the sort order. However, (c) is able to maintain the same sn order as in (a) even with ‘HAVING sn=2’ clause in it, although it is not using the ‘GROUP BY id’ anymore.

    Is there a way I can retain the sn sort order in (b) same as in (a)?

    | version() |
    +———–+
    | 5.0.51a |

  7. James — you bet! $album is part of the query string so something like $album = ‘typography’

  8. resist today art – seems like you probably ought to read up on SQL injection attacks. It’s a very bad idea to use string concatenation to build SQL queries, and especially bad if the strings being concatenated are from user input.

    http://en.wikipedia.org/wiki/SQL_injection