SQL tip: How to bind NULL with PHP and Oracle

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);

Leave a Reply

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