I am unable to set a nullable field with a default value of null to null using mysql pdo. I can do it using straight sql.
I have tried: (mostly from this question How do I insert NULL values using PDO?)
bindValue(':param', null, PDO::PARAM_INT);
bindValue(':param', null, PDO::PARAM_NULL);
bindValue(':param', 'NULL', PDO::PARAM_INT);
bindValue(':param', 'NULL', PDO::PARAM_NULL);
bindParamcounterparts of 5 and 6 with a variable that held the value of the binding.
Everything from PDO results in the value being set to 0.
MYSQL SERVER VERSION:
Screenshot of column info
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
The following works for me:
<?php $pdo = new PDO("mysql:host=localhost;dbname=test", "root", "pass"); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $stmt = $pdo->prepare("INSERT INTO `null_test` (`can_be_null`) VALUES (:null)"); $stmt->bindValue(":null", null, PDO::PARAM_NULL); $stmt->execute();
Pass in PHP’s
null, with type of
PDO::PARAM_NULL. Also, make sure your prepare emulation is set to false. That might help.
I will strong recommend to first assign all parameters to variables and then pass those variables to the
You can assign by passing NULL to those variables and it will work fine.
$myVar = NULL; $conn->bindParam(':param1' , $myVar , PDO::PARAM_STR);