Bug #65421 [Opn->Nab]: 'field=NULL' should be 'field IS NULL'

Edit report at https://bugs.php.net/bug.php?id=65421&edit=1 ID: 65421 Updated by: ab@php.net Reported by: schwalenberg1013 at gmail dot com Summary: 'field=NULL' should be 'field IS NULL' -Status: Open +Status: Not a bug Type: Bug Package: PDO related Operating System: Linux PHP Version: 5.5.1 Block user comment: N Private report: N New Comment: Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.php.net/manual/ and the instructions on how to report a bug at http://bugs.php.net/how-to-report.php This won't work as you describe, a binding method will always affect only the defined container, so the question mark or :someval. Say if you have x=:y, bindParam will never touch the x= part. But what you can do is build the statement string dynamically and use the third bindParam argument on condition. Also you could try to explicitly cast string if that's ok with your database design. Previous Comments: ------------------------------------------------------------------------ [2013-08-08 13:26:34] schwalenberg1013 at gmail dot com Description: ------------ When using PDO bindParam when it receives a NULL value the ultimate result that it sends to MySql is field=NULL instead of field IS NULL. Test script: --------------- $editStmt = $db->prepare("UPDATE employee SET startDate=:startDate,department=:department,jobTitle=:jobTitle WHERE name=:emplName AND startDate=:oldStartDate AND department=:oldDepartment AND jobTitle=:oldJobTitle LIMIT 1"); $editStmt->bindParam('startDate',$startDate); $editStmt->bindParam('department',$department); $editStmt->bindParam('jobTitle',$jobTitle); $editStmt->bindParam('emplName',$emplName); $editStmt->bindParam('oldStartDate',$oldStartDate); $editStmt->bindParam('oldDepartment',$oldDepartment); $editStmt->bindParam('oldJobTitle',$oldJobTitle); // catchException($editStmt,$db); $editStmt->execute(); $editResult = $editStmt->fetchAll(PDO::FETCH_ASSOC); /* If any one of these variables happens to be NULL then the whole statement fails because it will output, for example, 'AND department=NULL' instead of 'AND department IS NULL' */ Expected result: ---------------- MySql will not except field=NULL properly it should be field IS NULL Actual result: -------------- I can do this in MySql however when using PDO I have to create an entirely different statement myself for the exceptions of NULL values which becomes very tedious a problem I never had using the depreciated mysql functions or when using mysqli. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=65421&edit=1