Google Charts with PHP and MySQL

[rL]

hey,

i wanna build a simple PHP Page with retrieving all necessary data
from mysql but the the html starts with the javascript google function
and i cannot put my php data in cause php rendering is over...

how can i build up those graphs in php with getting data from mysql
without something complicated like JSON, AJAX and something...

Or is there any example of this anywhere?

Thanks

[Dinga Bogdan]

Hi, I will try to help you.

It is easy to get data from PHP/SQL using JASON format.
And also google api has a ajax "function" to get the data from the php
script.

If you could give me the columns, rows from youre sql I can show you a
more precise example.
Here the short version:
For PHP script you have to build 2 main arrays "rows" and "cols" .
Each array contains more arrays with values.
Something like this:
$rows = array('c' => array(
array( 'v' => $timestamp, 'f' => NULL ),
array( 'v' => (int)$getData_t1['power_avg'], 'f' => ''.
$getData_t1['power_avg'].' kWh' ),
array( 'v' => (int)$getData_t2['power_avg'], 'f' => ''.
$getData_t2['power_avg'].' kWh' ),
array( 'v' => (int)$getData_t3['power_avg'], 'f' => ''.
$getData_t3['power_avg'].' kWh' ),
array( 'v' => (int)$getData_t4['power_avg'], 'f' => ''.
$getData_t4['power_avg'].' kWh' ),
array( 'v' => (int)$getData_t5['power_avg'], 'f' => ''.
$getData_t5['power_avg'].' kWh' ),
array( 'v' => (int)$getData_t6['power_avg'], 'f' => ''.
$getData_t6['power_avg'].' kWh' ),
array( 'v' => (int)$getData_t7['power_avg'], 'f' => ''.
$getData_t7['power_avg'].' kWh' )
));
This is a single row with 8 columns. Now to build the columns:
$cols = array(
array( 'id' => '0', 'label' => 'Timestamp', 'type' => 'string'),
array( 'id' => '1', 'label' => 'WTG01', 'type' => 'number'),
array( 'id' => '2', 'label' => 'WTG02', 'type' => 'number'),
array( 'id' => '3', 'label' => 'WTG03', 'type' => 'number'),
array( 'id' => '4', 'label' => 'WTG04', 'type' => 'number'),
array( 'id' => '5', 'label' => 'WTG05', 'type' => 'number'),
array( 'id' => '6', 'label' => 'WTG06', 'type' => 'number'),
array( 'id' => '7', 'label' => 'WTG07', 'type' => 'number')
);
Not that we have the columns and for now 1 row, we echo out the json
string like this:

echo '{ "cols": '.json_encode($cols).', "rows":
'.json_encode($rows).'}';
This is it for the PHP, the script accessed from the browser itself
should print the JSON string.
For the js it is even simpler. Lets say that this PHP file is named
test.php
var jsonData = $.ajax({
url: "test.php",
dataType:"json",
async: false
}).responseText;

var data = new google.visualization.DataTable(jsonData);
And here you have the data table made based on the PHP JSON file :)

Give me the exact thing you want to do and I will try to make the
script for you. I had the same issue a few weeks ago.

[asgallant]

If you really don't want to use JSON, you can build the javascript with PHP directly.  As an example, put this inside the <script>:

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

function drawChart () {
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'foo');
    data.addColumn('number', 'bar');
    <?php
        // query MySQL and put results into array $results
        foreach ($results as $row) {
            echo "data.addRow(['{$row['foo']}', {$row['bar']}]);";
        }
    ?>
    var chart = new google.visualization.BarChart('chart_div');
    chart.draw(data, <options>);
}

[Dinga Bogdan]

Yes, asgallant this can be done,

But from my experience until now, this method required much more time
to render than JSON->Ajax.
And also I discovered that jason->ajax is not that complicated once
you have a working example :)

[Josmel Noel Yupanqui Huaman]

hi please would like a complete example where graphics can show up jsapi charextracting data from sql server 2008. 

[Jérôme]

Personnally, i use SQLServer2008 with php and the google chart things work fine

PHP side with PDO :

if(!$result=$stats->query($sql))

{

echo $sql;

}

$json='{"cols": [

{"id":"a","label":"Code,"type":"string"},

{"id":"b","label":"'Country","type":"string"},

{"id":"c","label":"Volume","type":"number"}

      ],

"rows": [';

while($val=$result->fetch())

{

$json .='{"c":[{"v":"'.$val['CodeISO'].'"},{"v":"'.$val['Description'].'"},{"v":'.$val['nb'].'}]},';

}

$json=substr($json,0,-1);

$json .=']

}';

echo $json;

JScript side :

 function drawChart() {

$.ajax({

url: "ajax/get-php.php",

dataType:"json",

async: true,

beforeSend: function()

{

$("#map").html('<div style=\"text-align:center\"><img src=\'img/ajax-loader.gif\' /></div>');

},

success: function(json)

{

var datamap = new google.visualization.DataTable(json);

var map = new google.visualization.GeoChart(document.getElementById('map'));

map.draw(datamap,{

 region: 'fr',

 resolution:'provinces',

 legend:'none',

 colorAxis: {colors:['#1D71B8']}

});

}

});

}

[asgallant]

Here's an example that is a bit easier to follow: https://groups.google.com/d/msg/google-visualization-api/GK0zwNbIwUo/i9DhpOUSKMEJ

$table['cols'] is an array of arrays, where each sub-array is a column definition with "type" and "label" members.  Create one sub-array for each column in your data set.

In the while loop, you need to add one element to $temp for each column (in the same order as your column definitions above).  Each element here is an array with one or two members: "v" (mandatory) is the value of the data point, and "f" (optional) is a string-formatted value for the data point.  The value is used for all calculations, and the formatted value is used whenever the data is output (ie, in a tooltip).  If you leave out the formatted value, the API will default to a string-interpreted version of the value (unless you override this some other way [calling #setFormattedValue, using a Formatter, etc]).

Also note that there is a minor typo in the code, in the while loop, the $i variables should be $r.

On Wednesday, August 29, 2012 4:17:57 PM UTC-4, Diana Flores wrote:

 hi i know its kind of to late to post, but im not so sure if i understand ur code!!!..i have this case!!, a table 14(row)x10(col),  the varible $getData_t1 etc.. is the variable where u get the query, i mean $getData=mysql_query("select * from table")? 

[Diana Flores]

thanks im gona try it rigth now, it looks way too easier!!!...

[Diana Flores]

ok i modify mi code to the one u suggest!!....first thing!!!....$flag = true; i dont see were it is used? second....HOW I TRIED WHAT I DID?? hahahahahhahahaha, im really new with this!!! sorry!!!

[Diana Flores]

sorry again!!!! https://developers.google.com/chart/interactive/docs/php_example i think this will work!!!.... :)

[asgallant]

The $flag variable is irrelevant, you don't need it.  To test it, use the javascript from the php example page you linked to, and replace the contents of the get_data.php file with this code (you need to keep the "<?php" and "?>" at the beginning and end though).

In the javascript, you should probably use something other than a PieChart since you have 10 columns of data, and the pie chart will throw an error if you feed it more than 2.

[Diana Flores]

it doesnt show the Columnchart!!!....

this what i have:


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

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

           var jsonData = $.ajax({
          url: "goo2.php",

          dataType:"json",
          async: false
          }).responseText;

         

        var data = google.visualization.arrayToDataTable(jsonData);
       
    
        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<?php

$con=mysql_connect("localhost","root","") or die("problemas al conectarse al server!!!!");
 
mysql_select_db("bdregistro", $con);

$sth = mysql_query("SELECT * FROM bd_salidas");
$rows = array();
$flag = true;

$table = array();

$table['cols'] = array(

    array('label' => 'PLACA', 'type' => 'string'),
    array('label' => 'SEMANA_1', 'type' => 'number'),
    array('label' => 'SEMANA_2', 'type' => 'number'),
    array('label' => 'SEMANA_3', 'type' => 'number'),
    array('label' => 'SEMANA_4', 'type' => 'number'),
    array('label' => 'SEMANA_5', 'type' => 'number')

);

$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $temp = array();

$temp[] = array('v' => (string) $r['PLACA']);
$temp[] = array('v' => (int) $r['S1']);
$temp[] = array('v' => (int) $r['S2']);
$temp[] = array('v' => (int) $r['S3']);
$temp[] = array('v' => (int) $r['S4']);
$temp[] = array('v' => (int) $r['S5']);

 
    $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;

$jsonTable = json_encode($table);


?>

it doesnt show an error!!!! just blank page!!!

[asgallant]

In your browser, go to the data source page (goo2.php) and see what shows up.  You should see something that looks like the sampleData.json file on the example page, except it will be all one long string without the line breaks.

Your javascript also needs a couple of small fixes: 

1) you need to use the DataTable constructor, not the #arrayToDataTable method, ie:

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

2) you need to define the "options" variable that you pass to the chart's #draw method, even if it's just an empty object:

var options = {
    // define the options in here
};

[Diana Flores]

sorry if i reply late i was working!. I made de small fixes, and still is all blank page!!!!..in the page that i put before, they used a getData.php file, do i need it, i just simple send the first page(ex:exampleusingphp.html//me goochart.html)  to the php file that i add u before:

  var jsonData = $.ajax({
          url: "goo2.php",
          dataType:"json",

......or i need to make a getdata, which will do:

<?php 

// This is just an example of reading server side data and sending it to the client.
// It reads a json formatted text file and outputs it.

$string = file_get_contents("sampleData.json");//me: insted of "sampleData.json" send something else???
maybe the goo2.php!!!??

echo $string;

// Instead you can query your database and parse into JSON etc etc

?>

im quite lost!!!! 0_0!!!

[asgallant]

The PHP file that you made with the code I gave you should be the same one you point to in the AJAX call (ie, "goo2.php").  Did you check that url in a browser to see what it was showing?

[Diana Flores]

Sorry again!!!...work!!!, well yes, i put at first the url to go to my goo2.php,  in the url subject u mean, get the url of the blankpage!!??

[Diana Flores]

Cause what i did was, "VIEW PAGE SOURCE" and then it show me the code of my page!!!!...., but now that i check, my projects, my http file have this [TXT], and all my php file like this [], does it means something? 

[asgallant]

If your PHP file is showing you a blank page, then something is wrong with the PHP.  If you post the full PHP code here or attach the file, I'll take a look.

[Diana Flores]

Really!!!??, well i send u the too files that im using!!!

[asgallant]

In the PHP file, you need to add one line to the end:

echo $jsonTable;

Fix attached.

[Diana Flores]

Well, i thing is alredy resolve :) , the goo2.php it alredy show something but the main thing is the 
grafic with the goochart.html file, its still blank, i have been changing a few things, but same result!!. 
Attach the "View page source code" of the goochart.html.


<html xmlns="http://www.w3.org/1999/xhtml"><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>GRAFICAS SEMANALES</title>
</head>

<body>

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

     <script type="text/javascript" src="jquery-1.6.2.min.js"></script>
    <script type="text/javascript">

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

		   var jsonData = $.ajax({
          url: "http://localhost/MyProject/getdata.php",//a chage this part to see what will happend,firt it was the goo2.php!!

          dataType:"json",
          async: false
          }).responseText;

		               

        var data = google.visualization.DataTable(jsonData);
		
		  var options = {
          title: 'SALIDAS SEMANALES',
          hAxis: {title: 'SEMANAS', titleTextStyle: {color: 'red'}}

        };

       
        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }

    </script><script src="https://www.google.com/uds/?file=visualization&amp;v=1&amp;packages=corechart" type="text/javascript"></script><script src="https://www.google.com/uds/api/visualization/1.0/d7d36793f7a886b687850d2813583db9/format+es,default,corechart.I.js" type="text/javascript"></script>

[asgallant]

Open the page in Chrome or Firefox and check the Developer's Console for errors.

[Diana Flores]

Have a nice lunch!, well i tried doing it but i dont understand,  i go to the firefox->web developer->console error ?  then what? it appears a lot of things!!! but i dont know which one is of my html file!!!!

[Diana Flores]

cause if is that, i have been looking and it only send warning of the other pages i have open!!!

 

Fecha y hora: 02/09/2012 12:11:50 p.m.
Advertencia: Se ha escrito un árbol no equilibrado usando document.write() lo que ha provocado que los datos de red se hayan reinterpretado ("reparsed"). Para más información, https://developer.mozilla.org/en/Optimizing_Your_Pages_for_Speculative_Parsing
Archivo de origen: http://ads.goear.com/ads/go_megabanner_home.php?country=Honduras
Línea: 15

do you understand spanish?

google translate: Warning: You have written an unbalanced tree using document.write () which has caused the network data have been reinterpreted ("reparsed"). For more information, https://developer.mozilla.org/en/Optimizing_Your_Pages_for_Speculative_Parsing
Source File: http://ads.goear.com/ads/go_megabanner_home.php?country=Honduras

something like this but its not of my page!!!

[Diana Flores]

got it!!!!!!!!!...............it show 1 warning and one error

Fecha y hora: 02/09/2012 06:02:16 p.m.
Error: ReferenceError: $ is not defined
Archivo de origen: file:///C:/wamp/www/MyProject/goochart21.html
Línea: 19

it in this part:
......

 18 function drawChart() {
 19		   var jsonData = $.ajax({
 20         url: "http://localhost/MyProject/getdata.php",
 21         dataType:"json",

.......

still a change it to ....=ajax({... but it generates another error:

Fecha y hora: 02/09/2012 06:11:23 p.m.
Error: ReferenceError: ajax is not defined
Archivo de origen: file:///C:/wamp/www/MyProject/goochart21.html
Línea: 19

do you have any idea?

[asgallant]

Ahh...you don't have jQuery on your server.  Change this line:

<script type="text/javascript" src="jquery-1.6.2.min.js"></script> 

to this:

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

which is the link to Google's hosted version of jQuery.

[Diana Flores]

i new it!!!, i was doing it, but i download it but thinking how to add it!!!!, i was just sending u a reply when i see the message u send!!! i will put what u say!! 

[Diana Flores]

it shows something!!!!!!!!!!!!!!!!!!!..........but not what i want!!! hahaha its al error...but better than a blank page i think!!!
and with the console i get this error:
,Data table is not defined.×

Fecha y hora: 02/09/2012 09:31:21 p.m.
Error: La codificación de caracteres del documento HTML no ha sido declarada. El documento se mostrará con texto "basura" en algunas configuraciones de navegador si el documento contiene caracteres externos al rango US-ASCII. La codificación de caracteres de la página debe ser declarada en el documento o en el protocolo de transferencia.
Archivo de origen: http://localhost/MyProject/goochart21.html
Línea: 0

and the next warning

Fecha y hora: 02/09/2012 09:31:21 p.m.
Advertencia: Propiedad desconocida 'box-sizing'.  Declaración rechazada.
Archivo de origen: http://localhost/MyProject/goochart21.html
Línea: 0

[asgallant]

I don't think either of those errors are related to the visualization API.  My guess is that there is a problem with the json used to build the DataTable.  Open the data source page ("goo2.php", unless you renamed it) and post what pops up here.

[Diana Flores]

{"cols":[{"label":"PLACA","type":"string"},{"label":"SEMANA_1","type":"number"},{"label":"SEMANA_2","type":"number"},{"label":"SEMANA_3","type":"number"},{"label":"SEMANA_4","type":"number"},{"label":"SEMANA_5","type":"number"}],"rows":[{"c":[{"v":"PCC2143"},{"v":4},{"v":3},{"v":20},{"v":8},{"v":1}]},{"c":[{"v":"PBZ7213"},{"v":5},{"v":4},{"v":21},{"v":7},{"v":2}]},{"c":[{"v":"PCA0085"},{"v":6},{"v":5},{"v":22},{"v":6},{"v":3}]},{"c":[{"v":"PCA5844"},{"v":7},{"v":6},{"v":23},{"v":5},{"v":4}]},{"c":[{"v":"PCA5853"},{"v":8},{"v":7},{"v":24},{"v":4},{"v":5}]},{"c":[{"v":"PCA2219"},{"v":9},{"v":8},{"v":25},{"v":3},{"v":6}]},{"c":[{"v":"PCC0728"},{"v":1},{"v":9},{"v":13},{"v":18},{"v":7}]},{"c":[{"v":"PCH1213"},{"v":2},{"v":12},{"v":12},{"v":19},{"v":8}]},{"c":[{"v":"PCD4842"},{"v":3},{"v":13},{"v":11},{"v":20},{"v":9}]},{"c":[{"v":"PCC0503"},{"v":4},{"v":14},{"v":10},{"v":1},{"v":1}]},{"c":[{"v":"PCM6736"},{"v":10},{"v":15},{"v":9},{"v":2},{"v":2}]},{"c":[{"v":"PCC2146"},{"v":11},{"v":16},{"v":8},{"v":3},{"v":3}]},{"c":[{"v":"PCA5854"},{"v":13},{"v":17},{"v":7},{"v":4},{"v":4}]},{"c":[{"v":"PCA5856"},{"v":7},{"v":18},{"v":6},{"v":5},{"v":5}]}]}

i ask a friend about the error, and told me to add     <meta charset="utf-8"></head><body> and that eliminate the error, but the warning still there!!!!, is the json table correct?

[asgallant]

The json is fine.  Insert this line after the AJAX call and check the developer's console:

alert(typeof(jsonData)); 

console.log(jsonData); 

You should get a popup that says "object" and the developer's console should have the exact same json as your PHP page.

[Diana Flores]

hi, well i add what u say but what i got was a popup that says "string", and in the console of error the same warning!!!

[asgallant]

You're using firefox, right?  Check the web console instead of the error console (ctrl-shift-k in the windows version) to see if the JSON ends up there.

If it is showing up properly, then since the type of jsonData is string, you may need to parse it before passing it to the DataTable constructor:

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

though in some browsers, this is not necessary.

[Diana Flores]

yes!!! it shows the table!!! i will add that line!!!

[Diana Flores]

  :(    !!, see the jpg!!!!......

[Diana Flores]

My server says:

json support	enabled
json version	1.2.1

im using as server the wamp server!,  but i think its something with my json!!!, cause i used:

 var data = google.visualization.DataTable(JSON.parse(jsonData, reviver));
 var data = google.visualization.DataTable(JSON.parse(jsonData));
 var data = google.visualization.DataTable(JSON.eval('('+ jsonData +')'));

and with the reviver and the eval, it says in the web console:

JSON.eval is not a function
reviver is not defined

http://www.json.org/js.html a went here to know what JSON.parse can do!!!


https://github.com/douglascrockford/JSON-js/blob/master/json_parse.js

On Tuesday, September 4, 2012 9:35:58 PM UTC-6, asgallant wrote:

[asgallant]

"reviver" is supposed to be a function (that you provide) that transforms every key/value pair in the JSON into the final javascript object.  It's useful for converting date strings into Date objects and stuff like that, but you probably don't have to worry about it.  There is no JSON.eval function - it is just eval.  I would avoid using that, though.

I think I see the problem, though.  You are missing the "new" keyword when you declare the DataTable.  It should be exactly like this:

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

or, if that doesn't work, this:

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

Either way, the "new" keyword is critically important.

[Diana Flores]

yeaaaaaaahhhHHHH!!!!, we did it!!!!!!!!!!!!!. well at first i tried the .DataTable(jsonData);  but it gave me errors but i put the JSON.parse(jsonData));  and it works!!!!!!!!!!!!!!!!!!....im so happy!!! i will attach the files in case someone has the same problem!!!!!!!!....really really grateful, cause with your help i learned a lot of things!!!!....one month ago I was "what its php or mysql....JSON O_O???"  i think its a lot, but thanks!!!!

[asgallant]

You're welcome.

[Barbara Gerstl]

Hello Diana!

Thank you very much for showing the whole process on how to combine Google Graph API with a MySQL-Database. That is exactly what I am looking for.

I tried to rebuild your example and I am having problems with the structure of the database/field settings. Can you show me structure and field settings of the table "bd_salidas"?

Thank you for your answer.
Barbara

[asgallant]

You can extrapolate from the code that the table has 6 columns: PLACA, S1, S2, S3, S4, S5.

[Barbara Gerstl]

That is what I did... but, when opening goochart2.html, the result is the Error-Massage "string".
I think, it has something to do with the field settings of the columns. Do you have any tipps?

Thank you!

[asgallant]

What is throwing that error message?  Is it PHP?

You will have to adjust the data types to the type of data you are using, so if your first column isn't type string, you need to change it to something else in the column definitions (this goes for all columns - types must always match).  Also, the (string) typecasting in this line:

$temp[] = array('v' => (string) $r['PLACA']);

is probably not necessary, unless you have a non-string data type that you need to specifically convert into a string.

If you can post a link to the page, I can help debug things on the javascript end, if it turns out that is where the problem is.

[asgallant]

You're not charting 3 series there, you have 1 series.  Looking at your SQL table, I would guess that you want to display one series for each sample id, right?  If so, then you need to break out the "prodPerct" column into 3 different columns - 1 for each series.  This is probably best achieved in SQL, maybe with a query like this:

SELECT 

PsiBar,

SUM(IF(id_sample = 1, prodPerct, null)) AS prodPerct1,

SUM(IF(id_sample = 2, prodPerct, null)) AS prodPerct2,

SUM(IF(id_sample = 3, prodPerct, null)) AS prodPerct3

FROM tbl_dilution

GROUP BY PsiBar

and then use this to build the table:

$table['cols'] = array(

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

array('label' => 'Series 1', 'type' => 'number')

array('label' => 'Series 2', 'type' => 'number')

array('label' => 'Series 3', 'type' => 'number')

);

$rows = array();

while($r = mysql_fetch_assoc($sth)) {

$temp = array();

$temp[] = array('v' => (float) $r['psiBar']); 

$temp[] = array('v' => (float) $r['prodPerct1']); 

$temp[] = array('v' => (float) $r['prodPerct2']); 

$temp[] = array('v' => (float) $r['prodPerct3']); 

$rows[] = array('c' => $temp);

}

On Thursday, October 11, 2012 12:50:04 PM UTC-4, Jose wrote:

Hi asgallant,

Seeing Diana's example, I tried doing something similar with a Line graph but it's not coming out as I'd like.
It displays the three series but links them all together instead of individually displaying them (lineChart.jpg).
What I'm trying to achieve, is something similar to how it's displayed in Excel (chart_xls.jpg).
If you could help me in the right direction, I'd appreciate it alot as I have been trying various things and the outcome
isn't what I'm expecting.

José

[Jose]

asgallant, you are right, I am trying to get three lines plotted for each 'id_sample'. Within each id group, there are 12 plot points.
I've tried the code you provided, thanks, but it appears to also plot the null values '0' between each data point. How do I fix this?

{"cols":[{"label":"PsiBar","type":"number"},{"label":"Series 1","type":"number"},{"label":"Series 2","type":"number"},{"label":"Series 3","type":"number"}],"rows":[{"c":[{"v":0.39},{"v":0.36},{"v":0},{"v":0}]},{"c":[{"v":0.5},{"v":0},{"v":0.26},{"v":0.11}]},{"c":[{"v":0.56},{"v":0.49},{"v":0.34},{"v":0}]},{"c":[{"v":0.57},{"v":0},{"v":0},{"v":0.16}]},{"c":[{"v":0.84},{"v":0.56},{"v":0.41},{"v":0.15}]},{"c":[{"v":1.01},{"v":0.62},{"v":0.42},{"v":0}]},{"c":[{"v":1.02},{"v":0},{"v":0},{"v":0.24}]},{"c":[{"v":1.3},{"v":0.66},{"v":0.49},{"v":0}]},{"c":[{"v":1.31},{"v":0},{"v":0},{"v":0.26}]},{"c":[{"v":1.45},{"v":0.66},{"v":0.5},{"v":0.27}]},{"c":[{"v":1.74},{"v":0},{"v":0.52},{"v":0}]},{"c":[{"v":1.75},{"v":0.68},{"v":0},{"v":0.28}]},{"c":[{"v":2.1},{"v":0},{"v":0},{"v":0.28}]},{"c":[{"v":2.11},{"v":0},{"v":0.52},{"v":0}]},{"c":[{"v":2.12},{"v":0.68},{"v":0},{"v":0}]},{"c":[{"v":2.57},{"v":0},{"v":0.49},{"v":0.27}]},{"c":[{"v":2.58},{"v":0.65},{"v":0},{"v":0}]},{"c":[{"v":3.07},{"v":0},{"v":0},{"v":0.25}]},{"c":[{"v":3.09},{"v":0.6},{"v":0.46},{"v":0}]},{"c":[{"v":3.56},{"v":0.56},{"v":0},{"v":0.23}]},{"c":[{"v":3.57},{"v":0},{"v":0.42},{"v":0}]},{"c":[{"v":4.23},{"v":0},{"v":0},{"v":0.21}]},{"c":[{"v":4.34},{"v":0},{"v":0.39},{"v":0}]},{"c":[{"v":4.36},{"v":0.51},{"v":0},{"v":0}]}]}

Really appreciate your help on this!

[asgallant]

Hmmm...I can see two ways of handling that.  One results in a messy DataTable, and will probably work; the other results in a cleaner DataTable, but might not work.

Method 1: change the SQL statement to this:

SELECT 

PsiBar,

IF(id_sample = 1, prodPerct, null) AS prodPerct1,

IF(id_sample = 2, prodPerct, null) AS prodPerct2,

IF(id_sample = 3, prodPerct, null) AS prodPerct3

FROM tbl_dilution

removing the sums and the group by clause.  This will result in more rows of data (with duplicate domain column entries) than necessary.

Method 2: this will work only if 0 is not a valid value for your data points to have.  Change the SQL to this:

SELECT 

PsiBar,

IF(prodPerct1 = 0, null, prodPerct1) as prodPerct1,

IF(prodPerct2 = 0, null, prodPerct2) as prodPerct2,

IF(prodPerct3 = 0, null, prodPerct3) as prodPerct3

FROM (

SELECT

PsiBar,

SUM(IF(id_sample = 1, prodPerct, null)) AS prodPerct1,

SUM(IF(id_sample = 2, prodPerct, null)) AS prodPerct2,

SUM(IF(id_sample = 3, prodPerct, null)) AS prodPerct3

FROM tbl_dilution

GROUP BY PsiBar

) AS foo

which tests to see if the sum is 0, and if it is, sets the value to null instead.  The DataTable will be cleaner, but it won't work if your values can be 0.

[Jose]

I gave both a try and neither looked as it should. Method 2 displays the chart as it did previous where it was plotting the null '0' values.

[asgallant]

I duplicated your table and ran the query, and Method 2 looked right to me.  I had to make 1 small change to the SQL to make it run (but that could be a quirk of my MySQL install), and 1 change to the chart options.  The SQL looked like this:

SELECT 

foo.PsiBar,

IF(prodPerct1 = 0, null, foo.prodPerct1) as prodPerct1,

IF(prodPerct2 = 0, null, foo.prodPerct2) as prodPerct2,

IF(prodPerct3 = 0, null, foo.prodPerct3) as prodPerct3

FROM (

SELECT

PsiBar,

SUM(IF(id_sample = 1, prodPerct, null)) AS prodPerct1,

SUM(IF(id_sample = 2, prodPerct, null)) AS prodPerct2,

SUM(IF(id_sample = 3, prodPerct, null)) AS prodPerct3

FROM tbl_dilution

GROUP BY PsiBar

) AS foo

and I set the "interpolateNulls" chart option to true:

var options = {

title: 'Line Chart Test',

interpolateNulls: true

};

[Jose]

Asgallant, I tried it again and still the same results, although I did add that var option but it didn't help. I'm wondering if its something else in your scripts/code that is different than mine. If you could post those, that would be great.
As another non sufficient way, could three separate SQL calls be made then plot the results onto the same chart?

[asgallant]

I used your code exactly as it appears in the files you posted, except for the modifications mentioned.  I didn't save a copy, though, so I can't post them back.  I'll see if I can duplicate it later today.

[asgallant]

D'oh!  My mistake, I did change something else.  The (float) typing converts null into 0, so you have to test for null and handle it specially.  In your php file, the while loop should look like this:

while($r = mysql_fetch_assoc($sth)) {

$temp = array();

$temp[] = array('v' => (float) $r['PsiBar']); 

$temp[] = array('v' => (is_null($r['prodPerct1'])) ? null : (float) $r['prodPerct1']); 

$temp[] = array('v' => (is_null($r['prodPerct2'])) ? null : (float) $r['prodPerct2']); 

$temp[] = array('v' => (is_null($r['prodPerct3'])) ? null : (float) $r['prodPerct3']); 

$rows[] = array('c' => $temp);

}

although, since your data is already in numerical form (and thus you don't have to force it to be type float), you can go with the simpler:

while($r = mysql_fetch_assoc($sth)) {

$temp = array();

$temp[] = array('v' => $r['PsiBar']); 

$temp[] = array('v' => $r['prodPerct1']); 

$temp[] = array('v' => $r['prodPerct2']); 

$temp[] = array('v' => $r['prodPerct3']); 

$rows[] = array('c' => $temp);

}

It's also a good idea to force the mime type to application/json by setting the header, before you echo the json:

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

echo $jsonTable;

[Jose]

Thank you very much, that helped! Now I'll use other chart options to fine tune it a bit to look similar to my excel charts.
Really appreciate the help asgallant!

[asgallant]

You're welcome.

[asgallant]

I need to see the javascript after it is rendered by the server.  Open the page in a browser and view to source code, then paste it here.

On Monday, November 12, 2012 12:34:23 PM UTC-5, Brenno Leal wrote:

Hey guys, i was tryng using the asgallant's exemple, but i've not sucess. 

the browser generate the right code, and if i copy and paste the code to playground, it works! why it doesnt works here?

take a look in my code:

<script type="text/javascript">

function drawVisualization() {

   // Create and populate the data table.

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

  data.addColumn('number', 'Sensor');

               

  <?php

  foreach ($val as $valores) {

     echo "data.addRows([[$valores]]);";

  }

   ?>

// Create and draw the visualization.

new google.visualization.ImageSparkLine(

 document.getElementById('visualization')).draw(data, null);

}

                  google.setOnLoadCallback(drawVisualization);

</script>

I've sure that $val isnt empty!

Cya!

[asgallant]

Yes, this is possible.  This thread outlines what your genera.php script would have to do with the results from the query and how to get those into your charts.

On Wednesday, December 5, 2012 5:05:24 AM UTC-5, Chrystopher Medina wrote:

hi . u know i have a problem since some months ago. i dondt speak english very well. but i ll try ..  well i have a file named report.php and this file contains this form like this  

<form id="generalreporte" method="post" action="reportegeneral.php">

                                    <div id="general" style="display: none; text-align: center;">

                                        <label style="font-size:medium">Fecha de:</label>&nbsp;&nbsp;

                                        <input type="date" name="fecha1" required/>&nbsp;&nbsp;

                                        <label style="font-size:medium">entre</label>&nbsp;&nbsp;

                                        <input type="date" name="fecha2" required/><br/><br/>

                                        <input type="submit"  value="Enviar"/>

                                        

                                    </div>

                                   </form>

and i use a library named jquery.form in order to send the values with ajax to another file named genera.php in this file i just want to do a query mysql with te values and then generate a google chart pie with ajax but in the same file that contains the form. its posible this . please help me my friend 

[Chrystopher Medina]

hi my frined thanks for answering me my question. could you be more expecific u know i really dont know speak english very well. and i just began to study this about how to program whit ajax .... really its posible .... could u help me my friend could u give me ur email or ur facebook page. or something like that. ....... its very important to me to do this. i ve spend months with this work. and it doesnt work ....... please explaime how i can do in order to achieve this ............

[asgallant]

Ok, here's example PHP to get you started:

<?php

/* $server = the IP address or network name of the server

 * $userName = the user to log into the database with

 * $password = the database account password

 * $databaseName = the name of the database to pull data from

 */

$con = mysql_connect($server, $userName, $password) or die('Error connecting to server');

 

mysql_select_db($databaseName, $con); 

// write your SQL query here (you may use parameters from $_GET or $_POST if you need them)

$query = mysql_query('SELECT column1, column2, column3 FROM myTable');

$table = array();

$table['cols'] = array(

/* define your DataTable columns here

* each column gets its own array

* syntax of the arrays is:

* label => column label

* type => data type of column (string, number, date, datetime, boolean)

*/

    array('label' => 'Label of column 1', 'type' => 'string'),

array('label' => 'Label of column 2', 'type' => 'number'),

array('label' => 'Label of column 3', 'type' => 'number')

// etc...

);

$rows = array();

while($r = mysql_fetch_assoc($query)) {

    $temp = array();

// each column needs to have data inserted via the $temp array

$temp[] = array('v' => $r['column1']);

$temp[] = array('v' => $r['column2']);

$temp[] = array('v' => $r['column3']);

// etc...

// insert the temp array into $rows

    $rows[] = array('c' => $temp);

}

// populate the table with rows of data

$table['rows'] = $rows;

// encode the table as JSON

$jsonTable = json_encode($table);

// return the JSON data

echo $jsonTable;

?>

You will have to fill in the database connection information, write your SQL query, define the columns in $table['cols'], and adjust the while loop to ensure that all columns of data get populated.  You should be able to navigate to that page in your browser and see a string of JSON data.  When you have a basic version running, I'll help you hook it up to your chart(s).

[Chrystopher Medina]

Ok my friend Im gonna work in this. Ok.  And i will tell u when i finish It. A lot of thanks . 

[Chrystopher Medina]

hi my friend i´ve finished this. my result file json is this:

{"cols":[{"label":"word of mouth recomendation","type":"number"},{"label":"magazine advertisement","type":"number"},{"label":"roadside advertisement","type":"number"}],"rows":[{"c":[{"v":"2"}]},{"c":[{"v":"1"}]},{"c":[{"v":"1"}]}]} 

i hope that this be the correct result......
look my php file:: my php file is this

:
<?php
$var1="$_POST[fecha1]";

$var2= "$_POST[fecha2]";

$server="localhost";
$username="root";
$password="chrystopher";
$databasename="encuestasavandaro";

$con=  mysql_connect($server,$username,$password)

        or die('Error connecting to server');

mysql_select_db($databasename,$con);

$query= mysql_query("select count(b.id_respuesta) from huesped a, rompe_encuesta b
where
b.id_huesped=a.id_huesped and b.id_aspecto=1 and b.id_respuesta=8 and a.fecha between '$var1' and '$var2';");

$query2= mysql_query("select count(b.id_respuesta) from huesped a, rompe_encuesta b
where
b.id_huesped=a.id_huesped and b.id_aspecto=1 and b.id_respuesta=9 and a.fecha between '$var1' and '$var2';");

$query3= mysql_query("select count(b.id_respuesta) from huesped a, rompe_encuesta b
where
b.id_huesped=a.id_huesped and b.id_aspecto=1 and b.id_respuesta=10 and a.fecha between '$var1' and '$var2';");

$table = array();

$table['cols']=array(
 

  array('label' => 'word of mouth recomendation'

      , 'type'=>'number'),

    array('label' => 'magazine advertisement'

      , 'type'=>'number'),

    array('label' => 'roadside advertisement'

      , 'type'=>'number')
   
   

);


$rows = array();
while($r=  mysql_fetch_assoc($query)){
   
    $temp = array();
    $temp[]=array('v' => $r['count(b.id_respuesta)']);

   
    $rows[] = array('c' => $temp);
}

while($r=  mysql_fetch_assoc($query2)){
   
    $temp = array();
    $temp[]=array('v' => $r['count(b.id_respuesta)']);

   
    $rows[] = array('c' => $temp);
}

while($r=  mysql_fetch_assoc($query3)){
   
    $temp = array();
    $temp[]=array('v' => $r['count(b.id_respuesta)']);

   
    $rows[] = array('c' => $temp);
}

$table['rows']=$rows;

$jsonTable=  json_encode($table);
echo $jsonTable;


?>
 

 
 
   

[asgallant]

We're going to have to work on the PHP a bit here.  There are two different paths to take to correct it, depending on what you actually want to get at the end.

The way you started this, you would get a table that looks like this:

word of mouth recomendation   |   magazine advertisement   |   roadside advertisement

-------------------------------------------------------------------------------------

         XXXX                 |            YYYY            |          ZZZZ

Which is fine for producing a table output of your data, but not very good if you want to produce a chart.  Charts would do better with an output like this:

Type                        | Count

-----------------------------------
word of mouth recomendation | XXXX

magazine advertisement      | YYYY

roadside advertisement      | ZZZZ

What kind of visualization do you want to make with the data?a

[Chrystopher Medina]

my work is to do a survey ... and those are the answers u know i just want to show in a certain date how many people responde.... so in my sql query i obtained the results from my survey........ u know i have a question in my survey,,,, and i just want to know how many people answered to each answer......

10 people answered the answer number one..
5 people answered the answer number two. and

2 people answered the answer number 3......

I want to display this data on a graph of pie with the api google charts.....

so u are right. i think that this is the correct 

 Type                        | Count

-----------------------------------
word of mouth recomendation | XXXX

magazine advertisement      | YYYY

roadside advertisement      | ZZZZ

so my result json was wrong ... ?  
I hope you understand me what i want--- excuse my English is very bad.....

[asgallant]

Your English is fine, don't worry >;o)

Here's a modified version of the PHP that should do what you want here:

<?php 

$var1=$_POST['fecha1'];

$var2=$_POST['fecha2'];

/*

 * IMPORTANT!

 * You should validate the input dates here to prevent SQL injection attacks

 *

 * Also, you should change the database password, since it was posted in plaintext on the internet

 *

 */

$server = "localhost";

$username = "root";

$password = "";  // new password

$databasename = "encuestasavandaro";

$con = mysql_connect($server,$username,$password)

        or die('{"error":"Error connecting to server"}');

mysql_select_db($databasename,$con);

$query = "SELECT b.id_respuesta, COUNT(b.id_respuesta) AS cnt

FROM huesped a, rompe_encuesta b

WHERE

b.id_huesped = a.id_huesped AND

b.id_aspecto = 1 AND

b.id_respuesta >= 8 AND

b.id_respuesta <= 10 AND

a.fecha BETWEEN '$var1' AND '$var2'

GROUP BY b.id_respuesta

";

$table = array();

$table['cols']=array(

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

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

);

$rows = array();

while($r = mysql_fetch_assoc($query)){

    $temp = array();

// using (int) $variable typecasts the variable as an integer, usefull when the SQL is returning numbers as strings

switch ((int) $r['id_respuesta']) {

case 8

$type = 'word of mouth recomendation';

break;

case 9

$type = 'magazine advertisement';

break;

case 10

$type = 'roadside advertisement';

break;

default

die('{"error":"Error in SQL query: unknown \'id_respuesta\'"}');

}

    $temp[] = array('v' => $type);

    $temp[] = array('v' => (int) $r['cnt']);

    $rows[] = array('c' => $temp);

}

$table['rows'] = $rows;

$jsonTable = json_encode($table);

echo $jsonTable;

?>

I don't have the means to test it, so there could be errors.  Also, I made a few notes in the code comments which you should read and consider (particularly at the top of the script).

[Chrystopher Medina]

i have an error like this : Parse error: syntax error, unexpected '$type' (T_VARIABLE) in C:\xampp\htdocs\finalproyectoavandaro\reportegeneral.php on line 39

the error is in this part 

line 33:    $rows = array();

line 34:   

while($r = mysql_fetch_assoc($query)){

line 35:   

    $temp = array();

line 36:   

// using (int) $variable typecasts the variable as an integer, usefull when the SQL is returning numbers as strings

line 37:   

switch ((int) $r['id_respuesta']) {

line 38:   

case 8

line 39:   

$type = 'word of mouth recomendation'; //here is the problem

line 40:   

break;

line 41:   

case 9

line 42:   

$type = 'magazine advertisement';

line 43:   

break;

line 44:   

case 10

line 45:   

$type = 'roadside advertisement';

line 46:   

break;

line 47:   

default

line 48:   

die('{"error":"Error in SQL query: unknown \'id_respuesta\'"}');

line 49:   

}

line 50:   

line 51:   

   $temp[] = array('v' => $type);

line 52:   

    $temp[] = array('v' => (int) $r['cnt']);

line 53:   

    $rows[] = array('c' => $temp);

line 54:   

}

 

[asgallant]

Oops, my mistake.  Those "case" statements need a colon after each, like this:

switch ((int) $r['id_respuesta']) {

case 8:

$type = 'word of mouth recomendation';

break;

case 9:

$type = 'magazine advertisement';

break;

case 10:

$type = 'roadside advertisement';

break;

default:

die('{"error":"Error in SQL query: unknown \'id_respuesta\'"}');

}

[Chrystopher Medina]

and u know in my netbeans ide . in the line 39,42.45  is shown a message saying :::: you should use only: 1assignment(s) (3 used) to a variable $type to avoid accidentally overwriting it and make your code easier to read.::::::::::::::

and I can not fix this error

[Chrystopher Medina]

and really a lot of thanks u are very kind... and really u are so good with this. u really optimized my query ..... thanks for ur time my friend 

[Diana Flores]

until now i saw that this group continued, jajajajajajaja good asgallant and sorry barbara seems i was quite late jajajaja!!!!!

[Chrystopher Medina]

i have this my friend .. but look.. somethimes the error that shows me as a message is this :{"error":"Error in SQL query: unknown 'id_respuesta'"} and sometimes the just is a message like this

 {"cols":[{"label":"Type","type":"string"},{"label":"Count","type":"number"}],"rows":[]}  

These dates are valid in my sql query:

 select b.id_respuesta, COUNT(b.id_respuesta) AS cnt FROM huesped a, rompe_encuesta b

 WHERE

 b.id_huesped = a.id_huesped AND

 b.id_aspecto = 1 AND

 b.id_respuesta >= 8 AND

 b.id_respuesta <= 11 AND

 a.fecha BETWEEN '2012-12-01' AND '2012-12-10' GROUP BY b.id_respuesta;

 this query is in mysql and this shows me this :

+--------------+-----+

| id_respuesta | cnt |

+--------------+-----+

|            8    |        2 |

|            9    |        1 |

|           10   |       1 |

|           11   |       2 |

but When I enter the same date in my interface this is the result : {"error":"Error in SQL query: unknown 'id_respuesta'"

u know I have only answer that people responded between these two dates  BETWEEN '2012-12-01' AND '2012-12-10 and for 

example when i put another date like 2012-11-01 and 2012-12-01 the message witch shows me is this . this is the result :

{"cols":[{"label":"Type","type":"string"},{"label":"Count","type":"number"}],"rows":[]}  

this is my file php

<?php 

$var1="$_POST[fecha1]";

$var2= "$_POST[fecha2]";

$server="localhost";

$username="root";

$password="chrystopher";

$databasename="encuestasavandaro";

$con = mysql_connect($server,$username,$password) or die ('Error connecting to server');

mysql_select_db($databasename,$con);

$query = "SELECT b.id_respuesta, COUNT(b.id_respuesta) AS cnt

FROM huesped a, rompe_encuesta b

WHERE

b.id_huesped = a.id_huesped AND

b.id_aspecto = 1 AND

b.id_respuesta >= 8 AND

b.id_respuesta <= 11 AND

a.fecha BETWEEN '$var1' AND '$var2'

GROUP BY b.id_respuesta;";

$table = array();

$table['cols']=array(

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

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

);

$rows = array();

 if (!mysql_query($query,$con))

  {

  die('Error: ' . mysql_error());

  }else{

      $sqlquery= mysql_query($query);

while($r = mysql_fetch_assoc($sqlquery)){

    $temp = array();

// using (int) $variable typecasts the variable as an integer, usefull when the SQL is returning numbers as strings

  

    switch ((int) $r['id_respuesta']) {

           

case 8:

$type = 'word of mouth recomendation';

break;

case 9:

$type = 'magazine advertisement';

break;

case 10:

$type = 'roadside advertisement';

break;

default:

die('{"error":"Error in SQL query: unknown \'id_respuesta\'"}');

}

    $temp[] = array('v' => $type);

    $temp[] = array('v' => (int) $r['cnt']);

    $rows[] = array('c' => $temp);

}

  }

[Chrystopher Medina]

hi again my friend u know i had to remove this line  : 

default:

die('{"error":"Error in SQL query: unknown \'id_respuesta\'"}');

and now the result is this :

{"cols":[{"label":"Type","type":"string"},{"label":"Count","type":"number"}],"rows":[{"c":[{"v":"word of mouth recomendation"},{"v":2}]},{"c":[{"v":"magazine advertisement"},{"v":1}]},{"c":[{"v":"roadside advertisement"},{"v":1}]},{"c":[{"v":"roadside advertisement"},{"v":2}]}]}

i think that is what i need to continue whit the pie chart... or what do u think

[asgallant]

You added one extra row of data with this:

b.id_respuesta <= 11

as your previous max used to be 10.  The case statement was written to deal with 8, 9, 10 as options, thus the 11 threw the error.  When you removed the "default" case, it treats the 11's like 10's (which is why your data has "roadside advertisement" in two rows).  You either need to change back to 10 or add a new case to the switch statement.  Either way, you should put the default case back in as a safety measure.

[Chrystopher Medina]

yes u are right.... my new result is this : 

{"cols":[{"label":"Type","type":"string"},{"label":"Count","type":"number"}],"rows":[{"c":[{"v":"word of mouth recomendation"},{"v":2}]},{"c":[{"v":"magazine advertisement"},{"v":1}]},{"c":[{"v":"roadside advertisement"},{"v":1}]},{"c":[{"v":"google"},{"v":2}]}]} 

yes my friend i need 4 answers .is for that i add  this :

$query = "SELECT b.id_respuesta , COUNT(b.id_respuesta) AS cnt

FROM huesped a, rompe_encuesta b

WHERE

b.id_huesped = a.id_huesped AND

b.id_aspecto = 1 AND

b.id_respuesta >= 8 AND

b.id_respuesta <= 11 AND // this

a.fecha BETWEEN '$var1' AND '$var2'

GROUP BY b.id_respuesta;";

I have modified the code like this:

  switch ((int) $r['id_respuesta']) {

           

case 8:

$type = 'word of mouth recomendation';

break;

case 9:

$type = 'magazine advertisement';

break;

case 10:

$type = 'roadside advertisement';

break;

                    case 11:

$type = 'google';

break;

                    default:

die('{"error":"Error in SQL query: unknown \'id_respuesta\'"}');

}

and this is all my file php. Check it please.

<?php 

$var1="$_POST[fecha1]";

$var2= "$_POST[fecha2]";

$server="localhost";

$username="root";

$password="chrystopher";

$databasename="encuestasavandaro";

$con = mysql_connect($server,$username,$password) or die ('Error connecting to server');

mysql_select_db($databasename,$con);

$query = "SELECT b.id_respuesta , COUNT(b.id_respuesta) AS cnt

FROM huesped a, rompe_encuesta b

WHERE

b.id_huesped = a.id_huesped AND

b.id_aspecto = 1 AND

b.id_respuesta >= 8 AND

b.id_respuesta <= 11 AND

a.fecha BETWEEN '$var1' AND '$var2'

GROUP BY b.id_respuesta;";

$table = array();

$table['cols']=array(

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

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

);

$rows = array();

 if (!mysql_query($query,$con))

  {

  die('Error: ' . mysql_error());

  }else{

      

      $sqlquery= mysql_query($query);

while($r = mysql_fetch_assoc($sqlquery)){

    $temp = array();

// using (int) $variable typecasts the variable as an integer, usefull when the SQL is returning numbers as strings

  

    switch ((int) $r['id_respuesta']) {

           

case 8:

$type = 'word of mouth recomendation';

break;

case 9:

$type = 'magazine advertisement';

break;

case 10:

$type = 'roadside advertisement';

break;

                    case 11:

$type = 'google';

break;

                    default:

die('{"error":"Error in SQL query: unknown \'id_respuesta\'"}');

}

    $temp[] = array('v' => $type);

    $temp[] = array('v' => (int) $r['cnt']);

    $rows[] = array('c' => $temp);

}

  }

[asgallant]

Awesome!  Now lets take a look at the javascript end of things.  You said before that you have a form and you are using jQuery to submit it, do you have code for that already, or do you need to write new code?

[Chrystopher Medina]

Yes my friend asome. U know im really learning a lot. Well i use a library named jquery.form.js in orden to send with ajax.     In this moment i Have to go at my school. When i come back Im gonna send u my file witch has the form And the jquery.form.js thanks a lot for Ur time my friend.

[Chrystopher Medina]

here we are again my friend...... well look i use a library named jquery.jform.js wich send the values with ajax ..... look :

<?php

      include 'sesion.php';

        ?><!DOCTYPE html>

<html dir="ltr" lang="es"><head>

    <meta charset="utf-8">

    <title>Encuesta</title>

    <meta name="viewport" content="initial-scale = 1.0, maximum-scale = 1.0, user-scalable = no, width = device-width">

    <!--[if lt IE 9]><script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script><![endif]-->

    <link rel="stylesheet" href="style.css" media="screen">

    <!--[if lte IE 7]><link rel="stylesheet" href="style.ie7.css" media="screen" /><![endif]-->

    <link rel="stylesheet" href="style.responsive.css" media="all">

    <script src="jquery.js"></script>

    <script src="script.js"></script>

    <script src="script.responsive.js"></script> 

  

    <script type="text/javascript" src="js/jquery.form.js"></script>   // this is the library.

     <link rel="stylesheet" type="text/css" href="css/thickbox.css"/>

    <script type="text/javascript" src="js/thickbox.js"></script>

    

   

<meta name="description" content="pagina de hotel avandaro dedicada a realizar encuesta de satisfacción a los huespedes del hotel avandaro en valle de bravo">

<meta name="keywords" content="encuesta de satisfacción, hotel avandaro, valle de bravo, huespedes, encuesta">

<script>

$(document).ready(function(){

     $("#reporte").click(function(e){

         $("#filtrar").fadeOut(500);

          $("#general").fadeIn(1000);

         

     });

      $("#reporte2").click(function(){

       $("#general").fadeOut(500);

          $("#filtrar").fadeIn(1000);

     });

    

   

    

});

</script>

<script type="text/javascript">  //function for the library jquery.jform.js wich send the values from my form with id="generalreporte" with ajax

       

        $(document).ready(function() { 

            // definimos las opciones del plugin AJAX FORM, options for the plugin ajaxform

            var opciones= {

                               beforeSubmit: mostrarLoader,

                               

                            //funcion que se ejecuta antes de enviar el form

                               success: mostrarRespuesta //funcion que se ejecuta una vez enviado el formulario

  

            };

             //asignamos el plugin ajaxForm al formulario myForm y le pasamos las opciones

            $('#generalreporte').ajaxForm(opciones) ; 

             function mostrarLoader(){

                    

                    

                        $("#loader_gif").fadeIn("slow");

                      

             };

             function mostrarRespuesta (responseText){

        

                          $("#loader_gif").fadeOut("slow");

                          $('#generalreporte').each (function(){

  this.reset();

});

                   $("#ajax_loader").append(responseText); 

              

                         

                        

             };

        });

            </script>

</head>

<body>

<div id="art-main">

<header class="art-header clearfix">

   <div class="art-shapes">

<h1 class="art-headline" data-left="99.68%">

    <a href="#">Área de Administración</a>

</h1>

<h2 class="art-slogan" data-left="99.06%">AYUDANOS A BRINDARTE UN MEJOR SERVICIO.<br/>

<br/>

<br/>

&nbsp;&nbsp;&nbsp;&nbsp;

<img src="images/login2.png" style="width: 40px; height: 40px;">Bienvenido : 

 <?php

 echo $_SESSION["usuario"];

 ?> | <a href="salir.php">cerrar sesión</a></h2>

<div class="art-object1548596889" data-left="0%"></div>

            </div>

                        

                    

</header>

<nav class="art-nav clearfix">

    <div class="art-nav-inner">

    <ul class="art-hmenu">

         

         <li><a href="reporteador.php" class="active">Reporte</a></li>

         <li><a href="datos.php">Datos y Comentarios</a></li>

        

       

    </ul> 

        </div>

    </nav>

<div class="art-sheet clearfix">

            <div class="art-layout-wrapper clearfix">

                <div class="art-content-layout">

                    <div class="art-content-layout-row">

                        <div class="art-layout-cell art-content clearfix"><article class="art-post art-article">

                                <div style="margin: 20px 20px 20px 20px;">

     <div style="text-align: center;"><label style="font-size:medium">Reporte General</label>&nbsp;&nbsp;&nbsp;<input type="radio" name="reporte" id="reporte"/>

         &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

         &nbsp;&nbsp;&nbsp;&nbsp;<label style="font-size:medium">Filtrar Resultados</label>&nbsp;&nbsp;&nbsp;<input type="radio" name="reporte" id="reporte2"/></div>

                                   <br/><br/>

                                  //this is my form 

                                   <form id="generalreporte" method="post" action="reportegeneral.php">

                                    <div id="general" style="display: none; text-align: center;">

                                        <label style="font-size:medium">Fecha de:</label>&nbsp;&nbsp;

                                        <input type="date" name="fecha1" required/>&nbsp;&nbsp;

                                        <label style="font-size:medium">entre</label>&nbsp;&nbsp;

                                        <input type="date" name="fecha2" required/><br/><br/>

                                        <input type="submit"  value="Enviar"/>

                                        

                                    </div>

                                   </form>

                                    <div id="filtrar" style="display:none; text-align: center;">

                                        <label style="font-size:medium">Fecha de:</label>&nbsp;&nbsp;

                                        <input type="date" name="fecha3" required/>&nbsp;&nbsp;

                                        <label style="font-size:medium">entre</label>&nbsp;&nbsp;

                                        <input type="date" name="fecha4" required/><br/><br/>

                                       

                                        

                                         

                                         <div>

                                             <label style="font-size: medium;">Área:</label>

                                         <select>

                                             <option id="supo" value="1">¿como supo de este hotel?</option>

                                             <option id="atendidas" value="2">¿como fueron atendidas sus reservaciones?</option>

                                             <option value="3">a travez d que medio hizo su reservación</option>

                                        </select>

                                             </div><br/><br/>

                                         

                                         <input type="submit" value="Enviar"/>

                                    </div>

                                

                                 <div id="ajax_loader"><img id="loader_gif" src="images/loader.gif" style=" display:none;"/></div>

                               

                               

                                </div>

                                

                               <div>        // I want to show the pie chart just here.....     </div>

                                      

                <div class="art-postcontent art-postcontent-0 clearfix"></div>

</article></div>

                    </div>

                </div>

            </div>

    </div>

<footer class="art-footer clearfix">

  <div class="art-footer-inner">

<p>Copyright © 2012 Avandaro<br>

Avandaro Hotel Golf &amp; SPA Resort<br>

Vega del Río S/N Fracc. Avandaro Valle de Bravo.<br>

Edo. De Mex. CP. 51200<br>

<br></p>

    <p class="art-page-footer">

        <span id="art-footnote-links">Designed by Chrystopher Medina Rebollar..</span>

    </p>

  </div>

</footer>

</div>

</body></html>

[asgallant]

Ok, you need to modify two things now.  First is the jQuery for the form (the js you highlighted in yellow above).  In your code, this is wrapped in a document ready event handler, which is good for most uses, but you will need to wrap it in a callback handler for the google loader to use it with the Visualization API.  Then, in the success function, you need to take the results of the query, create a DataTable object from them, create a chart, and draw the chart with the results.  The result will look something like this:

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

function initialize () {

    // definimos las opciones del plugin AJAX FORM, options for the plugin ajaxform
    var opciones = {
        beforeSubmit: mostrarLoader,

        dataType: 'json',

        //funcion que se ejecuta antes de enviar el form
        success: mostrarRespuesta //funcion que se ejecuta una vez enviado el formulario
    };
    //asignamos el plugin ajaxForm al formulario myForm y le pasamos las opciones
    $('#generalreporte').ajaxForm(opciones);

    function mostrarLoader() {
        $("#loader_gif").fadeIn("slow");
    }

    function mostrarRespuesta(responseText) {
        $("#loader_gif").fadeOut("slow");
        $('#generalreporte').each(function() {
            this.reset();
        });

        //$("#ajax_loader").append(responseText);

        // create the DataTable
        var data = new google.visualization.DataTable(responseText);
        
        // create the chart
        var chart = new google.visualization.PieChart(document.getElementById('pie_chart_div'));
        
        // draw the chart
        chart.draw(data, {
            // pie chart options
            height: 400,
            width: 500
        });
    }
}​

Then, you have to assign an ID to the div where you want the chart to go.  This ID has to be the same one you use in the line var chart = new google.visualization.PieChart(document.getElementById('pie_chart_div')); to put it in the right place.  It doesn't matter what the ID is, as long as it is unique in this file and is the same in the div and in the javascript.  Using the example javascript as a basis, your div would be this:

<div id="pie_chart_div"></div>​

Don't put anything inside that div, because the chart will erase everything inside when it draws.

[Chrystopher Medina]

Hi my friend.  Thanks a lot for answering.  I wll Try this when i arrive at home

[Chrystopher Medina]

my friend really a lot of thanks it works perfectly. im gonna put this in the footer "designed by asgallant and chrystopher" if u give me ur name it would be better. ....... u know i really want learn more.... i like so much this ... i like programing a lot ...... u know i had never heard about sql inyection.. so i just have to validate the values beforesubmit ... please... could u give me some links of web sites or some tutorials or some books in pdf in english........ ...... and i have another problem i have to encript my values from a contact form .... solo dime un metodo para cifrar estos valores. y yo voy a investigar sobre eso...... really a lot of thanks im so glad . because I had tried this for months and now .....

you are very kind.... if there is anything I can do for you just tell me

[Chrystopher Medina]

u are a great teacher . where are u from ... i just want to know who help me to achive this ..... im so glad really. 

[asgallant]

If you want to credit me on the page, that would be nice; my full name is Andrew Gallant.

SQL injection is a complicated topic, and there is a lot to learn about it if you want to fully secure (as much as possible) your web environment.  I suggest starting here: http://www.unixwiz.net/techtips/sql-injection.html, which has a good example of how security auditors broke into their client's webserver using SQL injection.  It also has some basic steps you can take to protect your database near the end of the article.  One of the best things you can do is to use prepared statements and bound parameters for the query.  Unfortunately, these are not supported by the standard PHP mysql_* functions (like mysql_query).  There is an alternative (using PDO's) that supports prepared statements and bound parameters; you will have to enable PDO's mysql library in your PHP configuration to use them (the method of doing so may depend on your webserver and PHP version, google for instructions if it's not already enabled on your server).  Once you have PDO enabled for mysql, I can help you rewrite the PHP to use that instead of mysql_query (it's a relatively simple rewrite).

The easiest way to encrypt data sent from the client to server is to use a secure connection (https instead of http).  You will probably have to configure this on your webserver so that it only accepts https connections for secured pages.  There are also javascript libraries available that will perform encryption on the client side; you would have to handle decryption on the server side using a compatible PHP library.

[Chrystopher Medina]

definitely im going to credit u on the page.

and where are u from my friend.....

 well im gonna start to read about mysql inyection and pdo´s ... and yes. help me to rewrite the php to use pdo´s instead of mysql_query.  if its possible please letme some homework to start with this .. and if u know some tool in order to audit my website would be great.............. thanks a lot again ..... do u have facebook. ...... another thing.... could you tell me what is node js ... is a tecnology to program websites or is just a webserver.

[asgallant]

I'm from the US.

I can't think of any "homework" for PDO's...you could try reading the documentation, but that's not very helpful for a first introduction.  I guess the first step would be to get some information on your webserver environment.  Create a php script with this:

<?php

phpinfo();

?>

and then go to that page in a browser; you should get a bunch of information about your PHP installation.  Search for information about PDO and any associated modules or libraries (I think the PDO mysql library is called pdo_mysql_lib, but I could be mistaken).  If PDO is enabled and the PDO mysql library is enabled, then you don't have to change the PHP configuration.  If one or both is missing, you will have to look up how to enable them in the PHP configuration for the version of PHP you are using (the PHP version will be displayed on this page as well).

I don't think there are any automated security auditing tools for websites.  There are companies that will perform security audits, but my understanding is that this is usually expensive.  If you are not hosting sensitive data (financial information, personally identifiable information [names, addresses, identification numbers, that kind of stuff], medical records, or other sensitive/confidential information), it probably isn't worth paying for an audit.  If you are hosting such data, then I highly suggest employing the services of a web security expert to help design your website from the ground up.

Node js is for doing your server-side scripting in javascript instead of other languages like PHP.  I've never used it, so I can't comment on how good it is, but in general, I've heard that it is a decent system.  It is convenient for programmers who are already experts in javascript but new to the other server-side scripting languages.

[Chrystopher Medina]

ok im gonna start with the php information. and u know i have another problem how i cant charge two pie charts in the same place but each with different results.... u know i have two questions and i want to charge the results from them. in two  pie charts..... because i read that just one pie chart can be charged in the same place.... i could be mistaken

[asgallant]

Do you want to update the chart with new data, or draw a completely different chart in the same div?

[asgallant]

At a guess, I would say you have an error in the SQL in PHP.  If you post your PHP code, I'll take a look at it.

On Wednesday, December 12, 2012 11:41:25 AM UTC-5, Ian Haylock wrote:

Hi asgallant, thank you very much for th taking the time to help others. i have a question if you dont mind, i followed Diana´s sample and got it to work, well sort of, i used my onw db and managed to show the graph and everithing, the only thing is that my mysql query running in phpmyadmin shows 55 records and my json result shows only 14 records, how is that possible ???

[Chrystopher Medina]

Yesterday i was working in the pie chart wich u help me to do.  And i was able to update the same pie chart with new data......    do u remeber that i Have a file with a form this form has 2 fileds type=date. Well And then another file obtains the values .... But now i want to do 2 mysql querys with the values.  And generate 2 json results completly diferent And send them to the file wich has the form And generate 2 pie charts in the same file And in the same time

[Ian Haylock]

thanks for the reply ..

the query im using in phpmyadmin showing 55 records: SELECT planta, capacidadMW FROM planta;

the code using to encode using JSon (result is only 14 records):

<?php

include('connect-db.php');

$sth = mysql_query("SELECT planta, capacidadMW FROM planta");
$rows = array();
$flag = true;

$table = array();
$table['cols'] = array(

    array('label' => 'planta', 'type' => 'string'),
    array('label' => 'capacidad en MW', 'type' => 'number')
);

$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $temp = array();
    $temp[] = array('v' => (string)$r['planta']);
    $temp[] = array('v' => (int) $r['capacidadMW']);

    $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;

$jsonTable = json_encode($table);

echo $jsonTable;
?>

[Ian Haylock]

just to let u know, the "planta" record in my table is type Varchar(45) and the "capacidadMW" is type decimal (9,2)

[asgallant]

This isn't causing your problem, but since your "capacidadMW" is a decimal type, and not an integer, you should probably change (int) $r['capacidadMW']); to (float) $r['capacidadMW']);

That aside, I don't see anything in your code that would truncate the results.  Try running this and see what number is displayed:

<?php

include('connect-db.php');

$sth = mysql_query("SELECT planta, capacidadMW FROM planta");

echo mysql_num_rows($sth);

?>

If that shows 55 records, then there is a problem in the PHP; if it shows less than 55, then there is a problem with your connection to the database.

[asgallant]

Making two queries is certainly possible.  The first question I have is, can these two queries be logically combined into one?  If you don't have to make a second query to get the data you need, then it makes sense to use only 1 query.  The Visualization API has a tool called a DataView that you can use to separate the data into the different sets you need for different charts.

[Ian Haylock]

query solved !!!!!!!! thank you.
now my only problem is that before the browsers shows the graphs i keep getting the  " (string) " message and have to click on it to see the graph ... any ideas ??? i have changed the types, added options, etc and still nothing

[asgallant]

I've never seen any " (string) " message.  Can you post a screenshot, your javascript code, and a sample of the json returned by your query?

[Ian Haylock]

json generated:

{"cols":[{"label":"planta","type":"string"},{"label":"capacidad en MW","type":"number"}],"rows":[{"c":[{"v":"Francisco Morazan"},{"v":300}]},{"c":[{"v":"Lufussa III"},{"v":267.4}]},{"c":[{"v":"Enersa"},{"v":259}]},{"c":[{"v":"EEHSA - Mesoamerica"},{"v":102}]},{"c":[{"v":"Elcosa"},{"v":80}]},{"c":[{"v":"Lufussa II"},{"v":80}]},{"c":[{"v":"Rio Lindo"},{"v":80}]},{"c":[{"v":"Emce Choloma"},{"v":55}]},{"c":[{"v":"Lufussa I"},{"v":40}]},{"c":[{"v":"Enersa (excedente de 30 MW)"},{"v":30}]},{"c":[{"v":"Nacaome"},{"v":30}]},{"c":[{"v":"Ca\u00f1averal"},{"v":29}]},{"c":[{"v":"La Ceiba"},{"v":26}]},{"c":[{"v":"La Grecia - Celsur"},{"v":25.5}]},{"c":[{"v":"Cahsa - Azucarera Hondure\u00f1a"},{"v":25}]},{"c":[{"v":"El Nispero"},{"v":22.5}]},{"c":[{"v":"Envasa"},{"v":21}]},{"c":[{"v":"Elcatex"},{"v":21}]},{"c":[{"v":"Chumbagua"},{"v":20}]},{"c":[{"v":"Nacional de Ingenieros"},{"v":20}]},{"c":[{"v":"La Puerta MEX - Hitachi"},{"v":18}]},{"c":[{"v":"Celsur"},{"v":18}]},{"c":[{"v":"La Puerta ENEE- General Electric"},{"v":15}]},{"c":[{"v":"CISA - La Esperanza"},{"v":12.7}]},{"c":[{"v":"Cuyamapa"},{"v":12.2}]},{"c":[{"v":"Ampac (American Pacific)"},{"v":10.1}]},{"c":[{"v":"Azunosa ( IHSA )"},{"v":10}]},{"c":[{"v":"Geen Valley - Park Dale"},{"v":10}]},{"c":[{"v":"AYSA"},{"v":8}]},{"c":[{"v":"Cuyamel"},{"v":8}]},{"c":[{"v":"Tres Valles"},{"v":7.8}]},{"c":[{"v":"El Cisne"},{"v":7}]},{"c":[{"v":"Cortecito"},{"v":6}]},{"c":[{"v":"La Gloria"},{"v":5.3}]},{"c":[{"v":"Santa Fe"},{"v":5}]},{"c":[{"v":"Rio Blanco"},{"v":5}]},{"c":[{"v":"Coronado"},{"v":4.5}]},{"c":[{"v":"Babilonia"},{"v":4.3}]},{"c":[{"v":"San Carlos"},{"v":4}]},{"c":[{"v":"Cececapa"},{"v":3.5}]},{"c":[{"v":"El Coyolar"},{"v":1.7}]},{"c":[{"v":"Mangungo I"},{"v":1.5}]},{"c":[{"v":"Santa Maria del Real"},{"v":1.2}]},{"c":[{"v":"Eda"},{"v":1.2}]},{"c":[{"v":"Aceydesa"},{"v":1.1}]},{"c":[{"v":"Yodeco"},{"v":1}]},{"c":[{"v":"Zacapa"},{"v":0.8}]},{"c":[{"v":"Yojoa"},{"v":0.6}]},{"c":[{"v":"Aguan"},{"v":0.5}]},{"c":[{"v":"Lean"},{"v":0.5}]},{"c":[{"v":"La Nieve"},{"v":0.5}]}]}

javascript:

<html>
  <head>
   <meta charset="utf-8">
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
     <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
    <script type="text/javascript">

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

      google.setOnLoadCallback(drawChart);
     
      function drawChart() {
           var jsonData =$.ajax({
          url: "plantas_capacidad_chart_info.php",
          dataType:"json",
          async: false
          }).responseText;

          alert(typeof(jsonData));
          console.log(jsonData);
            
        var data = new google.visualization.DataTable(JSON.parse(jsonData));
        var options = {'title':'Capacidad Instalada de Plantas',
                       'width':800,
                       'height':600,
                       'hAxis.showTextEvery':2,
                       'backgroundColor': '',
                       'bar.groupWidth' : '90%'
                       };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        chart.draw(data,options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" ></div>
  </body>
</html>




image  ----string:

[asgallant]

You have a couple of unnecessary lines in your javascript:

alert(typeof(jsonData));
console.log(jsonData);

The first is what pops up that message.  The second is dumping the contents of the json to the developers console - this will cause the script to bomb in older versions of IE, as they don't have a console object.  Delete or comment these two lines and you should be fine.

[Ian Haylock]

it worked !!!!!!! cant say thank you enough..... i wish there were more people like you.

[Chrystopher Medina]

Hi my friend Im gonna send u my code in orden to explain me better. ... i Have a question can i download the google library to draw the pie charts in orden to import It on my proyect. Cause when i Have not Internet It doesnt work

[Chrystopher Medina]

Yes ian haylock he is very kind. I wish there were more people like andrew ....

[asgallant]

You're welcome.

[asgallant]

You can't download the API, as downloading, mirroring, or locally hosting the API is forbidden by the Terms of Service.

[asgallant]

Adding user-defined parameters to the SQL string opens you up to SQL-injection attacks (mentioned above in my conversation with Chrystopher).  PHP's standard mysql library doesn't provide any measure of protection against them, so I strongly recommend changing to using PDO's with prepared statements and bound parameters.  This may require you to change your PHP configuration; run this script, and see if PDO is enabled, and if so, see if it is set up for use with mysql:

<?php

phpinfo();

?>

Once it is enabled and set up for use with mysql, converting from the mysql tools to PDO is relatively simple.

On Wednesday, December 12, 2012 5:08:05 PM UTC-5, Jose wrote:

Hello asgallant, I see your still very active in helping other people which is very kind of you!

Since you've last helped me, I've added just a couple of minor things but I'm still trying to figure out how to dynamically change/pass a value to the sql string so as it can plot the chart from different data. The two files I'm still using are 'chart.php' & 'chartdata.php'. How do I get chart.php to pass this variable and have the data file receive it? The sql fields are the same, it's just the column 'id_testKey' that will dictate what data is requested.

José

On Wednesday, October 17, 2012 5:40:15 PM UTC-7, asgallant wrote:

You're welcome.

On Wednesday, October 17, 2012 7:06:01 PM UTC-4, Jose wrote:

Thank you very much, that helped! Now I'll use other chart options to fine tune it a bit to look similar to my excel charts.
Really appreciate the help asgallant!

On Wednesday, October 17, 2012 10:31:40 AM UTC-7, asgallant wrote:

D'oh!  My mistake, I did change something else.  The (float) typing converts null into 0, so you have to test for null and handle it specially.  In your php file, the while loop should look like this:

while($r = mysql_fetch_assoc($sth)) {

$temp = array();

$temp[] = array('v' => (float) $r['PsiBar']); 

$temp[] = array('v' => (is_null($r['prodPerct1'])) ? null : (float) $r['prodPerct1']); 

$temp[] = array('v' => (is_null($r['prodPerct2'])) ? null : (float) $r['prodPerct2']); 

$temp[] = array('v' => (is_null($r['prodPerct3'])) ? null : (float) $r['prodPerct3']); 

$rows[] = array('c' => $temp);

}

although, since your data is already in numerical form (and thus you don't have to force it to be type float), you can go with the simpler:

while($r = mysql_fetch_assoc($sth)) {

$temp = array();

$temp[] = array('v' => $r['PsiBar']); 

$temp[] = array('v' => $r['prodPerct1']); 

$temp[] = array('v' => $r['prodPerct2']); 

$temp[] = array('v' => $r['prodPerct3']); 

$rows[] = array('c' => $temp);

}

It's also a good idea to force the mime type to application/json by setting the header, before you echo the json:

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

echo $jsonTable;

On Wednesday, October 17, 2012 10:54:15 AM UTC-4, asgallant wrote:

I used your code exactly as it appears in the files you posted, except for the modifications mentioned.  I didn't save a copy, though, so I can't post them back.  I'll see if I can duplicate it later today.

On Wednesday, October 17, 2012 10:11:01 AM UTC-4, Jose wrote:

Asgallant, I tried it again and still the same results, although I did add that var option but it didn't help. I'm wondering if its something else in your scripts/code that is different than mine. If you could post those, that would be great.
As another non sufficient way, could three separate SQL calls be made then plot the results onto the same chart?

On Tuesday, October 16, 2012 10:16:00 AM UTC-7, asgallant wrote:

I duplicated your table and ran the query, and Method 2 looked right to me.  I had to make 1 small change to the SQL to make it run (but that could be a quirk of my MySQL install), and 1 change to the chart options.  The SQL looked like this:

SELECT 

foo.PsiBar,

IF(prodPerct1 = 0, null, foo.prodPerct1) as prodPerct1,

IF(prodPerct2 = 0, null, foo.prodPerct2) as prodPerct2,

IF(prodPerct3 = 0, null, foo.prodPerct3) as prodPerct3

FROM (

SELECT

PsiBar,

SUM(IF(id_sample = 1, prodPerct, null)) AS prodPerct1,

SUM(IF(id_sample = 2, prodPerct, null)) AS prodPerct2,

SUM(IF(id_sample = 3, prodPerct, null)) AS prodPerct3

FROM tbl_dilution

GROUP BY PsiBar

) AS foo

and I set the "interpolateNulls" chart option to true:

var options = {

title: 'Line Chart Test',

interpolateNulls: true

};

On Tuesday, October 16, 2012 12:51:40 PM UTC-4, Jose wrote:

I gave both a try and neither looked as it should. Method 2 displays the chart as it did previous where it was plotting the null '0' values.

On Monday, October 15, 2012 9:38:58 PM UTC-7, asgallant wrote:

Hmmm...I can see two ways of handling that.  One results in a messy DataTable, and will probably work; the other results in a cleaner DataTable, but might not work.

Method 1: change the SQL statement to this:

SELECT 

PsiBar,

IF(id_sample = 1, prodPerct, null) AS prodPerct1,

IF(id_sample = 2, prodPerct, null) AS prodPerct2,

IF(id_sample = 3, prodPerct, null) AS prodPerct3

FROM tbl_dilution

removing the sums and the group by clause.  This will result in more rows of data (with duplicate domain column entries) than necessary.

Method 2: this will work only if 0 is not a valid value for your data points to have.  Change the SQL to this:

SELECT 

PsiBar,

IF(prodPerct1 = 0, null, prodPerct1) as prodPerct1,

IF(prodPerct2 = 0, null, prodPerct2) as prodPerct2,

IF(prodPerct3 = 0, null, prodPerct3) as prodPerct3

FROM (

SELECT

PsiBar,

SUM(IF(id_sample = 1, prodPerct, null)) AS prodPerct1,

SUM(IF(id_sample = 2, prodPerct, null)) AS prodPerct2,

SUM(IF(id_sample = 3, prodPerct, null)) AS prodPerct3

FROM tbl_dilution

GROUP BY PsiBar

) AS foo

which tests to see if the sum is 0, and if it is, sets the value to null instead.  The DataTable will be cleaner, but it won't work if your values can be 0.

On Monday, October 15, 2012 6:45:51 PM UTC-4, Jose wrote:

asgallant, you are right, I am trying to get three lines plotted for each 'id_sample'. Within each id group, there are 12 plot points.
I've tried the code you provided, thanks, but it appears to also plot the null values '0' between each data point. How do I fix this?

{"cols":[{"label":"PsiBar","type":"number"},{"label":"Series 1","type":"number"},{"label":"Series 2","type":"number"},{"label":"Series 3","type":"number"}],"rows":[{"c":[{"v":0.39},{"v":0.36},{"v":0},{"v":0}]},{"c":[{"v":0.5},{"v":0},{"v":0.26},{"v":0.11}]},{"c":[{"v":0.56},{"v":0.49},{"v":0.34},{"v":0}]},{"c":[{"v":0.57},{"v":0},{"v":0},{"v":0.16}]},{"c":[{"v":0.84},{"v":0.56},{"v":0.41},{"v":0.15}]},{"c":[{"v":1.01},{"v":0.62},{"v":0.42},{"v":0}]},{"c":[{"v":1.02},{"v":0},{"v":0},{"v":0.24}]},{"c":[{"v":1.3},{"v":0.66},{"v":0.49},{"v":0}]},{"c":[{"v":1.31},{"v":0},{"v":0},{"v":0.26}]},{"c":[{"v":1.45},{"v":0.66},{"v":0.5},{"v":0.27}]},{"c":[{"v":1.74},{"v":0},{"v":0.52},{"v":0}]},{"c":[{"v":1.75},{"v":0.68},{"v":0},{"v":0.28}]},{"c":[{"v":2.1},{"v":0},{"v":0},{"v":0.28}]},{"c":[{"v":2.11},{"v":0},{"v":0.52},{"v":0}]},{"c":[{"v":2.12},{"v":0.68},{"v":0},{"v":0}]},{"c":[{"v":2.57},{"v":0},{"v":0.49},{"v":0.27}]},{"c":[{"v":2.58},{"v":0.65},{"v":0},{"v":0}]},{"c":[{"v":3.07},{"v":0},{"v":0},{"v":0.25}]},{"c":[{"v":3.09},{"v":0.6},{"v":0.46},{"v":0}]},{"c":[{"v":3.56},{"v":0.56},{"v":0},{"v":0.23}]},{"c":[{"v":3.57},{"v":0},{"v":0.42},{"v":0}]},{"c":[{"v":4.23},{"v":0},{"v":0},{"v":0.21}]},{"c":[{"v":4.34},{"v":0},{"v":0.39},{"v":0}]},{"c":[{"v":4.36},{"v":0.51},{"v":0},{"v":0}]}]}

Really appreciate your help on this!

On Thursday, October 11, 2012 12:43:03 PM UTC-7, asgallant wrote:

You're not charting 3 series there, you have 1 series.  Looking at your SQL table, I would guess that you want to display one series for each sample id, right?  If so, then you need to break out the "prodPerct" column into 3 different columns - 1 for each series.  This is probably best achieved in SQL, maybe with a query like this:

SELECT 

PsiBar,

SUM(IF(id_sample = 1, prodPerct, null)) AS prodPerct1,

SUM(IF(id_sample = 2, prodPerct, null)) AS prodPerct2,

SUM(IF(id_sample = 3, prodPerct, null)) AS prodPerct3

FROM tbl_dilution

GROUP BY PsiBar

and then use this to build the table:

$table['cols'] = array(

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

array('label' => 'Series 1', 'type' => 'number')

array('label' => 'Series 2', 'type' => 'number')

array('label' => 'Series 3', 'type' => 'number')

);

$rows = array();

while($r = mysql_fetch_assoc($sth)) {

$temp = array();

$temp[] = array('v' => (float) $r['psiBar']); 

$temp[] = array('v' => (float) $r['prodPerct1']); 

$temp[] = array('v' => (float) $r['prodPerct2']); 

$temp[] = array('v' => (float) $r['prodPerct3']); 

$rows[] = array('c' => $temp);

}

On Thursday, October 11, 2012 12:50:04 PM UTC-4, Jose wrote:

Hi asgallant,

Seeing Diana's example, I tried doing something similar with a Line graph but it's not coming out as I'd like.
It displays the three series but links them all together instead of individually displaying them (lineChart.jpg).
What I'm trying to achieve, is something similar to how it's displayed in Excel (chart_xls.jpg).
If you could help me in the right direction, I'd appreciate it alot as I have been trying various things and the outcome
isn't what I'm expecting.

José

On Wednesday, September 26, 2012 10:11:01 AM UTC-7, asgallant wrote:

What is throwing that error message?  Is it PHP?

You will have to adjust the data types to the type of data you are using, so if your first column isn't type string, you need to change it to something else in the column definitions (this goes for all columns - types must always match).  Also, the (string) typecasting in this line:

$temp[] = array('v' => (string) $r['PLACA']);

is probably not necessary, unless you have a non-string data type that you need to specifically convert into a string.

If you can post a link to the page, I can help debug things on the javascript end, if it turns out that is where the problem is.

On Wednesday, September 26, 2012 4:47:20 AM UTC-4, Barbara Gerstl wrote:

That is what I did... but, when opening goochart2.html, the result is the Error-Massage "string".
I think, it has something to do with the field settings of the columns. Do you have any tipps?

Thank you!


Am Montag, 24. September 2012 19:26:26 UTC+2 schrieb asgallant:

You can extrapolate from the code that the table has 6 columns: PLACA, S1, S2, S3, S4, S5.

On Monday, September 24, 2012 10:15:44 AM UTC-4, Barbara Gerstl wrote:

Hello Diana!

Thank you very much for showing the whole process on how to combine Google Graph API with a MySQL-Database. That is exactly what I am looking for.

I tried to rebuild your example and I am having problems with the structure of the database/field settings. Can you show me structure and field settings of the table "bd_salidas"?

Thank you for your answer.
Barbara


Am Mittwoch, 5. September 2012 21:56:35 UTC+2 schrieb Diana Flores:

yeaaaaaaahhhHHHH!!!!, we did it!!!!!!!!!!!!!. well at first i tried the .DataTable(jsonData);  but it gave me errors but i put the JSON.parse(jsonData));  and it works!!!!!!!!!!!!!!!!!!....im so happy!!! i will attach the files in case someone has the same problem!!!!!!!!....really really grateful, cause with your help i learned a lot of things!!!!....one month ago I was "what its php or mysql....JSON O_O???"  i think its a lot, but thanks!!!!