PHP can not read SQL result statement from Oracle database

Question

I have this query:

$sql = "SELECT CPACK, ('[' || CPACK || '][' || REPLACE(UPPER(DESC_PACK),UPPER(' ".substr(str_replace($battlezone," ",""),0,-1)."'),'') || ']') CPACKITEM
        FROM p_product_indihome_valid
        where (1=1) and cpack in (select cpack from p_paket where JENIS = 'A' and valid = '1' and paket like '%~".$paket."~%' and 
                    id_rule in (select id_rule from p_paket_rule where (";
for($i=0;$i<count($param);$i++){
    $sql2 .= " or (MTARGET = '".$paramName[$i]."' and REGEXP_LIKE (REPLACE(mvalue,'&','dan'),'~".str_replace('&','dan',$param[$i])."~|0'))";
}
$sql .= ltrim($sql2,' or');
$sql .= ") group by id_rule having count(mtarget) = 3)) group by CPACK,DESC_PACK ORDER BY DESC_PACK";
$stid = oci_parse($this->_conn, $sql);
$r = oci_execute($stid);
print_r($sql);

It shows:

SELECT CPACK, ('[' || CPACK || '][' || REPLACE(UPPER(DESC_PACK),UPPER(' '),'') || ']') CPACKITEM
FROM p_product_indihome_valid
where (1=1) and cpack in (select cpack from p_paket where JENIS = 'A' and valid = '1' and paket like '%~1~%' and 
id_rule in (select id_rule from p_paket_rule where ((MTARGET = 'SUBLAYANAN' and REGEXP_LIKE (REPLACE(mvalue,'&','dan'),'~DCS - UCS II~|0')) or (MTARGET = 'LAYANAN' and REGEXP_LIKE (REPLACE(mvalue,'&','dan'),'~DCS~|0')) or (MTARGET = 'SOCIO' and REGEXP_LIKE (REPLACE(mvalue,'&','dan'),'~Lain-lain~|0'))) group by id_rule having count(mtarget) = 3)) group by CPACK,DESC_PACK
ORDER BY DESC_PACK;

I run the query in SQL Developer, and it shows this result:

cpack  | citem
USEE84  [USEE84][ADDITIONALSETUPBOXUSEETV]
C15062  [C15062][CS-INDIHOMEJOINPROMOVISACREDITCARDINTERNET]
C15061  [C15061][CS-INDIHOMEJOINPROMOVISACREDITCARDPHONE]
VOICE   [VOICE][FEATUREVOICE]

But when I show the result of the query with print_r($stid);, it shows null values. I don't know where the mistake is.

Clay · Accepted Answer · 2016-01-08 02:17:54Z

1

Your code is not currently fetching the result set.

Here is how to fetch result set using oci_fetch_array and get the desired result you're looking for -- add this to the bottom of your code:

$results = [];
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    $results[] = $row;
}
print_r($results);

oci_fetch_array — Returns the next row from a query as an associative or numeric array

Usage: array oci_fetch_array ( resource $statement [, int $mode ] )

Documentation and other examples below:

http://php.net/oci_fetch_array

edited Jan 8, 2016 at 2:17

answered Jan 8, 2016 at 2:10

Clay

4,7703 gold badges37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP can not read SQL result statement from Oracle database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related