带有 PHP 和 MySQl 的 Google 图表工具

Google Chart Tools with PHP & MySQl

提问by Iladarsda

I'm trying to create a chart using the Google Visualization API, with PHP & MySQL in the background.

我正在尝试使用 Google Visualization API 创建一个图表，并在后台使用 PHP 和 MySQL。

What I'm doing is:

我正在做的是：

• getting the data from db using PHP / SQL

  $sth = mysql_query("SELECT * FROM Chart");
  

• creating JSON with PHP

  $rows = array();
  
  while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;
  }
  
  $jdata = json_encode($rows);
  

• and then feeding Google Visualization API with JSON

  var data = new google.visualization.DataTable(<?php echo $jdata ?>); 
  

• 使用 PHP/SQL 从数据库获取数据

  $sth = mysql_query("SELECT * FROM Chart");
  

• 用 PHP 创建 JSON

  $rows = array();
  
  while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;
  }
  
  $jdata = json_encode($rows);
  

• 然后用 JSON 提供 Google Visualization API

  var data = new google.visualization.DataTable(<?php echo $jdata ?>); 
  

Just to make sure the JSON is actually in the correct format I did:

只是为了确保 JSON 实际上是我所做的正确格式：

$jdata = json_encode($rows);
print $jdata;

which returned:

返回：

[{"id":"1","quarters":"1","salary":"1250"},{"id":"2","quarters":"2","salary":"2500"},{"id":"3","quarters":"3","salary":"4526"},{"id":"4","quarters":"4","salary":"4569"}] 

So,

所以，

• db connection is OK.
• creating JSON from PHP array is OK.
• JSON format is OK.

• 数据库连接正常。
• 从 PHP 数组创建 JSON 是可以的。
• JSON 格式没问题。

Firebug is returning an error saying:

Firebug 返回一条错误消息：

Table has no columns. [Break On This Error] b,Sl),[b]}function Zq(a,b){var c=a[xc]..."].")):d(l("Table has no columns."))}

表没有列。[打破这个错误] b,Sl),[b]}function Zq(a,b){var c=a[xc]..."].")):d(l("表没有列。 "))}

The question is how can I create columns from JSON data?

问题是如何从 JSON 数据创建列？

UPDATE:

更新：

Code used to create the graph below:

用于创建以下图表的代码：

// SQL Query
$sth = mysql_query("SELECT * FROM Chart");
//$rows = array();

while($r = mysql_fetch_assoc($sth)) {
   if(!isset($google_JSON)){    
     $google_JSON = "{cols: [";    
     $column = array_keys($r);
     foreach($column as $key=>$value){
         $google_JSON_cols[]="{id: '".$key."', label: '".$value."'}";
     }    
     $google_JSON .= implode(",",$google_JSON_cols)."],rows: [";       
   }
   $google_JSON_rows[] = "{c:[{v: '".$r['id']."'}, {v: '".$r['quarters']."'}, {v: '".$r['salary']."'}]}";
}    

// you may need to change the above into a function that loops through rows, with $r['id'] etc, referring to the fields you want to inject..

$data = $google_JSON.implode(",",$google_JSON_rows)."]}";

Output HTML CODE:

输出 HTML 代码：

        <!-- load Google AJAX API -->
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">  
        //load the Google Visualization API and the chart  
        google.load('visualization', '1', {'packages':['columnchart']});  

        //set callback  
        google.setOnLoadCallback (createChart);  

        //callback function  
        function createChart() {  

            //create data table object  
            var data = new google.visualization.DataTable({cols: [{id: '0', label: 'id'},{id: '1', label: 'quarters'},{id: '2', label: 'salary'}],rows: [{c:[{v: '1'}, {v: '1'}, {v: '1250'}]},{c:[{v: '2'}, {v: '2'}, {v: '2500'}]},{c:[{v: '3'}, {v: '3'}, {v: '4526'}]},{c:[{v: '4'}, {v: '4'}, {v: '4569'}]}]});  

            //instantiate our chart objects  
            var chart = new google.visualization.ColumnChart (document.getElementById('chart'));  

            //define options for visualization  
            var options = {width: 400, height: 240, is3D: true, title: 'Company Earnings'};  

            //draw our chart  
            chart.draw(data, options);  

        }  
    </script>

    <div id="chart"></div>

采纳答案by SW4

Per the docs, have you tried establishing the column references and data seperately?

根据文档，您是否尝试过分别建立列引用和数据？

var data = new google.visualization.DataTable();
data.addColumn('string', 'Task');
data.addColumn('number', 'Hours per Day');
data.addRows([
  ['Work', 11],
  ['Eat', 2],
  ['Commute', 2],
  ['Watch TV', 2],
  ['Sleep', {v:7, f:'7.000'}]
]);

To format into the correct JSON for the object, you can set it up as follows:

要将对象格式化为正确的 JSON，您可以按如下方式进行设置：

while($r = mysql_fetch_assoc($sth)) {
   if(!isset($google_JSON)){    
     $google_JSON = "{cols: [";    
     $column = array_keys($r);
     foreach($column as $key=>$value){
         $google_JSON_cols[]="{id: '".$key."', label: '".$value."'}";
     }    
     $google_JSON .= implode(",",$google_JSON_cols)."],rows: [";       
   }
   $google_JSON_rows[] = "{c:[{v: '".$r['id']."'}, {v: ".$r['quarters']."}, {v: ".$r['salary']."}]}";
}    
// you may need to change the above into a function that loops through rows, with $r['id'] etc, referring to the fields you want to inject..
echo $google_JSON.implode(",",$google_JSON_rows)."]}";

回答by Anam

Complete Working Example: PHP/MYSQL/Google Chart/JSON

PHP MySQL Google Chart JSON - Complete Example

PHP MySQL Google Chart JSON - 完整示例