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?

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — tereško, Jan 30 '13 at 10:21
good read http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls562.htm — NullPoiиteя, Jan 30 '13 at 10:34
Does this answer your question? [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) — Dharman, Jun 18 '20 at 19:40

score 10 · Accepted Answer · 2013-01-30T11:25:35.303

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

Using ternary: `$phone = ($phone == '' ? NULL : $phone);` would be a lot less code. — TheCarver, Jun 06 '14 at 00:34

score 5 · Answer 2 · answered Jan 30 '13 at 09:46

5

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

answered Jan 30 '13 at 09:46

Gerald Schneider

17,416
9
60
78

1

this might look mighty complex for him... could you post a solution using just `mysqli_`? – IROEGBU Jan 30 '13 at 09:54
1

I don't see it being easier with `mysqli_*`. – Gerald Schneider Jan 30 '13 at 09:57
1

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_*` – IROEGBU Jan 30 '13 at 10:12

score 4 · Answer 3 · answered Jan 30 '13 at 09:42

4

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.

answered Jan 30 '13 at 09:42

Karoly Horvath

94,607
11
117
176

1

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 Jan 30 '13 at 09:52
1

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. – Karoly Horvath Jan 30 '13 at 09:54
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 Jan 30 '13 at 10:02

score 2 · Answer 4 · answered Jan 03 '14 at 09:43

2

Using ternary operator, you can also use this

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

answered Jan 03 '14 at 09:43

SagarPPanchal

9,839
6
34
62

score -1 · Answer 5 · answered Jun 18 '20 at 15:07

-1

fields can be NULL

-> qtd_aulas_previstas

-> qtd_aulas_dadas

$qtd_aulas_previstas = ( empty($qtd_aulas_previstas) ? 'NULL' : "'".$qtd_aulas_previstas."'");
$qtd_aulas_dadas     = ( empty($qtd_aulas_dadas)     ? 'NULL' : "'".$qtd_aulas_dadas."'");


//insere os dados na tabela
$inserir_tb = mysqli_query($conexao, "INSERT INTO tb_turma_bimestre VALUES('', '$cod_turma', '$cod_bimestre', $qtd_aulas_previstas, $qtd_aulas_dadas, '$data_abertura', '$data_encerramento', '$date_time', '$nome_login')")or die("Error2: " .mysqli_error($conexao));

answered Jun 18 '20 at 15:07

rafaelspfonseca

9
1

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?](https://stackoverflow.com/a/15320411/1839439) – Dharman Jun 18 '20 at 17:22
Thanks! @Dharman , I will be use `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` instead of `die` – rafaelspfonseca Jun 21 '20 at 16:28

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

5 Answers5

Linked