OCI / Oracle and IN clause using PHP array values as the IN values?

[Wolf_22]

Do any of you have any PHP examples of using OCI to execute an Oracle query consisting of a simple SELECT and IN clause where the IN values are based on a PHP array’s values? I’m in a specific situation where I need this and for the life of me, nobody seems to have anything like this in a clear, understandable form that actually works…

My case is actually somewhat simple: I have a basic SELECT query that uses a PHP array’s values as filter values for a table’s column. I thought I could bind the values after imploding them into a single comma-delimited string that I would feed into the query’s IN clause but apparently Oracle is a bit more picky because I tried this and it just wouldn’t work. (With no errors, too.)

Here’s an example of what I’m trying to do:

$stid = oci_parse($conn, '
    SELECT    mycolumn, 
    FROM      mytable
    WHERE     whatever IN (:filters)
');

Whereby I then bind :filters by using the following line:

oci_bind_by_name($stid, ':filters', $myphparray);

Unfortunately, this just doesn’t work. Oddly enough, the query (and the values being used within the IN logic) works fine in SQL Developer.

I’m completely stumped and any insights would be appreciated.

[oddz]

Each item in the array needs to be a separate parameter.

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

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

$sql = 'SELECT * FROM table WHERE whatever IN ('.implode(',',$placeholders).')';

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

foreach($bind as $placeholder=>$value) {
  oci_bind_by_name($stid, $placeholder, $value);
}

[Wolf_22]

Thanks, oddz. That’s getting me closer: I’m actually seeing results now! The only issue is that it’s returning one record when there should be 3 coming back but I have no clue why because it looks like the placeholders are being embedded where they need to be. I’m using oci_fetch_all as follows:

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

In SQL Developer, the results come back as expected (with 3 records).

Thoughts?

[oddz]

Post the portion of your script that queries Oracle and displays the data.

[Wolf_22]

This is everything:

    $search_string = array('1234A', '1234B', '1234C');

    $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    myfield, 
        FROM      mytable 
        WHERE     my_id IN ('.implode(',',$placeholders).')
    ';

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

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

    oci_execute($stid);

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

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

[oddz]

I’ve never used oci but looking at the docs what you have looks correct to me. Are you absolutely positive there is more than a single filter?

[Wolf_22]

To verify, I did the following:

print '<pre>';
var_dump(implode(',',$placeholders));
print '</pre>';

…which printed “:filter0,:filter1,:filter2”. So yes, it looks like multiple filters are being embedded into the SQL (or $stid variable). It’s the strangest thing…

[oddz]

I would resort to smart debugging.

[Wolf_22]

I’ll be the first to admit that I’m an idiot but could you shine some light on exactly what you mean by that? I’m assuming you’re referring to debugging on the database side? I’m not sure where logs would be for that layer nor do I know if I would even have access to them.

[Wolf_22]

oddz (and whoever else might see this), I’ve decided to use PDO instead of OCI. With it, I can execute the IN logic I need without issues. I guess OCI is buggy or something…

[ahundiak]

Good move on switching to PDO.

Consider stepping up one more notch to Doctrine 2’s database access layer (DBAL) which runs on top of PDO. http://doctrine-orm.readthedocs.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html

An IN statement reduces to:

    $ids = [1,2,3];
    $sql = 'SELECT * from projectPersonRoles WHERE projectPersonId IN (?)';
    $stmt = $this->conn->executeQuery($sql,[$ids],[Connection::PARAM_INT_ARRAY]);

As a bonus you also get their query builder for when simple string manipulations are cumbersome.
http://doctrine-orm.readthedocs.org/projects/doctrine-dbal/en/latest/reference/query-builder.html