php Question posted in
Official manual can be found here.

PHP Problems using NULL with bindvalue

I am almost certain I’ve just been looking at the same code too long and I’m not seeing it.

I’ve also read, over and over, again the simiilar question :
[https://stackoverflow.com/questions/11692773/php-mysql-pdo-refuses-to-set-null-value][1]

This works:

    $updateqry = $db->prepare(
        'UPDATE Objects SET InventorGroup=:NEWGROUP WHERE ' .
        'Inventor1=:INVENTOR1 AND ' .   
        'Inventor2=:INVENTOR2 AND ' .
        'Inventor3 IS NULL'
    );
    $updateqry->bindValue(:NEWGROUP, $iNewGroupVar, PDO::PARAM_INT);
    $updateqry->bindValue(:INVENTOR1, $iInventor1Var, PDO::PARAM_INT);
    $updateqry->bindValue(:INVENTOR2, $iInventor2Var, PDO::PARAM_INT);
    $updateqry->execute();

But this does not:

$updateqry = $db->prepare(
    'UPDATE Objects SET InventorGroup=:NEWGROUP WHERE ' .
        'Inventor1=:INVENTOR1 AND ' .   
        'Inventor2=:INVENTOR2 AND ' .
        'Inventor3=:INVENTOR3'
    );
    $updateqry->bindValue(:NEWGROUP, $iNewGroupVar, PDO::PARAM_INT);
    $updateqry->bindValue(:INVENTOR1, $iInventor1Var, PDO::PARAM_INT);
    $updateqry->bindValue(:INVENTOR2, $iInventor2Var, PDO::PARAM_INT);
    $updateqry->bindValue(:INVENTOR3, $iInventor3Var, PDO::PARAM_NULL);
    $updateqry->execute();

Even if I change my last bindValue this also does not work:

$updateqry->bindValue(:INVENTOR3, NULL, PDO_::PARAM_NULL);

As a inelegant workaraound, cause I have to get this to work to advance in the rest of my code, I’m not checking for Null in my 3rd parameter and executing an entirely different query for the 2 cases rather than elegantly using/passing the variable value.

What am I missing?

Tags:

2

Answers


  1. 0

    I think you have a SQL problem and not a PHP problem.

    As @shingo already mentioned comparing something with NULL won’t work. In SQL "NULL" means "unknown" – it’s not a null value like in most programming languages. This makes logical and comparison operations a little bit tricky.

    This blog post gives a good overview of what to expect when working with NULL and logical and comparison operations in SQL.

    The WHERE part of your second statement boils down to

    Inventor1=:INVENTOR1 AND Inventor2=:INVENTOR2 AND Inventor3=NULL

    what becomes

    Inventor1=:INVENTOR1 AND Inventor2=:INVENTOR2 AND NULL

    what becomes NULL or FALSE depending on the results of the remaining comparisons. Thus your WHERE clause will never return TRUE.

    If Inventor3 is the only nullable field the following WHERE clause should do the trick:

    Inventor1=:INVENTOR1 AND
Inventor2=:INVENTOR2 AND
(isNull(Inventor3) OR Inventor3=:INVENTOR3)
    Login or Signup to reply.
  2. 0

    As already mentioned above, you cannot use the = sign when comparing with NULL. Instead, you must use IS NULL. So one possible solutions is to conditionally adjust the query:

    'Inventor3 ' . ($iInventor3Var === null ? 'IS NULL' : '= :iInventor3Var' . ' '

    and then bind the value also conditionally

    if ($iInventor3Var !== null) {
   $stmt->bindValue(':iInventor3Var ', $iInventor3Var );
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search