PHP Chart.js - Get the days number of 30 days prior

PHP
1

Ok so currently i’m using chart.js and am trying to create a sales chart for the past 30 days from today’s date. My issue is that I cannot figure out how i show the days dynamically! What would the sql query look like to generate the days even if there is no data associated with the date like shown on the image below!

image
image746×47 5.74 KB

Currently I have done the code to get the data I need for the graph and converted it into json format just as I cannot figure out how to generate the days its not working! Right now my code returns all the sales data for the transactions that are from the last 30 days but when i try to populate the graph that is where i get stuck!

Please let me know if i’m doing the wrong approach or if you need further detail as i feel i have worded this really poorly!

Thanks

2

I would calculate which date it was 30 days in the past in PHP, and then adjust the MySQL query to only get data starting that day.

So first:

$thirtyDaysAgo = new \DateTimeImmutable('-30 day');

and then use that value in the query

$query = "SELECT * FROM sales WHERE sale_date >= ".$thirtyDaysAgo->format('Y-m-d');

(using a prepared statement would be better here, but I don’t know which driver you use …)

And then what you do is loop over all dates until you hit today, and fill in any data you have available:

$today = new \DateTimeImmutable();
$date = $thirtyDaysAgo;

while ($date <= $today) {
    // if there is any data for $date, add it to your dataset
    $date = $date->modify('+1 day');
}
3

I’m using PDO

This is my current code that gets all the data i could need for the graph:

    public function count30DaysSales($id)
    {
		$bind = [':id' => $id];
        $sql = 'SELECT msi_sales.*, msi_transactions.payment_date AS payment_date, msi_transactions.payment_amount AS amount FROM msi_sales INNER JOIN msi_transactions ON msi_transactions.id = msi_sales.transaction_id WHERE msi_sales.status = 1 AND CAST(msi_transactions.payment_date AS DATE) >= CURDATE() -30 AND msi_sales.author_id = :id';
        $results = $this->db->selectExtended($sql, $bind);
        return $results;
    }

Then I have this bit of code that produces the json ready:

    public function salesHistory()
    {
		if(isset($_SESSION['uid'])) : $getUserData = $this->user->getUserByUserId($_SESSION['uid']); else : $getUserData = NULL; endif;
		if($getUserData[0]['activate_author'] == 1):
		
			$getSalesData = $this->item->count30DaysSales($_SESSION['uid']);
			foreach ($getSalesData as $row) {
				$data[] = array(
					'date' => $row['payment_date'],
					'amount' => $row['payment_amount']
				);
			}
			echo json_encode($data);

		else:
		redirect('author');
		endif;
    }

And produces this data:
[{"date":"2018-06-30 04:11:12","amount":"6.49"},{"date":"2018-08-21 00:00:00","amount":"7.50"}]

but what i cannot figure out is how once i get all the data within the last 30 days how i get the dates inbetween even if there is no data associated but then I have to convert the date so that it only shows the day date so I would have to do something like this but your help is much appreciated thanks (and i’m using a custom mvc but it uses pdo)

 $string = "2010-11-24";
 $date = DateTime::createFromFormat("Y-m-d", $string);
 echo $date->format("d");
4

Pseudo-code for something I might do would be:

// pass in your data[] array
startdate = today-30
newarray = new blank array
while (startdate < today) {
  is there an entry in data[] for this date?
    yes: copy it into newarray
    no: create a blank entry with a zero value
  startdate++
}

Obviously you’d have to deal with there being more than one entry for a given date, but if you’re displaying daily totals in the graph you’re doing that anyway.

5

So a couple of thoughts:
Your JSON data includes the time. Your intention is to display/group things by day. Drop the time information from your JSON. That way you can do a simple .find() on the array to find the data object for the date in question.
Your database query isnt aggregating anything. Are you aggregating the sales data somewhere else? How do you know how many sales you made yesterday?

1 Like
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.