Chart with MySql and PhP

[GutoW]

Hi, I'm a brazillian developper and i'm need a help with google chart
api and mysql with php.

I have some tables where i create a query for give me the results. At
here it's ok, but how i get the information of my query and sent to
google chart api to give the chart to put on my website...? i'm
looking for some codes in the web but i don't codify to give my
prupose.

Any body help-me ?


Ps.: Sorry my english is poor...

[everest_man27]

I have a similar challenge - looking for advise on how to link mysql
using php into google visualization api to be able to create dynamic
chart. The documentation on connecting to data source is not clear on
how to connect to a mysql data base.

Advise on how to connect to mysql is greatly appreciated.

Thank you.

[Still]

Just build the URL from your retrieved data. Here is a sloppy example
for a
pie chart function.

function piechart($data, $name = 'piechart') {
$counter = count($data['metrics']);
$columns = (count($data,1)/count($data,0))-1;
$return = "<img src='http://chart.apis.google.com/chart?";
$return .= "chs=600x200&";
$return .= "cht=p&";
$return .= "chco=3399CC,FF9900,80C65A,7777CC,990066,999999&";
$return .= "chd=t:";
$total = 0;
foreach ($data['values'] as $key => $value)
{
$total += $value;
}
$i = 0;
foreach ($data['values'] as $key => $value)
{
if ($i == 0){
$return .= ($value/$total*100);
} else {
$return .= ",".($value/$total*100);
}
$i++;
}
$return .= "&chl=";
foreach ($data['metrics'] as $key => $value)
{
$return .= $value."|";
}
$return .= "' />";
return($return);

[everest_man27]

I have sensitive data and would not like to send this to google server
instead would like to use the javascript charts visualisation api and
avoids having to send the data to the server. Can you advise on how
to connect the mysql to the visualization api.

Thanks

> > > Ps.: Sorry my english is poor...- Hide quoted text -
>
> - Show quoted text -

[lucamag]

On 05/19/2011 05:43 AM, everest_man27 wrote:
> I have sensitive data and would not like to send this to google server
> instead would like to use the javascript charts visualisation api and
> avoids having to send the data to the server. Can you advise on how
> to connect the mysql to the visualization api.

Hi,
sorry, may be I'm missing something... but did you try with the Data
Source Library (http://code.google.com/p/google-visualization-java)?
With that library you can easily build a Java servlet exposing your
MySQL tables (or views..) as datasource, ready to be used by the
javascript google charts.

Regards,
Luca

[asgallant]

If you don't want to create a data source to Google's specs, you can implement your own (just don't expect it to be compatible with anything anyone else does).  Set up an AJAX query (I like jQuery for this):

// form is the js object for the form you are submitting

function submitQuery (form) {

var data = $(form).serialize();

$.ajax({

data: data,

url: "path/to/source.php",

method: "POST", // or method: "GET"

success: function (json) {

drawChart(json);

}

});

}

// drawChart takes the json and builds the data table with it

function drawChart(json) {

var data = new google.visualization.dataTable(json);

var chart = new google.visualization.<chartType>(document.getElementById("chart_div");

chart.draw(data, <options>);

}

then in your source php, query MySQL, build a json string out of your data (formatted for you chart), and echo it, with the "Content-type: application/json" header:

<?php

header("Content-type: application/json");

// $json hold the json string 

echo $json;

exit;

?>

Information on the format of the JSON is here: http://code.google.com/apis/chart/interactive/docs/dev/implementing_data_source.html#jsondatatable

[GutoW]

Hi,

I'm afraid, i'm not a expecialist in json programmer, but i need to
construct a same chart with sensistive data. I want to use javascript
to this, but i have the data from a mysql php query... anibody have an
example for this case? I think for my case i don't use datasource
ready i'm like to create my datasource (rsrsrs but i don't now
how....). Please if anibody think this is a stupid idea telme....
Thanks.....

> Information on the format of the JSON is here:http://code.google.com/apis/chart/interactive/docs/dev/implementing_d...

[asgallant]

You don't have to be an expert to make this work, JSON is just a string representation of your data.  For example, if you queried MySQL for columns "foo" and "bar", your code might look something like this:

// $json holds the JSON string

$json = '{"cols": [{"id": "foo", "label": "Foo", "type": "string"}, {"id": "bar", "label": "Bar", "type": "number"}], "rows": [';

// $results is an associative array holding the results of the query

foreach ($results as $row) {

     // add $row["foo"] to the first column in each row, and add $row["bar"] to the second column in each row

     $json .= '{"c": [{"v": "'. $row["foo"]. '"}, {"v": '. $row["bar"]. '}]}, ';

}

$json .= ']}';

// set header

header("Content-type: application/json");

// return data

echo $json;

[asgallant]

This is probably more helpful than the previous link I posted: http://code.google.com/apis/chart/interactive/docs/reference.html#dataparam

That explains the object format that the DataTable expects.  Note that if you use jQuery's AJAX methods, you have to put double-quotes around all object property names and strings in the JSON string or you will get a parse error.

[cont...@cm-ordenadores.es]

i`ve got a similar problem here, but i make things a kinda different
way:
$.post("getvotes.php",
{
votes:1,
type: opt

}, function(datax) {

vot=new
Array(parseInt(datax.YES),parseInt(datax.NO),parseInt(datax.DONO));
google.load('visualization', '1', {'packages':['corechart']});
google.setOnLoadCallback(drawChart(vot));
function drawChart(vot) {

car data = new google.visualization.DataTable();
data = new google.visualization.DataTable();
data.addColumn('string', 'Answers');
data.addColumn('number', 'Votes');
data.addColumn('number', 'Votes');
data.addColumn('number', 'Votes');
data.addRows(3);
data.setValue(0, 0, 'YES');
data.setValue(0, 1,vot[0]);
data.setValue(0, 2,0);
data.setValue(0, 3,0);

data.setValue(1, 0, 'No');
data.setValue(1, 1,0);
data.setValue(1, 2, vot[1]);
data.setValue(1, 3,0);

data.setValue(2, 0, 'NS/NC');
data.setValue(2, 1,0);
data.setValue(2, 2,0);
data.setValue(2, 3, vot[2]);....},"json")

the thing is it is working realy well in mozila, chrome and even
IE9...i got problems with IE versions less then 9 :
SCRIPT5022: Every row given must be either null or an array.
default,corechart.I.js, line 152 character 63

[asgallant]

This probably won't fix your problem, but you should move the google.load... and google.setOnLoadCallback... calls outside the AJAX success function.  If you want the AJAX call made on page load, you could do:

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

google.setOnLoadCallback(drawChart);

function drawChart () {

$.post(....);

}

and populate the chart data with the returned json directly, instead of calling an intermediate function.

As far as fixing your problem, I would drop some console.log(<variable>); lines in there and debug in Chrome's developer console, just to make sure that your variables have exactly the structure you expect them too.  If they do, then there is probably some javascript function that doesn't quite work right in IE < 9 that you will have to isolate and work around.

[asgallant]

I beg to differ, as I have multiple projects using the Visualization API running off of MySQL and PHP.  What is the problem you are experiencing?

On Wednesday, November 21, 2012 4:53:57 PM UTC-5, Fullmanido wrote:

This has been a huge problem, and that is I have not seen one php site that actually has this working. Yes, we can manipulate the tables in the html to make them work, as a whole I have not see 1 site that has accomplished this. Go to the forums and you will get code snippets, and "all you need to do" quotes, but believe me, I have not found a working template of this offering. Most of my friends have given up and went the flash route via xml. I am still holding on for a decent error free chart that will work seamlessly with html 5, php, and mysql. good luck.