Advanced MySQL Question

[ATLien]

Ok, here is my situation I am trying to setup a coworker website that will allow people to label other coworks as friends. I have a table right now that has varibles like friend1 and friend2. friend1 is the one who actually made the request to be a friends with the other person. Now if I wanted to show a list of one persons friends, what would the query be to seach both friend1 and friend to for say id 223. Then if the 223 is found in either of the 2 fields, the query will select the id of the person in the oposite varible. Ok, is is confusing let me draw it out.

friends table

(friend1) (friend2)

(223) (452)
(113) (223)
(005) (223)
(223) (103)

ok, that is what the table looks like. now I need a query that will search both varibles for 223. Then if friend1 = 223 it will select 452 for php output.

So for the first run of the query, it will select 452, the second it will select 113, third 005...

Ok, the part I am having trouble with is how to get it to scan both friend1 and friend2, and out put the opposite oh which ever one the id we are looking for is found in.

I appreciate the help.

[subwayman]

I would run it as two queries:

SELECT friend1, friend2 from friends order by friend1
SELECT friend2, friend1 from friends order by friend2

You will have to have some logic which loops rows and collects friends for each id. In your example 223 has two entries either way making 4 altogether. The order by clause will ensure all reacrds for the same id are together in each query.

[ATLien]

I need to do it as one query though, so I can just do $result['friend'] and have that set to the friend id that was in the opposite friend varible.

[toxic_brain]

try this,

$id = 223;
$result = mysql_query("SELECT friend1, friend2 FROM friends WHERE friend1=$id OR friend2=$id");
$row = mysql_fetch_object($result);

$friend = $row->friend1 == $id ? $row->friend1 :$row->friend2 ;

echo $friend;