Building a Google Chart with PHP and MySQL

Question

I am pretty new at PHP and MySQL queries. I am trying to build a Google chart from a MySQL database but after searching on Google for countless hours I could not find what I need. However I found an example that could be useful but I still can't make it the way I want. Here's an example of my table.

    Apple   | Orange  | Strawberry
    --------------------------
    Like    | Like    | Like
    Dislike | Like    | Like
    Dislike | Dislike | Like
    Like    | Dislike | Dislike
    Like    | Like    | Like

I want to count how many Like and Dislike for Apple, Orange and Strawberry. In the chart I want it to display how many people like and dislike these 3 fruits.

Here's the code I've been looking at and I've yet figured out how to attack it.

    $query = mysql_query('SELECT * FROM data');

    $table = array();
    $table['cols'] = array(
        array('label' => 'cas', 'type' => 'string'),
        array('label' => 'data', 'type' => 'number')
    );

    $rows = array();
    while($r = mysql_fetch_assoc($query)) {
        $temp = array();
        $temp[] = array('v' => $r['cas']);
        $temp[] = array('v' => (int) $r['data']);

    $rows[] = array('c' => $temp);
    }

    $table['rows'] = $rows;

    $jsonTable = json_encode($table);

    echo $jsonTable;

Any example would help! Thank you.

possible duplicate of [How to get mysql data into a google chart using php loop?](http://stackoverflow.com/questions/11219282/how-to-get-mysql-data-into-a-google-chart-using-php-loop) — skv, Aug 17 '13 at 08:39
How you approach the problem here depends on what you want the final result to be. Are you looking for something like this: http://jsfiddle.net/asgallant/xbqr6/, or like this: http://jsfiddle.net/asgallant/xbqr6/1/, or something different? — asgallant, Aug 17 '13 at 15:52

score 4 · Accepted Answer · answered Aug 17 '13 at 22:53

Try this as your PHP:

$query = mysql_query("
    SELECT
        'Like' as 'preference',
        SUM(IF(Apple = 'Like', 1, 0)) as Apple,
        SUM(IF(Orange = 'Like', 1, 0)) as Orange,
        SUM(IF(Strawberry = 'Like', 1, 0)) as Strawberry
    FROM data
    UNION
    SELECT
        'Dislike' as 'preference',
        SUM(IF(Apple = 'Dislike', 1, 0)) as Apple,
        SUM(IF(Orange = 'Dislike', 1, 0)) as Orange,
        SUM(IF(Strawberry = 'Dislike', 1, 0)) as Strawberry
    FROM data
");

$table = array();
$table['cols'] = array(
    array('label' => 'preference', 'type' => 'string'),
    array('label' => 'Apple', 'type' => 'number'),
    array('label' => 'Orange', 'type' => 'number'),
    array('label' => 'Strawberry', 'type' => 'number')
);

$rows = array();
while($r = mysql_fetch_assoc($query)) {
    $temp = array();
    $temp[] = array('v' => $r['preference']);
    $temp[] = array('v' => (int) $r['Apple']);
    $temp[] = array('v' => (int) $r['Orange']);
    $temp[] = array('v' => (int) $r['Strawberry']);

    $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;

$jsonTable = json_encode($table);

echo $jsonTable;

The SQL should return two rows of data, one a sum of likes and the other a sum of dislikes, which then gets parsed into the Google Visualization API DataTable format and echo'd as a JSON string. This is good for use as an AJAX data source for the chart, but with a minor modification, it would be suitable for directly outputting the data into the javascript for drawing a chart.

When I use that JSON string to build a DataTable, it works for me. Post your javascript so I can test it. — asgallant, Aug 19 '13 at 02:54

score 0 · Answer 2 · edited Aug 18 '13 at 00:54

"Here's the code I've been looking at and I've yet figured out how to attack it."

Well here's how to debug it, say these are the steps you are taking.

Get data from db
Create an array
Json encode the array
Send the Json to the chart

Try and hard code an array at step 2. above, removing step 1 from the equation for a moment.

Now, going forward from that step, does the rest of the code as it should? Can you see the chart that expect using the hard-coded values?

If yes, well now work backwards, var_dump()ing data till it matches the hard-coded values you had previously.

Building a Google Chart with PHP and MySQL

2 Answers2