mysql distinct problem

[kersten]

Hallo,

I'm sorry if this has been asked before, but I couldn't find something similar in the forums.

I have a table with 2 columns:

id name

1 user1
2 user2
3 user1
4 user4

I want to select all different users from the table:

SELECT DISTINCT name FROM table

But now I also want to know the id, not only the users, at the same time.

SELECT DISTINCT name, id from table doesn't work.

Can anybody help me please?

TIA
Kersten

[daholygoat]

DISTINCT does the distinct on the whole tuple, so it's not possible to do that on one field, while others aren't unique. You could use a temporary table or use some PHP fiddling to get around your problem.

[amcgrath]

I'm still trying to figure out why K would even want to know the ID, especially if there are more than one user names....

[[deleted]]

"But now I also want to know the id, not only the users, at the same time"

which id?
As you can see in your own example, user1 can have 2 id's, so how can you tell which one would you want?

[Anon]

Hrmm, am I reading this right? You basically want to see each "name" and all the userids associated with it, right?

For example, let's say you have:
id name
1 john
2 mary
3 john
4 bob

What kind of output do you want to see? Something like this:

john (id: 1,3)
mary (id: 2)
bob (id: 4)

I'm probably way off on this, but I thought I would give it a shot.

If you could tell us what you'd like to have outputted, that might help.

[kersten]

You're absolutely right, I thought about it again and I've come to the conclusion that I'm talking bullshit. sorry for wasting your time guys. damn, I'm such an idiot. I solved my problem with rawurlencode() ...decode() btw.

[[deleted]]

Not knowing something doesn't make you an idiot..

but how on earth did you solve an SQL problem by using rawurelencode? 🙂

[kersten]

I had open page like this:

query_dates.php?user=Kersten Lohmeyer

the blanc space makes problems in older netscape versions. so I thought about using the id of the user and not the user itself.
thas was a problem because I'm calling the url from within a select menu, where all different users are listed. so I used rawurlencode() now, to make a Kersten%20Lohmeyer out of Kersten Lohmeyer. and rawurldecode to decode it.

😉

Kersten

[[deleted]]

ah, now I see. thanks for explaining!