I think it is unlikely that you encountered any limits in the Visualization
API. I would check your query first to make sure it is returning the
correct data, and then check over your PHP code to make sure it is working
properly. Incidentally, your query looks like it could use some help with
aliases, eg:
SELECT
Jan,
SUM(two_o_one_four.jan_actual)*-1 as Jan_Actual,
SUM(two_o_one_four.jan_budget)*-1 as Jan_Budget,
SUM(two_o_one_three.jan_budget)*-1 as Jan_Prev,
// etc...
Then in PHP, you can reference them by alias instead of the calculation, eg:
$temp = array();
$temp[] = array('v' => $nt['Jan'], 'f' =>NULL);
$temp[] = array('v' => $nt['Jan_Actual'], 'f' =>NULL);
$temp[] = array('v' => $nt['Jan_Budget'], 'f' =>NULL);
$temp[] = array('v' => $nt['Jan_Prev'], 'f' =>NULL);
which makes your code a bit cleaner to read.
The calculations can be messy to read in SQL as well (and will cause the
database to calculate some of those values multiple times - like the
January figures, which will be calculated 12 separate times), so you may
want to consider simplifying your SQL by pulling only the relevant data for
each month and handling the summation in PHP.
On Wednesday, June 11, 2014 12:06:16 PM UTC-4, [email protected] wrote:
>
> 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.
