PHP get variable from on table and then use that variable to select from a second table

Question

I'm building a followers list for a user. I have two tables the first shows the relationships between users and the second table holds every users profile info. In the following code, I first select from the user relationships table to get a variable {$userid1} which is the value of all the user ids that follow the current user. When I echo out {$userid1} I get all the ids of the users who follow the current user but it is one giant connected string. I want to take the variable {$userid1} and use it to pull every one of those follower's user data from the profile table. I want every user's data to show up from the profile table that follows the current user. The code works however only the newest follower's profile data is pulled from the profile table. I was thinking putting the variable {$userid1} into an array and using foreach, but I'm not sure how the syntax would be. Anybody know how it could work? The problem is a variable can only hold one value at a time.

Output of the first query are the iduser numbers from the users following the current user.

e.g. when echoed echo $userid1 . " "; the results are the ids look like this: 45 56 67 I added a space between numbers in the echo statement.

$sql = mysql_query("SELECT * FROM followrelations Where iduser2='$uid' "); //followers from relations table
                    while($row = mysql_fetch_array($sql)){
$userid1 = $row['iduser1']; 
echo $userid1 . " ";


}
$sql = mysql_query("SELECT * FROM profile where iduser=$userid1 ORDER BY username ");//get followers infor from profile using variable
    while($row = mysql_fetch_array($sql)){  
    $iduserf = $row['iduser'];  //userid2 requires different var name so program does not get mixed up                      
    $username = $row['username'];
    $bio = $row['bio'];
    $avatar = $row['avatar'];

echo "
<div style='width:500px;height:100px;padding:20px 20px;float:left;border: solid black 1px;'>
<a href='profile.php?uid=$iduserf'><img src=$avatar height=50px width=50px /></a></br>
<a href='profile.php?uid=$iduserf'>$username </a></br>
</div>" ;

}

Answer 1

You might be looking for subqueries:

SELECT username 
FROM user_table 
WHERE id IN( SELECT user_id WHERE linked_user_id = 123 )

This is simplefied to be a better example. This will select the username of all users from the user_table, where the ID exist in the subquery.
In turn, the subquery selects all user_id where the linked user has id=123.
The subquery is quite similar to making an array in PHP and use that info for the next query.

Small note: Be carefull with subqueries. They're perfect in my example above, but eg not all servers support a limit in the subquery. This might effect performance. The more complecated functions don't work in a subquery. Try to keep those simple.

Answer 2

You can use something like the following to execute this in a single query:

$id = mysql_real_escape_string($uid);
$sql = <<<SQL
SELECT
  p.*
FROM profile as p
  INNER JOIN followrelations as fl
    ON fl.iduser1 = p.userid
WHERE fl.iduser2 = $id
ORDER BY username
SQL;
$stmt = mysql_query($sql);

while($row = mysql_fetch_array($stmt)){
// Rest of the logic here
}

This will return all the fields in your profile table. Note that the mysql_ extension is deprecated and unsafe - you should not be using it in new code. Take a look at How to replace MySQL functions with PDO? and How can I prevent SQL injection in PHP? for some good practices regarding this.

Answer 3

Found my mistake, the second $sql should be another name (I named it $sql1), and the second half should all be within the first while.

 $sql = mysql_query("SELECT * FROM followrelations Where iduser2='$uid' "); //followers session uid

                        while($row = mysql_fetch_array($sql)){


    $userid1 = $row['iduser1']; 


      $sql1 = mysql_query("SELECT * FROM profile where iduser='$userid1' ORDER BY username ");//get followers infor from profile using variable
        while($row = mysql_fetch_array($sql1)){ 
        $iduserf = $row['iduser'];  //userid2 requires different var name so program does not get mixed up                      
        $username = $row['username'];
        $bio = $row['bio'];
        $avatar = $row['avatar'];

    echo "
    <div style='width:500px;height:100px;padding:20px 20px;float:left;border: solid black 1px;'>
    <a href='profile.php?uid=$iduserf'><img src=$avatar height=50px width=50px /></a></br>
    <a href='profile.php?uid=$iduserf'>$username </a></br>
    </div>" ;                                   
    }
    }