List:General Discussion« Previous MessageNext Message »
From:Richard Kurth Date:May 11 2007 4:37pm
Subject:RE: finding next and prev record in mysql
View as plain text  
 

-----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):
> 
> SELECT id FROM contacts
>   WHERE category = '5'
>     AND subcategory = '1'
>     AND members_id= '8'
>     AND lastname < $_POST['lastname']
>   ORDER BY lastname
>   LIMIT 1;

I forgot to say earlier: my article is about finding both next and previous
records in a single query.  I don't know if this is what you need or not but
I assumed it was.


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.

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