CODEWITHSUNDEEP
phpmysqlnull
user1725661
user1725661

Reputation: 279

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

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?

Upvotes: 9

Views: 23895

Answers (5)

rafaelspfonseca
rafaelspfonseca

Reputation: 9

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

Upvotes: -1

SagarPPanchal
SagarPPanchal

Reputation: 10111

Using ternary operator, you can also use this

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

Upvotes: 2

user1646111
user1646111

Reputation:

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

Upvotes: 10

Gerald Schneider
Gerald Schneider

Reputation: 17797

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

Upvotes: 5

Karoly Horvath
Karoly Horvath

Reputation: 96258

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.

Upvotes: 4

Related Questions