List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:May 12 2007 1:53am
Subject:Re: finding next and prev record in mysql
View as plain text  
Hi,

Richard Kurth wrote:
> -----Original Message-----
> From: Baron Schwartz [mailto:baron@stripped] 
> Sent: Friday, May 11, 2007 9:25 AM
> To: Mogens Melander
> Cc: mysql@stripped
> Subject: Re: finding next and prev record in mysql
> 
> Mogens Melander wrote:
>> On Fri, May 11, 2007 07:15, Richard Kurth wrote:
>>> How would I find the next id and the prev id in sql statement like 
>>> the one below. The id number is not going to be in order so I can't 
>>> do a < or > limit 1 on the search
>>>
>>> SELECT id FROM contacts WHERE category = '5' AND subcategory = '1' 
>>> AND members_id= '8' ORDER BY lastname
>> The PHP variant could look like (prev id):
>>
> Yes I read your article but I can't figure out how to use it with my
> existing sql statement.
> This is just an example of the search sql statement it could be different
> than this. 
> I all ready know what Id number I what to find the prev and the next
> recorded number around. Say the id number is 52. If I print out the list of
> ids the one before number 52 is 503 the one after is 302. How can I use your
> script to find these numbers using the same sql statement. I am just not
> sure how to incorporate it in to the search.

Yes, now that I look more closely at your question it does require some tweaking 
to work.  I don't know your exact data, but I'll asume:

mysql> select * from contacts order by lastname;
+------+----------+-------------+---------------+
| id   | category | subcategory | lastname      |
+------+----------+-------------+---------------+
|  456 |        5 |           1 | everybody     |
|  483 |        5 |           1 | me            |
|  195 |        5 |           1 | them          |
|  182 |        5 |           1 | todo el mundo |
|  290 |        5 |           1 | us            |
|   44 |        5 |           1 | we            |
|  365 |        5 |           1 | yall          |
|  229 |        5 |           1 | you           |
+------+----------+-------------+---------------+

The first thing to notice is category and subcategory are constants (both in my 
data, and in your query, because you put them in the WHERE clause), so you can 
factor them out.  This makes the rest of the query easier to write, and then you 
can plug back in the category and subcategory.

Suppose I'm currently on record 290, 'us'.  Next is 'we' and prev is 'todo el 
mundo.'

select
    if(lastname >  'us', 'next', 'prev') as direction,
    if(lastname > 'us', min(lastname), max(lastname)) as lastname
from contacts
where lastname <> 'us'
group by 1
order by if(lastname > 'us', 1, -1);

+-----------+---------------+
| direction | lastname      |
+-----------+---------------+
| prev      | todo el mundo |
| next      | we            |
+-----------+---------------+

Now you can add back in the other clauses:

select
    if(lastname >  'us', 'next', 'prev') as direction,
    if(lastname > 'us', min(lastname), max(lastname)) as lastname
from contacts
where lastname <> 'us' and category = 5 and subcategory = 1
group by 1
order by if(lastname > 'us', 1, -1);

If you need to get the id column back, I think you will need to join this result 
set back to the original table to get other columns.

Baron
Thread
finding next and prev record in mysqlRichard Kurth11 May
  • Re: finding next and prev record in mysqlBrent Baisley11 May
    • RE: finding next and prev record in mysqlRichard Kurth11 May
  • Re: finding next and prev record in mysqlBaron Schwartz11 May
  • Re: finding next and prev record in mysqlMogens Melander11 May
    • Re: finding next and prev record in mysqlBaron Schwartz11 May
      • RE: finding next and prev record in mysqlRichard Kurth11 May
        • Re: finding next and prev record in mysqlBaron Schwartz12 May