PHP MySQL Query Question / Help - Joining tables

Question

Hi, I need to help to show wall posts from the friends of the logged in user, and the user himself ofc.. :P
I've searched on every coding forums, and google, did not find the answer I was looking for :/

The Session for the logged in user are:

$_SESSION['userid']

This is the MySQL Query I have so far:

SELECT DISTINCT * FROM status_updates JOIN friends ON status_updates.member_id = friends.friend_with LEFT JOIN members ON status_updates.member_id = members.member_id ORDER BY status_id DESC LIMIT 0,10

The query outputs the Status Update from the friends right, but when it comes to the logged in user, the Status Update get duplicated, here's what it looks like: http://i30.tinypic.com/29bkqaw.png

There is only two entries in status_updates, one for Test Bruker 4 and one for Endre Hovde..

I'm logged in as Endre Hovde by the way.

---

I'll be thankful for any help I can get, credz for the best answer ;)
Thanks in advance! :)

// Endre Hovde @ rCon^

Answer 1

I think that you will get a duplicate for the same answer for every friend a user has. You have to split up your request in several parts, or you use a unique ID (preferably the comment ID) to group them with PHP. That can be achieved easily using an array and using the comment ID as the key for that array.

Answer 2

What about this:

$query = "select su.* 
          from status_updates su 
          where 
               su.member_id in (
                  select " . $_SESSION['user_id'] . " as member_id
                  UNION 
                  select fr.member_id 
                  from friends fr
                  where exists (select 1 from friends
                                where member_id = fr.member_id
                                and friend_with = " . $_SESSION['user_id'] . ")

               )
          order by su.status_id desc limit 0,10";