John Davidson

Message:

I am using PDO for writing to sqlite database.

// $dbo is an instance of PDO



$query     = "INSERT INTO items (id, name, rank) VALUES (:id, :name, :rank)";

$statement = $dbo->prepare($query);



$values = array('id' => 1, 'name' => 'Some Name', 'rank' => '');

 

$statement->execute($values);



$values = array('id' => 1, 'name' => 'Some Name', 'rank' => NULL);

 

$statement->execute($values);

After executing any of these, I expect "rank" to be an empty string or even a NULL is acceptable.
But what I get in the database is 'null', yes a string 'null' value, not the real NULL.

Could not find any solution after several attempts.

---

My Answer:
To insert a NULL value into the database using PDO, you should pass the actual NULL value instead of an empty string. In your case, you can modify your code like this:

php
$query = "INSERT INTO items (id, name, rank) VALUES (:id, :name, :rank)";
$statement = $dbo->prepare($query);

$values = array('id' => 1, 'name' => 'Some Name', 'rank' => null);

$statement->execute($values);


By passing null instead of an empty string for the 'rank' value, you should be able to insert a NULL value into the database column. This should solve the issue of inserting 'null' as a string instead of a real NULL value.