从json获得每天的总和值

This is the code that i want to get the total value of NO_OF_CTN for each date. I use json to get the total sum value. But how do i able to seperate the value of each day to prevent them all sum together.

<?php

 include '../includes/db_conn.php';
 $final_query = "SELECT DATE, NO_OF_CTN
                 FROM `material_dtl_tbl` WHERE 
                 `material_dtl_tbl`.`DATE` BETWEEN '2018-11-01%' 
                 AND '2018-11-03%' GROUP BY STOCK_CODE ORDER BY DATE ASC
                ";

$rsEventList = mysqli_query($con, $final_query) or die(mysqli_error($con));

$data_array = array();

while ($row = mysqli_fetch_assoc($rsEventList)) {
    $data_array[] = array("date" => $row['DATE'],"num" => $row['NO_OF_CTN'],);
} 

?>
<script type="text/javascript"> 
var total = 0;
var data = <?php echo json_encode($data_array)?>;

for($i = 0; $i < data.length ; $i++){
         total += parseInt(data[$i].num);
}

document.write(total);

</script>

Using this way i able to get the sum of nov 1 to nov 3 but i want to get the total value of nov 1 , nov 2, nov 3 instead of 3 day together. Is there any ways to make it happen? Thank you

You can achieve this using SQL alone

$final_query = "SELECT DATE as date, sum(NO_OF_CTN) as num
                 FROM `material_dtl_tbl` WHERE 
                 `material_dtl_tbl`.`DATE` BETWEEN '2018-11-01' 
                 AND '2018-11-03' GROUP BY STOCK_CODE, DATE
                 ORDER BY DATE ASC
                ";

$rsEventList = mysqli_query($con, $final_query) or die(mysqli_error($con));

$data_array = array();

while ($row = mysqli_fetch_assoc($rsEventList)) {
    $data_array[] = $row;
} 

This will sum the NO_OF_CTN separated by date and STOCK_CODE. You don't have to loop through the array by yourself, hence improve the performance.

Since you don't seem to care about the STOCK_CODE on the cartons, and just want to get the total number of cartons per day, you should remove STOCK_CODE from your GROUP BY and replace it with DATE. You also need to remove that %s from the values you are comparing DATE against:

$final_query = "SELECT `DATE`, NO_OF_CTN
                FROM `material_dtl_tbl` 
                WHERE `DATE` BETWEEN '2018-11-01' AND '2018-11-03' 
                GROUP BY `DATE`
                ORDER BY `DATE` ASC
               ";

Note if your DATE columns is a TIMESTAMP or DATETIME column, you should use DATE(DATE) to convert it to just a date.