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);
On Sunday, March 24, 2013 11:06:21 AM UTC-4, melm wrote:
>
> 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:
>
>
> <https://lh5.googleusercontent.com/-wWc5MiWTNwk/UU8VY8tG-dI/AAAAAAAAAAM/wU5vyzp_Ujc/s1600/chartexample.bmp>
>
> 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
> *
> *
>
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at
http://groups.google.com/group/google-visualization-api?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
