Creating a Google chart using PHP and MySQL

Question

I am new to PHP and MySQL. I am trying to build a Google chart from a MySQL database, but after searching Google for countless hours, I could not find what I needed. However, I found an example that might be useful, but I still can’t do it the way I want. Here is 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 characters and Dislike for Apple , Orange and Strawberry . In the chart, I want it to show how many people love and dislike these 3 fruits.

Here is the code I was looking at, and I still have not 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 will help! Thanks.

Answer 1

Try this like 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; 

SQL should return two rows of data, one sum of likes and another sum of dislikes, which is then parsed in the DataTable format of the Google visualization API and displayed as a JSON string. This is useful for use as an AJAX data source for a chart, but with a slight modification, it would be convenient to directly output the data in javascript to draw a chart.

Answer 2

"Here is the code I was looking at, and I still have not figured out how to attack it."

Well, here's how to debug it, say, those are the steps you take.

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

Try and copy the array in step 2. above by removing step 1 from the equation for a moment.

Now, moving forward from this step, should the rest of the code be as it should? Can you see a chart awaiting the use of hard-coded values?

If so, now now work back, var_dump() with the data until it matches the previously encoded values.