[RESOLVED] Need help with query on two columns

[grantus]

I've created a friend system for my site and I've run into an issue. I have 2 columns:

• username

• username_friend

Let's say a member with the username "Fred" invited a member with the username "Jack". Then it would be like this:

• "Fred" would be in the username column

• "Jack" would be in the username_friend column

Now let's say a member with the username "Bill" invited a member with the username "Fred". Then it would be like this:

• "Bill" would be in the username column

• "Fred" would be in the username_friend column

Now to get all the friends for "Fred", I would do a query like this:

SELECT username, username_friend FROM friends
WHERE username='Fred' OR username_friend='Fred'

The problem is sorting the results. I want to show all of Fred's friends. This means I need to show all his friends whether Fred is in the username or username_friend column. What kind of query can I do? The above query will work, but how can I sort the results? I want to sort the results of all his friends (username or username_friend) in alphabetical order. How can this be done? I know it would be ideal if "Fred" was always in the same column, but this is a friends system, where members can invite one another, so they'll always be in 1 of 2 columns.

Thanks in advance.

[rulian]

I think the easiest way it to not assicate them by names but rather id's,
do a join and then sort by name

if you are unfamiliar with JOINS, they are definately worth a read.

For example. lets say you take your same friend schemes but normalize it

table users
ID | Username
"1" | "tommy
"2" | "jerry"
"3" | "linda"

table friends
ID | Users_ID | Friends_ID
1 | 1 | 2
2 | 1 | 3
3 | 2 | 3

SELECT `c`.* FROM `users` AS `a` 
LEFT JOIN `friends` AS `b` ON `a`.`id` IN (`b`.`user_id`, `b`.`friend_id`)
LEFT JOIN `users` AS `c` ON `c`.`id` IN (`b`.`user_id`, `b`.`friend_id`) 
WHERE `a`.`username` = "$username"

right now it will return you you $username as well, but you can tweak that until it's okay

[grantus]

I am using userid and userid_friend, I just put username and username_friend for the sake of this thread. I understand what you mean though, however that still only shows the username, plus I would have to split into 2 tables.

Thanks.

[ragedigital]

Yes, you need 2 tables. One that hold the persons name and the other to hold their friends.

The user_table will have "Fred" personal information.

The friend_table will have all of "Fred's" friends as well as everyone elses.

"SELECT friend_table.username_friend FROM user_table, friend_table WHERE user_table.username = friend_table.username AND user_table.username='Fred'";

echo "These are Fred's friends:"

while($getFriendList = mysql_fetch_array())
    echo $getFriendList['username_friend'] ."<BR>";

Rulian has it perfect, but I think my example brings it down a notch for easier understanding. +1 on the use of numbers vs. names. NOTE: My example dealt with names.

[tomhath]

SELECT username_friend as friend
FROM friends
WHERE username='Fred' 
UNION
SELECT username as friend
FROM friends
WHERE username_friend='Fred'
order by 1

[grantus]

ragedigital, thank you, this works nicely. I won't use it for this table in order to avoid splitting into 2 tables because tomhath's query did the trick for me. I will use it though on another issue for something else.

tomhath, just as I was thinking of UNION I saw your post. Works perfectly! Thank you.