CODEWITHSUNDEEP
phpmysql
bushdiver
bushdiver

Reputation: 771

php - MySql - organizing variables for an array

I'm attempting to fetch some rows from my db (php/mysql) and present them in a google chart using a javascript object as such: 

    var data = google.visualization.arrayToDataTable([
      ['Month', 'Sales Price 1', 'Sales Price 2'],
      ['2013-05',  555, 651 ],
      ['2013-04',  1170, 321],
    ]);

I'm doing just one fetch from db ( SELECT PRICE1, PRICE2, DATE FROM transactions ).
Having trouble figuring out how to iterate through the rows to organize and print them out for the google javascript object..

I realize I have to do some php loops and count the variables for each month before echoing the results into the array, but I can't think of an appropriate way to compare the months..
any ideas?

Upvotes: 1

Views: 151

Answers (2)

Niko
Niko

Reputation: 26730

With a DATE field containing the date of the transactions, you can easily obtain the total sums over the price fields grouped by month:

SELECT
  YEAR(date) AS year,
  MONTH(date) AS month,
  SUM(price1) AS total1,
  SUM(price2) AS total2
FROM transactions
GROUP BY YEAR(date), MONTH(date)
ORDER BY date

This will most likely be much faster than a PHP solution, especially if there are a lot of transactions stored in that table.

Demo: http://sqlfiddle.com/#!2/feddd/1

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191749

You can simply use ORDER BY in the query for the dates (you apparently know this). To create the array for JS, I would use JSON.

$values = array(array('MOnth', 'Sales Price 1', 'Sales Price 2'));
while ($row = $result->fetch()) {
    $values[] = array_values($row);
}
$for_javascript = json_encode($values);

Upvotes: 2

Related Questions