变量为选择约束

Question

我正在设置一个包含数组的变量，作为对选择SQL语句的约束。但是，约束似乎仅适用于数组中的一个数据。为什么这是？

代码下面：

<?php
include 'connection.php';
$Date = $_POST['date'];
$Unavail = 0;
$Avail = 0;
$Availid = 0;
$low = 99999;

$query = "SELECT username FROM daysoff WHERE date = '$Date'";               
$dayresult = mysql_query($query);                                                         
while($request = mysql_fetch_array($dayresult)) {                           
    $Unavail = $request;
    echo "<span>" . $Unavail['username'] . " is unavailable.</br>";
}   
$query1 = "SELECT Username, name, work_stats FROM freelance WHERE Username != '$Unavail[username]'";
$dayresult1 = mysql_query($query1);                                                      
while($request1 = mysql_fetch_array($dayresult1))   {
        echo "<span>" . $request1['name'] . " is available.</br>";
if ($request1['work_stats']<=$low) {                                
    $low = $request1['work_stats'];                             
    $Availid = $request1['name'];
    }}
    echo "<span>" . $Availid . " is available on " . $_POST['date'] . " and is on workstat level " . $low . ".</span></br>";
?>  

输出在第一个回声中显示两个名称，但随后显示了第二个回声中可用的其中一个名称（这些回声仅作为我的测试的一部分），非常感谢

Answer 1

第一个查询可以具有多个结果。

SELECT username FROM daysoff WHERE date = '$Date'

假设是否给出了两个行：Dave和John。

您只保留最后一个记录，因此看起来像Dave 是可用。

您可能应该做类似：

的事情

$query = "SELECT username FROM daysoff WHERE date = '$Date'";               
$dayresult = mysql_query($query);       
$unavailable_users = array();                                                  
while($request = mysql_fetch_array($dayresult)) {                           
    $unavailable_users[] = $request["username"];
    echo "<span>" . $Unavail['username'] . " is unavailable.</br>";
}   
$query1 = "SELECT Username, name, work_stats FROM freelance
    WHERE NOT Username IN ('" . implode("','", $unavailable_users) . "')";
// etc  

---

或一口气使用LEFT JOIN：

SELECT `Username`, `name`, `work_stats`
FROM `freelance`
    LEFT JOIN `daysoff` ON `freelance`.`Username` = `daysoff`.`username`
        AND `daysoff`.`date` = '$Date'
WHERE
    `daysoff`.`username` IS NULL