我正在尝试从本地MySQL数据库使用PHP与数据库一起创建google图表的所有人。我已经做了很多次了，没有问题。我在仓库中工作，我们有很多楼层，而不是每个楼层都有图表，我们的管理团队希望两个楼层都在一个图表上，一旦完成此操作，我将收到错误消息“此表无列”。这是在我加入F1表和F2表之后发生的。独立地，它们都工作得很好。我将附加合并和个人的源代码，因为我似乎还不太清楚问题出在哪里，所以我正在ash头。
谢谢！
工作代码（提供敏感数据假名）
mysql_select_db('db', $con);
// write your SQL query here (you may use parameters from $_GET or $_POST if you need them)
$query = mysql_query('select BalanceDate, Value
from fcfinance.DAT_METRICS
where FC in("warehouse") and metric = "utilization:Simple Bin Count"
and balancedate >= current_date-10
and Floor = "pa01"
order by balancedate;');
$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)
*/
// I assumed your first column is a "string" type
// and your second column is a "number" type
// but you can change them if they are not
array('label' => 'BalanceDate', 'type' => 'string'),
array('label' => 'Utilization', 'type' => 'number')
);
$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['BalanceDate']);
$temp[] = array('v' => (int) $r['Value']);
// 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);
// set up header; first two prevent IE from caching queries
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
// return the JSON data
echo $jsonTable;
?>
非工作代码（将两层楼合并为一张图表）：
<?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
* table structure - colum1 is cas: has text/description - column2 is data has the value
*/
$con = mysql_connect('hostname', 'user', 'password') or die('Error connecting to server');
mysql_select_db('db', $con);
// write your SQL query here (you may use parameters from $_GET or $_POST if you need them)
$query = mysql_query('select * from (select coalesce(f1.BalanceDate,f2.BalanceDate) BalanceDate,
case when f1.value is null then '0' else f1.value end f1_value,
case when f2.value is null then '0' else f2.value end f2_value from (
(select BalanceDate, Value, Floor
from fcfinance.DAT_METRICS
where FC in("warehouse")
and metric = "utilization:Simple Bin Count"
and balancedate >= current_date-10
and Floor = "pa01"
order by floor, balancedate)f1
left join
(select BalanceDate, Value, Floor
from fcfinance.DAT_METRICS
where FC in("warehouse")
and metric = "utilization:Simple Bin Count"
and balancedate >= current_date-10
and Floor = "pa02"
order by floor, balancedate) f2
on f1.BalanceDate = f2.BalanceDate));');
$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)
*/
// I assumed your first column is a "string" type
// and your second column is a "number" type
// but you can change them if they are not
array('label' => 'BalanceDate', 'type' => 'string'),
array('label' => 'f1 value', 'type' => 'number'),
array('label' => 'f2 value', 'type' => 'number')
);
$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['BalanceDate']);
$temp[] = array('v' => (int) $r['f1_value']);
$temp[] = array('v' => (int) $r['f2_value']);
// 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);
// set up header; first two prevent IE from caching queries
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
// return the JSON data
echo $jsonTable;
?>
最佳答案
这是我更正的代码，可以正常工作。我做了个菜鸟动作，就像在where子句中一样，我需要在0前后加上双引号。
mysql_select_db('fcfinance', $con);
// write your SQL query here (you may use parameters from $_GET or $_POST if you need them)
$query = mysql_query('select coalesce(f1.BalanceDate,f2.BalanceDate) BalanceDate,
case when f1.value is null then "0" else f1.value end f1_value,
case when f2.value is null then "0" else f2.value end f2_value from (
(select BalanceDate, Value
from fcfinance.DAT_KIVA_METRICS
where metric = "Utilization:Simple Bin Count"
and FC = ("OAK3")
and balancedate >= current_date-10
and Floor = "PaKiva01"
order by floor, BalanceDate) f1
left join
(select BalanceDate, Value
from fcfinance.DAT_KIVA_METRICS
where metric = "Utilization:Simple Bin Count"
and FC = ("OAK3")
and balancedate >= current_date-10
and Floor = "PaKiva02"
order by floor, BalanceDate) f2
on f1.BalanceDate = f2.BalanceDate);');
$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)
*/
// I assumed your first column is a "string" type
// and your second column is a "number" type
// but you can change them if they are not
array('label' => 'BalanceDate', 'type' => 'string'),
array('label' => 'Floor 1 Utilization', 'type' => 'number'),
array('label' => 'Floor 2 Utilization', 'type' => 'number')
);
$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['BalanceDate']);
$temp[] = array('v' => (int) $r['f1_value']);
$temp[] = array('v' => (int) $r['f2_value']);
// 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);
// set up header; first two prevent IE from caching queries
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
// return the JSON data
echo $jsonTable;
?>
关于php - PHP/MySQL Google图表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23229552/