summed daylie sales of last 30 days output in google chart

Question

I have a table with sales. From this table i take all results of the last 30 days, sum the prices with the same date and get this as 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 chart is looking like that:

['<? 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 the array like that:

date('d', strtotime("-2 day") , ARRAY ['daylieprice']);
date('d', strtotime("-1 day") , ARRAY ['daylieprice']);
date('d', ARRAY ['daylieprice']);

Should mean to take the array value easy with date('d') or date('d', strtotime("-1 day") witouth making a loop for each value ?

Or does i have to make for every day a sql request?

Answer 1

I came up with this. I use DateTime to give more control and felxibility with input and output formats. This loops through your input array and subtracts 2 days from first entry, 1 day from 2nd entry and keeps 3rd entry 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 produces an array like:

Array
(
    [0] => '2017-03-27', 1
    [1] => '2017-04-01', 5
    [2] => '2017-04-04', 3
)

Hope this 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 day of the month, that's easy, you can just change ->format('Y-m-d'); in the loop to ->format('d');

Demo: https://eval.in/784353