从预言机中选择数据并使用谷歌图表绘制图表

参考我的博客：http://howdyharish.wordpress.com/2014/08/11/create-google-charts-using-php-and-oracle-database/#more-3

这是代码。

索引.php

<?php
ini_set(‘max_execution_time’, 123456);
$conn=oci_connect(‘username‘,’password‘,’DBname‘);
If (!conn)
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$query= oci_parse($conn, “select col1, col2 from tablename“);
oci_execute($query);
$rows = array();
$table = array();
$table['cols'] = array(
array(‘label’ => ‘col1‘, ‘type’ => ‘string‘),
array(‘label’ => ‘col2‘, ‘type’ => ‘number‘),
);
$rows = array();
while($r = oci_fetch_array($query, OCI_ASSOC+OCI_RETURN_NULLS)) {
$temp = array();
//The below col names have to be in upper caps.
echo $r["COL1"];
$temp[] = array(‘v’ => (string) $r["COL1"]);
$temp[] = array(‘v’ => (int) $r["COL2"]);
$rows[] = array(‘c’ => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
//Use the line below to see the data in jason format
//echo $jsonTable;
//Use the below lines of code to see data in a HTML table
/*echo “<table border=’1′ >'n”;
echo “<tr><th>col1</th><th>col2</th></tr>'n”;
while ($row = oci_fetch_array($query, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo “<tr>'n “;
foreach ($query as $block) {
echo ” <td>” . ($block !== null ? htmlentities($block, ENT_QUOTES) : “&nbsp;”) . “</td>'n”;
}
echo “</tr>'n”;
}
echo “</table>'n”; */
?>
//In head section
<!–Load the Ajax API–>
<script type=”text/javascript” src=”https://www.google.com/jsapi”></script&gt;
<script type=”text/javascript” src=”http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js”></script&gt;
<script type=”text/javascript”>
google.load(‘visualization’, ‘1’, {‘packages':['corechart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable(<?=$jsonTable?>);
var options = {
title: ‘name of the chart‘,
is3D: ‘true’,
width: 1000,
height: 600,
fontName: ‘Times-Roman‘,
fontSize: 23,
hAxis: {textStyle: {
fontName: ‘Times-Roman‘,
fontSize: ‘25‘ }}
};
//To create a line chart
var chart = new google.visualization.LineChart(document.getElementById(‘chart_div’));
chart.draw(data, options);
//To create a column chart
var chart2 = new google.visualization.ColumnChart(document.getElementById(‘chart_div2′));
chart2.draw(data, options);
}
</script>
// In body section
<!–this is the div that will hold the pie chart–>
<div id=”chart_div” ></div>
<div id=”chart_div2″ ></div>

你不能只使用json_encode，PHP 输出需要采用 Google API 可以理解的格式（谷歌文档在这里）。所以基本上，你需要在php中有一些函数，将OCI结果转换为谷歌可读的JSON字符串。

我已经发布了一个在我的设置中运行良好的解决方案，其他用户可能会从中受益。你可以在GitHub上看到我的代码：

https://github.com/ernestomonroy/PHP-for-Google-Visualization-API

php 代码看起来像 folows：

<?php
    /*
     * @author Ernesto Monroy <ehmizmg@gmail.com>
     * @version 1.0
     * This function takes the Connection Details and the SQL String and creates an two arrays, one for the column info
     * and one for the row data. This is then passed to the arrayToGoogleDataTable that builds and outputs the JSON string
     * 
     * Input for this is:
     *      $un: User Name
     *      $pw: Password
     *      $db: Connection String for the DB
     *              (e.g. '(DESCRIPTION=(CID=MyDB)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SID=MyDB)))' )
     *              (tip, if you are running your PHP and Oracle Instance on the same server 127.0.0.1 should be used) 
     * 
     * WARNINGS:
     *      -I have not included any Oracle Error Handling
     *      -I have not included any row limit, so if your query returns an unmanageable amount of rows, it may become an issue for you or your web users.
     *FUTURE OPPORTUNITIES:
     *      -This functions only return type and label properties. If you want to use pattern, id or p (for styling) you can add a check  when looping through the columns
     *      and try to detect a particular column name that you define as the property (EG. if oci_field_name($stid, $i)=="GOOGLE_P_DATA" then ....)
     */
    function getSQLDataTable($un,$pw,$db,$SQLString){
        $conn=oci_connect($un,$pw,$db);
        $stid= oci_parse($conn, "ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
        oci_execute($stid);
        $stid= oci_parse($conn, $SQLString);
        oci_execute($stid);
        $ncols = oci_num_fields($stid);
        $cols = array();
        $rows = array();
        $cell = array();
        for ($i = 1; $i <= $ncols; $i++) {
            $column_label  = '"'.oci_field_name($stid, $i).'"';
            $column_type  = oci_field_type($stid, $i);
            switch($column_type) {
                case 'CHAR': case 'VARCHAR2':
                    $column_type='"string"';
                break;
                case 'DATE':
                    $column_type='"datetime"';
                break;
                case 'NUMBER':
                    $column_type='"number"';
                break;
            }
            $cols[$i-1]=array('"type"'=>$column_type,'"label"'=>$column_label);
        }
        $j=0;
        while (($row = oci_fetch_array($stid, OCI_NUM+OCI_RETURN_NULLS)) != false) {
            for ($i = 0; $i <= $ncols-1; $i++) {    
                switch(oci_field_type($stid, $i+1)) {
                    case 'CHAR': case 'VARCHAR2':
                        $cellValue='"'.$row[$i].'"';
                        $cellFormat='"'.$row[$i].'"';
                    break;
                    case 'DATE':
                        if($row[$i]==null){
                            $cellValue='""';
                            $cellFormat='""';
                        } else {
                            $cellValue=convertGoogleDate(date_create($row[$i]));
                            $cellFormat='"'.date_format(date_create($row[$i]), 'd/m/Y H:i:s').'"';
                        }
                    break;
                    case 'NUMBER':
                        $cellValue=number_format($row[$i], 2, '.', '');
                        $cellFormat='"'.$row[$i].'"';
                    break;

                } //end of switch
                $cell[$i]=array('"v"'=>$cellValue,'"f"'=>$cellFormat);  
            }   
            $rows[$j]=$cell;        
            $j++;
        }       
        arrayToGoogleDataTable($cols, $rows);
    }
    /*This function takes in the columns and rows created in the previous function and returns the JSON String*/
    function arrayToGoogleDataTable($cols, $rows) {
        //Convert column array into google string literal
        echo "{'n";
        echo "'t".'"cols"'.": ['n";
        for($i = 0; $i < count($cols)-1; $i++) {
            echo "'t't{";
            $n=count($cols[$i]);
            foreach($cols[$i] as $arrayKey => $arrayValue) {
                    echo $arrayKey . ":" . $arrayValue;
                $n--;
                if ($n>0) {echo ",";}
            }
            echo "},'n";
        }
        //Last column without ending comma (},)
        echo "'t't{";
        $n=count($cols[$i]);
        foreach($cols[$i] as $arrayKey => $arrayValue) {
                echo $arrayKey . ":" . $arrayValue;
            $n--;
            if ($n>0) {echo ",";}
        }
        echo "}'n't]";
        //Now do the rows
        //Check if empty first
        if (count($rows)>0){
            echo ",'n't".'"rows"'.": ['n";
            //For each row
            for($j = 0; $j < count($rows)-1; $j++) {
                echo "'t't{".'"c":[';
                //For each cell
                for($i = 0; $i < count($rows[$j])-1; $i++) {
                    echo "{";
                    $n=count($rows[$j][$i]);
                    foreach($rows[$j][$i] as $arrayKey => $arrayValue) {
                            echo $arrayKey . ":" . $arrayValue;
                        $n--;
                        if ($n>0) {echo ",";}
                    }
                    echo "},";
                }
                //Last column without ending comma (},)
                $n=count($rows[$j][$i]);
                echo "{";
                    foreach($rows[$j][$i] as $arrayKey => $arrayValue) {
                        echo $arrayKey . ":" . $arrayValue;
                    $n--;
                    if ($n>0) {echo ",";}
                }
                echo "}]},'n";
            }
            //Last row
            echo "'t't{".'"c":[';
            //For each cell
            for($i = 0; $i < count($rows[$j])-1; $i++) {
                echo "{";
                $n=count($rows[$j][$i]);
                foreach($rows[$j][$i] as $arrayKey => $arrayValue) {
                        echo $arrayKey . ":" . $arrayValue;
                    $n--;
                    if ($n>0) {echo ",";}
                }
                echo "},";
            }
            $n=count($rows[$j][$i]);
            echo "{";
                foreach($rows[$j][$i] as $arrayKey => $arrayValue) {
                    echo $arrayKey . ":" . $arrayValue;
                $n--;
                if ($n>0) {echo ",";}
            }
            echo "}]}'n";
            echo "'t]";
        }   
        echo"'n}";
    }
    /*This simply takes in a Date and converts it to a string that the google API recognizes as a Date*/
    function convertGoogleDate(DateTime $inDate) {
        $googleString='"Date(';
        $googleString=$googleString.date_format($inDate, 'Y').',';
        $googleString=$googleString.(date_format($inDate, 'm')-1).',';
        $googleString=$googleString.(date_format($inDate, 'd')*1).',';
        $googleString=$googleString.(date_format($inDate, 'H')*1).',';
        $googleString=$googleString.(date_format($inDate, 'i')*1).',';
        $googleString=$googleString.(date_format($inDate, 's')*1).')"';
        return $googleString;
    }
?>

对于Java和HTML上的其余实现，请检查上面发布的git存储库

我知道这是一个迟到的回复，但仍然是谷歌的热门，我被问了几次这个问题