php - 如何在跨三个表的 mysql 查询中使用联接

Question

首先我的 table :

+----------+      +--------------+     
| users    |      | users_groups |     +--------------+
+----------+      +--------------+     | groups       |
| user_id  |----->| user_id      |     +--------------+
| username |      | group_id     |<----| group_id     |
| realname |      +--------------+     | group_name   |
| password |                           | group_desc   |
+----------+                           +--------------+

在另一个 PHP 脚本中，我有一个变量 $group_id，现在我想要该组中的所有用户名和 user_ids ($group_id)。

我尝试了这个，但没有成功:

SELECT u.username, u.user_id
FROM users u
WHERE users_groups.group_id = $group_id

查询应该是什么样子？有连接的东西，不是吗？

我的 PHP 代码:

$group_id = $_POST["group_id"];

    $query_user = mysqli_query($db, 
                                    "SELECT 
                                    User.user_id, User.username
                                    FROM users User 
                                    INNER JOIN user_groups UserGroup
                                        ON User.user_id = UserGroup.user_id
                                    WHERE UserGroup.group_id = $group_id
                                    ");
    while ($row_user = mysqli_fetch_object($query_user)) {
        echo'
        '.$row_user->username.'<br>
        ';
    }

Answer 1

SELECT 
    User.user_id, User.username
FROM users User 
INNER JOIN users_groups UserGroup
    ON User.user_id = UserGroup.user_id
WHERE UserGroup.group_id = $group_id

希望这能正常工作!

编辑:

$group_id = mysqli_real_escape_string($db, $_POST["group_id"]);

    $query_user = mysqli_query($db, 
                                    "SELECT 
                                    User.user_id, User.username
                                    FROM users User 
                                    INNER JOIN users_groups UserGroup
                                        ON User.user_id = UserGroup.user_id
                                    WHERE UserGroup.group_id = '$group_id'
                                    ");
    while ($row_user = mysqli_fetch_object($query_user)) {
        echo $row_user->username.'<br>';
    }