I am writing a php/JS script to populate a google charts graph from a MySQL
database. The structure of the database is unfortunate but it is not mine
and I am stuck with it. The chart I am having problems with is a "Year to
Date" representation of financial information. It renders a bar chart of
year to date revenue from Jan through December for actual, budget and
previous year. Each month is an accumulation of the year. So the Jan figure
is just Jan, the Feb figure is Jan plus Feb, the Mar figure is Jan plus Feb
plus Mar and so on. Given the table structure I am working with, the query
becomes very long by the time it gets to December. It works fine up to Aug
and then when I add the Sep query it stops working and I get a "uncaught
object" error in the javascript and the google chart does not work. Here
are the Jan and Feb months of the query. The query just keeps getting
longer as I add the subsequent months to it but for brevity I have just
shown the first two months:
$STH = $DBH->prepare("SELECT
'Jan',SUM(two_o_one_four.jan_actual*-1),
SUM(two_o_one_four.jan_budget)*-1,SUM(two_o_one_three.jan_actual)*-1,
'Feb',SUM(two_o_one_four.jan_actual*-1+two_o_one_four.feb_actual*-1),
SUM(two_o_one_four.jan_budget*-1+two_o_one_four.feb_budget*-1),SUM(two_o_one_three.jan_actual*-1+two_o_one_three.feb_actual*-1),
etc. etc
I fetch the query as an associative array and then write it to an array in
the form required by google charts. Here is that code for Jan and Feb -
once again for brevity I am just showing the first two months:
$rows = array();
while ($nt = $STH->fetch()) {
$temp = array(); $temp[] = array('v' => $nt["Jan"], 'f' =>NULL);
$temp[] = array('v' => $nt['SUM(two_o_one_four.jan_actual*-1)'], 'f' =>
NULL);
$temp[] = array('v' => $nt['SUM(two_o_one_four.jan_budget)*-1'], 'f' =>
NULL);
$temp[] = array('v' => $nt['SUM(two_o_one_three.jan_actual)*-1'], 'f' =>
NULL);
$rows[] = array('c' => $temp); $temp = array(); $temp[] = array('v' =>
$nt["Feb"], 'f' =>NULL);
$temp[] = array('v' => $nt[
'SUM(two_o_one_four.jan_actual*-1+two_o_one_four.feb_actual*-1)'], 'f' =>
NULL);
$temp[] = array('v' => $nt[
'SUM(two_o_one_four.jan_budget*-1+two_o_one_four.feb_budget*-1)'], 'f' =>
NULL);
$temp[] = array('v' => $nt[
'SUM(two_o_one_three.jan_actual*-1+two_o_one_three.feb_actual*-1)'], 'f' =>
NULL);
$rows[] = array('c' => $temp); etc. etc.
I then call json_encode on the array and send it to google charts.
It all works well for an eight month query but as soon as I add another
month to it I get an uncaught object error. I have increased memory
allocation in PHP to no avail. The MySQL query runs fine on MySQL
workbench. I am pretty sure there are no syntax or logic errors as the code
works fine up to August so am left thinking there must be some kind of
limit to the length of these things. However I have spent hours searching
but have turned up nothing. Is there a limit I am running into?
Regards Alistair
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.
