PHP Tip: Binding variables in a SQL WHERE IN clause

3 Replies

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.

SQL tip: How to bind NULL with PHP and Oracle

Reply

Neat little trick to bind a null value without using an if clause.

How often have you done something like this when a variable might be null:

$sql = "SELECT * FROM someTable WHERE col1 = :firstVar";
if (isset($secondVar)) {
   $sql.= " AND col2 = :secondVar";
}
...
oci_bind_by_name($stmt, ':firstVar', $firstVar);
if (isset($secondVar)) {
   oci_bind_by_name($stmt, ':secondVar', $secondVar);
}

Then checkout this, which is quite a bit shorter and a lot easier to read:

$sql = "SELECT * FROM someTable WHERE col1 = :firstVar
   AND (col2 = :secondVar OR :secondVar IS NULL)";
...
oci_bind_by_name($stmt, ':firstVar', $firstVar);
oci_bind_by_name($stmt, ':secondVar', $secondVar);