query in database fetching

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";

}

What result are you getting? And what result do you want to get? — Mureinik, Aug 08 '15 at 10:50
i'm not sure that IN values is right , what is $row_post['frnd_id'] values? — Cyrus Raoufi, Aug 08 '15 at 10:53
what is that "$row_post" ? is that a result set? are fetching it from result set or u already stored it in an array? — Rafique Mohammed, Aug 08 '15 at 11:15
When i run query from post_id [1] it gives me the correct result of frnd_id [1,2] but when i run it from post_id [2] it gives me the same result of frnd_id[1,2] not from frnd_id[3] — Omkar, Aug 08 '15 at 11:27
So you want to display posts posted by you only on your friends pages? And not on user pages which are not your friends? — Jo Smo, Aug 08 '15 at 11:46

Jo Smo · Answer 1 · 2015-08-10T07:11:24.090

0

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'] . "";

edited Aug 10 '15 at 07:11

answered Aug 08 '15 at 12:00

Jo Smo

6,923
9
47
67

No its not working its going on first row and coming with same result again in short for second user id it shows result of first user id – Omkar Aug 08 '15 at 12:59
@Omkar can i see the PHP statement where you query the data/fetch the rows? It's just a hunch... But i think that you are missing a loop. `while ($row = $result->fetch_assoc()) {}` and not just `$row = $result->fetch_assoc()` – Jo Smo Aug 08 '15 at 14:26
$query_post="select * from upload_post.post_details pd where pd.post_status=1 $result_post=mysql_query($query_post);$row_post=mysql_fetch_assoc($result_post); $row_post=mysql_fetch_assoc($result_post); $query_img="SELECT * FROM upload_post.post_details pd INNER JOIN upload_post.image_post_details ipd ON pd.post_id = ipd.post_id WHERE pd.profile_id = " .$_SESSION['pro_id'] . " AND pd.frnd_id = " . $row_post['frnd_id'] . ""; $result_img=mysql_query($query_img);echo "
".$query_img."
";while($row_img=mysql_fetch_assoc($result_img)) – Omkar Aug 10 '15 at 06:49
Its gives correct result for 1 and 2 row because they have common friends when displaying the third row that is the 4th user it displays the result of 1 and 2 row again plz help me – Omkar Aug 10 '15 at 06:57
Updated answer working properly,but the third one which is not in my friend list its getting same result in my output – Omkar Aug 10 '15 at 08:01
How does you database look now? Please list all values for `Friends_details`, `post_details` and `image_post_details` – Jo Smo Aug 10 '15 at 11:40
Friends_details Friend_id Profile_id Friend_with 1 1 3 2 3 1 3 2 3 4 3 2 5 7 2 6 2 7 7 7 8 8 8 7 9 3 7 10 7 3 post_details post_id profile_id Frind_id 1 1 1,2 2 2 3,4,5,6 3 3 1,2,3,4,9,10 7 7 5,6,7,8,9,10 8 8 7,8 Img_post_details Image_id Post_id 1 1 2 2 4 3 5 7 6 8 – Omkar Aug 10 '15 at 13:08
Just trying to figure out if i understood you right. So the `user_id` of the third user is 3 and yours is 1? And the third user can see your posts but he shouldn't? – Jo Smo Aug 10 '15 at 16:42
By the way... Why are `user_id` and `profile_id` different in `user_details`? don't they serve the same purpose? – Jo Smo Aug 10 '15 at 16:43
And update the data in your question please, because it's kind of hard to read here in comments. Thank you. – Jo Smo Aug 10 '15 at 16:44
I Update my data in question...it work good still 3rd user after if i add 4th user its stop working 4th one and next all (5,6,7..) Cant see thier own post as well as friends post.. – Omkar Aug 11 '15 at 10:29
You have completely changed your SQL statement now. Please describe what you are trying to archive again. I'm confused. And put braces around AND and OR. Because AND has priority over OR, and will be executed first. Maybe this is why your query is failing. Take a look at this post: http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or – Jo Smo Aug 11 '15 at 13:43

query in database fetching

1 Answers1