CODEWITHSUNDEEP
phpmysql
Sal Orozco
Sal Orozco

Reputation: 45

MYSQL AND PHP joining 2 queries

query to show post made by users.

$sql = "SELECT B.title, B.article, B.article_id, B.posted_by, B.users_id,DATE_FORMAT(updated, '%m-%d-%Y %l:%i:%s') AS updated , U.user_pic_path, U.user_id, U.username, I.filename
        FROM users U 
        INNER JOIN blog B
        ON B.posted_by = U.user_id
        LEFT OUTER JOIN images AS I 
        ON B.image_id = I.image_id
        WHERE B.users_id = ?
        AND B.posted_by = ?
        ORDER BY updated  DESC"

Query to select users who are my friends. 

$sql = "SELECT F.status,U.user_id, U.username, U.email, U.user_pic_path
      FROM users U, friends F
      WHERE
      CASE
      WHEN F.friend_one =? 
      THEN F.friend_two = U.user_id  
      WHEN F.friend_two = ? 
      THEN F.friend_one= U.user_id 
      END 
      AND F.status = 2";

So I want to show all blog post made by users who are my frineds. To do that I would have to join both join both queries. I'm stuck, have no clue how I could do that.

Upvotes: 0

Views: 27

Answers (1)

Grommy
Grommy

Reputation: 367

You can do it with 'WHERE id_users IN (SELECT())':

Posts query WHERE id_users IN (friends query).

Something like this:

$sql = "SELECT B.title, B.article, B.article_id, B.posted_by, B.users_id,DATE_FORMAT(updated, '%m-%d-%Y %l:%i:%s') AS updated , U.user_pic_path, U.user_id, U.username, I.filename
        FROM users U 
        INNER JOIN blog B
        ON B.posted_by = U.user_id
        LEFT OUTER JOIN images AS I 
        ON B.image_id = I.image_id
        WHERE B.users_id IN (
           SELECT U.user_id
           FROM users U, friends F
           WHERE
           CASE
           WHEN F.friend_one =? 
           THEN F.friend_two = U.user_id  
           WHEN F.friend_two = ? 
           THEN F.friend_one= U.user_id 
           END 
           AND F.status = 2
        )
        AND B.posted_by = ?
        ORDER BY updated  DESC"

Upvotes: 2

Related Questions