title: Populating MySQL data with Google Charts
body: <p>I'd like to create a pie chart which basically should display how many tasks a student has completed out of all tasks he has been assigned to. so my table for tasks looks like this</p>
  
  <p>tasks
  task_id | task_student_id | task_status</p>
  
  <p>*task_student_id* references the *user_id* in the students table</p>
  
  <p>So lets say my sample data for student with user_id looks like this in the tasks table</p>
  
  <p>task_id | task_student_id |task_status
    1              6          complete
    2              6          complete
    3              6          not_complete
    4              6          not_complete
    5              6          complete</p>
  
  <p>So the pie chart for this student should display either the number of completed and not completed tasks or the percentage (i.e. 40% not complete 60%complete)</p>
  
  <p>I found a tutorial online. There's two php files the first one lets you select the user_id from students table</p>
  
  <pre><code>&lt;html&gt;
  &lt;head&gt;
    &lt;!--Load the AJAX API--&gt;
    &lt;script type="text/javascript" src="http://www.google.com/jsapi"&gt;&lt;/script&gt;
    &lt;script type="text/javascript" src="jquery-1.7.1.min.js"&gt;&lt;/script&gt;
    &lt;script type="text/javascript"&gt;
  
    // 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});
    }
  
    &lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
    &lt;form&gt;
    &lt;select name="users" onchange="drawItems(this.value)"&gt;
    &lt;option value=""&gt;Select a student:&lt;/option&gt;
    &lt;?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 '&lt;option value='. $row['user_id'] . '&gt;'. $row['user_name'] . '&lt;/option&gt;';
      }
      mysql_close($con);
    ?&gt;
    &lt;/select&gt;
    &lt;/form&gt;
    &lt;div id="chart_div"&gt;&lt;/div&gt;
    &lt;div id="table_div"&gt;&lt;/div&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>and then the second php file populates the chart based on the user_id selected</p>
  
  <pre><code>&lt;?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' =&gt; array(array('label' =&gt; 'Not completed', 'type' =&gt; 'string'),
                                array('label' =&gt; 'Completed', 'type' =&gt; 'string')),
                'rows' =&gt; array());
  
      while($row = mysql_fetch_row($result)) {
     $data['rows'][] = array('c' =&gt; array(array('v' =&gt; $row[0]), array('v' =&gt; $row[1])));
  }    
  
  echo json_encode($data);
  
  
  
    mysql_close($con);
  ?&gt;
  </code></pre>
  
  <p>I'm sure I'm doing something wrong with the arrays and the query. Also I was thinking of creating two separate sql queries and to save them in two php variables; $not_completed, $completed and populate it in the chart. Don't know which one is the best option for my question. can someone please help?</p>