Google Vis annotated timeline from SQL database using PHP JSON issue_问答_开发者

I am trying to create a simple annotated timeline from a PostgreSQL database using a server side PHP script to access the data from the database, then a JavaScript client side script to display the graph. Basically pretty straightforward stuff there. Now as you may guess, nothing is showing up when I visit the page.

So here is where I've gotten: When I looked at my apache log files, I can see that my PHP script is parsing correctly, and when I use the hyperlink (including all the extra goodies that my JavaScript added to properly ask PHP for what it wants from the database) I can see the response in the correct Google format. At least I think. I KINDA think that the issue may be that the response from my PHP function is actually in an incorrect syntax for the Google annotated timeline object, but I cant find enough documentation to prove that is true or not.

Here is a truncated version of what my PHP function spits out:

Google.visualization.Query.setResponse({version:'0.5',reqId:'0',status:'ok',table:{cols: [{id:'date',label:"date",type:'datetime'},{id:'temp',label:"temp",type:'number'}],
rows: [{c:[{v:new Date(2011,2,22,13,47,26),f:"03\/22\/2011 01:47pm"},{v:132.8,f:"133"}]},{c:[{v:new Date(2011,2,22,13,48,57),f:"03\/22\/2011 01:48pm"},{v:136.8,f:"137"}]},
       {c:[{v:new Date(2011,2,22,13,56,49),f:"03\/22\/2011 01:56pm"},{v:132.8,f:"133"}]},{c:[{v:new Date(2011,2,22,13,58,42),f:"03\/22\/2011 01:58pm"},{v:128.8,f:"129"}]},
       {c:[{v:new Date(2011,2,22,14,1,26),f:"03\/22\/2011 02:01pm"},{v:124.8,f:"125"}]},{c:[{v:new Date(2011,2,22,14,4,19),f:"03\/22\/2011 02:04pm"},{v:128.8,f:"129"}]},{c:[{v:new Date(2011,2,22,14,5,51),f:"03\/22\/2011 02:05pm"},{v:132.8,f:"133"}]},

And it goes on of course, but I figured I would give you an idea of what I was seeing, not put you to sleep with a complete dump.

Now I know by putting in document.write("got here"); kinda tags into my JavaScript, I can tell that the program does finish, and doesn't throw any crazy errors due to silly oversights on my part...(Opening mouth in preparation to insert foot)... However somehow from what I have read elsewhere it this query response looks mal-formatted somehow (I did change the formatting and insert spacing for readability in this post, so if you see something wrong with the spacing, it was more than likely me). I will include both my PHP and my JavaScript code at the bottom of this post. If anyone sees anything glaring that I missed, or have any insights into what could be the problem, I would really appreciate some help with this!

Thanks in advance everyone!

JavaScript index.html

<html> 
  <head> 
      <script type="text/javascript" src="http://www.google.com/jsapi"></script> 
          <script type="text/javascript"> 
        google.load("visualization", "1", {packages:["annotatedtimeline"]});
        google.setOnLoadCallback(drawChart);
        function drawChart() {
                var query = new google.visualization.Query('/vis.php');
            query.setQuery('SELECT date,temp FROM temp1 ORDER BY date');
            query.send(function(result) {
                document.write(result.getDetailedMessage());
                  if(result.isError()) {
                  alert(result.getDetailedMessage());

            } else {

                var data = result.getDataTable();
            var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_div'));                   
            chart.draw(data,{displayAnnotations: false});
                 }
               });
               }
            </script> 
      </head> 
    <body> 
    <div id="chart_div"></div> 
  </body> 
 </html>

PHP vis.php

<?php
require_once 'MC/Google/Visualization.php';

$user = 'postgres';
$db = new PDO('pgsql:host=localhost;dbname=house',$user,'');
$vis = new MC_Google_Visualization($db,'postgres');

$vis->addEntity('temp1', array(
'fields' => array(
'date' => array('field' => 'date', 'type' => 'datetime'),
'temp' => array('field' => 'temp', 'type' => 'number')
   )
));

$vis->setDefaultEntity('temp1');
$vis->handleRequest();
?>

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

EDIT: Functional Code Alert!

Ok, so here is where I'm at now, this code works, but of course doesnt pull anything from my database. As you can see, the formatting is exactly the same as I was getting from the PHP function above.

FUNCTIONAL CODE but not what I'm looking for

<html> 
  <head> 
      <script type="text/javascript" src="http://www.google.com/jsapi"></script> 
          <script type="text/javascript"> 
       google.load("visualization", "1", {packages:["annotatedtimeline"]});
       google.setOnLoadCallback(drawChart);
           function drawChart() {
                  var data = new google.visualization.DataTable( 
                  { 
                cols: [{id:'date',label: 'date', type: 'datetime'}, 
                       {id:'level',label: 'level', type: 'number'}], 
                rows: [ 
                       {c:[{v: new Date(2011,2,24,6,52,26),f:"03\/24\/2011 06:52am"}, {v:91.4,f:"91"} ]}, 
                       {c:[{v: new Date(2011,2,25,7,35,20),f:"03\/25\/2011 07:35am"}, {v:89.4,f:"89"} ]}, 
                       {c:[{v: new Date(2011,2,26,1,2,15),f:"03\/26\/2011 01:02am"}, {v:85.4,f:"85"} ]}, 
                       {c:[{v: new Date(2011,2,27,0,27,13),f:"03\/27\/2011 12:27am"}, {v:85.4,f:"85"} ]}] 
            },   0.6); 

       var annotatedtimeline = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_div'));
       annotatedtimeline.draw(data, {'displayAnnotations': true});
                                            开发者_开发百科                                }
            </script> 
          </head> 
        <body> 
        <div id='chart_div' style="width:400; height:250"></div> 
  </body> 
</html>

To me this indicates that there must be one parsing step I'm missing. Like I said before, from all the troubleshooting I know how to do with the google code, I got that the row and col sizes were correct in the response from the database after calling the var data = result.getDataTable() function. So there must be one further modification I have to do to the variable data before I try to use it in my annotatedtimeline.draw(data, {OPTIONS}) call.... Any ideas?

** ------------------------------------------------------------------------- **

EDIT 2: NON WORKING CODE This is the non working code. The only real difference is the source of the data. And since I know that the database is responding, I am confused by the fact that its still showing a blank page.

 <html> 
    <head> 
        <script type="text/javascript" src="http://www.google.com/jsapi"></script> 
            <script type="text/javascript"> 
        google.load('visualization', '1', {packages: ['annotatedtimeline']});
        function drawChart() {
         //Tell Google Visualization where your script is
                var query = new google.visualization.Query('/vis.php');
            query.setQuery('SELECT date,level FROM tank1 ORDER BY date');
            query.send(function(result) {
                //if there is an error
                document.write(result.getDetailedMessage());
                  if(result.isError()) {
                  alert(result.getDetailedMessage());

            } else {
                // otherwise plot the data
                var data = result.getDataTable();
                          // Inserting a document.write(data.getNumberOfRows()) proves that the datatable is loading seemingly correctly here
            var annotatedtimeline = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_div'));
            annotatedtimeline.draw(data, { 'displayAnnotations': false });
                 }
               });
               }
           google.setOnLoadCallback(drawChart);
            </script> 
      </head> 
    <body> 
        <div id="chart_div" style="width:400px; height:250px"></div> 
  </body> 
</html>

I have experienced this problem before, just fix this line in NON_WORKING_CODE

query.setQuery('SELECT date,level FROM tank1 ORDER BY date');

into

query.setQuery('SELECT *');

This should work. If not, try to fix the preg_quote issue https://code.google.com/p/mc-goog-visualization/issues/detail?id=16

A look at the Google docmentation page here : http://code.google.com/apis/visualization/documentation/gallery/annotatedtimeline.html Shows the following :

Important: To use this visualization, you must specify the height and width
           of the container element explicitly on your page. So, for example:
           <div id="chart_div" style="width:400; height:250"></div>.

You don't appear to do this.

EDIT : I just tried the example on that page and indeed if I remove the explicit height and width I get nothing; with the height and width the timeline displays.

In your response, the keys are not enclosed in quote, it might be the reason why the chart is not rendered

{id:'date', type:'date'}

{"id":"date", "type":"date"}

I just searched for visualization.query documentation and this live example uses a response like yours but in the response, the keys are enclosed in quote:

https://spreadsheets.google.com/tq?key=pCQbetd-CptGXxxQIG7VFIQ&pub=1

I'm not familiarized with PHP and that visualization library you use in vis.php but if you could manage to add those quotes, I'm almost sure that it will work

I'm usign a tabledate with object notation as you did but in .NET and that's the only thing I see difference

Hope it helps, I know is an old post but for people like me in the future