Previous and Next Record Delimma 1

[tektipsismyfavorite]

I have a main page that has a query to get all my clients in alphabetical order.

"SELECT * FROM clients ORDER BY cname ASC"

This obviously mixes up their IDs. So when I click on a client's name "Goofy" (id = 6), I need the next client "Mickey Mouse" (id = 2), and "Donald Duck" (id = 9) to be the previous client.

And I would prefer this to happen on the same page where it displays my client's details rather than have to put those vars in the URL.

To get my client, it's simply:

$client = $_GET['c'];
"SELECT * FROM clients WHERE clientid = '".$client."'"

 

[TonyGroves]

Do you mean you want to compose a query to get the "next" or "previous" client, given the current client's id?

For the "previous" client, you could use something like:
[tt]
SELECT clientid,cname
FROM clients
WHERE
cname<
(SELECT cname FROM clients WHERE clientid=6000)
ORDER BY cname DESC
LIMIT 1
[/tt]
This assumes the names are all different. If they might not be, then instead of comparing just the names, you would compare values consisting of a concatenation of (1) the name padded to its maximum length and (2) the clientid.

 

[TonyGroves]

I used the value 6000 there as the current clientid. Obviously you would replace it with the actual value.

 

[tektipsismyfavorite]

that worked out great! thanks man. I never realized you could do 2 SQL queries like that... I'm assuming i use > and ASC on the order by to get the next record.