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' =…

Accepted Answer

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.

Building a Google Chart with PHP and MySQL

Solution

Problem

Related problems