There are two basic approaches to this problem: 1)dynamically generating the SQL statement, 2) handle NULL with expressions in a static SQL statement
The dynamic approach, if the query is being executed from application...
we start with a static string, what the query is going to look like everytime:
$sql = 'SELECT t.id
FROM mytable t
WHERE 1=1';
and then decide if we're going to append another condition to the WHERE clause
if( $p1 !== '' ) {
$sql .= ' AND t.propertyA = :p1';
}
if( $p2 !== '' ) {
$sql .= ' AND t.propertyB = :p2';
}
// prepare the SQL statement
$sth=$pdo->prepare($sql);
// conditionally bind values to the placeholders
if( $p1 !== '' ) {
$sth->bindValue(':p1',$p1);
}
if( $p2 !== '' ) {
$sth->bindValue(':p2',$p2);
}
The downside of dynamic SQL is that with more complex problems, it can get overly tricky to properly construct the SQL statement. With just conditions in the WHERE clause, it's a viable approach.
Another downside is the wide variety of SQL statements we can end up producing, and ensuring that each variation will have a suitable execution plan gets complicated. With only two optional conditions in the WHERE clause, we've got a very manageable total of 4 variations...
Oh, the reason for including the condition 1=1 in the WHERE clause doesn't impact the statement; the optimizer is smart enough to figure out that its true for every possible row, so that condition gets tossed. What that buys us is when we're appending to the WHERE clause, we eliminate the need to check "is this the first condition in the WHERE clause?" so we know whether to append WHERE or AND to the statement.
The second approach is to use static with SQL, using some expressions.
As an example, assuming propertyA and propertB are character type columns:
$sql = "SELECT t.id
FROM mytable t
WHERE t.propertyA <=> IFNULL(NULLIF(:p1,''),t.propertyA)
AND t.propertyB <=> IFNULL(NULLIF(:p2,''),t.propertyB)";
$sth = $pdo->prepare($sql);
$sth->bindValue(':p1',$p1);
$sth->bindValue(':p2',$p2);
If we supply a non-zero length string for :p1, then the NULLIF function returns :p1, and the IFNULL function returns :p1. It will be as if we had just written:
t.propertyA <=> :p1
If we supply a zero length string for $p1 (for placeholder :p1) then the SQL NULLIF function will return a NULL. And in turn, the IFNULL function will return t.propertyA, so the statement will be comparing propertyA to itself, so the net result will be as we had written
AND t.propertyA <=> t.propertyA
or just
AND 1=1
(The difference is that the optimizer won't discard our condition, since the optimizer doesn't know what value we're going to supply for :p1 when the execution plan is prepared.
NOTE: The <=> spaceship operator is a NULL-safe comparsion. It's guaranteed to return TRUE or FALSE (and not return NULL), unlike the standard equality comparison (=) which returns NULL when either (or both) of the values being compared are NULL.
This:
foo <=> bar
is essentially shorthand for the equivalent:
foo = bar OR ( foo IS NULL AND bar IS NULL )
If we are guaranteed that propertyA will never be NULL (for example, by an explicit NOT NULL constraint in the table definition), we can forgo the spaceship operator and just use a plain equality comparison.
The downside of this approach is a less intuitive SQL statement; the uninitiated might be scratching their heads over it. So we are going to want to leave a comment in the code, explaining that the matching condition is condition, if :p1 is empty string, there's no comparison to :p1.
We could use different syntax to achieve the same result, for example, using the more portable ANSI-standards compliant COALESCE function in place of IFNULL, and a CASE expression in place of NULLIF. (That would require us to supply $p1 to an extra placeholder, writing it like we did, we only have to supply $p1 one time.)
But those are the two basic patterns. Pick your poison.
