Adding zero to an array if empty values occurs in array

Question

This is the array which I am getting after selecting sum(amounts) for each products group by date. when the dates did not having correspondent amount it is displaying as empty.I need to display it as zero.

Array
(
    [2019-05-16] => Array
        (
            [0] => 499
        )

    [2019-05-17] => Array
        (
            [0] => 1998
        )

    [2019-05-18] => Array
        (
            [0] => 195
        )

    [2019-05-19] => Array
        (
            [0] => 1194
            [1] => 999
        )

)

But output should like this,

Is there any way to make empty values as 0


Array
(
    [2019-05-16] => Array
        (
            [0] => 499
            [1] => 0

        )

    [2019-05-17] => Array
        (
            [0] => 1998
           [1] => 0
        )

    [2019-05-18] => Array
        (
            [0] => 195
            [1] => 0
        )

    [2019-05-19] => Array
        (
            [0] => 1194
            [1] => 999
        )

)

This is the script


foreach ($productid as $itemid) {

  $query1 = "select date, SUM(amount) as amount from app_product_details where  date between '2019-05-14 00:00:00' and '2019-05-20 23:59:59' and appid = $itemid group by date";

        $getquery1 = mysqli_query($conn,$query1);
        while($getcount1 = mysqli_fetch_assoc($getquery1))
        {

          $grset1[] = $getcount1;

        }
      }

 foreach($grset1 as $r){
$dates1[$r['date']][] = $r['amount'];

}

Please help me to reach to the output.

`SUM(amount)+0` ? Or maybe here https://stackoverflow.com/questions/8888390/how-to-select-sum-or-0-if-no-records-exist — AbraCadaver, May 20 '19 at 15:05
I have already tried COALESCE and IFNULL but it is not working. — arj, May 20 '19 at 15:09

score 0 · Accepted Answer · answered May 20 '19 at 15:10

0

Rather than trying to hack your array, you can change your query so that it returns a 0 value when there were none of a given product sold on that day. This should work:

$query1 = "SELECT date, SUM(CASE WHEN appid = $itemid THEN amount ELSE 0 END) AS amount 
           FROM app_product_details
           WHERE date BETWEEN '2019-05-14 00:00:00' AND '2019-05-20 23:59:59' 
           GROUP BY date";

answered May 20 '19 at 15:10

Nick

138,499
22
57
95

Thanks, @Nick It Works...Thanks a lot. :) – arj May 20 '19 at 15:15

Adding zero to an array if empty values occurs in array

1 Answers1

Linked