Ammend code to dislay 'friends'

[hoponhiggo]

Hi Guys,

 

I have the following code which displays all of the members of my site, and allows a user to add a member as a friend:

 

<?php
echo "<center>";
if(isset($_GET['user'])) { //if there trying to view a profile
//gets the user name and makes it safe
$username = $_GET[user];
//querys the db to find the username
$getuser = mysql_query("SELECT * FROM `users` WHERE `username` = '$username'");
//checks see if the username exists in the db 

$usernum = mysql_num_rows($getuser);
//if it don't exist 
if($usernum == 0) 


{ 
//don't exist

echo ("User Not Found"); 

} 

//if it does exist then show there profile
else{
$user = mysql_fetch_array($getuser);

//to display image from source
$dir = "prof_pics";

$sql = "SELECT prof_pic FROM users WHERE username = '$username'";
$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) == 0) die("Username not found in database.");

$row = mysql_fetch_array($res);
$pic="$dir/".$row['prof_pic'];
$img="<img src=\"$pic\" width=\"88\" height=\"88\" align=\"center\"><br>";
echo "
<b>$user[username]'s Profile</b><br><br>
$img <br>
Email: $user[email]<br>
<a href='friendrequest.php?user=$user[username]'>Add as Friend</a>
";

}
}else{
//gets all the members from the database
$getusers = mysql_query("SELECT * FROM `users` ORDER BY `uid` ASC") or die(mysql_error()); 
//loops there name out
while ($user = mysql_fetch_array($getusers)) { 
echo "<a href='members.php?user=$user[username]'>$user[username]</a><br>
$img <br>
"; 
}
}
echo "<center>";
?>

 

What i am trying to do is to change this to display only the members that have already been added as a friends. I thought i could just change the SQL from:

 

$getusers = mysql_query("SELECT * FROM `users` ORDER BY `uid` ASC") or die(mysql_error()); 

 

to:

 

$getusers = mysql_query("SELECT * FROM friends WHERE username = '$username' ORDER BY uid ASC") or die(mysql_error()); 

 

But this does not work. Can anybody see where i am going wrong or suggest a better method?

 

Thanks

 

 

[TeNDoLLA]

I think the best way would be to have a link table between users and their friends (user_id <--> user_id pairs at the simplest). Then you would get all users from users table that is matched as a friend in this link table to the current user.

[TeNDoLLA]

Say you have tables like:

 

users:

--------

user_id

nickname

 

 

user_friends (the link table)

------------

user_id (refers to user_id in users table)

friend_id (refers also to user_id in users table)

 

Then to get the friends for current user do something like

 

SELECT u.user_id, u.nickname FROM users u
   JOIN user_friends f ON f.friend_id = u.user_id
   WHERE f.user_id = $current_user_id  

I did not test this just fyi.