Encode PHP array to JSON and add to chart row

Wesley Chin

unread,

Jun 11, 2012, 3:09:59 AM6/11/12

to Google Visualization API

Hi everyone,

I was wondering if you could please help me out, I am trying to pull
data (status and value) from a table in php, save it to an array (and
encode it to JSON??) and then use the encoded JSON array to add the
rows in the Pie Chart I am trying to create. Can anyone please guide
me on how to successfully do this?

My code below:

<?
// Connection to the database
$db = mysql_connect("localhost", "root", "elves") or die("Could not
connect");
mysql_select_db("wlp_FIX001");

// Selecting the data
$s = "SELECT * FROM `lu_opportunity_status`";
$m = mysql_query($s) or die("$s dies - " . mysql_error());

while ($row = mysql_fetch_array($m)) {

$oppStatusName = $row['status'];

$s1 = "SELECT `lu_opportunity_status`.`status`,
SUM(`opportunities`.`value`) AS `totalvalue` FROM `opportunities` LEFT
JOIN `lu_opportunity_status` ON `opportunities`.`status` =
`lu_opportunity_status`.`id` WHERE `lu_opportunity_status`.`status` =
'$oppStatusName'";
$m1 = mysql_query($s1) or die("$s1 dies - " . mysql_error());

while ($row1 = mysql_fetch_array($m1)) {

$oppStatus = $row1['status'];
$oppValue = $row1['totalvalue'];
$oppStageTotals = array("status" => $oppStatus, "oppValue" =>
$oppValue);
// I need some code here to encode the array???
}
}
?>
<html>
<head>
<TITLE>Form Scaff Charts</TITLE>


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

<script type="text/javascript">

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

// Set a callback to run when the Google Visualization API
is loaded.
google.setOnLoadCallback(drawChart);

// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.
function drawChart() {
// Create the data table.
var data = new google.visualization.DataTable();
data.addColumn('string', 'Status');
data.addColumn('number', 'Value');
data.addRows([
// I need the code here to insert the rows???
]);

// Set chart options
var options = {'title':'Opportunities',
'width':300,
'height':300};

// Instantiate and draw our chart, passing in some
options.
var chart = new
google.visualization.PieChart(document.getElementById('chart_div'));

function selectHandler() {
var selectedItem = chart.getSelection()[0];
var selectedItem2 = chart.getSelection()[1];
if (selectedItem) {
var topping = data.getValue(selectedItem.row,
0);
var amount = data.getValue(selectedItem.row,
1);
}
}

google.visualization.events.addListener(chart,
'select', selectHandler);
chart.draw(data, options);
}
</script>
<style>
body {
margin:0px;
padding:0px;
}

.container {
width: 210mm;
height: 297mm;
margin-left: auto;
margin-right: auto;
}

@media print
{
.container {
position: absolute;
top: 0px;
bottom: 0px;
left: 0px;
right: 0px;
}

iframe {
/* float:left;
display: block;*/
}

}
</style>
</head>
<body>
<div class="container">
<div id="chart_div" class=span4></div>
</div>
</body>
</html>

asgallant

unread,

Jun 11, 2012, 11:40:23 AM6/11/12

to google-visua...@googlegroups.com

To make a JSON representation of a DataTable in JSON, you need to create a multi-dimensional array of your output data and then use PHP json_encode() function. The json_encode function translates associative arrays into object maps and non-associative arrays into javascript arrays. Start by defining the columns in your DataTable:

$dataTable = array(

'cols' => array(

// each column needs an entry here, like this:

array('type' => 'string', 'label' => 'foo'),
array('type' => 'number', 'label' => 'bar')

)

);

Then, while iterating over the results of the query, add the rows:

while ($row = ....) {

....

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

'c' => array (

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

)

);

}

Then call json_encode on the array:

$json = json_encode($dataTable);

You can then echo the json into the DataTable constructor:

// Load the Visualization API and the piechart package.

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

// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);

// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.

function drawChart() {
// Create the data table.

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

  // Set chart options
  var options = {
  'title': 'Opportunities',
  'width': 300,
  'height': 300
  };

  // Instantiate and draw our chart, passing in some options.
  var chart = new google.visualization.PieChart(document.getElementById('chart_div'));

  function selectHandler() {
  var selectedItem = chart.getSelection()[0];
  var selectedItem2 = chart.getSelection()[1];
  if (selectedItem) {
  var topping = data.getValue(selectedItem.row, 0);
  var amount = data.getValue(selectedItem.row, 1);
  }
  }

  google.visualization.events.addListener(chart, 'select', selectHandler);
  chart.draw(data, options);
}

Note that you only want to call google.load(...) once. You can load both the corechart and table packages at the same time if you want to.

Wesley Chin

unread,

Jun 13, 2012, 3:38:21 AM6/13/12

to google-visua...@googlegroups.com

Hi there,

Thanks, I did the alterations but it still doesn't want to show the chart. Here is my updated code (am I missing something?):

<?

// Connection to the database

$db = mysql_connect("localhost", "root", "elves") or die("Could not

connect");

mysql_select_db("wlp_FIX001");

// Selecting the data

$s = "SELECT * FROM `lu_opportunity_status`";

$m = mysql_query($s) or die("$s dies - " . mysql_error());

while ($row = mysql_fetch_array($m)) {

$oppStatusName = $row['status'];

$s1 = "SELECT `lu_opportunity_status`.`status`,

SUM(`opportunities`.`value`) AS `totalvalue` FROM `opportunities` LEFT

JOIN `lu_opportunity_status` ON `opportunities`.`status` =

`lu_opportunity_status`.`id` WHERE `lu_opportunity_status`.`status` =

'$oppStatusName'";

$m1 = mysql_query($s1) or die("$s1 dies - " . mysql_error());

$dataTable = array(

'cols' => array(

// each column needs an entry here, like this:

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

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

)

);

while ($row1 = mysql_fetch_array($m1)) {

$oppStatus = $row1['status'];

$oppValue = $row1['totalvalue'];

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

'c' => array(

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

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

)

);

print_r($dataTable);

}

$json = json_encode($dataTable);

}

?>

<html>

<head>

<TITLE>Form Scaff Charts</TITLE>

<!--<link rel="stylesheet" media="print" title="Printer-

Friendly Style"

type="text/css" href="print.css">-->

jsapi"></script>

// Load the Visualization API and the piechart package.

google.load("visualization", "1", {packages:

["corechart", 'table']});

//google.load('visualization', '1.0', {'packages':

//['corechart']});

//google.load('visualization', '1', {packages:['table']});

// Set a callback to run when the Google Visualization API is loaded.

google.setOnLoadCallback(drawChart);

// Callback that creates and populates a data table,

// instantiates the pie chart, passes in the data and

// draws it.

function drawChart() {

// Create the data table.

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

// Set chart options

var options = {'title':'Opportunities',

'width':300,

'height':300};

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

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

function selectHandler() {

// var selectedItem = chart.getSelection()[0];

// var selectedItem2 = chart.getSelection()[1];

// if (selectedItem) {

// var topping = data.getValue(selectedItem.row,

// 0);

// var amount = data.getValue(selectedItem.row,

// 1);

// }

Thanks,

Wesley

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/sZ9JMsF7DRYJ.

To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

Wesley Chin

unread,

Jun 13, 2012, 7:12:37 AM6/13/12

to google-visua...@googlegroups.com

Thank you, I have solved it, the format of the JSON was incorrect!

On Wed, Jun 13, 2012 at 10:19 AM, John M <jm.li...@gmail.com> wrote:

Do you get any javascript error? ( You can check it with firebug in Firefox or ctrl+shift+i in Chrome )
Try to check the json with an online json validator.

--

You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/UFhMmxvx24QJ.

Ákos Kovács

unread,

Aug 18, 2014, 4:07:25 AM8/18/14

to google-visua...@googlegroups.com

How can you please encode multiple lines?

To unsubscribe from this group, send email to google-visualization-api+unsub...@googlegroups.com.

Reply all

Reply to author

Forward