Isn't PHP's null value equal to MySQL's null value? [duplicate]

Question

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?

Answer 1

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.)

Answer 2

(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,
)