a work on process

Next/Previous with MySQL

You are reading a post by James Stewart entitled Next/Previous with MySQL. It was posted on 27 June 2005 at 12:58 pm.

You can find more posts by returning to the index.

Filed under: Snippets
Tagged: ,

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.

Recommend this post:

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]

Related Posts

 

3 Comments »

  • 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.

    Comment by RaginBajin — 30 August 2005 @ 10:49 am

  • 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

    Comment by ace suares — 9 April 2007 @ 8:14 pm

  • 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…

    Comment by ace suares — 9 April 2007 @ 8:16 pm

TrackBack URI

Leave a comment

Login Method

OpenID

Anonymous