Fetch Data from Oracle using OCI 8 and create Google Pie Chart

Question

Fetch Data from Oracle:

echo "<br/>Self User data<br/>";
$curs = oci_new_cursor($conn);
$varin1 = "111"; //employee_id
$varin2 = "FEB-2015"; //month_year
$varin3 = "1";
$stid = oci_parse($conn, "begin WEEKLY_EMPLOYEE_DETAILS(:varIn1,:varIn2,:cursbv); end;");
oci_bind_by_name($stid, ":varIn1", $varin1);
oci_bind_by_name($stid, ":varIn2", $varin2);
oci_bind_by_name($stid, ":cursbv", $curs, -1, OCI_B_CURSOR);
oci_execute($stid);
oci_execute($curs);  // Execute the REF CURSOR like a normal statement id

echo "<pre>";
while (($row = oci_fetch_array($curs, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    print_r($row) . "<br />\n";
}
oci_free_statement($stid);
oci_free_statement($curs);

Ouput of above code:

Self User data

Array
(
    [EMPLOYEEID] => 111
    [EMPLOYEENAME] => John Doe
    [TOTAL_HOURS] => 24
    [STATUS] => 1
    [WEEK_ID] => 2
)
Array
(
    [EMPLOYEEID] => 111
    [EMPLOYEENAME] => John Doe
    [TOTAL_HOURS] => 20
    [STATUS] => 2
    [WEEK_ID] => 3
)
Array
(
    [EMPLOYEEID] => 111
    [EMPLOYEENAME] => John Doe
    [TOTAL_HOURS] => 40
    [STATUS] => 2
    [WEEK_ID] => 4
)

Google Pie chart Code:

<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {

        var data = google.visualization.arrayToDataTable([
          ['Work', 'Hours per Day'],
      ['Week 1',     25],
      ['Week 2',      25],
      ['Week 3',  25],
      ['Week 4', 25]
        ]);

        var options = {
          title: 'My Daily Activities'
        };

        var chart = new google.visualization.PieChart(document.getElementById('piechart'));

        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="piechart" style="width: 900px; height: 500px;"></div>
  </body>
</html>

How can I show the data fetched in form of pie chart. If there are 5 weeks in a month it will display the pie chart slice likewise. Along with it, it should also display hours for that week which are coming in array.

piscator piscator · Accepted Answer · 2015-02-25T11:06:30

The pie chart looks great. You could pass the php variables to javascript and use them in the array.

var week1 = <?php echo json_encode($week1['TOTAL_HOURS']); ?>;
etc..

var data = google.visualization.arrayToDataTable([
      ['Work', 'Hours per Week'],
      ['Week 1',     week1],
      ['Week 2',      week2],
      ['Week 3',  week3],
      ['Week 4', week4]
    ]);

It would be cleaner to put these variables in an array, and do a check first how many weeks there are in the month. You could do the same for the daily hours by fetching the daily employee details from the oracle database (if they exist).

To read more about passing variables to javascript:

How to pass variables and data from PHP to JavaScript?

Fetch Data from Oracle using OCI 8 and create Google Pie Chart

1 Answers