I am trying to insert the following line into mySQL. I then received the following error.

INSERT failed: INSERT INTO tbl_md4c (project, level, part, part_desc, status, edate, rdate, qty, pr, po, line, factory, error, prod) VALUES( '60227648 ','3','500012A-042','VALVE-CHK,SWING,SXS,316-PTFE,0.50,KITZ ', 'SubctctReq','','09/11/02', '1','','','', '3899','','')
Incorrect date value: '' for column 'edate' at row 1

The problem appear to be that the value associated with the "edate" is empty. I have set up the db as a date type value with a default NULL value.

Not sure why it will not accept the data or the lack there of as the case might be?

Never mind my earlier post (now deleted), I mistook the commas in 4th value to mean multiple ones. So I thought you had unequal columns to values.

Are you sure that it's not your rdate column that is set to allow null?

Can you show us the structure of your table?

Here is a screen shot of the structure for the table.

I checked it and NULL is displayed as the default value.

Ok, I see it now...
In the field "edate", you try to insert the date '', which is invalid.

An empty string is not a null.
A null is represented by the keyword "null".

So, your query must be written:

INSERT INTO tbl_md4c (project, level, part, part_desc, status, edate, rdate, qty, pr, po, line, factory, error, prod) 
VALUES( '60227648 ','3','500012A-042','VALVE-CHK,SWING,SXS,316-PTFE,0.50,KITZ ', 'SubctctReq',NULL,'09/11/02', '1','','','', '3899','','')

So if I removed the default NULL from the table and set it to none, should it then accept ""? I tried this but it still did not work but want to make sure I understand this?

I guess I am also a bit confused as the PO and LINE are set to NULL yet I have records that have been accepted without a problem. Is it a case that DATE does not accept "" as a value?

So if I removed the default NULL from the table and set it to none, should it then accept ""? I tried this but it still did not work but want to make sure I understand this?

No.
You have not fully understood what a null is.

Basically, you could translate "null" by "unknown".
An empty string is not null, it's empty.

The problem is that your column is typed as a date/time column. So, you can only insert a date in it.
When you give mysql '09/11/02', the string is converted automatically in a date.
But when you give it '', then mysql cannot convert this to a date, thus the error.

If you don't want to have a value in the field, you must tell explicitly mysql so, by using the null keyword.

Is it a case that DATE does not accept "" as a value?

It's exactly that!

That the reason too, why

select NULL+3

gives a result of NULL.
An unknown value+3 is unknown.

Originally Posted by dgkindy

So if I removed the default NULL from the table and set it to none, should it then accept ""? I tried this but it still did not work but want to make sure I understand this?

Or remove the column and the associated value from the insert UNLESS there is a valid date to insert.

Okay, based on your commentary, I did a bit more investigation. This is what I found.

When I create the variable, it is created with a NULL value.

PHP Code:

class Material  {
        public $level = NULL;       //Part level
        public $part = NULL;            //Part number
        public $part_desc = NULL;       //Part description
        public $status = "Blank";    //Status of part
        public $edate = NULL;           //Date the parts are expected
        public $rdate = NULL;           //Date the parts are required by
        public $qty = NULL;             //Required quantity for project
        public $pr = NULL;              //Purchase requisition number to buy part 
        public $po = NULL;              //PO number that is buying the part
        public $line = NULL;            //Line on PO that is purchasing the part
        public $factory = NULL;         //Manufacturing facility for part
        public $error = NULL;           //SAP error code
        public $prod = NULL;            //Production order number
        } 


I then confirmed the value is NULL after creation

PHP Code:

           if(is_null($material[$bin]->edate)){
   echo 'value is null <br />';
   } 


I then confirmed the value is still NULL just prior to inserting the data into the database.

PHP Code:

             if(is_null($value->edate)){
   echo 'value is null on dump <br />';
   } 


code that is used to generate the sql statement

PHP Code:

  $query = "INSERT INTO tbl_md4c (project, level, part, part_desc, status, edate, rdate, 
          qty, pr, po, line, factory, error, prod) 
          VALUES"."(
          '$project','$value->level','$value->part','$value->part_desc',
          '$value->status','$value->edate','$value->rdate',
          '$value->qty','$value->pr','$value->po','$value->line',
          '$value->factory','$value->error','$value->prod')"; 


Results that are printed on the screen.

value is null on dump
INSERT failed: INSERT INTO tbl_md4c (project, level, part, part_desc, status, edate, rdate, qty, pr, po, line, factory, error, prod) VALUES( '60227648 ','2','3037920','U-BOLT,GLV,0.25-20UNC,0.50PIPE ', 'Plant','','09/12/02', '1','','','', '3899','','')
Incorrect date value: '' for column 'edate' at row 1

So, it would seem that the value is NULL but when I go to insert the value, php is converting the NULL value to "". How do you maintain the NULL value. Understand that, out of 14 values, about 5 of them are mandatory, while the other values may or may not exist depending on the record. All variables are required.

you need to drop the single quotes and replace the value null with the keyword "null" in that case:

PHP Code:

<?
function nullOrNot($val){
    $ret=$val===null?"NULL":"'$val'";
    return $ret;
}

$query = "INSERT INTO tbl_md4c (project, level, part, part_desc, status, edate, rdate, qty, pr, po, line, factory, error, prod) 
          VALUES (
          '$project'
          ,".nullOrNot($value->level)."
          ,".nullOrNot($value->part)."
          ,".nullOrNot($value->part_desc)."
          ,".nullOrNot($value->status)."
          ,".nullOrNot($value->edate)."
          ,".nullOrNot($value->rdate)."
          ,".nullOrNot($value->qty)."
          ,".nullOrNot($value->pr)."
          ,".nullOrNot($value->po)."
          ,".nullOrNot($value->line)."
          ,".nullOrNot($value->factory)."
          ,".nullOrNot($value->error)."
          ,".nullOrNot($value->prod).")";