绘制每周每天的用户交易

Question

In my DB there is Transaction table. This table contains information about user money transactions. My task is to generate two diagrams(graphs) showing transaction amount per day, and per month.

Example of the diagram that I should implement is displayed on the image below:

As you can see from the diagram, when there are no transactions per day, the total value should be 0.

In the database, I group my transactions by day using the following query:

select DATE_FORMAT(t.transactionDate ,"%b %d") as dayName, sum(t.amount) total from `Transaction` t, t.transactionStatus = 1 and t.user=17
group by dayName
order by t.transactionDate asc;

This is working like a charm when there are transactions for every day, but this is not working fine when there is a day without any transaction. Let's say in DB we have the following transactions:

May 1st 20$
May 1st 30$
May 1st 38$
May 2nd 20$
May 4th 100$
May 4th 50$

So no transactions on May 3rd.

When I group these transactions I get the following result:

May 1st 88$
May 2nd 20$
May 4th 150$

What I want now is to generate May 3rd 0$. Is it possible to do it directly in DB, or I have to process it using PHP?

If I have to process it using PHP, any ideas?

For plotting I'm using Chart JS library, and here is example of data input:

        var areaChartData = {
          labels: ["May 01", "May 02", "May 04"],
          datasets: [
            {
              label: "Transactions",
              fillColor: "rgba(210, 214, 222, 1)",
              strokeColor: "rgba(210, 214, 222, 1)",
              pointColor: "rgba(210, 214, 222, 1)",
              pointStrokeColor: "#c1c7d1",
              pointHighlightFill: "#fff",
              pointHighlightStroke: "rgba(220,220,220,1)",
              data: [88,20,150]
            },

          ]
        };

As you can see, my idea is to use values from the database directly in the chart, where transaction date is my X-axis, and total is my Y-axis.

Answer 1

One approach is, to have all possible dates and values first as an PHP array:

$labels = array(
     "May 1st" => 0,
     "May 2nd" => 0,
     "May 3rd" => 0,
     "May 4th" => 0,
     "May 6th" => 0,
     "May 6th" => 0,
     ....

);

Then fill all the values from the database to their according date key in the array

...
foreach ( $rows as $row )
{
   $labels[$row['date']] = $row['agg_val'];
   ...

Now you can iterate over the $labels array and put out the values. Those, which are not in the DB (no transactions) are initialized to zero.