使用MySql在数据库中输入日期值或空值

Question

I have searched the site and although I have found questions and answers similar I haven't been able to find an answer. After 4 hours of searching I've decided to bite the bullet and ask the question.

I have 4 date fields in a form that aren't required. I would like it to enter a date into the database if one of the fields has an entry or null if any are left blank.

I have an if statement that checks if the value is empty and if so $value = null, otherwise use $value = date("Y-m-d",strtotime($_post['value'])) to convert it to a date and this works well.

The problem is in my query. If I use '$value' it will insert the date correctly but won't insert a null value because using 'null' makes sql think it's a string. If I use just $value the null inserts just fine but the date goes in as 0000-00-00.

Any advice would be very much appreciated

Thanks for the advice so far...

Null is allowed, this is my script...

if(empty($_POST['fp32_original_install_date'])){
$fp32_install = NULL;
    }else{
$fp32_install = date("Y-m-d",strtotime($_POST['fp32_original_install_date']));
 }

 $sql = "INSERT INTO accounts_cstm (id_c, support_c, install_date_c, sware_renewal_date_c, product_key_c, account_status_c, fp32_support_type_c, fp32_support_renewal_date_c, fp32_original_install_date_c) VALUES ('$Guid','$cdr_support', '$cdr_install', '$cdr_renew', '$prod_key', '$account_status', '$fp32_support', '$fp32_renew', $fp32_install)";

If I use in the query $fp32_install a null value goes in just fine but a date goes in as 0000-00-00, if I use '$fp32_install' the date goes in fine but a NULL value goes in as 1970-01-01 (probably because it sees 'NULL' as a string)

If I echo $fp32_install the value is shown as 2012-08-16 and the SQL type for the column is date and the default is NULL

Answer 1

If you are using posted values from a form, then $_POST['value'] will not be NULL.

You should check for empty values instead.

if($_POST['value']=="")
{
    $value="NULL";
}
else 
{
    $value="'".date("Y-m-d",strtotime($_POST['value']))."'";
}