php - SQL 好友列表创建

Question

我不确定如何实际插入 SQL 命令来在两个用户之间建立“友谊”。我已经在 PHPMyAdmin 中创建了表格 我不知道该怎么做才能创建类似于下面链接的 friend 列表。如果您需要更多信息，请告诉我。

1. Buddy List: Relational Database Table Design
2. Facebook database design?

PHP 查询:

if(!empty($_POST)) 
{
$searchParams=$_POST['search'];
$appUser = $_POST['username'];

//check if username and or email is already registered
$query = " SELECT 1 FROM Users WHERE username = :user OR email = :email";
//now lets update what :user should be
$query_params = array(
    ':user' => $searchParams,
    ':email'=> $searchParams,
);

try {
    // These two statements run the query against your database table. 
    $stmt   = $db->prepare($query);
    $result = $stmt->execute($query_params);
}
catch (PDOException $ex) 
{

}

//check results of above query and determine if username or email already exists, output results
$row = $stmt->fetch();
if ($row) {

    echo '{"success":2}';

$currentUser = mysqli_result(mysqli_query("SELECT UserID FROM Users WHERE username = $appUser"),0);
$addedUser = mysqli_result(mysqli_query("SELECT UserID FROM Users WHERE username = $searchParams"),0);


   $query2 = "Insert INTO Friends (IDUser,FriendID) VALUES (:username,:friend)"; 

   $query_params = array(
    ':username' => $currentUser,
    ':friend' => $addedUser
    );

 $stmt   = $db->prepare($query2);
 $result = $stmt->execute($query_params);

  echo '{"success":1}';  
}
}
 else
{

}   

我的表格如下:

用户:

friend :

当前用户表数据:

Answer 1

假设您的用户表中有这样的数据:

insert into users (iduser, username) values (1, 'amy');
insert into users (iduser, username) values (2, 'bill');
insert into users (iduser, username) values (3, 'chris');

假设您通过 friend 表中的记录代表用户之间的“友谊”，您将创建这样的友谊:

insert into friends (iduser, friendid) values (1, 2);

这将在用户 ID 1 (amy) 和用户 ID 2 (bill) 之间建立关系。

然后，要随后查找用户的所有 friend ，您可以执行以下操作:

select username from users u
                  inner join friends f on f.friendid = u.iduser
where f.iduser = 1

这会给你艾米所有 friend 的名字。

为了回应您关于根据姓名动态添加 friend 的评论，您可以这样做。此处艾米已登录到您的站点，因此为了简化查询，我们假设您已经可以访问她的用户 ID 1。

insert into friends (iduser, friendid)
select 1, iduser from users where username = :friendusername;