Undefined Index with Oracle connection

Question

Undefined Index with Oracle connection

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

For some reason, my code is resulting in error

Notice: Undefined Index IRRAD

when attempting to query the Oracle database. The PHP code I'm using:

set_time_limit( 600 );
date_default_timezone_set('America/Sao_Paulo');    
$query2 = 'SELECT \'0 - 300\' as intervalo, ROUND(SUM('.$irradInc.')/3600000, 2) as irrad FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 0 AND 300 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')
            UNION ALL
            SELECT \'300 - 700\', ROUND(SUM('.$irradInc.')/3600000, 2) FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 300 AND 700 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')
            UNION ALL
            SELECT \'700 - 1000\', ROUND(SUM('.$irradInc.')/3600000, 2) FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 700 AND 1000 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')
            UNION ALL
            SELECT \'1000 - 1200\', ROUND(SUM('.$irradInc.')/3600000, 2) FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 1000 AND 1200 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')
            UNION ALL
            SELECT \'> 1200\', ROUND(SUM('.$irradInc.')/3600000, 2) FROM '.$tabela.' WHERE '.$irradInc.' > 1200 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')';
    //echo $query2;
    $conn = oci_connect('****', '***', '****');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }
    $stid = oci_parse($conn, $query2);
    oci_execute($stid);
    $array = array();
    while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) {
        array_push(
                $array,
                array(
                'value' => $row['irrad'],
                'label' => $row['INTERVALO']
                 )
             );


        unset($row);  
    }

The query I'm running is:

SELECT \'0 - 300\' as intervalo, ROUND(SUM('.$irradInc.')/3600000, 2) as irrad FROM '.$tabela.' WHERE '.$irradInc.' BETWEEN 0 AND 300 AND TS_SAMPLETM BETWEEN TO_DATE(\''.$data1.'\', \'DD/MM/YY\') AND TO_DATE(\''.($data2->format('d/m/y')).'\', \'DD/MM/YY\')

Using the query directly in Oracle's SQL Developer, the result comes correctly, with the column names as it is in the code in PHP. The index Interval works correctly, however, the index IRRAD it accuses the error quoted.

Is there a rule to follow to read the headers, or is there something wrong with the code?

php oracle

asked by anonymous 22.10.2015 / 03:27

2 answers

How to set search box in the menu? Do not allow onBackPressed to run

score 1 · Answer 1

The problem was that when the query returned NULL value, the $row variable did not receive the index of this column. So when I was reading the $row['IRRAD'] it was resulting in Undefined Index .

To fix, I've added a checkline:

while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) {
    //print_r($row);
    if(!oci_field_is_null( $stid , 'IRRAD' )){
        array_push(
            $array,
            array(
            'value' => round(($row['IRRAD']/$irradTotal)*100, 2),
            'label' => $row['INTERVALO']
             )
         );
    }   


    unset($row);  
}

The command oci_field_is_null returns true if the field under analysis is NULL and false if the field contains any value.

score 1 · Answer 2

In addition to the solution already presented, there are also these:

Force the return of columns with NULL values reporting this in $ mode of any oci_fetch _ *

oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS+OCI_RETURN_NULLS)

Avoid returning null to any field in the query with the command COALESCE

$query2 = 'SELECT \'0 - 300\' as intervalo, COALESCE(ROUND(SUM('.$irradInc.')/3600000, 2), 0) as irrad FROM(...)