Building data for google charts

Hey there,

i am going to be using google charts in this project. (Simple and easy to use) in order to build the data i am thinking i should run a foreach loop looping though each result in the database. So here’s the question. Is there a better way that this could be done?

If you take the first example on G charts

You can build the necessary string for this part:


        data.addRows([
          ['Mushrooms', 3],
          ['Onions', 1],
          ['Olives', 1],
          ['Zucchini', 1],
          ['Pepperoni', 2]
        ]);

Using this idea:



// lets imagine this is the result of your sql statement
// "SELECT veg, count from vegetables"
// returned as an associated array with mysql_fetch_array()
// it will resemble this in PHP:
$rows[0]['veg'] = 'Mushrooms';
$rows[0]['count'] = 3;
$rows[1]['veg'] = 'Onions';
$rows[1]['count'] = 1;
$rows[2]['veg'] = 'Olives';
$rows[2]['count'] = 1;
$rows[3]['veg'] = 'Zuccini';
$rows[3]['count'] = 1;
$rows[4]['veg'] = 'Pepperoni';
$rows[4]['count'] = 2;

// start your string
$data =   "data.addRows([";

// add the rows
foreach( $rows as $row){
 $data .= "['" . $row['veg'] ."'," . $row['count'] . "],";
}

// get rid of trailing comma, not permitted in JS (I dont think)
$data = trim($data, ",");

// add the end of the string
echo $data . "]);";

The puts into your JS output the string:


data.addRows([['Mushrooms',3],['Onions',1],['Olives',1],['Zuccini',1],['Pepperoni',2]]);

There are probably better ways using JSON and so on, I’d look further into the G Charts docs for ways to do this, but essentially, yes, you can use PHP to build the required JS.

From memory I have used PHP/Mysql to output a js array into a separate js file, which you just include in your page - so creating a better separation of PHP and JS which is important because using PHP to output JS can get very messy when dealing with correctly quoted items (hint: use the PHP Heredoc syntax when you can to make this less painful.)

First google search result:
http://code.google.com/p/gchartphp/
http://www.splitbrain.org/blog/2008-04/16-google_chart_api_via_php

fetch_All the query and then array_map the result to a lambda function that returns (using cups’ example) “[$in[‘veg’],$in[‘count’]]” then implode the array on “,”

Thanks for your help! It has been great. I have not got down to working with gCharts yet but soon should be.