How to insert NULL into decimal type column using PHP?

I’m trying to insert NULL into a column, called price, which is of type Decimal.

The column is nullable and the default is NULL. I want to insert NULL, only when nothing has been entered in the “price” input on a HTML form. This is my PHP code:

$item_name = $con->real_escape_string($_POST['item_name']);
$price = $con->real_escape_string($_POST['price']);

$con->query("INSERT INTO items (item_name,price) VALUES ('$item_name','$price')");

​x
 
$item_name = $con->real_escape_string($_POST['item_name']);$price = $con->real_escape_string($_POST['price']);​$con->query("INSERT INTO items (item_name,price) VALUES ('$item_name','$price')");​

With the above code, when I leave the “price” input empty, 0.00 is inserted into the database.

I am pretty sure that, when the “price” input field is left empty, $price is NULL because I tried:

if ($price == NULL) {
  $price == 4;
}

 
if ($price == NULL) {  $price == 4;}​

and 4.00 was inserted into the database.

I also tried:

$con->query("INSERT INTO items (item_name,price) VALUES ('$item_name',NULL)");

 
$con->query("INSERT INTO items (item_name,price) VALUES ('$item_name',NULL)");​

and NULL was inserted into the database.

How do I insert NULL into price when $price is NULL?

Answer

Here’s how to insert null in your price column.

$con->query("INSERT INTO items (item_name,price) VALUES ('$item_name',NULLIF('$price', ''))");

 
$con->query("INSERT INTO items (item_name,price) VALUES ('$item_name',NULLIF('$price', ''))");​

Advertisement

Answer