Posted by Hilarion on 07/24/05 18:47

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

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):

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]