Forum

Posted: **11 Jul 2017, 09:27**

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]'  ";

Posted: **11 Jul 2017, 16:11**

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'] ."'";

Posted: **11 Jul 2017, 17:22**

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

Posted: **11 Jul 2017, 19:05**

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'] ."'"

Posted: **12 Jul 2017, 10:08**

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'] ."'";

Posted: **12 Jul 2017, 16:01**

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.

Posted: **12 Jul 2017, 17:13**

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'] ."'

 ";

Posted: **12 Jul 2017, 18:40**

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.

Forum

Select Friends id from different table

Select Friends id from different table

Select Friends id from different table

Select Friends id from different table

Select Friends id from different table

Select Friends id from different table

Select Friends id from different table

Select Friends id from different table

Select Friends id from different table