Summarized daily sales for the last 30 days in google chart
I have a table with sales. From this table, I take all the results for the last 30 days, summing prices with the same date and get that as an array.
SQL:
SELECT date
, price
, id
, SUM(price) AS daylieprice
FROM sales
WHERE id = :id
AND date BETWEEN DATE_FORMAT(CURDATE() , '%Y-%m-%d') - interval 1 month AND DATE_FORMAT(CURDATE() , '%Y-%m-%d'))
GROUP
BY date
So I have, for example:
ARRAY ['date'] - ARRAY ['daylieprice']
"2017-03-29" - "1"
"2017-04-02" - "5"
"2017-04-04" - "3"
Google looks like this:
['<? echo date('d', strtotime("-2 day")) ?>', VALUE]
['<? echo date('d', strtotime("-1 day")) ?>', VALUE]
['<? echo date('d') ?> ', VALUE]
Is there a way to output the value of such an array:
date('d', strtotime("-2 day") , ARRAY ['daylieprice']);
date('d', strtotime("-1 day") , ARRAY ['daylieprice']);
date('d', ARRAY ['daylieprice']);
Should mean to make the array value simpler with date ('d') or date ('d', strtotime ("- 1 day") witouth creating a loop for each value?
Or do I have to make a sql query every day?
source to share
I came up with this. I am using DateTime to give more control and flexibility with input and output formats. This goes through your input array and subtracts 2 days from the first record, 1 day from the second record, and keeps the 3rd record the same:
<?php
$input = [
[
'date' => '2017-03-29',
'daylieprice' => 1,
],
[
'date' => '2017-04-02',
'daylieprice' => 5,
],
[
'date' => '2017-04-04',
'daylieprice' => 3,
],
];
$output = [];
$number_of_dates = count($input) - 1;
foreach ($input as $v) {
$date = DateTime::createFromFormat('Y-m-d', $v['date'])
->modify(sprintf('-%d days', $number_of_dates))
->format('Y-m-d');
$number_of_dates--;
$output[] = "'" . $date . "', " . $v['daylieprice'];
}
This creates an array like:
Array
(
[0] => '2017-03-27', 1
[1] => '2017-04-01', 5
[2] => '2017-04-04', 3
)
Hope it helps and you can figure out exactly how to implement it to solve your problem.
Edit: just saw echo date('d'
, so maybe you only want the month of the month, it's simple, you can just change ->format('Y-m-d');
in the loop to->format('d');
Demo: https://eval.in/784353
source to share