Andrew, thank you so much for your help with this. Your suggestions got me
on the right track. I did as you suggested and handled the summation in
PHP. I used bindColumn and FETCH_BOUND to bring the db query results across
into variables and performed the summation in the data table construction.
For completeness, here is the amended code which works well and very quick
although some further abstraction of the PDO is required for best practice.
>
> <?php
//define host, database name, user and pass:
$table;
$host = 'localhost';
$dbname = 'ab_my';
$user = 'root';
$pass = '';
//connect:
try {
$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
//insert placeholder in this section
$STH = $DBH->prepare("SELECT
'Jan' as Jan,SUM(two_o_one_four.jan_actual)*-1 as JanActual,
SUM(two_o_one_four.jan_budget)*-1 as
JanBudget,SUM(two_o_one_three.jan_actual)*-1 as JanPrevious,
'Feb' as Feb,SUM(two_o_one_four.feb_actual)*-1 as FebActual,
SUM(two_o_one_four.feb_budget)*-1 as
FebBudget,SUM(two_o_one_three.feb_actual)*-1 as FebPrevious,
'Mar' as Mar,SUM(two_o_one_four.mar_actual)*-1 as MarActual,
SUM(two_o_one_four.mar_budget)*-1 as
MarBudget,SUM(two_o_one_three.mar_actual)*-1 as MarPrevious,
'Apr' as Apr,SUM(two_o_one_four.apr_actual)*-1 as AprActual,
SUM(two_o_one_four.apr_budget)*-1 as
AprBudget,SUM(two_o_one_three.apr_actual)*-1 as AprPrevious,
'May' as May,SUM(two_o_one_four.may_actual)*-1 as MayActual,
SUM(two_o_one_four.may_budget)*-1 as
MayBudget,SUM(two_o_one_three.may_actual)*-1 as MayPrevious,
'Jun' as Jun,SUM(two_o_one_four.jun_actual)*-1 as JunActual,
SUM(two_o_one_four.jun_budget)*-1 as
JunBudget,SUM(two_o_one_three.jun_actual)*-1 as JunPrevious,
'Jul' as Jul,SUM(two_o_one_four.jul_actual)*-1 as JulActual,
SUM(two_o_one_four.jul_budget)*-1 as
JulBudget,SUM(two_o_one_three.jul_actual)*-1 as JulPrevious,
'Aug' as Aug,SUM(two_o_one_four.aug_actual)*-1 as AugActual,
SUM(two_o_one_four.aug_budget)*-1 as
AugBudget,SUM(two_o_one_three.aug_actual)*-1 as AugPrevious,
'Sep' as Sep,SUM(two_o_one_four.sep_actual)*-1 as SepActual,
SUM(two_o_one_four.sep_budget)*-1 as
SepBudget,SUM(two_o_one_three.sep_actual)*-1 as SepPrevious,
'Oct' as Oct,SUM(two_o_one_four.oct_actual)*-1 as OctActual,
SUM(two_o_one_four.oct_budget)*-1 as
OctBudget,SUM(two_o_one_three.oct_actual)*-1 as OctPrevious,
'Nov' as Nov,SUM(two_o_one_four.nov_actual)*-1 as NovActual,
SUM(two_o_one_four.nov_budget)*-1 as
NovBudget,SUM(two_o_one_three.nov_actual)*-1 as NovPrevious,
'Dec' as Dem,SUM(two_o_one_four.dec_actual)*-1 as DecActual,
SUM(two_o_one_four.dec_budget)*-1 as
DecBudget,SUM(two_o_one_three.dec_actual)*-1 as DecPrevious
FROM two_o_one_four, two_o_one_three
WHERE two_o_one_four.object_code = two_o_one_three.object_code;
");
//Bind query results to variables
$STH->bindColumn('Jan', $Jan);
$STH->bindColumn('JanActual', $JanActual);
$STH->bindColumn('JanBudget', $JanBudget);
$STH->bindColumn('JanPrevious', $JanPrevious);
$STH->bindColumn('Feb', $Feb);
$STH->bindColumn('FebActual', $FebActual);
$STH->bindColumn('FebBudget', $FebBudget);
$STH->bindColumn('FebPrevious', $FebPrevious);
$STH->bindColumn('Mar', $Mar);
$STH->bindColumn('MarActual', $MarActual);
$STH->bindColumn('MarBudget', $MarBudget);
$STH->bindColumn('MarPrevious', $MarPrevious);
$STH->bindColumn('Apr', $Apr);
$STH->bindColumn('AprActual', $AprActual);
$STH->bindColumn('AprBudget', $AprBudget);
$STH->bindColumn('AprPrevious', $AprPrevious);
$STH->bindColumn('May', $May);
$STH->bindColumn('MayActual', $MayActual);
$STH->bindColumn('MayBudget', $MayBudget);
$STH->bindColumn('MayPrevious', $MayPrevious);
$STH->bindColumn('Jun', $Jun);
$STH->bindColumn('JunActual', $JunActual);
$STH->bindColumn('JunBudget', $JunBudget);
$STH->bindColumn('JunPrevious', $JunPrevious);
$STH->bindColumn('Jul', $Jul);
$STH->bindColumn('JulActual', $JulActual);
$STH->bindColumn('JulBudget', $JulBudget);
$STH->bindColumn('JulPrevious', $JulPrevious);
$STH->bindColumn('Aug', $Aug);
$STH->bindColumn('AugActual', $AugActual);
$STH->bindColumn('AugBudget', $AugBudget);
$STH->bindColumn('AugPrevious', $AugPrevious);
$STH->bindColumn('Sep', $Sep);
$STH->bindColumn('SepActual', $SepActual);
$STH->bindColumn('SepBudget', $SepBudget);
$STH->bindColumn('SepPrevious', $SepPrevious);
$STH->bindColumn('Oct', $Oct);
$STH->bindColumn('OctActual', $OctActual);
$STH->bindColumn('OctBudget', $OctBudget);
$STH->bindColumn('OctPrevious', $OctPrevious);
$STH->bindColumn('Nov', $Nov);
$STH->bindColumn('NovActual', $NovActual);
$STH->bindColumn('NovBudget', $NovBudget);
$STH->bindColumn('NovPrevious', $NovPrevious);
$STH->bindColumn('Dem', $Dec);
$STH->bindColumn('DecActual', $DecActual);
$STH->bindColumn('DecBudget', $DecBudget);
$STH->bindColumn('DecPrevious', $DecPrevious);
//Execute DB query
$STH->execute();
//instantiate table to hold date for subsequent encoding to JSON
$table = array();
//define table columns as nested arrays in cols index
$table['cols'] = array(
array("id"=>"", "label"=>"Month", "pattern"=>"",
"type"=>"string"),
array("id"=>"", "label"=>"Actual", "pattern"=>"",
"type"=>"number"),
array("id"=>"", "label"=>"Budget", "pattern"=>"",
"type"=>"number"),
array("id"=>"", "label"=>"Last Year", "pattern"=>"",
"type"=>"number"),
);
//instantiate an array in which to next further arrays containing table
rows
$rows = array();
//Fetch the results from the DB query, bind to the variables specified
above, perform functions on variables as required and insert results along
with other required attributes into $rows array
while ($nt = $STH->fetch(PDO::FETCH_BOUND))
{
$temp = array();
$temp[] = array('v' => $Jan, 'f' =>NULL);
$temp[] = array('v' => $JanActual, 'f' =>NULL);
$temp[] = array('v' => $JanBudget, 'f' =>NULL);
$temp[] = array('v' => $JanPrevious, 'f' =>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Feb, 'f' =>NULL);
$temp[] = array('v' => $JanActual+$FebActual, 'f' =>NULL);
$temp[] = array('v' => $JanBudget+$FebBudget, 'f' =>NULL);
$temp[] = array('v' => $JanPrevious+$FebPrevious, 'f' =>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Mar, 'f' =>NULL);
$temp[] = array('v' => $JanActual+$FebActual+$MarActual, 'f'
=>NULL);
$temp[] = array('v' => $JanBudget+$FebBudget+$MarBudget, 'f'
=>NULL);
$temp[] = array('v' => $JanPrevious+$FebPrevious+$MarPrevious, 'f'
=>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Apr, 'f' =>NULL);
$temp[] = array('v' => $JanActual+$FebActual+$MarActual+$AprActual,
'f' =>NULL);
$temp[] = array('v' => $JanBudget+$FebBudget+$MarBudget+$AprBudget,
'f' =>NULL);
$temp[] = array('v' =>
$JanPrevious+$FebPrevious+$MarPrevious+$AprPrevious, 'f' =>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $May, 'f' =>NULL);
$temp[] = array('v' =>
$JanActual+$FebActual+$MarActual+$AprActual+$MayActual, 'f' =>NULL);
$temp[] = array('v' =>
$JanBudget+$FebBudget+$MarBudget+$AprBudget+$MayBudget, 'f' =>NULL);
$temp[] = array('v' =>
$JanPrevious+$FebPrevious+$MarPrevious+$AprPrevious+$MayPrevious, 'f'
=>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Jun, 'f' =>NULL);
$temp[] = array('v' =>
$JanActual+$FebActual+$MarActual+$AprActual+$MayActual+$JunActual, 'f'
=>NULL);
$temp[] = array('v' =>
$JanBudget+$FebBudget+$MarBudget+$AprBudget+$MayBudget+$JunBudget, 'f'
=>NULL);
$temp[] = array('v' =>
$JanPrevious+$FebPrevious+$MarPrevious+$AprPrevious+$MayPrevious+$JunPrevious,
'f' =>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Jul, 'f' =>NULL);
$temp[] = array('v' =>
$JanActual+$FebActual+$MarActual+$AprActual+$MayActual+$JunActual+$JulActual,
'f' =>NULL);
$temp[] = array('v' =>
$JanBudget+$FebBudget+$MarBudget+$AprBudget+$MayBudget+$JunBudget+$JulBudget,
'f' =>NULL);
$temp[] = array('v' =>
$JanPrevious+$FebPrevious+$MarPrevious+$AprPrevious+$MayPrevious+$JunPrevious+$JulPrevious,
'f' =>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Aug, 'f' =>NULL);
$temp[] = array('v' =>
$JanActual+$FebActual+$MarActual+$AprActual+$MayActual+$JunActual+$JulActual+$AugActual,
'f' =>NULL);
$temp[] = array('v' =>
$JanBudget+$FebBudget+$MarBudget+$AprBudget+$MayBudget+$JunBudget+$JulBudget+$AugBudget,
'f' =>NULL);
$temp[] = array('v' =>
$JanPrevious+$FebPrevious+$MarPrevious+$AprPrevious+$MayPrevious+$JunPrevious+$JulPrevious+$AugPrevious,
'f' =>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Sep, 'f' =>NULL);
$temp[] = array('v' =>
$JanActual+$FebActual+$MarActual+$AprActual+$MayActual+$JunActual+$JulActual+$AugActual+$SepActual,
'f' =>NULL);
$temp[] = array('v' =>
$JanBudget+$FebBudget+$MarBudget+$AprBudget+$MayBudget+$JunBudget+$JulBudget+$AugBudget+$SepBudget,
'f' =>NULL);
$temp[] = array('v' =>
$JanPrevious+$FebPrevious+$MarPrevious+$AprPrevious+$MayPrevious+$JunPrevious+$JulPrevious+$AugPrevious+$SepPrevious,
'f' =>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Oct, 'f' =>NULL);
$temp[] = array('v' =>
$JanActual+$FebActual+$MarActual+$AprActual+$MayActual+$JunActual+$JulActual+$AugActual+$SepActual+$OctActual,
'f' =>NULL);
$temp[] = array('v' =>
$JanBudget+$FebBudget+$MarBudget+$AprBudget+$MayBudget+$JunBudget+$JulBudget+$AugBudget+$SepBudget+$OctBudget,
'f' =>NULL);
$temp[] = array('v' =>
$JanPrevious+$FebPrevious+$MarPrevious+$AprPrevious+$MayPrevious+$JunPrevious+$JulPrevious+$AugPrevious+$SepPrevious+$OctPrevious,
'f' =>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Nov, 'f' =>NULL);
$temp[] = array('v' =>
$JanActual+$FebActual+$MarActual+$AprActual+$MayActual+$JunActual+$JulActual+$AugActual+$SepActual+$OctActual+$NovActual,
'f' =>NULL);
$temp[] = array('v' =>
$JanBudget+$FebBudget+$MarBudget+$AprBudget+$MayBudget+$JunBudget+$JulBudget+$AugBudget+$SepBudget+$OctBudget+$NovBudget,
'f' =>NULL);
$temp[] = array('v' =>
$JanPrevious+$FebPrevious+$MarPrevious+$AprPrevious+$MayPrevious+$JunPrevious+$JulPrevious+$AugPrevious+$SepPrevious+$OctPrevious+$NovPrevious,
'f' =>NULL);
$rows[] = array('c' => $temp);
$temp = array();
$temp[] = array('v' => $Dec, 'f' =>NULL);
$temp[] = array('v' =>
$JanActual+$FebActual+$MarActual+$AprActual+$MayActual+$JunActual+$JulActual+$AugActual+$SepActual+$OctActual+$NovActual+$DecActual,
'f' =>NULL);
$temp[] = array('v' =>
$JanBudget+$FebBudget+$MarBudget+$AprBudget+$MayBudget+$JunBudget+$JulBudget+$AugBudget+$SepBudget+$OctBudget+$NovBudget+$DecBudget,
'f' =>NULL);
$temp[] = array('v' =>
$JanPrevious+$FebPrevious+$MarPrevious+$AprPrevious+$MayPrevious+$JunPrevious+$JulPrevious+$AugPrevious+$SepPrevious+$OctPrevious+$NovPrevious+$DecPrevious,
'f' =>NULL);
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
//catch exception if "try" query fails
}
catch(PDOException $e) {
echo $e->getMessage();
}
//close the DB connection
$DBH = null;
//encode the $table array to JSON and echo it out as the
google.visualization.DataTable
$jsonTable = json_encode($table);
echo $jsonTable;
?>
>
On Thursday, 12 June 2014 08:20:56 UTC+12, Andrew Gallant wrote:
>
> 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.
