How to insert an actual NULL value into a nullable column?

Question

function save($gmt, $name, $address, $phone, $remark)
{
    $query= "INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('$gmt', '$name', '$address', '$phone', '$remark')";
    mysql_query($query);
}

Here, address, phone, and remark can be NULL. I need it to save NULL whenever the variable is set to NULL and the column is nullable, instead of inserting an empty string.

How can I insert NULL value into the database using PHP?

Solution 1

This is PHP solution, but you have to use mysqli because mysql deprecated, please read more about mysqli. Also, you must consider SQL injection

function save($gmt, $name, $address, $phone, $remark)
{
  if(empty($phone)){
   $phone = 'NULL';
  }else{
   $phone = "'".$phone."'";
  }
  if(empty($remark)){
   $remark = 'NULL';
  }else{
   $remark = "'".$remark."'";
  }
    $query= "INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('$gmt', '$name', '$address', $phone, $remark)";
    mysql_query($query);
}
//tests
save("a", "b", "c", "", "")."<br>";
save("a", "b", "c", "d", "")."<br>";
save("a", "b", "c", "d", "e")."<br>";
/*
INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('a', 'b', 'c', NULL, NULL)
INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('a', 'b', 'c', 'd', NULL)
INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('a', 'b', 'c', 'd', 'e')
*/
?>

DEMO

Solution 2

Try switching to prepared statements (which as a bonus is less prone to SQL injections).

function save($gmt, $name, $address, $phone, $remark)
{
    if(!isset($phone) || empty($phone)) { $phone = null; }
    if(!isset($remark) || empty($remark) { $remark = null; }

    $db = new PDO(...);

    $stmt = $db->prepare("INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES (:gmt, :name, :address, :phone, :remark)");
    $stmt->bindValue("gmt", $gmt, PDO::PARAM_STR);
    $stmt->bindValue("name", $name, PDO::PARAM_STR);
    $stmt->bindValue("address", $address, PDO::PARAM_STR);
    $stmt->bindValue("phone", $phone, PDO::PARAM_STR);
    $stmt->bindValue("remark", $remark, PDO::PARAM_STR);
    $stmt->execute();
}

This will handle the null values correctly in MySQL

Solution 3

PHP doesn't print NULL - it is just an empty string. So in your example you will try to insert '', which in SQL again is an empty string.

You have to use NULL (without quotes).

And the best practice to achieve that is to use an ORM or a PHP framework with a database abstraction layer which does this for you.

Solution 4

Using ternary operator, you can also use this

$add = ($address == '' ? NULL : $address);
$phn = ($phone == '' ? NULL : $phone);
$rmk = ($remark == '' ? NULL : $remark);

View more solutions

Comments

[tereško]

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

[NullPoiиteя]

good read publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topi‌​c=/…

[Dharman]

Does this answer your question? How to include a PHP variable inside a MySQL statement

[IROEGBU]

But, then I would think he needs to check if the variable is empty... he can't just substitute variables in his query for NULL

[IROEGBU]

this might look mighty complex for him... could you post a solution using just mysqli_?

[Karoly Horvath]

He has to use not the deprecated functions (prefereably through a wrapper), escape the values, and put the quotes only when there's actual value... so yes, a lot of stuff to do.

[Gerald Schneider]

I don't see it being easier with mysqli_*.

[IROEGBU]

Assume he is a beginner... for example look at Gerald's answer, makes a lot of sense but still complex for anybody not using OOP

[IROEGBU]

I guess it is just the bias I have. Being very comfortable using mysql_* it is easier for my to migrate my projects to mysqli_*. That's why I think it's easier for a new use to grasp mysqli_*

[TheCarver]

Using ternary: $phone = ($phone == '' ? NULL : $phone); would be a lot less code.

[candlejack]

What about if $phone is a string value?

[Dharman]

It is a very bad idea to use die(mysqli_error($conn)); in your code, because it could potentially leak sensitive information. See this post for more explanation: mysqli or die, does it have to die?

[rafaelspfonseca]

Thanks! @Dharman , I will be use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); instead of die