Isn't PHP's null value equal to MySQL's null value?

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?

is this your actual code? If so, you're missing a closing quote. — Mike 'Pomax' Kamermans, Nov 19 '15 at 16:32
i missed it while writing my question here. I have a closing quoute on my code. — hakki, Nov 19 '15 at 16:33
NULL values are not considered equal in MySQL. Plus, in your example, `$variable` is converted to an empty string => SQL error. You should be using prepared statements. — BenMorel, Nov 19 '15 at 16:34
As for _Isn't PHP's null value equal to MySQL's null value?_ The answer is NO. — AbraCadaver, Nov 19 '15 at 16:35
no. php null in a string context becomes an empty string. you're doing `set mycolumn = ;`, with no value. if you want a php null to become an sql null, you have to `if ($var === null) { $var = 'null'; }` convert it to a STRING with the chars `n`, `u`, `l`, `l` — Marc B, Nov 19 '15 at 16:35
http://stackoverflow.com/questions/9314353/set-value-to-null-in-mysql — Andrew, Nov 19 '15 at 16:38
Using PDO, bind the parameter and set the type to `PDO::PARAM_NULL` should do the trick : http://php.net/manual/en/pdo.constants.php - so while PHP's `null` is not necessarily the same as MySQLs you can use it to ensure you're inserting `NULL` into the DB when your PHP value is `null` — CD001, Nov 19 '15 at 16:43
this question actually an answer for my question: http://stackoverflow.com/a/4620409/1848929 — hakki, Nov 19 '15 at 16:45

Niki van Stein · Accepted Answer · 2015-11-19T16:40:01.947

4

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

edited Nov 19 '15 at 16:40

answered Nov 19 '15 at 16:35

Niki van Stein

10,564
3
29
62

This is the answer for my question, but if null is a string on mysql why i'm using `.. SET column = null ` instead of `.. SET column= "null" ` on my mysql editor? – hakki Nov 19 '15 at 16:39
`$insert = "insert into mytable set mycolumn = $variable" ;` with $variable set to "null" will produce that as well. – Niki van Stein Nov 19 '15 at 16:41
The problem here is conversion from php to mysql, you need to provide the query as a string that is why it has ", when you concatenate the strings the " from the variable are gone. – Niki van Stein Nov 19 '15 at 16:42

score 2 · Answer 2 · edited May 23 '17 at 12:23

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

Isn't PHP's null value equal to MySQL's null value?

2 Answers2

Linked

Related