Posted by Stefan Rybacki on 07/24/05 19:31

Hilarion wrote:
>>>>>> Maybe this way:
>>>>>>
>>>>>> previous:
>>>>>> SELECT * FROM adresse WHERE nom<='Dupont' AND id<>123 ORDER BY nom
>>>>>> DESC LIMIT 1
>>>>>>
>>>>>> next:
>>>>>> SELECT * FROM adresse WHERE nom>='Dupont' AND id<>123 ORDER BY nom
>>>>>> ASC LIMIT 1
>>>>>
>>>>>
>>>>> This will work OK only if "nom" values are unique. If they are not,
>>>>> then you'll
>>>>> always get other "duplicate" of current record as previous and as
>>>>> next.
>>>>> In that case you should use something like:
>>>>
>>>>
>>>> No, thats wrong. Since I used the constraint of id<>123!
>>>
>>>
>>> You are wrong. If there are two "Dupont" entries, one with ID 123 and
>>> another with for example 321 then your "previous" select would return
>>> the record with ID 321, and your "next" select would also return
>>> ID 321. If the current record was 321, then "previous" and "next"
>>> would return 123.
>>
>>
>> yes sorry, didn't recognized this case. Whatever adding "AND id <>
>> previousid"
>> should do the trick or the use of your queries ;)
>
>
> Nope :).
> Adding "AND id <> previousid" will stil be not enough.
> If there are two "Dupond" entries, then always the other one will appear
> as "previous". So clicking "previous" link the user will see only those
> two records.
> If there are three "Dupont" entries then always other two are "previous"
> and "next". Which means that if user visits one of them, he will not be
> able to get to other (not "Dupond") entries (using "previous" or "next"
> links).

oh yes, damn ;)

>
> The only way to make "previous" and "next" work properly is to use
> column subset which has unique values. This whole subset has to be used
> in ORDER BY clause and in WHERE clause (the more columns, the more
> complex this clause gets). Example for three columns (again all ordered
> ascending to make things simplier):
>

yes I agree to this.

> next:
> SELECT *
> FROM some_tables
> WHERE (col1 > $curr_col1)
> OR (col1 = $curr_col1 AND col2 > $curr_col2)
> OR (col1 = $curr_col1 AND col2 = $curr_col2 AND col3 > $curr_col3)
> ORDER BY col1 ASC, col2 ASC, col3 ASC
> LIMIT 1
>
> previous:
> SELECT *
> FROM some_tables
> WHERE (col1 < $curr_col1)
> OR (col1 = $curr_col1 AND col2 < $curr_col2)
> OR (col1 = $curr_col1 AND col2 = $curr_col2 AND col3 < $curr_col3)
> ORDER BY col1 DESC, col2 DESC, col3 DESC
> LIMIT 1
>
>
> Hilarion

[Back to original message]