How can I add data from Oracle columns into a PHP array?

Question

I have PHP code that's reading data from a CSV file for population into HTML tables. The CSV file being read looks like this (sample; not entire file):

A,2020-06-01,1,15,0,0,99.5,problem,
B,2020-06-01,0,0,0,0,100,,
A,2020-06-02,0,0,0,0,100,,
B,2020-06-02,0,0,0,0,100,,
A,2020-06-03,0,0,0,0,100,,
B,2020-06-03,0,0,1,30,99.2,problem,
A,2020-06-04,0,0,0,0,100,,
B,2020-06-04,0,0,0,0,100,,
A,2020-06-05,0,0,0,0,100,,
B,2020-06-05,0,0,1,10,99.7,problem,
A,2020-06-06,0,0,0,0,100,,
B,2020-06-06,0,0,0,0,100,,
A,2020-06-07,0,0,0,0,100,,
B,2020-06-07,0,0,0,0,100,,
A,2020-06-08,1,60,0,0,98.9,problem,
B,2020-06-08,0,0,0,0,100,,

Services "A" and "B" are recording values on a daily basis.

The code being used to process the CSV file looks like this:

<?php

$filename = "/var/tmp/dataset.csv";

if (($h = fopen($filename, "r")) !== FALSE) 
{
    $A_totals = array();
    $B_totals = array();
    $cols = array();
    $A_index = 1;
    $B_index = 1;
    $cols = array("service_name", "date", "nbr_out", "hrs_out", "nbr_dgrd", "hrs_dgrd", "pct_avail", "comments");

    // Loop through rows in CSV file
    while (($row = fgetcsv($h, 10000, ",")) !== FALSE) {
    // Assign variables and create summary arrays for each service
        $recordYear = strpos($row[1], $searchYear.'-');
        $recordMonth = strpos($row[1], '-'.$searchMonth.'-');
        
            if ($recordYear !== FALSE && $recordMonth !== FALSE) {
        
                if ($row[0] == 'A') {
                    $A_totals['item_' . $A_index++] = array_combine($cols, $row);
                }
                if ($row[0] == 'B') {
                    $B_totals['item_' . $B_index++] = array_combine($cols, $row);
                } 

            }
    
    }

    $A_nbr_out_totals = array_sum(array_column($A_totals, 'nbr_out'));
    $A_hrs_out_totals = array_sum(array_column($A_totals, 'hrs_out'));
    $A_nbr_dgrd_totals = array_sum(array_column($A_totals, 'nbr_dgrd'));    
    $A_hrs_dgrd_totals = array_sum(array_column($A_totals, 'hrs_dgrd'));
    $A_pct_avail_totals = array_sum(array_column($A_totals, 'pct_avail'));
    $A_pct_avail_total_count = count(array_column($A_totals, 'pct_avail'));
    $A_pct_avail_avg = round(( $A_pct_avail_totals / $A_pct_avail_total_count ), 4 );
    
    $B_nbr_out_totals = array_sum(array_column($B_totals, 'nbr_out'));
    $B_hrs_out_totals = array_sum(array_column($B_totals, 'hrs_out'));
    $B_nbr_dgrd_totals = array_sum(array_column($B_totals, 'nbr_dgrd'));    
    $B_hrs_dgrd_totals = array_sum(array_column($B_totals, 'hrs_dgrd'));
    $B_pct_avail_totals = array_sum(array_column($B_totals, 'pct_avail'));
    $B_pct_avail_total_count = count(array_column($B_totals, 'pct_avail'));
    $B_pct_avail_avg = round(( $B_pct_avail_totals / $B_pct_avail_total_count ), 4 );
}

The code reads the CSV file and comes up with totals by service for each numeric "column" in the CSV file for a given month/year. These totals are then passed along to the HTML table for display by service.

I've exported the CSV data into an Oracle table in a "like for like" fashion. Column names used in the table are similar to the names used for the CSV file, with the exception that a "IA_" prefix is added to the beginning of each Oracle table column.

My challenge: Now that I'm using an Oracle table to reference the data, I'm using OCI8 as needed in the PHP code. If at all possible, I'd like to keep the PHP array approach intact (as I know it works). The OCI8 code would read each row of the Oracle table and add it to the array for the other array commands (array_combine/array_sum/array_map) to work against.

In attempting this, I've revised my code to look like this:

<?php

    $A_totals = array();
    $B_totals = array();
    $cols = array();
    $A_index = 1;
    $B_index = 1;
    $cols = array("service_name", "date", "nbr_out", "hrs_out", "nbr_dgrd", "hrs_dgrd", "pct_avail", "comments");

$sql1 = "SELECT IA_SERVICE, TO_CHAR(IA_DATE, 'MM/DD/YYYY HH12:MI:SS AM') AS IA_DATE, IA_NBR_OUT, IA_HRS_OUT, IA_NBR_DGRD, IA_HRS_DGRD, IA_PCT_AVAIL, IA_COMMENTS FROM i_avail WHERE TO_CHAR(IA_DATE, 'Mon')='$searchMonth' AND TO_CHAR(IA_DATE, 'YYYY')='$searchYear'";

$stid1 = oci_parse($connect, $sql1);

$r = oci_execute($stid1);

    // Loop through rows in Oracle table
    while (($row = oci_fetch_array($stid1, OCI_BOTH+OCI_RETURN_LOBS))!= false) {
    // Assign variables and create summary arrays for each service
        
        if ($row['IA_SERVICE'] == 'A') {
        $A_totals['item_' . $A_index++] = array_combine($cols, $row);
        }
        if ($row['IA_SERVICE'] == 'B') {
        $B_totals['item_' . $B_index++] = array_combine($cols, $row);
        } 

    }
    

    $A_nbr_out_totals = array_sum(array_column($A_totals, 'nbr_out'));
    $A_hrs_out_totals = array_sum(array_column($A_totals, 'hrs_out'));
    $A_nbr_dgrd_totals = array_sum(array_column($A_totals, 'nbr_dgrd'));    
    $A_hrs_dgrd_totals = array_sum(array_column($A_totals, 'hrs_dgrd'));
    $A_pct_avail_totals = array_sum(array_column($A_totals, 'pct_avail'));
    $A_pct_avail_total_count = count(array_column($A_totals, 'pct_avail'));
    $A_pct_avail_avg = round(( $A_pct_avail_totals / $A_pct_avail_total_count ), 4 );
    
    $B_nbr_out_totals = array_sum(array_column($B_totals, 'nbr_out'));
    $B_hrs_out_totals = array_sum(array_column($B_totals, 'hrs_out'));
    $B_nbr_dgrd_totals = array_sum(array_column($B_totals, 'nbr_dgrd'));    
    $B_hrs_dgrd_totals = array_sum(array_column($B_totals, 'hrs_dgrd'));
    $B_pct_avail_totals = array_sum(array_column($B_totals, 'pct_avail'));
    $B_pct_avail_total_count = count(array_column($B_totals, 'pct_avail'));
    $B_pct_avail_avg = round(( $B_pct_avail_totals / $B_pct_avail_total_count ), 4 );

My results from this attempt reflect zeroes for each value defined, which is not expected.

Since the data is now in Oracle, I could probably add extra SQL statements for each variable that leverage COUNT and AVG functions...but that would be a LOT of statements. If additional services get added in the future, the number of SQL statements would increase accordingly. I'd like to avoid that if at all possible...thus my hope that I can leverage the code that's already in place.

Is this possible? If not, is there another approach I could try that wouldn't require a multitude of SQL statements?

Answer 1

Found the problem with my OCI8 revised code. Error "PHP Warning: array_combine(): Both parameters should have an equal number of elements" was being reported in /var/log/httpd/ssl_error_log. After adding some debugging to the page, I noted that while my $cols array was defined to use seven elements, the $row returned by Oracle had 14 elements. I adjusted my $cols array to also use 14 elements, and the code is now working correctly.