CODEWITHSUNDEEP
mysqlsql
hsnsd
hsnsd

Reputation: 1823

sql query | handling multiple where conditions with a potential null value

Assume the following table:

╔═══╦══════════════╦═════════════╗
║   ║Property A    ║Property B   ║
╠═══╬══════════════╬═════════════╣
║ 1 ║ slow         ║low          ║
║ 2 ║ fast         ║high         ║
╚═══╩══════════════╩═════════════╝

User has the option to filter result using either both properties or just one. The input is stored in variables $p1, $p2

If both the variables get a value from user I can easily query them like this:

select * from table where (propertyA=$p1 and propertyB=$p2)

However if one of them gets no value from user, how do I query? Since the line in the above query propertyA=$p1 would become false since $p1 could be null, however the query result should return based on the value of $p2.

For example for the input (null,low) the query should return the first record, but with my query, it will not.

This example is just for two properties, I have multiple properties in my table so implementing multiple if conditions would be a hassle.

I was wondering if there is any concise way to go about this.

Thank you!

Upvotes: 0

Views: 3842

Answers (3)

spencer7593
spencer7593

Reputation: 108460

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.

Upvotes: 2

mindaJalaj
mindaJalaj

Reputation: 448

Well query is a string afterall, you need to first format the string, based on conditions then when final string is ready, execute the query.

function chechForProperty($variable, $colName)
{
      if(isset($variable) || $variable != '')
      {
           return $colName."=".$variable." and ";
      } 
      return "";
}


$query = "select * from table where (";
$query .= checkForProperty($p1,'propertyA');
$query .= checkForProperty($p2,'propertyB');
$query .= checkForProperty($p3,'propertyC');
$query = substr($query,0,-5);
$query .= ");";

// now execute the query, I am just printing
print_r($query);

Hope this helps, thanks

Upvotes: 0

xvan
xvan

Reputation: 4855

If both properties are strings, you may use wildcard '%'

select * from table where (propertyA like $p1 and propertyB like $p2)

Upvotes: 1

Related Questions