Mysql Query AnyChart JSON Data missing dates

Question

I'm building a dashboard with anycharts. I get my data from mysql database in JSON format. Example of a query:

SELECT DATE( data_inscricao ) AS name, COUNT( id ) AS value FROM utilizador GROUP BY name

So, I'm getting a count of all users registered grouped by registration date.

Example result:

[{name: "2016-01-22",value: "7"}, {name: "2016-01-25", value: "3"}, {name: "2016-01-26", value: "1"}, {name: "2016-01-27", value: "2"}, {name: "2016-02-02", value: "1"}, ...

I'il like to show 0 (zero) for dates where there weren't any registrations, like 2016-01-23, 2016-01-24, 2016-01-28, ...

Is that possible?

Thanks

Answer 1

Firstly you need to define min and max values of dates. Then you can generate array with contains every day in period and merge it with values from database. In your case we have 2016-01-22 - 2016-02-02 period:

<?php
$begin = new DateTime( '2016-01-22' );
$end = new DateTime( '2016-02-02' );

$allDays = [];
$interval = DateInterval::createFromDateString('1 day');
$period = new DatePeriod($begin, $interval, $end);
foreach ( $period as $dt ) {
    $allDays[$dt->format('Y-m-d')] = '0';
}
foreach ($array as $key => $dayFromDB) {
    $allDays[$dayFromDB->name] = $dayFromDB->value;
}
var_dump($allDays);

Here the $array is your array of DB. after this you'll get something like:

array (size=12)
'2016-01-22' => string '7' (length=1)
'2016-01-23' => string '0' (length=1)
'2016-01-24' => string '0' (length=1)
'2016-01-25' => string '3' (length=1)
'2016-01-26' => string '1' (length=1)
'2016-01-27' => string '2' (length=1)
'2016-01-28' => string '0' (length=1)
'2016-01-29' => string '0' (length=1)
'2016-01-30' => string '0' (length=1)
'2016-01-31' => string '0' (length=1)
'2016-02-01' => string '0' (length=1)
'2016-02-02' => string '1' (length=1)

I hope it will helpful for you!