From a JSON format to a Pie Chart (PHP, MySQL)

[Hugo Mendoza]

Hi there - 

I figured out how to put my data into a JSON format using PHP and MySQL, see below.  Now, could someone please help me create, say, a pie chart with Google Charts.  I have not done this before, and I could use some help creating it.  

<?php

// Connect to MySQL

include ("./mylibrary/login.php");

login();

// Fetch the data

$query = "

   SELECT priority, COUNT(*) AS num_count\n"

     . " FROM issue\n"

     . " GROUP BY priority ";

$result = mysql_query( $query );

// All good?

if ( !$result ) {

// Nope

$message  = 'Invalid query: ' . mysql_error() . "\n";

$message .= 'Whole query: ' . $query;

die( $message );

}

// Print out rows

$prefix = '';

echo "[\n";

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

echo $prefix . " {\n";

echo '  "priority": "' . $row['priority'] . '",' . "\n";

echo '  "num_count": ' . $row['num_count'] .  "\n";

echo " }";

$prefix = ",\n";

}

echo "\n]";

?>  

Thank you, 

Hugo

[asgallant]

Your JSON format is not correct for the Visualization API, and it will cause IE to throw an error (due to an extra comma at the end of the array of objects).  Here's the correct format:

$table = array(
    'cols' => array(
        array('label' => 'priority', 'type => 'string'),
        array('label' => 'num_count', 'type => 'number')
    ),
    'rows' => array()
);
while ($row = mysql_fetch_assoc($result)) {
    $table['rows'][] = array(
        'c' => array(
            array('v' => $row['priority']),
            array('v' => $row['num_count'])
        )
    )
}
echo json_encode($table, JSON_NUMERIC_CHECK);

You can put the echo statement in your javascript for drawing the chart:

function drawChart () {
    var data = new google.visualization.DataTable(<?php echo json_encode($table, JSON_NUMERIC_CHECK); ?>);
    var chart = new google.visualization.PieChart(document.querySelector('#chart_div'));
    chart.draw(data, {
        // put the chart options in here
        height: 400,
        width: 600
    });
}
google.load('visualization', '1', {packages: ['corechart'], callback: drawChart});

and in your HTML, you need a div like this to put the chart in:

<div id="chart_div"></div>

[Hugo Mendoza]

That was a very fast response--thank you!  

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/Ncg-2tCEmlE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.

[Hugo Mendoza]

I am close, but I am getting this error:  Warning: json_encode() expects exactly 1 parameter, 2 given in /homepages/36/d339323257/htdocs/LogIt/data-file.php on line 40

Here is my code:

<?php

// Connect to MySQL

include ("./mylibrary/login.php");

login();

// Fetch the data

$query = "

   SELECT priority, COUNT(*) AS num_count\n"

     . " FROM issue\n"

     . " GROUP BY priority ";

$result = mysql_query( $query );

// All good?

if ( !$result ) {

// Nope

$message  = 'Invalid query: ' . mysql_error() . "\n";

$message .= 'Whole query: ' . $query;

die( $message );

}

// Print out rows

$table = array(

    'cols' => array(

        array('label' => 'priority', 'type' => 'string'),

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

    ),

    'rows' => array()

);

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

    $table['rows'][] = array(

        'c' => array(

            array('v' => $row['priority']),

            array('v' => $row['num_count'])

        )

    );

}

echo json_encode($table, JSON_NUMERIC_CHECK); 

?>

On Saturday, November 16, 2013 5:00:15 PM UTC-5, Hugo Mendoza wrote:

[Hugo Mendoza]

Never mind, I went with the following and it worked:  

    

      echo json_encode($table); 

Thank you.

On Saturday, November 16, 2013 5:00:15 PM UTC-5, Hugo Mendoza wrote:

[Hugo Mendoza]

Hi there - 

Here is what I receive from my db (which I think is fine): {"cols":[{"label":"priority","type":"string"},{"label":"num_count","type":"number"}],"rows":[{"c":[{"v":"High"},{"v":"4"}]},{"c":[{"v":"Low"},{"v":"3"}]},{"c":[{"v":"Medium"},{"v":"7"}]},{"c":[{"v":"Urgent"},{"v":"2"}]}]}

But when I run this (posted below), I get the following error message: Table has no columns.  Could someone help me out?  Thank you!

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Sample Pie Chart</title>

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>

<script type="text/javascript">

      google.load("visualization", "1", {packages:["corechart"]});

      google.setOnLoadCallback(drawChart);

      function drawChart() {

      var jsonData = $.ajax({

          url: "data-file.php",

          dataType:"json",

          async: false

          }).responseText;

      var data = new google.visualization.DataTable(<?php echo json_encode($table); ?>);

      var chart = new google.visualization.PieChart(document.querySelector('#chart_div'));

      chart.draw(data, {

          // put the chart options in here

          height: 800,

          width: 600

      });

      }

 google.load('visualization', '1', {packages: ['corechart'], callback: drawChart});

    </script>

</head>

<body>

   

<p>Pie Chart </p>  

    <div id="chart_div" style="width: 900px; height: 500px;"></div>

    

</body>

</html>

On Saturday, November 16, 2013 5:00:15 PM UTC-5, Hugo Mendoza wrote:

[Daniel LaLiberte]

I see you have two google.load() calls, one with a 'callback', and one without, followed by the setOnLoadCallback.  I'm not sure what that is going to do, but it doesn't seem like a good idea.

dan

--
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 google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.

--

Daniel LaLiberte  - 978-394-1058

dlaliberte@Google.com   5CC, Cambridge MA

daniel.laliberte@GMail.com 9 Juniper Ridge Road, Acton MA

[asgallant]

Are you fetching the data via AJAX?  If so, then you need to change the DataTable constructor to use jsonData instead of the echo statement:

var data = new google.visualization.DataTable(jsonData);

Dan is correct -  you only want one google.load call.  Remove these redundant lines:

google.load("visualization", "1", {packages:["corechart"]});

google.setOnLoadCallback(drawChart);

Also, if you are getting an error when using

echo json_encode($table, JSON_NUMERIC_CHECK);

then your PHP version is old and out of date, I would recommend upgrading to the latest version if that is an option for you.  If you can't upgrade, then you need to make one small adjustment in your PHP code to correct the numbers: change this line:

array('v' => $row['num_count'])

to this:

array('v' => (int) $row['num_count'])

as your database is outputting the counts as strings instead of integers.  The Visualization API will throw an error when you present a number as a string, so you have to convert them into proper numbers (int or float, depending on the type of number) when creating the JSON string (which is what JSON_NUMERIC_CHECK does automatically in recent versions of PHP).

[Hugo Mendoza]

Thank you for all the help.  

I am still having some problems, but I think that I am almost there.  

Here is the code to my data-file.php (see below), which returns this JSON format: {"cols":[{"label":"priority","type":"string"},{"label":"num_count","type":"number"}],"rows":[{"c":[{"v":"High"},{"v":4}]},{"c":[{"v":"Low"},{"v":3}]},{"c":[{"v":"Medium"},{"v":7}]},{"c":[{"v":"Urgent"},{"v":2}]}]}

<?php

include ("./mylibrary/login.php");

login();

$query = "

   SELECT priority, COUNT(*) AS num_count\n"

     . " FROM issue\n"

     . " GROUP BY priority ";

$result = mysql_query( $query );

if ( !$result ) {

$message  = 'Invalid query: ' . mysql_error() . "\n";

$message .= 'Whole query: ' . $query;

die( $message );

}

$table = array(

    'cols' => array(

        array('label' => 'priority', 'type' => 'string'),

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

    ),

    'rows' => array()

);

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

    $table['rows'][] = array(

        'c' => array(

            array('v' => $row['priority']),

array('v' => (int) $row['num_count'])  //Using this because PHP server with host is out of date

        )

    );

}

echo json_encode($table); 

?>

Here is my other file, but it does not display a chart and am not getting any errors (I tried both html and php extensions with no luck):

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Sample Pie Chart</title>

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript">

     

 google.load("visualization", "1", {packages:["corechart"]});

      function drawChart() {

      

      var jsonData = $.ajax({   

          url: "data-file.php",

          dataType:"json",

          async: false

          }).responseText;

      var data = new google.visualization.DataTable(<?php echo json_encode($table); ?>);

      var chart = new google.visualization.PieChart(document.querySelector('#chart_div'));

      chart.draw(data, {

          // put the chart options in here

          height: 800,

          width: 600

      });

      }

 

    </script>

</head>

<body>

   

<p>Pie Chart Sample</p>  

    <div id="chart_div" style="width: 900px; height: 500px;"></div>

    

</body>

</html>

Thank you in advance for any help that you can provide.  

On Saturday, November 16, 2013 5:00:15 PM UTC-5, Hugo Mendoza wrote:

[asgallant]

You deleted the jQuery script tag, which is needed to use the $.ajax function:

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>

[Hugo Mendoza]

Thank you, I appreciate the help.  Adding the tag did not help, still no chart.  I'll look over things a bit more and see what the cause might be.  Again, thank you for your time.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/Ncg-2tCEmlE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

Check the developer's console in Chrome for errors (ctrl+shift+j to open it in the Windows version).

To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsub...@googlegroups.com.

[Hugo Mendoza]

Hi - I just checked it; it is empty.  I went through all the options, refreshing the page throughout to make sure.  Nothing comes back.

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[Hugo Mendoza]

here are the locations, just in case:

http://www.hugomendoza.com/LogIt/chart-myTest.php

http://www.hugomendoza.com/LogIt/data-file.php

On Sun, Nov 17, 2013 at 7:09 PM, asgallant <drew_g...@abtassoc.com> wrote:

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[asgallant]

 Your current version of "chart-myTest.php" is missing the enhancements I said you should make above.  Replace the javascript in that file with this:

function drawChart() {
    var jsonData = $.ajax({  
        url: "data-file.php",
        dataType:"json",
        async: false
    }).responseText;

    var data = new google.visualization.DataTable(JSON.parse(jsonData));

    var chart = new google.visualization.PieChart(document.getElementById('chart_div'));

    chart.draw(data, {
        // put the chart options in here
        height: 800,

        width: 800
    });
}
google.load('visualization', '1', {'packages':['corechart'], callback: drawChart});

[Hugo Mendoza]

That did it!

Thank you for all your help.  

--

[Andres Guilllermo Caviedes Campos]

I am trying to send the result of the query, but not how. I need to plot the total number of employees by gender. Initially I have total men. As I can make this graph a pie chart with Google Charts?  I could help?

<html>

<head>

<title>Salario Aproximado</title>

</head>

<body>

<?php

$conexion=mysql_connect(" "," "," ") or

  die("Problemas en la conexion");

mysql_select_db(" ",$conexion) or

  die("Problemas en la selección de la base de datos");

$query="SELECT COUNT( * ) AS HOMBRES FROM empleado WHERE sex_emp =  'HOMBRE'";

$resultado=mysql_query($query);

if ( !$result ) {

// Nope

$message  = 'Invalid query: ' . mysql_error() . "\n";

$message .= 'Whole query: ' . $query;

die( $message );

}

$table = array(

    'cols' => array(

        array('label' => 'HOMBRES', 'type => 'string')

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

    ),

    'rows' => array()

);

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

    $table['rows'][] = array(

        'c' => array(

            array('v' => $row['HOMBRES']),

            array('v' => $row['sex_emp'])

        )

    )

}

echo json_encode($table, JSON_NUMERIC_CHECK);

 

mysql_close($conexion);

?>

</body>

</html>

[Andrew Gallant]

This will be easier to demonstrate with both genders.  First, adjust your query to group by gender:

$query="SELECT sex_emp, COUNT( * ) AS cnt FROM empleado GROUP BY sex_emp";

and build your DataTable like this:

$table = array(

    'cols' => array(

        array('label' => 'Gender', 'type => 'string')

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

    ),

    'rows' => array()

);

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

    $table['rows'][] = array(

        'c' => array(

            array('v' => $row['sex_emp']),

            array('v' => $row['cnt'])

        )

    )

}

Then, you need to remove this line:

echo json_encode($table, JSON_NUMERIC_CHECK);

as it needs to output the data to the DataTable constructor.  Add in some chart code; as an example:

<script type="text/javascript" src="http://www.google.com/jsapi" />

<script type="text/javascript">

function drawChart() {

    var data = new google.visualization.DataTable(<?php echo json_encode($data, JSON_NUMERIC_CHECK); ?>);

    

    var chart = new google.visualization.PieChart(document.querySelector('#chart'));

    chart.draw(data, {

        height: 400,

        width: 600

    });

}

google.load('visualization', '1', {packages:['corechart'], callback: drawChart});

</script>

and a <div> to hold the chart:

<div id="chart"></div>

The ID of the chart should match the ID used in the document.querySelector('#chart') call.