OCI keeps retrieving only 1 record when I see 3 in the database...

Wolf_22

The SQL I'm trying to use in my PHP script is as follows:

SELECT my_id
FROM my_table
WHERE my_id IN ('id1', 'id2', 'id3');

As you can see, it's nothing too complex.

Here's my entire PHP with the above SQL in it:

$search_string = array('id1', 'id2', 'id3');

$placeholders = array();
$bind = array();

$conn = oci_connect('admin', '1234', 'testdb.mydomain.com/TEST');

foreach($search_string as $index => $filter) {
        $placeholders[] = ':filter'.$index;
        $bind[':filter'.$index] = $filter;
}


$stid = '
    SELECT              my_id
    FROM                my_table
    WHERE               my_id IN ('.implode(',',$placeholders).')
';


$stid = oci_parse($conn,$stid);

foreach($bind as $k=>$v){
    oci_bind_by_name($stid, $k, $v);
}

$r = oci_execute($stid);

oci_fetch_all($stid, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);

print '<pre>';
var_dump($res);
print '</pre>';

oci_free_statement($stid);
oci_close($conn)

Again, nothing too complex. My problem is that the above PHP retrieves only 1 record when 3 are apparent in the database using the *EXACT* same SQL.

What might be causing this issue? Is there maybe something associated with Oracle configurations that prevent the entire result from being obtained in the PHP execution?

Any insights would be appreciated. Thanks in advance.

Wolf_22

This can be marked as solved for all intents and purposes... I went instead with PDO. I think the OCI stuff is a little buggy... (Using PDO, I can make my query and execute the IN logic without issues.)

Moderators (or whoever): please mark this as solved if you want.

Wolf_22

This can be marked as solved for all intents and purposes... I went instead with PDO. I think the OCI stuff is a little buggy... (Using PDO, I can make my query and execute the IN logic without issues.)

Moderators (or whoever): please mark this as solved if you want.

Christopher Jones-Oracle

OCI8 is better than PDO_OCI in many respects.

At a wild guess, I'd say you hadn't committed your data when you were trying OCI8.

Christopher Jones-Oracle

At a more detailed look, check out example #3 in PHP: oci_bind_by_name - Manual :

foreach ($ba as $key => $val) {

    // oci_bind_by_name($stid, $key, $val) does not work
    // because it binds each placeholder to the same location: $val
    // instead use the actual location of the data: $ba[$key]
    oci_bind_by_name($stid, $key, $ba[$key]);
}

Also see The Underground PHP and Oracle Manual | Homepage