Filter data using daterange with checkbox and join table

user_10538214 0
Viewed 68

I want to filter data with date range and checkbox. It works for checkbox but i got this error where i add some queries for date range.

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

Is it possible to use filter daterange ? because i use type data datetime in database.

here's my code 

<?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
        }
    }
}
?>

thank you

mysqli php
2 Answers

I have several security and efficiency concerns as I review your snippet, but to focus on the question asked, the issue seems to be that you have a rogue WHERE at the end of $modal that will surely be causing an issue.

If this doesn't alleviate the error, you should improve your question to include the exact error message that is generated.

And I've discovered that there is no , before the third condition in your GROUP BY clause.

And while I really wish I could test my snippet before posting it here, I think script will offer improved security, stability, efficiency, and readability. (Of course, no guarantees that it is bug-free)

<?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>";
    }
}

p.s. I recommend that you keep your database table names and column names in all lowercase so that it is easier to differentiate your table/column names from the MYSQL KEYWORDS/FUNCTIONS.

user_29434030

It's solved!, Thanks everyone!. here's my code

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."`";
  }

  if (isset($_POST['machine'])) {
    $machine        = $_POST['machine'];
  }
  $start_date   = $_POST['start_date'];
  $end_date     = $_POST['end_date'];
  // echo "<script>alert('".$start_date." - ".$end_date."')</script>";
  if (isset($_POST['grade'])) {
    $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




  if (isset($_POST['grade']) && isset($_POST['machine'])) {
    $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 WHERE t_name_file.PROCESS_TIME BETWEEN '".$start_date."' AND '".$end_date."' AND t_name_file.MACHINE_ID = '".$machine."' AND t_name_file.NAMEFILE_ID = '".$grade."' GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME");
  } else if (isset($_POST['machine']) && !isset($_POST['grade'])) {
    $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 WHERE t_name_file.PROCESS_TIME BETWEEN '".$start_date."' AND '".$end_date."' AND t_name_file.MACHINE_ID = '".$machine."' GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME");
  } else if (isset($_POST['grade']) && !isset($_POST['machine'])) {
    $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 WHERE t_name_file.PROCESS_TIME BETWEEN '".$start_date."' AND '".$end_date."' AND t_name_file.NAMEFILE_ID = '".$grade."' GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME");
  } else {
    $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 WHERE t_name_file.PROCESS_TIME GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME");
  }
  // echo "<script>alert(\"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 WHERE t_name_file.PROCESS_TIME BETWEEN '".$start_date."' AND '".$end_date."' AND t_name_file.MACHINE_ID = '".$machine."' AND t_name_file.GRADE = '".$grade."' GROUP BY t_transaction.DEFECT_CLASS_ID, t_name_file.PROCESS_TIME ORDER BY t_name_file.PROCESS_TIME\")</script>";
  $no = 0;
  while($defect=mysqli_fetch_array($modal)){
  $no++;

?>
  <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 
} 
} ?>
user_105382140
Related