MySQL-选择具有特定权限的用户(INNER JOIN） - MySQL - Select Users with certain privileges (INNER JOIN)

Hello我需要从名为"users"的表中选择所有具有特权的用户，1、2和3。之后，我将使用他们的ID在另一个名为"Status"的表中进行新的选择。

<?php
include_once("include/connection.php");
$sql = 'select * from USERS where Privileges != "4"';
$rs = mysqli_query($connect, $sql) or die ("error");
$op = mysqli_num_rows($rs);
while($row = mysqli_fetch_array($rs))
{
    $id = $row['ID'];
    $name = $row['Name'];
    $priv = $row['Privileges'];
    $sql = 'select * from Status where ID="$id"';
    $result = mysqli_query($connect, $sql) or die ("database error");
    $gmon = mysqli_num_rows($result);
    $login = $row['Login'];
    switch($login)
    {
        case "ONLINE":
            $login = "<font color='"#84FB84'"><strong>".$name."</strong></font>";
            break;
        case "OFFLINE":
            $login = "<font color='"#46AAEB'"><strong>".$name."</strong></font>";
            break;
    }
    $online_adms .= "<td>$login</td>";
}
?>
<?php echo $online_adms; ?>

更新我无法获得"$login"变量的输出。。。如果我尝试输出"$name"或"$id"，效果很好。。。但我需要输出"$login"。我想我可以在这里使用一些INNER JOin。。。

有线索吗？

谢谢。

解决方案

$sql = 'SELECT c.Name, c.ID, c.Privileges, d.Login FROM USERS c 
        INNER JOIN Status d 
        ON c.ID = d.ID
        where Privileges != 4';

您可以使用联接选择所有必需的字段

SELECT `ID`,`Name`,`Privileges`,`Login` 
 FROM `USERS` 
 INNER JOIN `Status`
 ON `USERS`.`ID` = `Status`.`ID`
 WHERE Privileges != 4

然后删除您的第二个查询

如果您刚刚遇到PHP语法错误，您忘记关闭while循环。这是更正后的代码：

$sql = 'select * from USERS where Privileges != "4"';
$rs = mysqli_query($connect, $sql) or die ("error");
$op = mysqli_num_rows($rs);
while($row = mysqli_fetch_array($rs))
{
    $id = $row['ID'];
    $name = $row['Name'];
    $priv = $row['Privileges'];
    $sql = 'select * from Status where ID="$id"';
    $result = mysqli_query($connect, $sql) or die ("database error");
    $gmon = mysqli_num_rows($result);
    $login = $row['Login'];
    switch($login)
    {
        case "ONLINE":
            $login = "<font color='"#84FB84'"><strong>".$name."</strong></font>";
            break;
        case "OFFLINE":
            $login = "<font color='"#46AAEB'"><strong>".$name."</strong></font>";
            break;
        default:
            $login = 'OOPS! The value of $login is '.$login.' that is why nothing is being printed. :(';
    }
}

此外，你可能想改进你的产品线：

$result = mysqli_query($connect, $sql) or die ("database error");

发件人：

$result = mysqli_query($connect, $sql) or die ("database error: ".mysql_error().'<br />query: '.$sql);

基本上，Login列应该在Status表中，但它没有被使用。试试这样的东西：

<?php
include_once("include/connection.php");
$sql  = 'SELECT USERS.*, STATUS.* FROM USERS INNER JOIN STATUS ';
$sql .= 'ON USERS.ID=STATUS.ID WHERE USERS.Privileges != "4"';
$rs = mysqli_query($connect, $sql) or die ("error");
$op = mysqli_num_rows($rs);
while($row = mysqli_fetch_array($rs))
{
    $id = $row['ID'];
    $name = $row['Name'];
    $priv = $row['Privileges'];
    $login = "<font color='"#46AAEB'"><strong>".$name."</strong></font>";
    $loginStatus = $row['Login'];
    switch($loginStatus)
    {
        case "ONLINE":
            $login = "<font color='"#84FB84'"><strong>".$name."</strong></font>";
            break;
        case "OFFLINE":
            $login = "<font color='"#46AAEB'"><strong>".$name."</strong></font>";
            break;
        default:
            $login = "<font color='"#999999'"><strong>".$name."</strong></font>";
            break;
    }
    $online_adms .= "<td>$login</td>";
}
?>
<?php echo $online_adms; ?>