Count ID by STATUS with DATE RANGE

Question

So I wanna know the total of each task by it's status which is CREATED, ASSIGNED, DELIVERED & CANCEL. SO I started to get the data from SQL by filtering by date range. however when I tried to count by differentiate it's status, the result only count all without considering the status. Where is my mistake? I could't find it. Help me, so below is my codes and the result show like this.

Total Task Created = Warning: Undefined variable $cntcreate in C:\xampp\htdocs\tms\des_rpt.php on line 98 (should be 0)

Total Pending Task = Warning: Undefined variable $cntcreate in C:\xampp\htdocs\tms\des_rpt.php on line 99 (should be 1)

Total Task Delivered = 4 (should be 2)

Total Task Cancelled = Warning: Undefined variable $cntcreate in C:\xampp\htdocs\tms\des_rpt.php on line 101 (should be 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>";
}?>

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, May 26 '22 at 09:51

score -1 · Answer 1 · answered May 26 '22 at 04:42

I did some changes in your query, check the column name& make change if required. Let me know if you getting any issue with the query.

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

Count ID by STATUS with DATE RANGE

1 Answers1