数据库提取中的查询

Question

Following query where I am trying to display post on my friends profile but it display on both of my friends and not friend from user table

my database structure is,

Friends_details

Friend_id----Profile_id------Friend_with
1----------------1---------------- 2
2---------------- 2 ---------------- 1
3 ---------------- 1 ---------------- 3
4---------------- 3---------------- 1
5---------------- 4---------------- 3
6---------------- 3 ---------------- 4

post_details
post_id---------------- profile_id--------------- Frind_id

1---------------- 1---------------- 1,2
2---------------- 2---------------- 1,2
3---------------- 3---------------- 3,4,5,6
4---------------- 4---------------- 5,6

Img_post_details
Image_id---------------- Post_id

1---------------- 1
2---------------- 2
3---------------- 3
4---------------- 4

$fid[]=$row_post['frnd_id'];
//echo $fid;
foreach($fid as $frnd)
{
    $excludes=explode(',',$frnd);
    //print_r(explode(',' ,$frnd));
    //\$a=print_r($excludes);
    $query_img="SELECT * FROM upload_post.post_details pd
    INNER JOIN upload_post.image_post_details ipd
    ON pd.post_id = ipd.post_id
    INNER JOIN social_panel_db.friends_details fd ON fd.friends_id=pd.frnd_id
    WHERE pd.profile_id=".$rows['profile_id']." OR pd.profile_id=".$_SESSION['pro_id']."
    AND fd.req_friend_profile_id IN(".$frnd.") OR fd.profile_id IN(".$frnd.") AND fd.frend_request_status=2 AND fd.profile_id=".$_SESSION['pro_id']." OR fd.req_friend_profile_id=".$_SESSION['pro_id']." order by pd.post_id desc";

}

Answer 1

You need to join another table to get the id of your friends profile. And when 2 people become friends, you should enter 2 values (for the schema you are currently using).

Example:

Friends_details
Friends_id  Profile_id  Friend_with status
1   1   2   conf
2   2   1   conf

This way it goes both ways, user with id 1 is friends with user with id 2 and vice versa.

$query_img="SELECT * FROM upload_post.post_details pd 
INNER JOIN upload_post.image_post_details ipd 
ON pd.post_id = ipd.post_id 
INNER JOIN Friends_details fd
ON fd.Friends_id = pd.Frnds_id 
WHERE pd.profile_id = " . $_SESSION['pro_id'] . " 
AND fd.Friend_with = " . $row_post['frnd_id'] . "";