php - SQL连接两个不同的表

Question

表中有列:

friends:  id | id1 | id2 | accepted | notification

users:    id | username | email | password

好友中的id1始终是好友请求的发送者，id2是他的好友。

$sql = "SELECT * FROM friends WHERE id1='$id' AND accepted=1 AND notification=1;";
$result = mysqli_query($conn, $sql);
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);

header("Content-Type: application/json");
echo json_encode($data);

$id 取自 session 。我想从 id(in users)=id2(如果是 friend )的用户那里获取用户名

$sql 会是什么样子？

Answer 1

不确定您想在逻辑上实现什么目标。这意味着什么:id(在用户中)= id2(如果是 friend )？你能用文字描述一下吗

也许这样的事情可以让您了解如何继续:

SELECT 
   sender.id1 as senderID,
   u.username as senderUsername,
   receiver.id1 as receiverID,
   u.username as receiverUsername
FROM users u 
INNER JOIN friends sender ON u.id = sender.id1
INNER JOIN friends receiver ON u.id = receiver.id2
WHERE sender.id1='$id' 
  AND receiver.accepted=1
  AND receiver.notification=1;