PLS HELP! creating Google Charts using MySQL data and PHP

melm

unread,

Mar 24, 2013, 11:06:21 AM3/24/13

to google-visua...@googlegroups.com

Hi,

what I'd like to do is pull data from from mysql and display it in Google charts. I have two tables

1st table is the students table which has the following columns:

user_id (PK) | student_name

2nd table is tasks table which has the following columns

task_id (PK) | task_name | task_status (can have 'complete' and 'not_complete' values) | task_assignee_id (which is a FK that references to user_id in students table)

All I would like to do is, display the number or percentage of complete and not complete tasks for a specific student. For example for student_id: 5 if the tasks table looks like this:

task_id | task_name | task_status | task_assignee_id

1 test complete 5

2 test complete 5

3 test not_complete 5

4 test not_complete 5

And I need the chart to display something like this:

So what I've already got is 2 php files. the first one lets you choose the student name from a dropdown list and the second one populates the chart.

first script

<html>
<head>
  <!--Load the AJAX API-->
  <script type="text/javascript" src="http://www.google.com/jsapi"></script>
  <script type="text/javascript" src="jquery-1.7.1.min.js"></script>
  <script type="text/javascript">

  // Load the Visualization API and the piechart,table package.
  google.load('visualization', '1', {'packages':['corechart','table']});

  function drawItems(num) {
    var jsonPieChartData = $.ajax({
      url: "getpiechartdata.php",
      data: "q="+num,
      dataType:"json",
      async: false
    }).responseText;

    var jsonTableData = $.ajax({
      url: "gettabledata.php",
      data: "q="+num,
      dataType:"json",
      async: false
    }).responseText;

    // Create our data table out of JSON data loaded from server.
    var piechartdata = new google.visualization.DataTable(jsonPieChartData);
    var tabledata = new google.visualization.DataTable(jsonTableData);

    // Instantiate and draw our pie chart, passing in some options.
    var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
    chart.draw(piechartdata, {
      width: 700,
      height: 500,
      chartArea: { left:"5%",top:"5%",width:"90%",height:"90%" }
    });

    // Instantiate and draw our table, passing in some options.

    var table = new google.visualization.Table(document.getElementById('table_div'));
    table.draw(tabledata, {showRowNumber: true, alternatingRowStyle: true});
  }

  </script>
</head>
<body>
  <form>
  <select name="users" onchange="drawItems(this.value)">
  <option value="">Select a student:</option>
  <?php
    $dbuser="";
    $dbname="";
    $dbpass="";
    $dbserver="";
    // Make a MySQL Connection
    mysql_connect($dbserver, $dbuser, $dbpass) or die(mysql_error());
    mysql_select_db($dbname) or die(mysql_error());
    // Create a Query
    $sql_query = "SELECT user_id, user_name FROM students";
    // Execute query
    $result = mysql_query($sql_query) or die(mysql_error());
    while ($row = mysql_fetch_array($result)){
    echo '<option value='. $row['user_id'] . '>'. $row['user_name'] . '</option>';
    }
    mysql_close($con);
  ?>
  </select>
  </form>
  <div id="chart_div"></div>
  <div id="table_div"></div>
</body>
</html>

and this php file to populate the chart based on the student id selected

<?php
  $q=$_GET["q"];

  $dbuser="";
  $dbname="";
  $dbpass="";
  $dbserver="";

  $sql_query = "SELECT task_status, COUNT(*) FROM tasks
    WHERE  task_student_id=" . $q . ""

  $con = mysql_connect($dbserver,$dbuser,$dbpass);
  if (!$con){ die('Could not connect: ' . mysql_error()); }
  mysql_select_db($dbname, $con);

  $result = mysql_query($sql_query);

    $data = array('cols' => array(array('label' => 'Not completed', 'type' => 'string'),
                              array('label' => 'Completed', 'type' => 'string')),
              'rows' => array());

    while($row = mysql_fetch_row($result)) {
   $data['rows'][] = array('c' => array(array('v' => $row[0]), array('v' => $row[1])));
}    

echo json_encode($data);



  mysql_close($con);
?>

There definitely is something wrong with the second php file, could anyone please help as its for a school project?

Thanks in adnvance

asgallant

unread,

Mar 24, 2013, 11:19:03 AM3/24/13

to google-visua...@googlegroups.com

The code looks mostly good, the only thing that sticks out is your column definitions. You want two columns, but they should be one string type for task status and 1 number type for count. Some versions of MySQL also output numbers as strings, so it helps to explicitly type convert the output from the count. Try this:

$q=$_GET["q"];

$dbuser="";

$dbname="";

$dbpass="";

$dbserver="";

$sql_query = "SELECT task_status, COUNT(*) FROM tasks

WHERE task_student_id=" . $q . ""

$con = mysql_connect($dbserver,$dbuser,$dbpass);

if (!$con){ die('Could not connect: ' . mysql_error()); }

mysql_select_db($dbname, $con);

$result = mysql_query($sql_query);

$data = array(

'cols' => array(

array('label' => 'Task Status', 'type' => 'string'),

array('label' => 'Count', 'type' => 'number')

),

'rows' => array()

);

while($row = mysql_fetch_row($result)) {

$data['rows'][] = array('c' => array(array('v' => $row[0]), array('v' => (int) $row[1])));

}

echo json_encode($data);

mysql_close($con);

melm

unread,

Mar 24, 2013, 11:32:26 AM3/24/13

to google-visua...@googlegroups.com

Thank you for this however I still have 2 problems, first when I select the name from dropdown list it doesn't automatically populate the chart php, it just doesnt do anything when I select a name.

Second, I tried to test the 2nd php code with a static value with student_id: 6 it gave the following error:

Parse error: syntax error, unexpected T_VARIABLE on line 17

for this line: $result = mysql_query($sql_query);

asgallant

unread,

Mar 24, 2013, 5:21:45 PM3/24/13

to google-visua...@googlegroups.com

You are missing the semicolon at the end of the query statement:

$sql_query = "SELECT task_status, COUNT(*) FROM tasks WHERE task_student_id=" . $q . "";

Nothing updates in your javascript because in the "onchange" event, "this" refers to the <select> element and not the selected option. What you want is this:

melm

unread,

Mar 24, 2013, 5:53:00 PM3/24/13

to google-visua...@googlegroups.com

thanks for looking into my issue.

the thing is the first page still isnt populating the graph after i've chosen the name, do you think the issue is within this code:

var jsonTableData = $.ajax({

url: "gettabledata.php",

data: "q="+num,

dataType:"json",

async: false

}).responseText;

Also, I have run the second php to see if it was populating the chart with a static value, all it displayed on the screen was this:

{"cols":[{"label":"Task Status","type":"string"},{"label":"Count","type":"number"}],"rows":[{"c":[{"v":"Completed"},{"v":5}]}]}

so it didnt actually draw the pie chart :(

Also I had 3 completed and 2 not completed values in the task_status column for that user, but it's only showing 5 completed. I think it's merging them into one...

asgallant

unread,

Mar 24, 2013, 7:44:09 PM3/24/13

to google-visua...@googlegroups.com

It probably is merging them. The SQL needs a "group by" clause:

$sql_query = "SELECT task_status, COUNT(*) FROM tasks WHERE task_student_id=" . $q . " GROUP BY task_status";

The JSON constructed by the PHP looks good, and the javascript looks good too. When you run the script, do you get an error messages (run in chrome, and open the developer's console to check [ctrl+shift+j to open])?

melm

unread,

Mar 24, 2013, 8:57:51 PM3/24/13

to google-visua...@googlegroups.com

THANKS SO MUCH, it works now ! PHEW ! I had the issue with the dropdown list because i didnt install the jquery properly.

could I please ask you for one more thing, this is not necessary but would be nice. can I display the numbers on the chart itself or next to where it says Open/Closed like Open:6 closed: 4

asgallant

unread,

Mar 24, 2013, 9:23:19 PM3/24/13

to google-visua...@googlegroups.com

You can set the "pieSliceText" option to "value" which will replace the percentages with the value of the slice, or "label" which will replace the percentages with the name of the slice.

melm

unread,

Mar 25, 2013, 8:16:00 PM3/25/13

to google-visua...@googlegroups.com

thanks for all your help, everything works now

Reply all

Reply to author

Forward