1

here is a parameterized query:

SELECT
field1,field2,etc1,etc2
FROM my_table WHERE
conditionA=:conditionA AND
conditionB=:conditionB

and my parameters:

array( 'conditionA'=> 5, 'conditionB'=>NULL )

Now in this theoretical case, conditionB could be a value OR it could be null. But in SQL, you do not say conditionB=NULL - you write it "conditionB IS NULL".

How am I supposed to do this? First of all, if I pass 'conditionB'=>NULL in my params, then I'm assuming that node is simply not passed. Second, am I going to need to rewrite the query between "=" and "IS" based on the type value of conditionB, or can PDO do this for me as well?

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Samuel Fullman
  • 1,262
  • 1
  • 15
  • 20

1 Answers1

0

Mysql handles NULL differently and you cannot use common comparison operators and even STRCMP() doesn't do what you need.

When I needed to implement a != b on strings I've end up using (a IS NULL XOR b IS NULL) OR (a != b)...

So I think you can either implement 2 different queries based on condition or extend your condition to 

WHERE conditionA=:conditionA
AND (
    (conditionB IS NULL AND :conditionB IS NULL) OR (conditionB=:conditionB)
)
Community
  • 1
  • 1
Vyktor
  • 20,559
  • 6
  • 64
  • 96
  • that's the best answer, but I must say I see this as a shortcoming of PDO to make things easier. This is a fairly common occurrence I think – Samuel Fullman Jul 08 '14 at 11:30
  • @SamuelFullman I think this is thing of MySQL... You may write boolean function yourself to compare items correctly without having to write all the xors, ors, ands, isnulls and stuff... But I think indexes won't work on that ... MySQL sucks with nulls :-/ – Vyktor Jul 08 '14 at 12:01
  • The entire `( (conditionB IS NULL AND :conditionB IS NULL) OR (conditionB=:conditionB) )` segment can be shortened down to `conditionB <=> :conditionB`. – hjpotter92 Jul 08 '14 at 16:42