CODEWITHSUNDEEP
phpmysqlmysqli
MisterP
MisterP

Reputation: 467

Use a SELECT result as WHERE condition to another SELECT in the same query, is that possible?

So, I'm working with 3 table: users, follow and user_share.

Users: 

user_id | username |  first_name | last_name | 
______________________
1       | a        | ....
2       | b        | ....
3       | c        | ....
4       | d        | ....
5       | e        | ....
......................  


Follow:  

id | follower |  followed | 
______________________
1  | 20        | ....
2  | 20        | ....
3  | 12        | ....
4  | 22        | ....
5  | 77        | ....
......................

User_share:  

user_id | share_id |  share_type | share_path | share_flag 
______________________
12       | 1        | ....
22       | 2        | ....
22       | 3        | ....
12       | 4        | ....
4        | 5        | ....
......................

Follow holds the ids' of who is following who, user_share instead holds the information about what people did shared (such as a new biography, a new profile image etc). What I'm trying to achieve is building a logged_in section into the home page of a 'social network' I'm currently working on as college project. In order to do that I need to get every information I'm gonna show up about every people followed by someone (i.e. user holding the session).
That's what I got so far: 

$conn=mysqli_connect('localhost', 'root', '', 'database');
              // return the id of all the people followed by the session user
              $result = mysqli_query($conn, "SELECT `followed` FROM `follow` WHERE `follower` = '$session_user_id'");
              $follow_id = mysqli_fetch_array($result, MYSQLI_NUM);
              $id_length = count($follow_id);
              for($i = 0; $i < $length; $i++){
              //return the information about every people followed
              $data = mysqli_query($conn,
    "
SELECT u.username
     , u.first_name
     , u.last_name
     , u.user_id
     , s.share_type type
     , s.share_path path
     , s.shared_flag flag
     , s.share_id
  FROM users u
  JOIN user_share s
    ON u.user_id = s.user_id
 WHERE u.user_id = '$follow_id[$i]'
 ORDER 
    BY user_share.share_id
    "
                                      );
               /*Within this loop I'm gonna process the information of data() and eventually output them*/                       
              }

I'm just wondering: can I incorporate the two query above in a single query? Something like a nested SELECT where the first result is used as WHERE condition in the second SELECT. I don't even know if this is a stupid question,I'm quite newbie to MySQL. Thanks.

Upvotes: 2

Views: 295

Answers (2)

Alex
Alex

Reputation: 17289

"SELECT f.followed,
    u.username, 
    u.first_name, 
    u.last_name, 
    u.user_id, 
    user_share.share_type AS type, 
    user_share.share_path AS path, 
    user_share.shared_flag AS flag, 
    user_share.share_id
FROM follow f
LEFT JOIN users u
ON f.followed = u.user_id
INNER JOIN user_share us
ON u.user_id = us.user_id
WHERE follower = '$session_user_id'
ORDER BY f.followed, us.share_id"

And by the way your code is wrong WHERE users.user_id = '$follow_id[$i]' will not work never. you are trying to get $i column from the record $follow_id. I guess you were trying to get $i record.

And here is another error:

 $id_length = count($follow_id);
 for($i = 0; $i < $length; $i++){

So you have $id_length but in condition you use $i < $length.

So just to simplify your life a little I would offer this code as a start point:

$conn = new PDO('mysql:dbname=database;host=localhost', 'root', '');
$query = "SELECT f.followed,
    u.username, 
    u.first_name, 
    u.last_name, 
    u.user_id, 
    user_share.share_type AS type, 
    user_share.share_path AS path, 
    user_share.shared_flag AS flag, 
    user_share.share_id
FROM follow f
LEFT JOIN users u
ON f.followed = u.user_id
INNER JOIN user_share us
ON u.user_id = us.user_id
WHERE follower = ?
ORDER BY us.share_id";
if ($stmt = $conn->prepare($query)) {
    $stmt->bindParam(1, $session_user_id);
    $stmt->execute();
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        print_r($row);
    }
}

Upvotes: 3

Uueerdo
Uueerdo

Reputation: 15941

Your detail is hard to follow, but the answer the question header; yes, like so:

SELECT *
FROM aTable
WHERE (field1, field2) IN (
   SELECT [corresponding field list] 
   FROM ....
   )
;

Upvotes: 3

Related Questions