select 'whatever' from friends, and myself and grab data for that user

[TeddyKiller]

$query = "SELECT u.displayname, u.avatar, us.id, us.user_id, us.status, us.posted FROM `users` AS u JOIN `user_status` AS us ON u.id = us.user_id JOIN `friends` AS f ON us.user_id = f.friend_id WHERE f.user_id = '$user->id' ORDER BY us.posted DESC";

 

What that code does, is grab the statuses by the users friends and get the data of that status poster. This seems to work fine.. although I want to add in so it gets all statuses by friends, or by the user. ($user->id holds the id of the user)

 

All the methods I've tried, seems to duplicate displays.

 

I really need this sorting out =[

[TeddyKiller]

I now have this..

$query = "SELECT u.displayname, u.avatar, us.id, us.user_id, us.status, us.posted FROM users AS u, user_status AS us, friends AS f WHERE u.id = us.user_id AND f.user_id = '$user->id' AND (us.user_id = f.friend_id OR us.user_id = '$user->id') ORDER BY us.posted DESC";

 

On my account, it duplicates my results by 8... (ONLY MINE, NOBODY ELSES) but if I go onto my mates account, his status isn't duplicated.. at all.

[TeddyKiller]

It's grabbing extra data foreach of my friends. I have 8 friends, it duplicates by 8. Thats where the problem is.. but how do I fix it

[TeddyKiller]

Bump... I really need this sorting, I can't think why it multiples your own statuses by your own friends..

[ignace]

1. Post your entire code

2. You performed a JOIN, if the second table contains many records for your one user record then the data is duplicated

3. Chances are it also is duplicated for your friends, your account only shows duplicates because it probably only contains links to your friends while your friends only link to you once.

[TeddyKiller]

That was the whole query. My results come out with the rest in the while.

 

The problem was: (us.user_id = f.friend_id OR us.user_id = '$user->id') - For somereason, it was causing the duplication for $user->id.. and duplicating the status made by that id on that account by the amount of their friends. So.. what I did is this..

 

      $query = $db->execute("SELECT friend_id FROM friends WHERE user_id = '$user->id'");
      while($row = $db->fetchassoc($query)) {
          $friend_ids[] = $row['friend_id'];
      }
      $friend_ids[] = $user->id;
      $friend_ids = implode(', ', $friend_ids);
      
      $query = "SELECT u.displayname, u.avatar, us.id, us.user_id, us.status, us.posted FROM users AS u, user_status AS us WHERE u.id = us.user_id AND us.user_id IN ($friend_ids) ORDER BY us.posted DESC";

[fenway]

Or you could use a derived table.