MYSQL Join tables and display data from a contact list

Question

Table users:

  id firstname lastname    email         avatar
  1     Coman      Paul   x@y.com    ./images/a.png

Table friendships:

 id user1 user2
  1    1     2
  2    1     5
  3    3     1
  4    2     3

User 1 is friend with 2,3,5

User 2 is friend with 1,3

I tried this:

       $querym = mysql_query("SELECT  user1
       FROM    friendships
       WHERE   user2 = '1'
       UNION ALL
       SELECT  user2
      FROM    friendships
       WHERE   user1 = '1'"); 

    $numrows = mysql_num_rows($querym);

if($numrows!=0)
{

Echo '<div id="members">    <ul >';
while ($row = mysql_fetch_assoc($querym))
{


$firstname = $row['firstname'];
$lastname = $row['lastname'];
$email = $row['email'];
$avatar = $row['avatar'];
Echo '
<li>
.............

It will display all my friend but i can;t see they're names... I also tried

$querym = mysql_query("SELECT users.*, friendships.* FROM users, friendships WHERE     users.id=friendships.user1 AND( friendships.user1='1' OR friendships.user2='1')");

but I got an error...

Warning: mysql_num_rows() expects parameter 1 to be resource, null given in C:\xampp\htdocs\OnTheRunningLine\membri.php on line 346

Please help me with a core for displaying my friends(users.id=1) and data about them with explaining why because i need to learn it.

Oh, and if you can show me a code that will display after these results, the other users that are not friends with users.id = 1 would be great !

possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — John Conde, Feb 23 '14 at 18:10

Tony Hopkinson · Accepted Answer · 2014-02-16T10:13:15.587

0

Would be

select u.firstname, u.lastname,u.email,u.avatar from users u
inner join friendships f on f.user2 = u.id 
where f.user1 = 1
union
select u.firstname, u.lastname,u.email,u.avatar from users u
inner join friendships f on f.user1 = u.id 
where f.user2 = 1

I think given your intent.

edited Feb 16 '14 at 10:13

answered Feb 15 '14 at 10:40

Tony Hopkinson

20,172
3
31
39

not friends would be just changing the where statements, not sure how useful that query would be though... – Tony Hopkinson Feb 15 '14 at 11:02
i were still getting the same error until i added `$numrows = mysql_num_rows($querym) or die (mysql_error()); ` and i found out that: _Unknown column 'u.firstname' in 'field list'_. I changed letter "u" in your code with "users" and it works like a charm ! **Thank you !** – Coman Paul Feb 16 '14 at 08:35
Do you have any idea if it's possible to display the rest of users wich are not friends with that user (eg: id=1 ) ? I tried to replace "=" operathor with "!=" but it displays all users.... – Coman Paul Feb 16 '14 at 08:52
Something like Select u.firstname, u.lastname, u.email, u.avatar from users u join (Select distinct user1 as auser from friendships where user1 <> 1 and user2 <> 1 union select disctinct user2 from friendhsips where user2 <> 1 and user1 <> 1) notmyfriends on notmyfriends.auser = u.id would be one way – Tony Hopkinson Feb 16 '14 at 10:21
It doesn't work...i want to display the result wich are not displayed in the first time(when i show the list of friends). It seems Your querry doesn't work because in the friendships table there are links between users that are friends of user.id=1, or not...so it will display all users... – Coman Paul Feb 19 '14 at 11:32
Ask another question then mate. – Tony Hopkinson Feb 19 '14 at 11:54

MYSQL Join tables and display data from a contact list

1 Answers1