Sal Orozco Sal Orozco - 4 months ago 8
MySQL Question

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.

Answer

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"