MySQL query problem.

Question

When a user sends a friend request and accepts it the friends info and pic are displayed perfectly on the users side but on the friends side the friends own pic and info is displayed instead of the user who friended the friend how can I fix this problem?

users friends table

CREATE TABLE users_friends (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED UNSIGNED NOT NULL,
  friend_id INT UNSIGNED NOT NULL,
  friendship_status TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  date_created DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY user_id (user_id),
  KEY friend_id (friend_id)
);

My current MySQL code

  SELECT users.*, 
         users_friends.*
    FROM users
    JOIN users_friends ON users.user_id = users_friends.friend_id 
   WHERE (   users_friends.user_id = '" . $user_id . "' 
          OR users_friends.friend_id = '" . $user_id . "')
     AND users_friends.friendship_status = 1
GROUP BY users_friends.date_created

Answer 1

Can't use this same query for both tasks.

That is, you could use the same query,

but

when you add the data (at the time a user accepts a friend request) you have to add two rows to the users friends table.

edit, explained in more detail

Right now when user a friends user b you add one entry to the user friends table

user a is friend of user b.

If you also add

user b is friend of user a (<-- the 2nd row)

you could use the same query you have above except change

WHERE (   users_friends.user_id = '" . $user_id . "' 
          OR users_friends.friend_id = '" . $user_id . "')

to

WHERE (   users_friends.user_id = '" . $user_id . "' )

---

Or, you could write sql that has the following logic (both cases).

• I have a friend that I friended and
• I am a friend that was friended.

Right now you do something else.