doudeng5218 2014-04-22 20:28
浏览 43
已采纳

PHP / MySQL谷歌图表

Everyone I am trying to create a google chart with PHP with Database from a local MySQL database. I've done so many times without issues. I work in a warehouse and we have many floors, instead of having charts for each floor, our management team wants both floors on one chart, once I've done this I've received an error 'This table has no columns'. This occured after I joined my F1 table, and F2 Table. Independently they all work perfectly fine. I will attach source code for Combined and individual, I am bashing my head as I can't quite seem to see what the issue is.

Thank you!

Working Code(Gave sensitive data fake names)

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;
?>

Non-Working Code(Combing both floors onto one chart):

<?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;
?>
  • 写回答

1条回答 默认 最新

  • dongtaidai0492 2014-04-23 22:32
    关注

    Here is the code that I corrected that works. I made a rookie move, I needed a double quote around 0 as I did with my where clauses.

    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;
    ?>
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 如何解决蓝牙通话音频突发失真问题
  • ¥15 安装opengauss数据库报错
  • ¥15 【急】在线问答CNC雕刻机的电子电路与编程
  • ¥60 在mc68335芯片上移植ucos ii 的成功工程文件
  • ¥15 笔记本外接显示器正常,但是笔记本屏幕黑屏
  • ¥15 Python pandas
  • ¥15 蓝牙硬件,可以用哪几种方法控制手机点击和滑动
  • ¥15 生物医学数据分析。基础课程就v经常唱课程舅成牛逼
  • ¥15 云环境云开发云函数对接微信商户中的分账功能
  • ¥15 空间转录组CRAD遇到问题