我想用日期范围和复选框过滤数据。它适用于复选框，但我在添加一些日期范围查询时遇到此错误。

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\BMJSGCR\1\dt_rtrend1.php on line 51

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\BMJSGCR\1\dt_rtrend1.php on line 58

是否可以使用过滤器日期范围？因为我在数据库中使用类型数据日期时间。

这是我的代码

<?php
if (isset($_POST['chk'])) {
    $defect_query = "";
    foreach ($_POST['chk'] as $id => $data) {
        $defect_query = $defect_query . ", COUNT(CASE t_transaction.DEFECT_CLASS_ID WHEN " . $data . " THEN t_defect_class.DEFECT_CLASS_NAME END) AS `" . $data . "`";
    }

    //$machine        = $_POST['machine'];
    $start_date = $_POST['start_date'];
    $end_date = $_POST['end_date'];
    //$grade      = $_POST['grade'];
    $chk = $_POST['chk'];
    ?>

    <thead>
    <th class="text-center" >Date Process</th>
    <?php
    foreach ($_POST['chk'] as $id => $data) {
        $get_defect_name = mysqli_query($mysqli, "SELECT * FROM `t_defect_class` WHERE `DEFECT_CLASS_ID` = $data");
        $defect_name_table = mysqli_fetch_assoc($get_defect_name);
        ?>
        <th class="text-center"><?php echo $defect_name_table['DEFECT_CLASS_NAME']; ?></th>
    <?php } ?>
    </thead>
    <?php
    $modal = mysqli_query($mysqli, "SELECT t_name_file.PROCESS_TIME" . $defect_query . " FROM t_transaction INNER JOIN t_name_file ON t_transaction.NAMEFILE_ID=t_name_file.NAMEFILE_ID INNER JOIN t_defect_class ON t_transaction.DEFECT_CLASS_ID=t_defect_class.DEFECT_CLASS_ID GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME t_name_file.PROCESS_TIME BETWEEN '$start_date' AND '$end_date' ORDER BY t_name_file.PROCESS_TIME");

    $count = mysqli_num_rows($modal);

    if ($count == "0") {
        ?>
        <td class="text-center">NOT FOUND</td>
        <?php
    } else {

        while ($defect = mysqli_fetch_array($modal)) {
            ?>
            <tr>
                <td class="text-center"><?php echo $defect['PROCESS_TIME']; ?></td>
                <?php foreach ($_POST['chk'] as $id => $data) { ?>
                    <td class="text-center"><?php echo $defect[$data]; ?></td>
                <?php } ?>
            </tr>


            <?php
        }
    }
}
?>

谢谢

最佳答案

在查看您的代码段时，我有几个安全和效率问题，但要关注提出的问题，问题似乎是您在 $ 末尾有一个流氓 WHERE肯定会导致问题的模态。

如果这不能减轻错误，您应该改进您的问题以包含生成的确切错误消息。

而且我发现在 GROUP BY 子句中的第三个条件之前没有 ,。

虽然我真的希望我可以在将代码段发布到此处之前对其进行测试，但我认为脚本将提供更高的安全性、稳定性、效率和可读性。 (当然，不能保证没有bug)

<?php
if (empty($_POST['chk']
    || empty($_POST['start_date'])
    || empty($_POST['end_date'])
    || !is_array($_POST['chk'])
    || date('Y-m-d', strtotime($_POST['start_date'])) !== $_POST['start_date']
    || date('Y-m-d', strtotime($_POST['end_date'])) !== $_POST['end_date']) {
    echo "Missing/Invalid Submission Data";
} else {  // all expected submission data is present and sufficiently valid
    $defect_select_ext = "";
    foreach ($_POST['chk'] as &$data) {
        $data = (int)$data; // for security
        $defect_select_ext .= ", COUNT(CASE a.DEFECT_CLASS_ID WHEN $data THEN c.DEFECT_CLASS_NAME END) AS `$data`";
    }
    // I assume you are already connected...
    // avoid iterated queries but maintain order
    if (!$result = mysqli_query($mysqli, "SELECT DEFECT_CLASS_NAME FROM t_defect_class WHERE DEFECT_CLASS_ID IN (" . implode(',', $_POST['chk']) . ") ORDER BY FIELD(DEFECT_CLASS_ID," . implode(',', $_POST['chk']) . ")")) {
        echo "Syntax Error While Retrieving DEFECT CLASS NAMES";
    } else {
        ?>
        <table>
            <thead>
                <tr>
                    <th class="text-center">Date Process</th>
                    <?php
                    while ($row = mysqli_fetch_assoc($result)) {
                        echo "<th class=\"text-center\">{$row['DEFECT_CLASS_NAME']}</th>";
                    }
                    ?>
                </tr>
            </thead>
            <tbody>
            <?php
            $query = "SELECT t_name_file.PROCESS_TIME" . $defect_select_ext . "
                      FROM t_transaction a
                      INNER JOIN t_name_file b ON a.NAMEFILE_ID = b.NAMEFILE_ID
                      INNER JOIN t_defect_class c ON a.DEFECT_CLASS_ID = c.DEFECT_CLASS_ID
                      GROUP BY a.DEFECT_CLASS_ID,
                               b.PROCESS_TIME,
                               b.PROCESS_TIME BETWEEN '$start_date' AND '$end_date'
                      ORDER BY t_name_file.PROCESS_TIME");
            $colspan = sizeof($_POST['chk']) + 1;
            if (!$modal = mysqli_query($mysqli, $query)) {
                echo "<tr><td colspan=\"$colspan\" class=\"text-center\">Syntax Error @ Master Query</td></tr>";
            } elseif (!mysqli_num_rows($modal)) {
                echo "<tr><td colspan=\"$colspan\" class=\"text-center\">NOT FOUND</td></tr>";
            } else {
                while ($row = mysqli_fetch_array($modal)) {
                    echo "<tr>";
                        echo "<td class=\"text-center\">{$row['PROCESS_TIME']}</td>";
                        foreach ($_POST['chk'] as $data) {
                            echo "<td class=\"text-center\">{$row[$data]}</td>";
                        }
                    echo "</tr>";
                }
            }
        echo "</table>";
    }
}

附注我建议您将数据库表名和列名全部小写，以便更容易将您的表/列名与 MYSQL 关键字/函数区分开来。

关于php - 使用带有复选框和连接表的日期范围过滤数据，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/53038498/