Google Vis 使用 PHP JSON 问题从 SQL 数据库注释时间线
我正在尝试使用服务器端 PHP 脚本从 PostgreSQL 数据库创建一个简单的带注释的时间线来访问数据库中的数据，然后使用 JavaScript 客户端脚本来显示图形。那里的东西基本上非常简单。现在，正如您可能猜到的那样，当我访问该页面时，什么也没有显示。
所以这就是我得到的结果：当我查看我的 apache 日志文件时，我可以看到我的 PHP 脚本正在正确解析，并且当我使用超链接时（包括我的 JavaScript 添加的用于正确询问 PHP 的所有额外好处）它想要从数据库中得到什么）我可以看到正确的 Google 格式的响应。至少我认为。我认为问题可能是我的 PHP 函数的响应实际上是 Google 注释的时间轴对象的错误语法，但我找不到足够的文档来证明这是真的还是假的。
这是我的 PHP 函数输出的截断版本：
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"}]},
当然，它会继续，但我想我会让你了解我所看到的内容，而不是让你带着完整的转储入睡。
现在我知道通过放入 document.write("got here");在我的 JavaScript 中添加一些标签，我可以看出程序确实完成了，并且不会由于我的愚蠢疏忽而引发任何疯狂的错误...（张开嘴准备插入脚）...但是不知何故，从我的情况来看在其他地方读过，这个查询响应看起来格式不正确（我确实在这篇文章中更改了格式并插入了间距以提高可读性，所以如果您发现间距有问题，很可能是我）。我将在这篇文章的底部包含我的 PHP 和 JavaScript 代码。如果有人看到我错过的任何明显的地方，或者对可能出现的问题有任何见解，我将非常感谢对此提供帮助！
预先感谢大家！
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();
?>
------------------------ -------------------------------------------------- -------------
编辑：功能代码警报！
好的，这就是我现在所处的位置，这段代码可以工作，但当然不行从我的数据库中提取任何内容。正如您所看到的，格式与我从上面的 PHP 函数获得的格式完全相同。
功能代码 但不是我正在寻找的
<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>
对我来说，这表明我肯定缺少一个解析步骤。就像我之前所说的，从我知道如何使用谷歌代码进行的所有故障排除中，在调用 var data = result.getDataTable()< 后，我发现数据库响应中的行和列大小是正确的/代码> 函数。因此，在我尝试在 annotatedtimeline.draw(data, {OPTIONS}) 调用中使用变量数据之前，我必须对它进行进一步的修改......有什么想法吗？
** ------------------------------------------------ ------------------------- **
编辑 2：非工作代码 这是非工作代码。唯一真正的区别是数据的来源。因为我知道数据库正在响应，所以我对它仍然显示空白页这一事实感到困惑。
<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 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>
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我以前遇到过这个问题，只需修复 NON_WORKING_CODE 中的这一行
进入
这应该可行。如果没有，请尝试解决 preg_quote 问题 https:// code.google.com/p/mc-goog-visualization/issues/detail?id=16
I have experienced this problem before, just fix this line in NON_WORKING_CODE
into
This should work. If not, try to fix the preg_quote issue https://code.google.com/p/mc-goog-visualization/issues/detail?id=16
请查看此处的 Google 文档页面：http://code.google。 com/apis/visualization/documentation/gallery/annotatedtimeline.html
显示以下内容：
您似乎没有这样做。
编辑：我刚刚尝试了该页面上的示例，实际上，如果我删除显式的高度和宽度，我什么也得不到；以及时间线显示的高度和宽度。
A look at the Google docmentation page here : http://code.google.com/apis/visualization/documentation/gallery/annotatedtimeline.html
Shows the following :
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.
在您的回复中，键未用引号引起来，这可能是图表未呈现的原因
我刚刚搜索了 Visualization.query 文档和 这个 实例使用与您类似的响应，但在响应中，键用引号引起来：
https://spreadsheets.google.com/tq?key=pCQbetd-CptGXxxQIG7VFIQ&pub=1
我'我不熟悉 PHP 和您在 vis.php 中使用的可视化库，但如果您可以设法添加这些引号，我几乎可以肯定它会起作用
我将使用带有对象表示法的表日期，就像您在 .NET 中所做的那样这是我看到的唯一区别
希望它有帮助，我知道这是一篇旧帖子，但对于像我这样的人来说
In your response, the keys are not enclosed in quote, it might be the reason why the chart is not rendered
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