Liz Banach Liz Banach - 3 months ago 18
JSON Question

Add together values from JSON by month for a Fusion Charts graph

I am using MySQL, PHP, and Fusion Charts to create a line graph to track the amount of money invoiced for each month in 2016. The two columns in my table that I am using for this graph are InvoiceAmount (Decimal type) and InvoiceDate (DateTime type).

My goal is to have the X-axis of my graph be the dates by month (Jan, Feb, Mar, etc.) from InvoiceDate and the Y-axis be the dollar amount from InvoiceAmount. I got started by converting my MySQL data into a JSON format for it to be readable by Fusion Charts:

//the SQL query to be executed
$query = "SELECT DATE_FORMAT(InvoiceDate, '%M') AS InvoiceMonth, InvoiceAmount FROM Estimates WHERE InvoiceDate IS NOT NULL AND YEAR(InvoiceDate) = 2016 AND InvoiceAmount IS NOT NULL AND InvoiceAmount > 0";

//storing the result of the executed query
$result = $conn->query($query);

//initialize the array to store the processed data
$jsonArray = array();

//check if there is any data returned by the SQL Query
if ($result->num_rows > 0) {
//Converting the results into an associative array
while($row = $result->fetch_assoc()) {
$jsonArrayItem = array();
$jsonArrayItem['label'] = $row['InvoiceMonth'];
$jsonArrayItem['value'] = $row['InvoiceAmount'];
//append the above created object into the main array.
array_push($jsonArray, $jsonArrayItem);
}
}

//Closing the connection to DB
$conn->close();

//set the response content type as JSON
header('Content-type: application/json');
//output the return value of json encode using the echo function.
echo json_encode($jsonArray, JSON_PRETTY_PRINT);


This outputs a JSON like this:

[
{
"label": "January",
"value": "11361.00"
},
{
"label": "December",
"value": "1164.40"
},
{
"label": "February",
"value": "166.80"
},
{
"label": "July",
"value": "5088.00"
},
{
"label": "January",
"value": "214.50"
},
{
"label": "June",
"value": "620.40"
},
{
"label": "July",
"value": "5250.00"
},
{
"label": "March",
"value": "3425.00"
},
{
"label": "January",
"value": "3790.00"
},
{
"label": "February",
"value": "1909.80"
},
{
"label": "January",
"value": "1780.00"
},
{
"label": "January",
"value": "3060.00"
},
{
"label": "January",
"value": "2680.00"
},
{
"label": "February",
"value": "604.80"
}
]


etc.

As you can see, there are several values that have the same month attached to them. Currently, the line graph is treating each of these label/value pairs as one distinct instance, when what I need is for all of the values from a specific month to be added together to make overall monthly value for Jan, Feb, Mar, etc.

Example of line graph as it is currently.

Is there a function I can use in PHP or MySQL that will check which month is attached to the value, and then add all of the values from the same month together? Please let me know the best way to tackle this problem. Thank you for your help.

Answer

You could use GROUP BY and SUM to let the SQL statement only return one record per month:

SELECT   DATE_FORMAT(InvoiceDate, '%M') AS InvoiceMonth, 
         SUM(InvoiceAmount) AS InvoiceAmount
FROM     Estimates 
WHERE    InvoiceDate IS NOT NULL 
     AND YEAR(InvoiceDate) = 2016 
     AND InvoiceAmount IS NOT NULL 
     AND InvoiceAmount > 0
GROUP BY DATE_FORMAT(InvoiceDate, '%M')

Be aware that a query with a hard-coded year is not maintainable in the longer term.

I would suggest showing the last 12 months. The last column gives the "year-month", like 201508. It is used to order the results:

SELECT   DATE_FORMAT(InvoiceDate, '%M') AS InvoiceMonth, 
         SUM(InvoiceAmount) AS InvoiceAmount,
         EXTRACT(YEAR_MONTH FROM InvoiceDate) As InvoiceYearMonth
FROM     Estimates 
WHERE    InvoiceDate IS NOT NULL 
     AND EXTRACT(YEAR_MONTH FROM InvoiceDate) >= EXTRACT(YEAR_MONTH FROM CURDATE())-100 
     AND InvoiceAmount IS NOT NULL 
     AND InvoiceAmount > 0
GROUP BY DATE_FORMAT(InvoiceDate, '%M')
ORDER BY InvoiceYearMonth
Comments