php loop logic trouble

Question

I'm working on a friends list type function that will display a user's friends and I'm having some logic problems. The way the table for storing friends is set up, there are 3 columns; userid1, userid2 and friendstatus. A friendstatus of 1 indicates the users are friends, status of 0 indicates a request is pending. When a user adds another user to be their friend, the user who sends the request is placed in userid1, and the user who receives the request is placed in userid2.

So, the issue that I'm having is getting the function to properly select the user's friends, not the user himself. Basically, I'm trying to get this function to determine which column is the user(me for instance) and which column is the friend.

I originally had the first SQL statement as the following, but this didn't work either.

 $friends = mysql_query("Select * from friends where (userid1 = $myUsername OR userid2=$myUsername) AND friendstatus = '1'");

Here's the logic I'm trying to create.

1. Get userid(mine)
2. Search column 1 for my ID: if found, display column 2(which would be my friend)
3. search column 2 for my ID: if found, display column 1(which would be my friend)

The function friendslookup just displays the information and pulls the user's info. It doesn't add to the logic.

$myUsername = $_GET['myFriends'];
if(isset($myUsername)){
        $friends = mysql_query("Select * from friends where userid1 = $myUsername AND friendstatus = '1'");
        $friends2 = mysql_query("Select * from friends where userid2 = $myUsername AND friendstatus = '1'");
        if(mysql_num_rows($friends) > 0 OR mysql_num_rows($friends2) >0 ) {

echo '<table><tr><td>Username </td><td> Location</td></tr>';
        $col1rows = mysql_num_rows($friends);
        $col2rows = mysql_num_rows($friends2);
            }
        for ($i=0; $i<$col1rows;$i++){
            $myFriends = mysql_query("Select * from friends where userid1 = $myUsername AND friendstatus = '1'");
                $friend = mysql_fetch_array($myFriends);
                $friend_1 = $friend['userid2'];
                friendsLookup($friend_1,$myUsername);
            }
        for ($i=0; $i<$col2rows;$i++){
            $myFriends_2 = mysql_query("Select * from friends where userid2 = $myUsername AND friendstatus = '1'");
                $friend = mysql_fetch_array($myFriends_2);
                $friend_2 = $friend['userid1'];
                friendsLookup($friend_2,$myUsername);
                }
            }

Answer 1

This might help:

SELECT (CASE $myUsername
    WHEN `userid1` THEN `userid2`
    WHEN `userid2` THEN `userid1`
END) AS `friend_ids`
FROM `friends`
WHERE $myUsername IN (`userid1`, `userid2`)
AND `friendstatus` = 1;

Few issues with your logic -

1. you do not need to run the same query multiple times.

2. using only your first 2 queries, you can read all friends by fetching column "userid2" from query 1 and column "userid1" from query 2.

3. In your for loops, you are always reading the first record. So in each iteration, you are calling the same query again and always reading only the first record. Instead you should run the query once and call mysql_fetch_array() in a loop.

For example here:

$myFriends = mysql_query("Select * from friends where userid1 = $myUsername AND friendstatus = '1'");
while ($friend = mysql_fetch_array($myFriends) {
    $friend_1 = $friend['userid2'];
    friendsLookup($friend_1,$myUsername);
}

Hope the above helps!

Answer 2

I would suggest you do everything in one SQL (including that "friendsLookup" function, 'coz I bet there's another mysql query for that), because it will become really slow. Supposing the Table for your users is "user". Use this SQL:

$sql = "SELECT * FROM user u, friend f WHERE f.userid1 = $myUsername AND f.friendstatus = '1' AND u.userid = f.userid2
UNION
SELECT * FROM user u, friend f WHERE f.userid2 = $myUsername AND f.friendstatus = '1' AND u.userid = f.userid1";

This will fetch all your friends (including their user information). Moreover, if you want to sort or limit (or whatever you wanted to do further with your results), you can do this:

For example you want to sort your friends by their First Name in ascending order:

$sql = "SELECT * FROM (
SELECT * FROM user u, friend f WHERE f.userid1 = $myUsername AND f.friendstatus = '1' AND u.id = f.userid2
UNION
SELECT * FROM user u, friend f WHERE f.userid2 = $myUsername AND f.friendstatus = '1' AND u.id = f.userid1 ) tblResult r
ORDER BY r.first_name ASC";

Then you can fetch the final results by doing this:

$query = mysql_query( $sql );
while ( $col = mysql_fetch_array( $query ) ){
  ?>
  <!-- DO YOUR THING HERE WITH PHP -->
  <?php
}

Finally, I would like to comment on your Looping. Notice inside your loops, you have this code:

$myFriends = mysql_query("Select * from friends where userid1 = $myUsername AND friendstatus = '1'");
$friend = mysql_fetch_array($myFriends);

This is actually unnecessary. The first line just resets your query every single loop. Remove the first line, and in the second line, instead of using $myFriends, use $friends (this is the first query you have at the top). So it should look like this for the first two lines in your first loop:

$friend = mysql_fetch_array($friends);

Do this as well for the second loop.

Answer 3

I would use a single UNION'd sql query:

$myUsername = $_GET['myFriends'];
if(isset($myUsername)){
        $friends = mysql_query("
        SELECT userid1 as friendId
        FROM friends
        WHERE userid2 = $myUsername AND friendstatus = 1
        UNION
        SELECT userid2 as friendId
        FROM friends
        WHERE userid1 = $myUsername AND friendstatus = 1
        ");

        if(mysql_num_rows($friends) > 0) {
            echo '<table><tr><td>Username </td><td> Location</td></tr>';
            while($friend = mysql_fetch_array($friends)) {
                $friend_data = friendsLookup($friend['friendId'],$myUsername);
                //display friend data here
            }
            echo '</table>';
        }
}