Select Friends id from different table

[JanMolendijk]

I have a friend-system that works with a database
I need from another table the user-id (id), so i can link to their profile

table: my_friends

Code: Select all

id  username  friend  

68 andreamein Wendy P 
67 Wendy P andreamein 
66 Vrouw Holland Wendy P 
65 Wendy P Vrouw Holland 

table: user

Code: Select all

id  name  email  pass  

0 Anonymous anonymous@hotmail.com 
1 Community -community@hotmail.com 

This is without the id from eatch user:

Code: Select all

<?php
// connect to the "tests" database
$conn = new mysqli('127.0.0.1', 'comments', '123456', 'comments');
// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}
// SELECT sql query
$sql = "SELECT `id`, `username`, `friend` FROM `my_friends` WHERE username='$users[name]'  "; 

// perform the query and store the result
$result = $conn->query($sql);
// if the $result contains at least one row
if ($result->num_rows > 0) {
  // output data of each row from $result
  while($row = $result->fetch_assoc()) {
    echo '<br> '. $row['friend']. ' ' ;
  }
}
else {
  echo '<br>No Friends';
}
$conn->close();
?>

Now i tried this but it is complete wrong

Code: Select all

// SELECT sql query
$sql = "SELECT A.`username`  , B.`name` , B.`id` 
FROM `my_friends`
AS A
INNER JOIN `user` AS B
ON B.`name` = A.`username` 
 WHERE username='$users[name]'  "; 
  

If the "username" from my_friends table is the same with the "name" from user table, try this:

Code: Select all

$sql = "SELECT A.username  , B.name , B.id 
FROM my_friends AS A 
INNER JOIN user AS B 
ON B.name = A.username 
 WHERE A.username='". $users['name'] ."'"; 

When i do this i getting only the id from one user
but i would like to have the id from all the users

It is normal in your results to get one id because you use this condition:

Code: Select all

WHERE A.username='". $users['name'] ."'"

I not know what ids you want to get.
Maybe something with the condition to friend field:

Code: Select all

WHERE A.friend='". $users['name'] ."'"

my english is not optimal sorry...

I want also from my friendlist, every single id from my friends with their names
Something like this (but code is wrong):

Code: Select all

$sql = "SELECT A.username, A.friend, B.id
 FROM my_friends AS A
 INNER JOIN user AS B
 ON B.name = A.username
 WHERE A.username ='". $users['name'] ."'
 WHERE A.friend='". $users['name'] ."'";

I not know what relationship is between your tables.
For a friend-system it is good to have the tables with relationship on ID:
Table user:

Code: Select all

id  name  email  pass

Table my_friends:

Code: Select all

idu  idf

Where "idu" is the user ID, and "idf" is the id of its friend.

Add an example with few rows from those table:
- Table user:

Code: Select all

5-6 rows..

- Table my_friends:

Code: Select all

5-6 rows..

- And, to get this result:

Code: Select all

result according to the rows added above.

The table has no relationship with each other
I understand that I should add id`s to my_friends table
But then I have to convert a whole script `I will not succeed`

is their any posibility ???

something like this

Code: Select all

$sql = "SELECT A.username, A.friend, B.id
 FROM my_friends AS A
 INNER JOIN user AS B
 ON B.name = A.username

 WHERE A.username ='". $users['name'] ."'";

$sql1 = "SELECT A.username, A.friend, B.id
 FROM my_friends AS A
 INNER JOIN user AS B
 ON B.name = A.username

 WHERE A.friend ='". $users['name'] ."'";

// perform the query and store the result
$result = $conn->query($sql);
$result1 = $conn->query($sql1);
// if the $result contains at least one row
if ($result->num_rows > 0) {
if ($result1->num_rows > 0) {
  // output data of each row from $result
  while($row = $result->fetch_assoc()) {
  while($row1 = $result1->fetch_assoc()) {
    echo '<br>  '. $row1['id'] .'  ' ;
 echo '<br> '. $row1['friend']. '  '. $row1['id']. '' ;
  }
} }
}
else {
  echo '<br>No Friends';

This with AND is also not working

Code: Select all

$sql = "SELECT A.username, A.friend, B.id
 FROM my_friends AS A
 INNER JOIN user AS B
 ON B.name = A.username

 WHERE A.username ='". $users['name'] ."'
 AND A.friend ='". $users['name'] ."'

 ";

The tables must have some relation between each other, to can make what you want.
The relation can be on id or other fields.
The idea is to have the value from one table into a column of the other table.

- If the code I gave not work, I not know other solution.