How to set up a varchar field to NULL when no value given, in php insert value coding

searain

How to set up a varchar field to NULL when no value given?

For example

product

product_id
product_code
price

product_id is the auto increment int key
product_code is varchar but must be unique, if no value given, it should be set to NULL.
price is type of "decimal", and if no value given, it should be set to NULL.

So in my php prgramming

I will do the insert like that

$sql_insert="INSERT INTO product (product_code, price) values ('$product_code', $price)";

It is easy to handle price if no value given set to NULL

I can just do like this

if (!(is_numeric($price)))
$price="NULL";

So the sql will be the same as

$sql_insert="INSERT INTO product (product_code, price) values ('$product_code', NULL)";

But for product_code
if I do like this

if (trim($product_code)=="")
$product="NULL";

The sql will be the same as

$sql_insert="INSERT INTO product (product_code, price) values ('NULL', $price)";

The product_code is not set as NULL, but as string "NULL" in the database, right?

So I have to write another sql statment specially for this case?

if (trim($product_code)=="")
$sql_insert="INSERT INTO product (product_code, price) values (NULL, $price)";
else
$sql_insert="INSERT INTO product (product_code, price) values ('$product_code', $price)";

So my questions will be if the field type is varchar etc., and if no value given, it should be set as NULL, how can I do it in the php programming?

Instead of writting one sql statement for the Insert, and pass the input values to the sql through php variables, do I have to write several sql statement, if certain value is supposed to be NULL, I should specailly write sql for that?

Thanks!

konsu

sql will usually set the fields that you do not pass any values for during INSERT to their default values, which are usually NULL unless you use the DEFAULT syntax when creating your tables.

searain

Thanks!

I still have to write another sql statment specially for this case, right?

if (trim($product_code)=="")
$sql_insert="INSERT INTO product (price) values ($price)";
else
$sql_insert="INSERT INTO product (product_code, price) values ('$product_code', $price)";

I would like to know if there are other ways that I don't have to write the sql specially for this SET to NULL case.

konsu

well, if you do not want to save the empty string for the product code then yes, or just pass null:

insert into product (product_code,price) values (null,...)

no quotes