Friend List Not Show all the user friends

Question

I am doing a friend list and it's not working very well. Well I tried to show all the user's friends but it's not working, it only shows one instead of all.

In MySQL friends_request table I have something like this:

(I don't know how to put a draw-able table here so i put the html of the table)

<table border="1">
    <tr>
        <td>id</td>
        <td>from_username</td>
        <td>to_username</td>
        <td>requested</td>
        <td>accepted</td>
    </tr>
    <tr>
        <td>1</td>
        <td>username1</td>
        <td>username2</td>
        <td>0</td>
        <td>1</td>
    </tr>
    <tr>
        <td>2</td>
        <td>username1</td>
        <td>username3</td>
        <td>0</td>
        <td>1</td>
    </tr>
    <tr>
        <td>3</td>
        <td>username2</td>
        <td>username3</td>
        <td>0</td>
        <td>1</td>
    </tr>
</table>

What it shows is just only one username instead all where username is the user logged in... For example, in 'username1' just appears as friends with 'username2' and not show as well with 'username3' I want to know why.

<?php
$selfriends = "SELECT * FROM friends_request WHERE from_username = '$user' OR to_username='$user' AND accepted = '1'";
$resultfriend = $sql->query($selfriends);   // select the table of friends_request from database
$rowfriend = mysqli_fetch_assoc($resultfriend); // calls the string, char or number from friends_request table

$t1 = $rowfriend['from_username']; // calls user from_username from friends_request table
$t2 = $rowfriend['to_username']; // calls user to_username from friends_request table
$accepted = $rowfriend['accepted']; // calls accepted from friends_request table
$fid = $rowfriend['id'];

/* CHECK IF FRIEND GOT SOME PICTURE */
$selPicturePro = "SELECT * FROM users WHERE username = '$t1'";
$resultsPicPro = $sql->query($selPicturePro); // select the users table and find out the content of pic
$rowProfiles = mysqli_fetch_assoc($resultsPicPro); // calls the row of profile picture.

if($rowProfiles > 1) {
    $profileView = "<img src='".$rowProfiles['profile']."' width='auto' height='155px' />";
} else {
    $profileView = "<img src='images/no-picture.png' width='auto' height='100px' />";
}

$selPicturePro2 = "SELECT * FROM users WHERE username = '$t2'";
$resultsPicPro2 = $sql->query($selPicturePro2); // select the users table and find out the content of pic
$rowProfiles2 = mysqli_fetch_assoc($resultsPicPro2); // calls the row of profile picture.

if($rowProfiles2 > 1) {
    $profileView2 = "<img src='".$rowProfiles2['profile']."' width='auto' height='155px' />";
} else {
    $profileView2 = "<img src='images/no-picture.png' width='auto' height='100px' />";
}

if($user != $t1) { // if the user is not the sender and accepted = 1, then the 
    // receiver sees the other user.
    echo '<h3>Friends</h3>';
    echo '<a href="profile.php?u='.$t1.'" class="no-style">';
    echo $profileView;.'<br />'.$t1;
    echo '</a>';
}
else if($user != $t2) { // if the user is not the receiver and accepted = 1, then the    
    // sender see the other user.
    echo '<h3>Friends</h3>';
    echo '<a href="profile.php?u='.$t2.'" class="no-style">';
    echo $profileView2.'<br />'.$t2;
    echo '</a>';
}

if($user != $t1 && $accepted == 0) {
    echo "<br clear='all' />";
    echo "<h3>Friends</h3>";
    echo "You don't have friends yet.";
}

if($user != $t2 && $accepted == 0) {
    echo "<br clear='all' />";
    echo "<h3>Friends</h3>";
    echo "You don't have friends yet.";
}           
?>

Loop the `mysqli_fetch_assoc`. Also use parameterized queries. Also couldnt you this all in one query? — chris85, Dec 16 '16 at 22:48
Maybe, but I am still learning how to make this list, so I am trying a lot of possibilities, but for sometime, I can't make it work. — , Dec 16 '16 at 23:15
I tried the loop but didn't work.. It appears only one friend for hundred or thousands times — , Dec 16 '16 at 23:18
The problem is your `WHERE` clause. Change it to `WHERE (from_username = '$user' OR to_username = '$user') AND accepted = 1`. The default grouping of `AND` and `OR` doesn't match what you want. — Barmar, Dec 16 '16 at 23:43
You need the loop. You're only fetching one row. You need a loop with `while ($rowfriend = mysqli_fetch_assoc($resultfriend)) { ... }` — Barmar, Dec 16 '16 at 23:44
The query has `AND accepted = 1`, so why does your code use `&& $accepted == 0`? It should never return friend requests that aren't accepted. — Barmar, Dec 16 '16 at 23:55
`if($rowProfiles > 1)` makes no sense. `$rowProfiles` is an array, not a number. — Barmar, Dec 16 '16 at 23:58

Barmar · Accepted Answer · 2016-12-17T01:12:51.347

You need a loop to process all the friends. Also, you can combine all the queries with a join.

Instead of duplicating all your code for from_username and to_username, you can change the query to get them both into a single column with UNION.

The test for whether the user has any friends can't be done while processing the row, because when you don't have any friends there won't be any rows selected. Do that before the loop, by checking the number of rows that are returned.

<?php

$selfriends = "SELECT fr.username, u.profile
               FROM (SELECT from_username AS username
                     FROM friends_request
                     WHERE to_username = '$user' AND accepted = 1
                     UNION ALL
                     SELECT to_username AS username
                     FROM friends_request
                     WHERE from_username = '$user' AND accepted = 1) AS fr
               JOIN users AS u ON u.username = fr.username";
$resultfriend = $sql->query($selfriends);                                       // select the table of friends_request from database
if ($resultfriend->num_rows == 0) {
    echo "<br clear='all' />";
    echo "<h3>Friends</h3>";
    echo "You don't have friends yet.";
} else {
    echo '<h3>Friends</h3>';
    while ($rowfriend = mysqli_fetch_assoc($resultfriend)) {                        // calls the string, char or number from friends_request table
        $t1 = $rowfriend['username'];
        $profile = $rowfriend['profile'];

        if(!empty($profile)) {
            $profileView = "<img src='".$profile."' width='auto' height='155px' />";
        } else {
            $profileView = "<img src='images/no-picture.png' width='auto' height='100px' />";
        }

        <a href="profile.php?u=<?php echo $t1; ?>" class="no-style">
        <?php echo $profileView; ?><br />
        <?php echo $t1; ?>
        </a>
        <?php
    }
}

the 'fr.username' and 'u.profile' is the same as saying 'from_username' and 'users.profile' or it means 'friends_request.username' and 'users.profile'? I am asking, so that I can be sure of it. — , Dec 17 '16 at 01:10
`fr.username` is `from_username` when the row matches `to_username = $user`, or `to_username` when the row matches `from_username = $user`. And `u.profile` is `users.profile` for the matching user. — Barmar, Dec 17 '16 at 01:12

Friend List Not Show all the user friends

1 Answers1