Reputation: 125
t_msg
msg_id | message | user_id
example data :
01 | hai how are you | 01
02 | will you marry me ? | 02
t_users
example data :
uid | username | full_name
01 | greg | Greg Fish
02 | drown | Leon Drown
t_friendship
example data :
uid | uid_fk | status
01 | 02 | friend
02 | 01 | friend
I have 3 tables relations. Now I want message from user show and from other user if status friend.
Example if uid 1 logged in:
uid 1
hai how are you
uid 2 (this show because uid 1 and uid 2 are friend.
will you marry me ?
and this is the query that I made :
$query = mysql_query("
SELECT
M.msg_id,
M.uid_fk,
M.message,
M.created,
U.full_name,
U.profile_pic,
U.username,
U.uid,
F.status,
F.uid
FROM
t_haps_wall M,
t_users U,
t_join_user F
WHERE
M.uid_fk=U.uid
Upvotes: 1
Views: 133
Reputation: 79979
You have to JOIN
the three tables, with ON f.uid_fk = u.uid
with the thread table, like so:
SELECT
M.msg_id,
M.uid_fk,
M.message,
M.created,
U.full_name,
U.profile_pic,
U.username,
U.uid,
F.status,
F.uid
FROM t_haps_wall M
INNER JOIN t_users U ON m.user_id = u.uid
INNER JOIN t_join_user F ON f.uid_fk = u.uid
Upvotes: 1