I have a variable on php and it sometimes can be NULL and I want to insert this variable to my db. But problem is, PHP's null value does not insert to db as null value.
My column on db allows null values.
if($variable != true){ $variable = null; } //insert my null value to db $insert = $db->prepare("insert into mytable set mycolumn = $variable" ); $insert->execute(); //mycolumn is an integer column which is DEFAULT null //above query fails. Why? That is because PHP null is converted into the empty string "" when you create the query string.
$variable = null; $insert = "insert into mytable set mycolumn = $variable" ; echo $insert; Will produce:
insert into mytable set mycolumn = To fix your query you would need to check if the PHP variable is null and change it to string NULL. (Also now mentioned in the comment of @MarkB.)
if ($variable == null){ $variable = "NULL"; } This will produce:
"insert into mytable set mycolumn = NULL" Note that NULL has no " around it because it is now concatenated to the other string.
*(note: insert into tablename set .. is not correct, you either insert data or you update tablename set data.)
.. SET column = null instead of .. SET column= "null" on my mysql editor?
$insert = "insert into mytable set mycolumn = $variable" ; with $variable set to "null" will produce that as well.(Not an answer to your actual question but maybe to your problem. The "immediate" problem regarding the variable substitution in your double-quoted string has been answered here)
Since you're already using prepare you can simply make it a parametrized statement
$insert = $db->prepare('insert into mytable set mycolumn=?' ); $insert->execute( array($variable) ); and $variable===NULL will result in a NULL value in your MySQL table.
e.g.
<?php $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array( PDO::ATTR_EMULATE_PREPARES=>false, PDO::MYSQL_ATTR_DIRECT_QUERY=>false, PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION )); setup($pdo); $stmt = $pdo->prepare('INSERT INTO soFoo SET mycolumn=?'); $variable = 1; $stmt->execute( array($variable) ); $variable = NULL; $stmt->execute( array($variable) ); $variable = 2; $stmt->execute( array($variable) ); foreach( $pdo->query('SELECT id,mycolumn FROM soFoo', PDO::FETCH_ASSOC) as $row) { var_export($row); } function setup($pdo) { $pdo->exec(' CREATE TEMPORARY TABLE soFoo ( id int auto_increment, mycolumn int, primary key(id) ) '); } prints
array ( 'id' => 1, 'mycolumn' => 1, )array ( 'id' => 2, 'mycolumn' => NULL, )array ( 'id' => 3, 'mycolumn' => 2, ) 
$variableis converted to an empty string => SQL error. You should be using prepared statements.set mycolumn = ;, with no value. if you want a php null to become an sql null, you have toif ($var === null) { $var = 'null'; }convert it to a STRING with the charsn,u,l,lPDO::PARAM_NULLshould do the trick : php.net/manual/en/pdo.constants.php - so while PHP'snullis not necessarily the same as MySQLs you can use it to ensure you're insertingNULLinto the DB when your PHP value isnull