CODEWITHSUNDEEP
phpsqlmysqli
Ethan Bradley
Ethan Bradley

Reputation: 31

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

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

and 4.00 was inserted into the database.

I also tried:

$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?

Upvotes: 2

Views: 919

Answers (3)

Dharman
Dharman

Reputation: 33400

First of all stop using real_escape_string. You should be using prepared statements instead. It will help you solve your problem!

Here is how it should be done properly:

$item_name = $_POST['item_name'];
$price = $_POST['price'] ?: null; // make the value null if it is empty
$stmt = $con->prepare("INSERT INTO items (item_name,price) VALUES (?,?)");
$stmt->bind_param('ss', $item_name, $price);
$stmt->execute();

?: operator can be used in PHP to specify a default value if the variable is empty.

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13016

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

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

Upvotes: 1

Daniel Sosa
Daniel Sosa

Reputation: 39

you must use blank, not null, if you want the default value to be null.

one example:

eg: mysql_query("INSERT INTO number1,number2 VALUES(NULL,1)");

Upvotes: -1

Related Questions