php - PHP 中的 mysql 查询返回 0 个结果

Question

我正在尝试创建一个图表，该图表从我们的一个数据库中获取数据，并计算过去 12 个月中每个月遇到的问题数量。查询运行正常，但即使数据库中有与查询匹配的记录，它仍然返回 0 结果。我正在使用的代码如下...

 <?php
if (isset($_POST['dept'])) {
    $dept = $_POST['dept'];

} else {

    $dept = '';

}


for ($i = 1; $i <= 12; $i++) {
    $months[] = date("Y-m-d", strtotime(date('Y-m-01') . " -$i months"));

}

foreach ($months as $month => $value) {

    $theMonth = date("M", strtotime($value));
    $theYear  = date("Y", strtotime($value));

    $sql = "SELECT `c_ID`, `department`, `departmentSub`,  `todaysDate`, \n" . "SUM(CASE WHEN department='$dept' AND  departmentSub LIKE 'Loading Error' AND MONTH(todaysDate) = MONTH($value) AND YEAR(todaysDate) = YEAR($value) THEN 1 ELSE 0 END) AS loadingErrors,\n" . "SUM(CASE WHEN department='$dept' AND  departmentSub LIKE 'Bad Customer Service' AND MONTH(todaysDate) = MONTH($value) AND YEAR(todaysDate) = YEAR($value) THEN 1 ELSE 0 END) AS custService\n" . "FROM `nonConformance`";


    if ($result = mysqli_query($conn, $sql)) {
        // Return the number of rows in result set
        while ($row = $result->fetch_assoc()) {
            $loadingErrors = $row['loadingErrors'];
            $custService   = $row['custService'];
        }

    }
}
?>

我不明白为什么这没有返回任何结果，任何人都可以帮助我解决这个问题，我将不胜感激。

谢谢!

[示例数据] http://imgur.com/g0UzcpR

Answer 1

在您的查询中，您没有将 $value 作为字符串传递。尝试用撇号将 $value 括起来:

 $sql = "SELECT `c_ID`, `department`, `departmentSub`,  `todaysDate`, \n" . "SUM(CASE WHEN department='$dept' AND  departmentSub LIKE 'Loading Error' AND MONTH(todaysDate) = MONTH('$value') AND YEAR(todaysDate) = YEAR('$value') THEN 1 ELSE 0 END) AS loadingErrors,\n" . "SUM(CASE WHEN department='$dept' AND  departmentSub LIKE 'Bad Customer Service' AND MONTH(todaysDate) = MONTH('$value') AND YEAR(todaysDate) = YEAR('$value') THEN 1 ELSE 0 END) AS custService\n" . "FROM `nonConformance`";

MONTH($value) 变为 MONTH('$value') 并且 YEAR($value) 变为 YEAR(' $value')

此外，请确保在 foreach 中输出 $loadingErrors 和 $custService 的值($months as $month => $value) { 循环，否则具有零值的日期将覆盖前一个。