The PHP code I posted above shows the basics of how to create the DataTable in PHP. Other than that, there isn't much (if anything) you need to do in PHP to make a chart - the rest is all in javascript.
On Monday, March 17, 2014 7:28:28 PM UTC-4, [email protected] wrote: > > Thanks a ton asgallant, Could you please share any sample php (input from > SQL as array format)program for column or bar chart? > > > On Monday, March 17, 2014 4:36:11 PM UTC-6, asgallant wrote: >> >> You would have to make that a subquery, like this: >> >> $CountQuery = "SELECT >> Year, >> SUM(CASE WHEN Month = '01' THEN Count ELSE 0 END) as Jan, >> SUM(CASE WHEN Month = '02' THEN Count ELSE 0 END) as Feb, >> SUM(CASE WHEN Month = '03' THEN Count ELSE 0 END) as Mar, >> SUM(CASE WHEN Month = '10' THEN Count ELSE 0 END) as Oct, >> SUM(CASE WHEN Month = '11' THEN Count ELSE 0 END) as Nov, >> SUM(CASE WHEN Month = '12' THEN Count ELSE 0 END) as Dec >> FROM ( >> SELECT Year_Sd as Year...rest of your query in here... >> ) a >> GROUP BY Year >> ORDER BY Year"; >> >> There may be a more efficient way of handling that, but I'm not certain >> off the top of my head how you would handle the problems that COUNT >> DISTINCT brings up in conjunction with the case statements, so I would try >> this first and see if runs OK. >> >> On Monday, March 17, 2014 6:20:27 PM UTC-4, [email protected] wrote: >>> >>> Thank you asgallant. >>> >>> But my query look like this, I am not sure whether I can use SUM on top >>> of Count. Could you please help? >>> >>> Select >>> >>> Year_Sd as Year, >>> >>> TO_CHAR(Date_sd,'MM') as Month, >>> >>> count(distinct user_id) as Count >>> >>> from student_sd >>> >>> where active)ind = 'A' >>> >>> AND ((Year_Sd = '1314' >>> >>> and Date_sd < ('09-MAR-2013')) >>> >>> OR (Year_Sd = '1213' >>> >>> and Date_sd < ('05-MAR-2012')) >>> >>> OR (Year_Sd = '1112' >>> >>> and Date_sd < ('05-MAR-2011')) >>> >>> OR (Year_Sd = '1011' >>> >>> and Date_sd < ('05-MAR-2010')) >>> >>> OR (Year_Sd = '1415' >>> >>> and Date_sd < ('05-MAR-2014'))) >>> >>> group by TO_CHAR(Date_sd,'MM'),Year_Sd >>> order by Year_Sd >>> >>> On Monday, March 17, 2014 4:08:30 PM UTC-6, asgallant wrote: >>>> >>>> First, SQL does not guarantee that your data is returned in any >>>> particular order, so you can't count on getting data back in the order you >>>> expect it unless you specifically order it in the query. Second, what you >>>> are trying to do in PHP is quite unwieldy; it is much easier to make SQL >>>> do >>>> the heavy lifting for you. Try this instead: >>>> >>>> $CountQuery = "SELECT >>>> Year, >>>> SUM(CASE WHEN Month = '01' THEN Count ELSE 0 END) as Jan, >>>> SUM(CASE WHEN Month = '02' THEN Count ELSE 0 END) as Feb, >>>> SUM(CASE WHEN Month = '03' THEN Count ELSE 0 END) as Mar, >>>> SUM(CASE WHEN Month = '10' THEN Count ELSE 0 END) as Oct, >>>> SUM(CASE WHEN Month = '11' THEN Count ELSE 0 END) as Nov, >>>> SUM(CASE WHEN Month = '12' THEN Count ELSE 0 END) as Dec >>>> FROM table >>>> GROUP BY Year >>>> ORDER BY Year"; >>>> >>>> Your PHP then becomes vastly simplified: >>>> >>>> $table = array( >>>> 'cols' => array( >>>> // Labels for your chart, these represent the column titles >>>> array('label' => 'AidYear', 'type' =>'string'), >>>> array('label' => 'Jan', 'type' => 'number'), >>>> array('label' => 'Feb', 'type' => 'number'), >>>> array('label' => 'Mar', 'type' => 'number'), >>>> array('label' => 'Oct', 'type' => 'number'), >>>> array('label' => 'Nov', 'type' => 'number'), >>>> array('label' => 'Dec', 'type' => 'number') >>>> ). >>>> 'rows' => array() >>>> ); >>>> while($r = oci_fetch_array($CountQuery)) { >>>> $table['rows'][] = array('c' => array( >>>> array('v' => $r['Year']), >>>> array('v' => $r['Jan']), >>>> array('v' => $r['Feb']), >>>> array('v' => $r['Mar']), >>>> array('v' => $r['Oct']), >>>> array('v' => $r['Nov']), >>>> array('v' => $r['Dec']) >>>> )); >>>> } >>>> echo json_encode($table, JSON_NUMERIC_CHECK); >>>> >>>> On Monday, March 17, 2014 5:28:57 PM UTC-4, [email protected] wrote: >>>>> >>>>> >>>>> Please find attached for sample chart of what i am trying achieve. >>>>>> >>>>> >>>>> Thanks in advance. >>>>> >>>> -- 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.
