PHP Tip: Binding variables in a SQL WHERE IN clause

Posted bySimon 3 Comments on PHP Tip: Binding variables in a SQL WHERE IN clause

Have you ever wondered if it is possible to bind an unknown amount of variables in a SQL WHERE IN clause, e.g.:

SELECT id, text FROM myTable WHERE id IN (:myId1, :myId2, myId3, ...)

Use a OCI collection object and wonder no more:

$keyList = array(100, 250, 350);
$stmt = oci_parse($cnn, "SELECT id, text FROM myTable WHERE id IN (SELECT column_value from table(:myIds))");
$coll = oci_new_collection($cnn, 'ODCIVARCHAR2LIST','SYS');
foreach ($keyList as $key) {
   $coll->append($key);
}
oci_bind_by_name($stmt, ':myIds', $coll, -1, OCI_B_NTY);
oci_execute($stmt);

while($row = oci_fetch_array($stmt, OCI_ASSOC)) {
    echo "{$row['ID']}, {$row['TEXT']}";
}

I found the above solution by ThinkJet on stackoverflow.

Join the Conversation

3 Comments

Your email address will not be published. Required fields are marked *

  1. What does oci_new_collection() do exactly and are the parameters (what are they, what do they mean, etc.)?

    Reply

    1. Unfortunately, this function is not documented very well. Quoting from the The Oracle + PHP Cookbook:

      Collections are PL/SQL structures that can be used in Oracle in the same manner that arrays can be used in PHP

      and from the PHP documentation:

      $cnn: An Oracle connection identifier
      ‘ODCIVARCHAR2LIST’: Should be a valid named type (uppercase).
      ‘SYS’: Should point to the scheme, where the named type was created.

      Reply
  2. Reply