Gab gab Gab gab - 1 month ago 6
SQL Question

Mysql select query for getting current user post and followed friend post

My current select query statement:

SELECT
T.postID,
T.message,
T.time,
U.userID,
U.name,
U.username,
U.picture_url
FROM
post AS T,
users AS U
WHERE
T.postID = '$uid' //$uid holds the id of the current logged in user
order by T.postID DESC;


My select query outputs the post of the current logged in user but I want to output the post of the followed user of the current logged in user as well, how can I do it?

USER TABLE:

+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| userID |int(11) | NO | PRI | NULL | auto_increment |
| name |VARCHAR(60) | NO | | NULL | |
| username |VARCHAR(20) | NO | | NULL | |
| picture_url|VARCHAR(200)| NO | | NULL | |
+------------+------------+------+-----+---------+----------------+


POST TABLE:

+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| postID |int(11) | NO | PRI | NULL | auto_increment |
| pUserID |int(11) | NO | | NULL | |
| message |VARCHAR(140)| NO | | NULL | |
| time |datetime | NO | | NULL | |
+------------+------------+------+-----+---------+----------------+


FOLLOW USER TABLE

+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| fid |int(11) | NO | PRI | NULL | auto_increment |
| userId1 |int(11) | NO | | NULL | |
| userId2 |int(11) | NO | | NULL | |
+------------+------------+------+-----+---------+----------------+

Ram Ram
Answer

First it is recommended to use explicit JOINS over implicit CROSS JOIN

Try the following query.

 SELECT
    T.postID, 
    T.message, 
    T.time,
    U.userID,
    U.name, 
    U.username, 
    U.picture_url,
    F.userID2,
 FROM
    users AS U  
 INNER JOIN 
    follow_user AS F
 ON U.userID = F.userId1    
 INNER JOIN 
    post AS T
 ON T.pUserID = U.userID OR T.pUserID = F.userId2 
 WHERE
    U.userID = '$uid' //$uid holds the id of the current logged in user
    order by T.postID DESC;