0

I have the following prepared statement. All is good...

$stmt=$conn->prepare('UPDATE t1 SET c1=? WHERE c2=? AND c3!=?');
$stmt->execute(array(1,2,3));

But now I wish to use the same prepared statement, but include NULL values in the WHERE clause.

$stmt->execute(array(1,NULL,NULL));

Will this resolve to UPDATE t1 SET c1=1 WHERE c2=NULL AND c3!=NULL? Isn't the proper query UPDATE t1 SET c1=1 WHERE c2 IS NULL AND c3 IS NOT NULL? How can I have a single prepared statement which allows both NULL and not NULL values in the WHERE clause?

user1032531
  • 24,767
  • 68
  • 217
  • 387
  • possible duplicate of [Execute PDO with an array containing null values](http://stackoverflow.com/questions/17723839/execute-pdo-with-an-array-containing-null-values) – Rahil Wazir Jun 25 '14 at 19:30
  • @RahilWazir Referenced "possible duplicate" is for inserting NULL. INSERTs or SETs do not cause a problem, only the WHERE clause. – user1032531 Jun 25 '14 at 19:33
  • 1
    To the individual who downvoted. Please give reason why. – user1032531 Jun 25 '14 at 19:39

1 Answers1

1

You asked if a SQL query containing 

 WHERE col = ?

will automatically be converted to 

 WHERE col IS NULL

by binding a NULL value to the parameter.

The answer is no. If a column has a NULL value, it can never compare equal to anything.

You need a different prepared query for that.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks Ollie. Do I understand you in that I cannot use the same prepared statement when having NULL and non-NULL in the WHERE clause? – user1032531 Jun 25 '14 at 19:42
  • Correct. If you think NULL might come into play, you need to handle that in your query, which is why nullable columns shouldn't be overused. – Andrew Jun 25 '14 at 19:49
  • Thanks again Ollie. Well, that is a bummer! – user1032531 Jun 25 '14 at 20:44