Выбрать данные из оракула и нарисовать диаграмму, используя диаграммы Google?

См. Мой блог: http://howdyharish.wordpress.com/2014/08/11/create-google-charts-using-php-and-oracle-database/#more-3

Вот код.

Index.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="/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 будет понимать (Документация Google здесь). В принципе, вам нужно иметь какую-то функцию в php, которая преобразует результаты OCI в строку JSON, читаемую Google.

Я опубликовал решение, которое очень хорошо работает в моей настройке, другие пользователи могут извлечь из этого выгоду. Вы можете увидеть мой код на GitHub:

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

PHP-код выглядит следующим образом:

<?php
    /*
     * @author Ernesto Monroy <[email protected]>
     * @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 repo, опубликованную выше

Я знаю его поздний ответ, но все же топ-хит в Google, и меня пару раз задавали вопрос