这是我的表结构(fun_users)
id first_name last_name email passkey place profession self_des profile_img user_type active last_login reg_date reported_banned
我的友谊表是
id user_id friend_id status date_request from_ip
这是查询即时消息,用于获取已登录用户朋友的详细信息
SELECT `fun_friends`.`id` as fid, `fun_users`.`id` as uid, `fun_users`.`first_name`, `fun_users`.`profile_img`, `fun_users`.`profession`, `fun_users`.`place` FROM (`fun_friends`) JOIN `fun_users` ON `fun_users`.`id`=`fun_friends`.`friend_id` WHERE (`fun_friends`.user_id= '".$_SESSION['user_row_id']."' AND `fun_friends`.`status` =1) OR (`fun_friends`.friend_id= '".$_SESSION['user_row_id']."' AND `fun_friends`.`status` =1)
结果是
fid uid first_name profile_img profession place
11 47 Agnii thumbs/2013-03-311364721555.jpg Software engineer somewhere
该查询返回登录用户的详细信息,而不是其朋友的详细信息。谁能帮我 ?
最佳答案
下面的查询使用一个子查询,该子查询获取特定用户的所有朋友。表fun_users
对子查询两次连接,因为子查询上有两个依赖于它的列。
SELECT a.id AS FID,
IF(a.user_ID = 'user_row_id_HERE', c.id, b.id) AS UID,
IF(a.user_ID = 'user_row_id_HERE', c.first_name, b.first_name) AS first_name,
IF(a.user_ID = 'user_row_id_HERE', c.last_name, b.last_name) AS last_name,
IF(a.user_ID = 'user_row_id_HERE', c.profile_img, b.profile_img) AS profile_img,
IF(a.user_ID = 'user_row_id_HERE', c.profession, b.profession) AS profession,
IF(a.user_ID = 'user_row_id_HERE', c.place, b.place) AS place
FROM
(
SELECT id, user_ID, friend_ID
FROM friendship
WHERE 'user_row_id_HERE' IN (user_ID, friend_ID) AND
status = 1
) a
INNER JOIN fun_users b
ON a.user_ID = b.id
INNER JOIN fun_users c
ON a.friend_ID = c.ID
因此,问题来了,这条线上会发生什么?
IF(a.user_ID = 'user_row_id_HERE', c.id, b.id) AS UID
基本上,它从子查询中测试
user_ID
的值是否等于当前用户。如果恰好相等,则将返回表fun_users c
中的列,反之亦然。要进一步获得有关联接的知识,请访问以下链接:
Visual Representation of SQL Joins
关于php - 从所需的表格帮助中获取 friend 详细信息,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15863077/