Member Avatar for 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!!!

  • 4 Contributors
  • 7 Replies
  • 133 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

A few practical points to finish this reliably and securely (addresses the link bug in Post #6 and the schema/flow questions raised earlier).

A recommended data model

  • Use one row per friend-request/friendship with explicit requester_id, recipient_id, status and timestamp fields (created_at, responded_at). That makes acceptance/decline a single UPDATE and keeps history. If you choose the two-row approach mentioned by , be aware it stores duplicate relationships and needs extra checks to avoid double entries. was right to flag storing status — you need it to know "pending" vs "accepted". s UNION idea is a good way to present a combined view to a user.

Fix for the "friend_id always 1" bug

  • The usual cause is building the URL string incorrectly and/or using the wrong variable. When iterating DB rows, read the id column and inject it safely. Better: use a small POST form per user (avoids side effects via GET) and cast the id to int.

Example form for each listed user

<form method="post" action="add_friend.php">
  <input type="hidden" name="friend_id" value="<?= (int)$row['id'] ?>">
  <button type="submit">Add friend</button>
</form>

Server-side flow (key checks)

  • Authenticate the session user.
  • Cast and validate friend_id as an integer and prevent self-requests.
  • Check for existing row(s) to avoid duplicates (query both directions if using one-row design).
  • Insert a pending request (use prepared statements / PDO or mysqli).

Example server-side outline (PDO)

$me = (int)$_SESSION['user_id'];
$friend = (int)$_POST['friend_id'];
if ($friend === $me) exit;
$stmt = $pdo->prepare('SELECT id FROM friends WHERE (requester_id=? AND recipient_id=?) OR (requester_id=? AND recipient_id=?)');
$stmt->execute([$me,$friend,$friend,$me]);
if ($stmt->fetch()) exit; // already pending/accepted
$insert = $pdo->prepare('INSERT INTO friends (requester_id,recipient_id,status,created_at) VALUES (?,?,?,NOW())');
$insert->execute([$me,$friend,'pending']);

Acceptance

  • The recipient updates the same row: UPDATE friends SET status='accepted', responded_at=NOW() WHERE id=? AND recipient_id=? AND status='pending'.

Security and DB notes

  • Use prepared statements, validate inputs, and implement CSRF protection for POST actions.
  • Add indexes on requester_id/recipient_id and a UNIQUE constraint strategy to prevent duplicate requests (application-level ordering or a DB constraint depending on design).
  • Prefer PDO or mysqli over the old mysql_* extension.

These steps resolve the echo/URL bug, give a robust approval flow, and balance query simplicity with data integrity.

Recommended Answers

All 7 Replies

Member Avatar for 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.

Member Avatar for Joemeister

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

Member Avatar for 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"?

Member Avatar for Shamrocks

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

Member Avatar for 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!

Edited by Joemeister because: n/a
Member Avatar for Joemeister

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

Member Avatar for Member #120589

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.

Edited by Member #120589 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.