当sql表中没有日期时，在php数组中存储零[重复]

Question

This question already has an answer here:

• MySQL: Select All Dates In a Range Even If No Records Present 6 answers

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 ?

</div>

Answer 1

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