php friending system HELP!

[Joemeister]

Hi fellow programmers!

What I want to do is this...

* I have a list of registered users
* next to each user is a link "Add as friend"
* user_1 clicks the link to add user_2
* user_2 now needs to confirm or deny the request

WILL THIS TABLE WORK?

+-------------------------------------+
|               friends               |
+----+-----------+-----------+--------+
| id |  user_id  | friend_id | status |
+----+-----------+-----------+--------+
|    |           |           |        |
|    |           |           |        |
|    |           |           |        |
|    |           |           |        |
+----+-----------+-----------+--------+

And lets say I have add_friends.php. HOW WILL THE QUERY look in the php file?

what I have so far is this:

<?php

        //create_cat.php
        include 'connect.php';
        include 'header.php';

        if(isset($_SESSION['signed_in']) == false || isset($_SESSION['user_level']) != 1 )
            {
                //the user is not an admin
                echo '<br/>';
                echo 'Sorry! You have to be <a href="/signin.php"><b>logged in</b></a> to view all the <a href="signup.php" title="Become a registered user!"><b>registered</b></a> members.';
                echo '<br/><br/>';
            }
            else
            {
                echo '<h2>Registered users:</h2>';

                $sql    = "SELECT `user_name` FROM `users` ORDER BY `user_name` ASC";
                $result = mysql_query($sql);

                while($user = mysql_fetch_array($result))
                echo $user['user_name'].' 
                     <a href="#"> ADD TO FRIENDS </a><br/><br/>';

                ///////////////////////////////
                /// adding users as friends ///
                ///////////////////////////////

                //HOW WILL THE CODE/QUERY LOOK IF I WANT TO ADD THE USER AS A FRIEND?
            }

            include 'footer.php';
?>

I'm struggling a long time now with this... YOUR HELP WILL BE SO MUCH APPRECIATED!!!

[ajbest]

<?php
//create_cat.php
include 'connect.php';
include 'header.php';

if(isset($_SESSION['signed_in']) == false || isset($_SESSION['user_level']) != 1 ){
	//the user is not an admin
	echo '<br/>';
	echo 'Sorry! You have to be <a href="/signin.php"><b>logged in</b></a> to view all the <a href="signup.php" title="Become a registered user!"><b>registered</b></a> members.';
	echo '<br/><br/>';
}
else{
	echo '<h2>Registered users:</h2>';
	
	$sql = "SELECT `user_name` FROM `users` ORDER BY `user_name` ASC";
	$result = mysql_query($sql);
	
	while($user = mysql_fetch_array($result)){
		echo $user['user_name'].'<a href="create_cat.php?add_friend_id='.$user['id'].'"> ADD TO FRIENDS </a><br/><br/>';
	}
	///////////////////////////////
	/// adding users as friends ///
	///////////////////////////////
	if (isset($_GET['add_friend_id'])){
		mysql_query("INSERT INTO friends SET user_id='".$the_current_users_id."', friend_id='".$user['id']."', status='pending'");
		mysql_query("INSERT INTO friends SET user_id='".$user['id']."', friend_id='".$the_current_users_id."', status='pending'");
		//note there are 2 queries here because person a is friends with person b and person b is friends with person a. This will make queries easyer later on. 
	}
}
include 'footer.php';
?>

Few notes on this.

- This doesn't account for duplicate entries in the friends database.

- as it is now your user could friend them self.

- based on your table you would want 2 entries in the database for each friendship. a friending b and b friending a. That way later when you are doing a check for who is friends you would only need to query one column

-the script requires a reload of the page. Upon reloading with the get var add_friend_id the actual adding would happen.

[Joemeister]

wow.. thank you so much ajbest!! Appreciate it!

[Joemeister]

I presume the table will look something like this then?

+----------------------------+
|           friends          |
+----+-----------+-----------+
| id |  user_id  | friend_id |
+----+-----------+-----------+
|    |           |           |
|    |           |           |
|    |           |           |
|    |           |           |
+----+-----------+-----------+

I think it's best that the "friendship" has an id to refer to...

and then ANOTHER BIG question is how will the SQL QUERY look if I want to achieve this in the table? INSERTING the values?

+----------------------------+
|           friends          |
+----+-----------+-----------+
| id |  user_id  | friend_id |
+----+-----------+-----------+
| 1  |     1     |     3     |
|    |           |           |
|    |           |           |
|    |           |           |
+----+-----------+-----------+

and then where will the friendship be "approved"?

[Shamrocks]

I would add back to the table the status column, some may disagree but i would do this way.

[Joemeister]

Ok, everything is sort of working but my biggest problem now is to get the person's id which the link next to is clicked. I have all the members in a list but I can't get it to get the "friend's" id the person wishes to add. PLEASE CHECK this:

$sql    = "SELECT * FROM `users` ORDER BY `user_name` ASC";
    $result = mysql_query($sql);
                                
    $num=mysql_numrows($result);

    $i=0;
    while ($i < $num)
    {
         $name = mysql_result($result,$i,"user_name");
         $picture = mysql_result($result,$i,"pic_location");

         echo "<b>$name</b>";
         echo '<br/>';
         echo '<img width="50" height="50" src="' . $picture . '">';
         echo '<br/>';
         
         //Here I try to get the freind's id but just gives me a 1 as his id everytime. If I want to add the person with the id of 4 for e.g.     i want 4 to be displayed in the db and NOT 1 all the time.                              
         echo '<a href="friends.php?friend_id="'.$name['user_name'].'"> ADD TO FRIENDS </a><br/><br/>';

         echo '<br/><br/>';
                                        
         $i++;
     }

Thanks!

[Joemeister]

Or to make it easier, how do I get the friend_id? :/

I think your original table is fine. You need some sort of status field to allow the recipient of the request to know that there is a pending friendship request. If you don't have this, then the table is pretty meaningless as a friend request is added and is automatically accepted.

When you check for friends:

SELECT friend_id AS f,`status`, IF (`status` = 0,'waitingforme','friend') AS s FROM friends WHERE user_id = $me UNION SELECT user_id AS f,`status` , IF (`status` = 0,'waiting','friend') AS s FROM friends WHERE friend_id = $me ORDER BY s

where $me is the user's id.

Obviously you need JOINS to get the friend or user name, surname, avatar

The sql will give you 3 types of status: friend (accepted - who asked who is not impt), waitingforme (a member asked to become my friend, but I've yet to accept), waiting (the guy I want to befriend is yet to accept).

This means you can place these into categories / sections in the user's profile/control page.