按状态计数ID与日期范围

Question

因此，我想通过创建，分配，交付＆amp;取消。因此，我开始通过按日期范围过滤从SQL获取数据。但是，当我试图通过区分状态进行计数时，结果仅计算全部不考虑状态。我的错误在哪里？我找不到。帮助我，所以下面是我的代码，结果表明这样。

创建的总任务=警告：c：\ xampp \ htdocs \ tms \ tms \ des_rpt.php in Line 98 （应为0）

总待定任务=警告：c：\ xampp \ htdocs \ tms \ tms \ des_rpt.php in Line 99 （应为1）

总任务= 4（应为2）

总数取消=警告：c：\ xampp \ htdocs \ tms \ tms \ tms \ des_rpt.php在第101行上 （应该是1）

'''

<?php
//filter by date range
print "<form name=history>";
print "<table><tr>";
print "<td><b>From :</b></td>";
print "<td><b>To :</b></td></tr>";
print "<tr><td><input type=date name=tarikh1 value=\"$tarikh1\"></td>";
print "<td><input type=date name=tarikh2 value=\"$tarikh2\"><input type=hidden name=action value=cari1></td>";
print "<td><input type=submit value=SUBMIT></td>";
print "</tr></table></form>";
print " <br/>\n";

$sql1 = "SELECT * FROM task WHERE dateupdate >= '$tarikh1' AND dateupdate <= '$tarikh2'";
$a = 0;
$result1 = mysqli_query($conn, $sql1);
if(mysqli_num_rows($result1) > 0)
{
while($row1 = mysqli_fetch_array($result1))
{
    $id[] = $row1[1];
    $from[] = $row1[5];
    $to[] = $row1[6];
    $cdate[] = $row1[9];
    $ajen[] = $row1[11];
    $stat[] = $row1[13];
    $udate[] = $row1[14];
    $utime[] = $row1[15];
    $a++;
}
mysqli_free_result($result1);
} 

if (!empty($id))
{
 $tid=$id;
    foreach (array_unique($ajen) as $agent){
    $b = 0;

       for ($idk = 0; $idk < $a; $idk++)
       {
        if ($agent == $ajen[$b])
        {
            if ($stat[$b] == 'ASSIGNED'){
                $cntasg = count($tid);
            }

            if ($stat[$b] == 'CREATED'){
                $cntcreate = count($tid);
            }

            if ($stat[$b] == 'DELIVERED'){
                $cntdel = count($tid);
            }

            if ($stat[$b] == 'CANCEL'){
                $cntccl = count($tid);
            }
        }
       }


    } ?>

<p><b><font face=verdana>Total Task Created = <?php echo $cntcreate;  ?></font></b></p>
<p><b><font face=verdana>Total Pending Task = <?php echo $cntasg;  ?></font></b></p>
<p><b><font face=verdana>Total Task Delivered = <?php echo $cntdel;  ?></font></b></p>
<p><b><font face=verdana>Total Task Cancelled = <?php echo $cntccl;  ?></font></b></p>

<?php
}else{
    echo "<h1>-NO RELATED TASK ON THIS DATE!-</h1>";
}?>

Answer 1

如果 elseif ，则需要替换所有。

if ($stat[$b] == 'ASSIGNED'){
  $cntasg = count($tid);
}elseif ($stat[$b] == 'CREATED'){
  $cntcreate = count($tid);
}elseif ($stat[$b] == 'DELIVERED'){
  $cntdel = count($tid);
}elseif ($stat[$b] == 'CANCEL'){
  $cntccl = count($tid);
}

Answer 2

我对您的查询进行了一些更改，请检查列名＆amp;如果需要进行更改。让我知道您是否在查询中遇到任何问题。

<?php
//filter by date range
print "<form name=history>";
print "<table><tr>";
print "<td><b>From :</b></td>";
print "<td><b>To :</b></td></tr>";
print "<tr><td><input type=date name=tarikh1 value=\"$tarikh1\"></td>";
print "<td><input type=date name=tarikh2 value=\"$tarikh2\"><input type=hidden name=action value=cari1></td>";
print "<td><input type=submit value=SUBMIT></td>";
print "</tr></table></form>";
print " <br/>\n";

$sql1 = "SELECT SUM(CASE WHEN stat = 'ASSIGNED' THEN 1 ELSE 0 END) AS ASSIGNED, SUM(CASE WHEN stat = 'CREATED' THEN 1 ELSE 0 END) AS CREATED, SUM(CASE WHEN stat = 'DELIVERED' THEN 1 ELSE 0 END) AS DELIVERED, SUM(CASE WHEN stat = 'CANCEL' THEN 1 ELSE 0 END) AS CANCEL FROM task WHERE dateupdate >= '$tarikh1'  AND created < dateupdate <= '$tarikh2'";

$result1 = mysqli_query($conn, $sql1);

if(mysqli_num_rows($result1) > 0) {

    $row1 = mysqli_fetch_array($result1)
    $cntcreate = $row1[1];
    $cntasg = $row1[0];
    $cntdel = $row1[2];
    $cntccl = $row1[3];

    echo "<p><b><font face=verdana>Total Task Created = $cntcreate </font></b></p>";
    echo "<p><b><font face=verdana>Total Pending Task = $cntasg </font></b></p>";
    echo "<p><b><font face=verdana>Total Task Delivered = $cntdel </font></b></p>";
    echo "<p><b><font face=verdana>Total Task Cancelled = $cntccl </font></b></p>";
}
else {
    echo "<h1>-NO RELATED TASK ON THIS DATE!-</h1>";
}
?>