MySQL coding problem.

Question

How can I join users with users_friends.user_id when user_id = '" . $user_id . "'

And join users with users_friends.friend_id when friend_id = '" . $user_id . "'

if user_id = '" . $user_id . "'

INNER JOIN users ON users_friends.user_id = users.user_id

if friend_id = '" . $user_id . "'

INNER JOIN users ON users_friends.friend_id = users.user_id

Current MySQL Code.

SELECT COUNT(user_id) as friends
FROM users_friends
WHERE (user_id = '" . $user_id . "' 
OR friend_id = '" . $user_id . "')
AND friendship_status = '1'

Answer 1

 (SELECT * FROM users_friends JOIN users ON users.user_id = users_friends.friend_id 
    WHERE   users_friends.friend_id = '" . $user_id . "')
 UNION
 (SELECT * FROM users_friends JOIN users ON users.user_id = users_friends.user_id 
    WHERE users_friends.user_id = '" . $user_id . "')

Something like this may do the work.

As you are looking in user_friends table you should use it to join with users table.

Answer 2

if i understood you correctly then try something like this:

call list_user_friends(1);

call list_all_friends_of_user(1);

full script:

-- TABLES

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
)
engine=innodb;

drop table if exists user_friends;
create table user_friends
(
user_id int unsigned not null,
friend_user_id int unsigned not null,
primary key (user_id, friend_user_id) -- note clustered composite PK (innodb only)
)
engine=innodb;

-- STORED PROCEDURES

drop procedure if exists insert_user_friend;

delimiter #
create procedure insert_user_friend
(
in p_user_id int unsigned,
in p_friend_user_id int unsigned
)
proc_main:begin

  if p_user_id = p_friend_user_id then
    leave proc_main;
  end if;

  insert into user_friends (user_id, friend_user_id) values (p_user_id, p_friend_user_id);

end proc_main #

delimiter ;

drop procedure if exists list_user_friends;

delimiter #
create procedure list_user_friends
(
in p_user_id int unsigned
)
proc_main:begin

  select
    u.*
  from
    user_friends uf
  inner join users u on uf.friend_user_id = u.user_id
  where
    uf.user_id = p_user_id
  order by
   u.username;

end proc_main #

delimiter ;

drop procedure if exists list_all_friends_of_user;

delimiter #
create procedure list_all_friends_of_user
(
in p_user_id int unsigned
)
proc_main:begin

  select
  u.*
  from
  users u
  inner join
  (
  select friend_user_id as user_id from user_friends where user_id = p_user_id
  union
  select user_id from user_friends where friend_user_id = p_user_id
  ) all_friends
  on u.user_id = all_friends.user_id
  order by
  u.username;

end proc_main #

delimiter ;

-- TEST DATA

insert into users (username) values ('f00'),('bar'),('alpha'),('delta'),('omega'),('theta');

call insert_user_friend(1,2);
call insert_user_friend(1,3);
call insert_user_friend(1,4);
call insert_user_friend(1,1); -- oops

call insert_user_friend(2,1);
call insert_user_friend(2,5);

call insert_user_friend(4,1);

call insert_user_friend(6,1);

-- TESTING (call these sproc from your php !)

call list_user_friends(1);

call list_all_friends_of_user(1);