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.