[php] Continuing query brain fail

[JonnySnip3r]

Please are going to start hating me about this haha.

 

Hey guys i have been trying to do this for a few hours and my brain is hurting haha hope someone can help. Ok here is what i am trying to do. I have been making a website that allows users to make friends below is my friends table::

 

NAME: member_friends

id

friend_id << Person who has been sent the friend request

my_id << Person who sent the friend request

friend_since << when did they become friends

is_friend << is set to 0 then friend request pending

 

and this is the member_topics

message_id

member_id << this stores the id of the member from the members table

message_contents << stores their message

message_date

message_reported << if set to 1 the message has been reported

 

Ok basically i want the messages to be displayed on the members home page if they are friends, so if my name is JamesDean and i befriended a person called Harry if i post a message i want harry to see this and any other friends i may have. Same goes for their messages.

 

Sorry if im confusing you stay with me.

 

Ok here is what i am trying to do (did i mention im fairly new to php so please if im doing something wrong dont hurt me (: )

 

$query = mysql_query("SELECT * FROM member_friends");
while($row = mysql_fetch_array($query)){
       $friend_id = $row['friend_id']; // Ok this is where i want the while loop to loop through all of my friends
       // Ok set up the new filter im gunna get lost here now
      $sql = mysql_query("SELECT a.*,b.* FROM member_topics as a, member_friends as b WHERE (a.member_id = b.my_id OR a.member_id = b.my_id) 
								AND ((b.friend_id = '$friend_id') AND b.my_id = '$my_id') ORDER BY a.message_id DESC");
     // $my_id is a variable created from session so thats ok and the $friend_id i want that to change fromt he first loop so the statement changes for all of my friends.
     // Sorry for the query above i have tried that many i dont even know my gender anymore 
    // So while loop in a while loop i think
     while($row = mysql_fetch_array($sql)){
			$message_id = $row['message_id'];
			$member_id = $row['member_id'];
			$message_contents = $row['message_contents'];
			$message_date = $row['message_date'];
			$message_type = $row['message_type'];
			$message_reported = $row['message_reported];


}
}

 

something like this hope someone can point me in the right direction before my brain fails thanks in advanced!!!!!!!

 

[Psycho]

The problem here is that the friend ID can be in the "my_id" column or the "friend_id" column depending upon who initiated the request. Regarding your query above, I see no reason to pull records from the "member_friends" since it doesn't look like you intend to use them. (I'd also suggest not using "a" and "b" as aliases. Aliases are supposed to make it easier for a human to interpret the code - use names that mean something.

 

I see two soolutions. I'll let you do some testing to figure out which is more efficient.

 

SELECT *
FROM topics
WHERE member_id IN (SELECT my_id
                    FROM member_friends
                    WHERE friend_id = '$user_id')
   OR member_id IN (SELECT friend_id
                    FROM member_friends
                    WHERE my_id = '$user_id')

 

SELECT t.*
FROM topics t
JOIN member_friends mf1
    ON mf1.my_id = t.member_id
JOIN member_friends mf2
    ON mf2.friend_id = t.member_id
WHERE mf1.friend_id = '$user_id'
   OR mf2.my_id = '$user_id'

[Psycho]

After a second thought I think the first query can be simplified a bit by using UNION

SELECT *
FROM topics
WHERE member_id IN (SELECT my_id as id
                    FROM member_friends
                    WHERE friend_id = '$user_id'
                        UNION
                    SELECT friend_id as id
                    FROM member_friends
                    WHERE my_id = '$user_id')

 

None of these are tested, but should point you in the right direction if they don't work out of the box.

[JonnySnip3r]

Thank you very much i will try them out now

 

Thanks again!!