data.addRows from php (pg_fetch

rocky

unread,

Oct 19, 2017, 10:17:20 AM10/19/17

to Google Visualization API

Hi, I have a problem with retrieving the data from php Query in google chart. I want to create line graph with "pH" values and "date" for last 5 records. I get graph with axis name but no more. Can someone show me next step. I am absolute beginner. I know I need addRows but I don't know how to do it. Thanks in advance. Already searched google groups but no luck.

In database date is date type, and pH is number.

Php

<?php

$db = pg_connect("host=localhost port=5432 dbname=postgres user=postgres");


$result = pg_query($db,"SELECT * FROM kv ORDER by date DESC LIMIT 5");
while ($row = pg_fetch_row($result)) {
  echo "ph: $row[2] date: $row[3]";
  echo "<br />\n";
}

?>

Result is:

ph: 6 date: 2017-10-30
ph: 7 date: 2017-10-29
ph: 9 date: 2017-10-28
ph: 5 date: 2017-10-27
ph: 6 date: 2017-10-26

And html

<html>
  <head>
    <!--Load the AJAX API-->
    <title>title</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript">

    google.charts.load('current', {packages: ['corechart', 'line']});
    google.charts.setOnLoadCallback(drawChart);
      
    function drawChart() {
      var jsonData = $.ajax({
          url: "getData2.php",
          dataType: "json"
          }).responseText;
          
      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(jsonData);
      data.addColumn('date', 'date');
      data.addColumn('number', 'ph');
     
        var options = {
        max:9,
        min:6.5,
        width: 1000, height: 300,
        hAxis: {
          title:'date'
        },
        vAxis: {
            title: 'pH'
        },
        };


      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>

  <body>
    <!--Div that will hold the pie chart-->
    <div id="text">some text</div>
    <div id="chart_div"></div>
  </body>
</html>

rocky

unread,

Oct 23, 2017, 6:06:52 AM10/23/17

to Google Visualization API

UPDATE

Encode into JSON, result now looks like :

{"date":"2017-10-30","ph":"6"},{"date":"2017-10-29","ph":"7"},{"date":"2017-10-28","ph":"9"},{"date":"2017-10-27","ph":"8"},{"date":"2017-10-27","ph":"5"},

<?php

$db = pg_connect("host=localhost port=5432 dbname=postgres user=postgres");


$result = pg_query($db,"SELECT * FROM kv ORDER by date DESC LIMIT 5");

while ($row = pg_fetch_object($result)) {
 
 echo json_encode($row),',';
}
pg_close($db);
?>

In html added add.row

<html>
 <head>
   <!--Load the AJAX API-->
   <title>title</title>
   <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
   <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
   <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
   <script type="text/javascript">

    google.charts.load('current', {packages: ['corechart', 'line']});
   google.charts.setOnLoadCallback(drawChart);
     
   function drawChart() {
     var jsonData = $.ajax({
         url: "getData2.php",
         dataType: "json"
         }).responseText;
         
     // Create our data table out of JSON data loaded from server.
     var data = new google.visualization.DataTable(jsonData);
     data.addColumn('date', 'date');
     data.addColumn('number', 'ph');

data.addRows(JSON.parse(jsonData));


       var options = {
       max:9,
       min:6.5,
       width: 1000, height: 300,
       hAxis: {
         title:'date'
       },
       vAxis: {
           title: 'pH'
       },
       };


      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
     chart.draw(data, options);
   }
   </script>
 </head>

  <body>
   <!--Div that will hold the pie chart-->
   <div id="text">some text</div>
   <div id="chart_div"></div>
 </body>
</html>

Still not working, I am not sure about this line

data.addRows(JSON.parse(jsonData)); is this ok?

Daniel LaLiberte

unread,

Oct 23, 2017, 9:02:34 AM10/23/17

to Google Visualization API

I don't know php, but it appears this code:

while ($row = pg_fetch_object($result)) {
 echo json_encode($row),',';

}

is only going to output a sequence of rows, with ',' terminator. Since the addRows method needs an array of arrays, you need to wrap all those rows in another array to get a single array of arrays: '[' ... ']'.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/d4ec70cc-ed0b-4d15-bcda-de812f3e7f30%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Daniel LaLiberte

dlaliberte@Google.com 5CC, Cambridge MA

rocky

unread,

Oct 23, 2017, 9:54:34 AM10/23/17

to Google Visualization API

I see. I tried manual like:

var foo = '[[2004, 6],[2005, 7],[2006,8],[2007,9]]';
data.addRows(JSON.parse(foo));

and it works. So my goal is to get date like var foo.

But I don't know how to do that (array of arrays). Is there another method like addRows or another way?

Appreciate your time.

To post to this group, send email to google-visua...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-visualization-api.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/d4ec70cc-ed0b-4d15-bcda-de812f3e7f30%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
Daniel LaLiberte
dlali...@Google.com 5CC, Cambridge MA

rocky

unread,

Oct 24, 2017, 4:02:59 AM10/24/17

to Google Visualization API

I think I made it.

getdata2.php

$arr = pg_fetch_all($result);
echo json_encode($arr);

Result is:

[{"ph":"6","date":"2017-10-30"},{"ph":"7","date":"2017-10-29"},{"ph":"9","date":"2017-10-28"},{"ph":"8","date":"2017-10-27"},{"ph":"5","date":"2017-10-27"}]

What about:

data.addRows(JSON.parse(jsonData));

Do I need some loop now?

Dana ponedjeljak, 23. listopada 2017. u 15:02:34 UTC+2, korisnik Daniel LaLiberte napisao je:

To post to this group, send email to google-visua...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-visualization-api.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/d4ec70cc-ed0b-4d15-bcda-de812f3e7f30%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
Daniel LaLiberte
dlali...@Google.com 5CC, Cambridge MA

rocky

unread,

Oct 25, 2017, 5:29:45 AM10/25/17

to Google Visualization API

I get JSON like;

[{"ph":6,"broj":4},{"ph":6,"broj":5},{"ph":7,"broj":6},{"ph":9,"broj":8},{"ph":5,"broj":9}]

And now I have troble in HTML row:

google.charts.setOnLoadCallback(drawChart); //row 13
data.addRows(JSON.parse(jsonData)); //row 28

ERROR is:

Uncaught (in promise) SyntaxError: Unexpected token u in JSON at position 0
    at JSON.parse (<anonymous>)
    at drawChart (load.html:28)
    at <anonymous>
drawChart @ load.html:28
Promise resolved (async)
google.G.H.T.nl @ loader.js:225
(anonymous) @ load.html:13

Do you have any idea?

HTML code

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



<html>
  <head>
    <!--Load the AJAX API-->


    <title>Pregled mjerenih podataka</title>


    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript">

    google.charts.load('current', {packages: ['corechart', 'line']});
    google.charts.setOnLoadCallback(drawChart);
      
    function drawChart() {
      var jsonData = $.ajax({
          url: "getData2.php",
          dataType: "json"
          }).responseText;
          
      // Create our data table out of JSON data loaded from server.


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


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


      data.addColumn('number', 'broj');
      
      //var foo = '[[2004, 5],[2005, 8.5],[2006,6.5],[2007,8]]';

     data.addRows(JSON.parse(jsonData));
      //data.addRows(JSON.parse(jsonData)); 
   
        var options = {
          title:'pH vrijednosti zadnjih 5 mjerenja',


       
        width: 1000, height: 300,
        
        hAxis: {


          title:'datum'


        },
        vAxis: {
            title: 'pH'
        },
        };


      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>

  <body>
    <!--Div that will hold the pie chart-->


    <div id="text">Graf</div>

Daniel LaLiberte

unread,

Oct 25, 2017, 8:07:00 AM10/25/17

to Google Visualization API

You should read the documentation at https://developers.google.com/chart/interactive/docs/datatables_dataviews

Note that the way you use JSON makes a difference. What you are doing would make sense, but it is not supported.

You should probably just use the simpler array of arrays form, so generate JSON like this instead:

[[6, 4], [6, 5}, ...]

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.

To post to this group, send email to google-visualization-api@googlegroups.com.

Visit this group at https://groups.google.com/group/google-visualization-api.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/d9d7f4b2-38c3-4424-aa70-3afdc87de577%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Daniel LaLiberte

dlaliberte@Google.com 5CC, Cambridge MA

rocky

unread,

Oct 27, 2017, 4:25:50 AM10/27/17

to Google Visualization API

Ok, I did it. With getdata.php:

$return = array();
while ($row = pg_fetch_array($result)) { 
    $return[] = array((int)$row[0],(int)$row[1]);
} 

pg_close($db); 

//var_dump($return);       
echo $data = json_encode($return);

I get:

[[6,4],[6,5],[7,6],[9,8],[5,9]]

and I use in index.php

<?php include 'getData2.php'; echo '$data'; ?>
 with

data.addRows(<?php echo ($data)?>);
 It turns out that is some problem with php inside html on IIS server.

index.php in browser

<!DOCTYPE HTML>

<head>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
 <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript">
    google.charts.load('current', {packages: ['corechart', 'line']});
    google.charts.setOnLoadCallback(drawChart);
      


    function drawChart() {
//here is my php include getData2.php echo data...
    	[[6,4],[6,5],[7,6],[9,8],[5,9]]


$data     
      var data = new google.visualization.DataTable();
      data.addColumn('number', 'ph');
      data.addColumn('number', 'broj');
      
    //here read php echo
        data.addRows([[6,4],[6,5],[7,6],[9,8],[5,9]]);
   
        var options = {
       
        width: 1000, height: 300,
        
        hAxis: {
          title:'datum'
        },
        vAxis: {
            title: 'pH'
        },
        };


      var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>

  <body>
  
    <!--Div that will hold the pie chart-->
    <div id="text">graf</div>
    <div id="chart_div"></div>
  </body>
</html>

rocky

unread,

Oct 30, 2017, 6:46:59 AM10/30/17

to Google Visualization API

Daniel, thanks for help. Here is answer:

php script

$return = array();
while ($row = pg_fetch_array($result)) { 
    $return[] = array((int)$row[0],(int)$row[1]);
} 

pg_close($db); 

//var_dump($return);       
echo $data=json_encode($return);

?>

Result is: [[6,4],[6,5],[7,6],[9,8],[5,9]]

and part of HTML code

function drawChart() {


      var data = new google.visualization.DataTable();
      data.addColumn('number', 'ph');

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


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

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

    var obj = JSON.parse(jsonData);
    data.addRows(obj);

I am still having problems with format 'date', because I want to display date on hAxis, JSON with date(as string) looks like:

[[6,"2017-11-05"],[6,"2017-10-30"],[7,"2017-10-29"],[9,"2017-10-28"],[5,"2017-10-27"]]

How to format dates now in google charts?

Daniel LaLiberte

unread,

Oct 30, 2017, 9:55:40 AM10/30/17

to Google Visualization API

This page should help: https://developers.google.com/chart/interactive/docs/datesandtimes

In particular, you need https://developers.google.com/chart/interactive/docs/datesandtimes#dates-and-times-using-the-date-string-representation

[[6, "Date(2017, 10, 5)"], [6, "Date(2017, 9, 30)"], ...]

Note also that months start at 0 when you specify dates this way.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/1a19711c-704c-4005-a8db-a95e98e4db98%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

rocky

unread,

Oct 31, 2017, 9:26:32 AM10/31/17

to Google Visualization API

Ok Daniel, I got now:

[[6,"Date(2017, 11, 05)"],[6,"Date(2017, 10, 30)"],[7,"Date(2017, 10, 29)"],[9,"Date(2017, 10, 28)"],[5,"Date(2017, 10, 27)"]]

Now is error

Uncaught (in promise) Error: Type mismatch. Value date(2017, 11, 05) does not match type date in column index 1.

In html I have:

function drawChart() {

      var data = new google.visualization.DataTable();
      data.addColumn('number', 'ph');

data.addColumn('date', 'date'); // maybe is here mistake???


       var jsonData = $.ajax({
        url: "getData2.php",
        dataType: "json",
        async: false
    }).responseText;

    var obj = JSON.parse(jsonData);
    data.addRows(obj);

        var options = {
          title:'pH vrijednost zadnjih 5 mjerenja',

         
        width: 1000, height: 300,
        
        hAxis: {
          title:'datum'
        },
        vAxis: {
            title: 'pH'
        },
        };


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

Do I need some hAxis { format:'some rule like Y/mm/dd' ....

Daniel LaLiberte

unread,

Oct 31, 2017, 10:31:59 AM10/31/17

to Google Visualization API

Sorry, my mistake. The string-formatted date doesn't work with addRows() (though I believe we should make all the various ways of constructing datatables consistent, so this will probably be fixed in the future). It only works with arrayToDataTable or with the DataTable constructor, so you should either switch to using arrayToDataTable or the DataTable constructor.

Also note, again, that you need to make your month numbers start with 0, not 1. November is month number 10. This is consistent with JavaScript and several other languages.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/34918671-8507-415c-95bc-e6e290026287%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

rocky

unread,

Nov 1, 2017, 10:02:42 AM11/1/17

to Google Visualization API

No problem Daniel. I am newbie and I learned something from these. You helped me a lot. So if I understand you I need JSON something like :

   {
     cols: [{id: 'task', label: 'Employee Name', type: 'string'},
            {id: 'startDate', label: 'Start Date', type: 'date'}],
     rows: [{c:[{v: 'Mike'}, {v: new Date(2008, 1, 28), f:'February 28, 2008'}]},
            {c:[{v: 'Bob'}, {v: new Date(2007, 5, 1)}]},
            {c:[{v: 'Alice'}, {v: new Date(2006, 7, 16)}]},
            {c:[{v: 'Frank'}, {v: new Date(2007, 11, 28)}]},
            {c:[{v: 'Floyd'}, {v: new Date(2005, 3, 13)}]},
            {c:[{v: 'Fritz'}, {v: new Date(2011, 6, 1)}]}
           ]
   }
)

I must create array of my own and then:

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

Is it better to use arraytodatetable or datatable?

Daniel LaLiberte

unread,

Nov 1, 2017, 10:17:17 AM11/1/17

to Google Visualization API

It may be better to use the DataTable constructor, but it is not going to make much difference.

But you *can* use an array of arrays with arrayToDataTable, so that is probably simpler for you to use. The array of arrays that you pass to arrayToDataTable is like your array of rows that you passed to addRows, but with an additional first row to define the columns.

--

You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/0f0214e8-e35b-4765-a1df-be28bf8d54f8%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

rocky

unread,

Nov 2, 2017, 6:14:37 AM11/2/17

to Google Visualization API

Finally it's over.

php code

$result = pg_query($db,"SELECT kv.ph, kv.date FROM public.kv ORDER BY date DESC LIMIT 5");


 $rows = array();
    //flag is not needed
    $flag = true;
    $table = array();

    $table['cols'] = array(

    
    array('label' => 'date', 'type' => 'date'),
    array('label' => 'ph', 'type' => 'number'),
    );

    $rows = array();

    while($r = pg_fetch_assoc($result)) {

    // assumes dates are in the format "yyyy-MM-dd"
    $dateString = $r['date'];
    $dateArray = explode('-', $dateString);
    $year = $dateArray[0];
    $month = $dateArray[1] - 1; // subtract 1 to convert to javascript's 0-indexed months
    $day = $dateArray[2];


    $temp = array();
    $temp[] = array('v' => "Date($year, $month, $day)"); 
    $temp[] = array('v' => (integer) $r['ph']);
    
    

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

    }

    $table['rows'] = $rows;
    echo $jsonTable = json_encode($table);
    /* echo $jsonTable; */  

?>

HTML

<!DOCTYPE HTML>

<head>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

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




  <script type="text/javascript">
 

    google.charts.load('current', {packages: ['corechart', 'line']});
    google.charts.setOnLoadCallback(drawChart);

    function drawChart() {

 var jsonData = $.ajax({
        url: "getData2.php",
        dataType: "json",
        async: false
    }).responseText;

      var data = new google.visualization.DataTable(jsonData);
      
      //data.addColumn('number', 'ph');
      //data.addColumn('date', 'date');

   //var obj = JSON.parse(jsonData);
   //data.addRows(obj);



        var options = {
          title:'pH vrijednost zadnjih 5 mjerenja',
         
        width: 1000, height: 300,
        
        hAxis: {
     
          title:'datum'
        },
        vAxis: {
            title: 'pH'
        },
        };


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

    </script>
  </head>

  <body>
  
    <!--Div that will hold the pie chart-->

<div id="text">graf</div>

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

data.addRows from php (pg_fetch_row) query to html

rocky

rocky

Daniel LaLiberte

rocky

rocky

rocky

Daniel LaLiberte

rocky

rocky

Daniel LaLiberte

rocky

Daniel LaLiberte

rocky

Daniel LaLiberte

rocky