Dynamic Form with Database Values FRIEND SYSTEM

[JohnnyKennedy]

Okay, so I have a basic table set up with ID, Friend1, Friend2, Confirmed etc.  With ID auto_incrementing, & confirmed being 0 or 1 (1 confirmed 0 pending).  MySQL script is working great and is returning the appropriate rows:

 

mysql_select_db($database_NewConnection, $NewConnection);

$query_buddy = "SELECT * FROM buddies WHERE `usera`='$me' AND `confirmed`='$one' OR `userb`='$me' AND `confirmed`='$one'";

$buddy = mysql_query($query_buddy, $NewConnection) or die(mysql_error());

$row_buddy = mysql_fetch_assoc($buddy);

$totalRows_buddy = mysql_num_rows($buddy);

 

however, what I can't seem to figure out is how to display the username of a users friend in the dynamic field without displaying my own.  For example:

 

I add a friend, my Username JOHN is added as friend1 and the other users name (KATE) is added into friend2 - assuming the relationship is confirmed and the value of confirmed is 1, the friendship is returned as a MySQL result.

 

I would like to display values from the query (from either Friend1 or Friend2) where my username is not included.  So in this example I only want to display the value of KATE (from friend2) and not my name (from friend1) - essentially display all of my friends..

 

Is this possible?  Can you please lead me to the right track.  ANY help would be appreciated.

[DavidAM]

First, your WHERE clause is not quite right. Although, it may work (especially with limited data). Your query essentially says:

WHERE `usera`='$me' 
AND (`confirmed`='$one' OR `userb`='$me') 
AND `confirmed`='$one'

You need to wrap the two separate conditions in parenthesis

WHERE (`usera`='$me' AND `confirmed`='$one')
OR (`userb`='$me' AND `confirmed`='$one')

 

Second, you are not looping through the results, so you are only getting one result. Something like this would give you all results:

$buddy = mysql_query($query_buddy, $NewConnection) or die(mysql_error());
$totalRows_buddy = mysql_num_rows($buddy);
while ($row_buddy = mysql_fetch_assoc($buddy)) {
    // Do something with the results
    echo $row_buddy['usera'], $row_buddy['userb'];
}

 

To echo only the friend that is NOT the user, you can use a conditional in PHP:

    echo ($row_boddy['usera'] == $me ? $row_buddy['userb'] : $row_buddy['usera']);

 

or you can have the query return the "other" person:

SELECT *, IF (usera = '$me', userb, usera) AS Other
FROM buddies 
WHERE '$me' IN (usera, userb) AND confirmed='$one'

[JohnnyKennedy]

OMG I LOVE YOU!  That 'other friend' trick solved like 90% of my other problems!!!!