Storing zero in a php array when date is not present in the sql table

Question

I have a sql table which looks like this :-

date    sales
2017-04-01  230
2017-04-02  120
2017-04-03  81
2017-04-04  911
2017-04-05  90
2017-04-06  11
2017-04-09  9
2017-04-10  140
2017-04-11  90

As it can be seen that for 2017-04-07 and 2017-04-08 there is no data. Following is query and php array :-

$sql="select sales from sales_data where date >= '2017-04-03'";
$RS=mysql_query($sql);

while(($row =  mysql_fetch_assoc($RS))) {
    $sales_val[] = $row['sales'];
}

The code works fine, however I want to store a zero in case the date is not present in the sql table. Is it possible to insert zero in the php array in this case where the date is not present ?

I might not use the link given in the comment above, but instead would use a calendar table inside your MySQL database. — Tim Biegeleisen, Apr 27 '17 at 05:31
Just to show another approach: http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range — Shadow, Apr 27 '17 at 05:35
In general the search expression is: `mysql fill gaps in date range`. There are dozens of questions with solutions out there. — Shadow, Apr 27 '17 at 05:36

score 1 · Accepted Answer · answered Apr 27 '17 at 08:21

Try this:

$sql="select `date`,`sales` from `sales_data` where `date` >= '2017-04-03' ORDER BY `date`";
$qrslt = mysqli_query($db,$sql);


$min_date = '9999-99-99';
$max_date = '0000-00-00';
$sales_val = array();
while(($row =  mysqli_fetch_assoc($qrslt))) {
    $sales_val[substr($row['date'],0,10)] = $row['sales'];
    $min_date = date('Y-m-d',strtotime($row['date'])) < $min_date ? date('Y-m-d',strtotime($row['date'])) : $min_date;
    $max_date = date('Y-m-d',strtotime($row['date'])) >= $max_date ? date('Y-m-d',strtotime($row['date'])) : $max_date;
}

$sd = new DateTime($min_date);
$ed = new DateTime($max_date);
$num_days = date_diff($sd,$ed)->days;
$rslts = array();
$cnt = 0;

for($d = 0;$d <= $num_days;$d++) {
    $dt = strtotime('+'.$d.' day',strtotime($min_date));
    $dts = date('Y-m-d',$dt);
    $rslts[$dts] = isset($sales_val[$dts]) ? $sales_val[$dts] : 0;
    $cnt++;
}

echo "<p>\$rslts:<pre>".print_r($rslts,true)."</pre></p>";

Storing zero in a php array when date is not present in the sql table

1 Answers1