Re: INSERT INTO NULLABLE FIELDS THROWING ERRORS WITH NULL VALUES

[Phillip Ward]

Quote

the definition of these fields should allow null (= empty?) values?

For every RDBMS on the planet except full-blown, Oracle Database:

null != ''

But this is MySQL, so it does NULLs properly! :-)

If you want to put a null value into a table column, then you have to use the NULL keyword:

insert into Invoices ( field1, nullableField2, field3 ) 
values ( 1, NULL, 3 ) ;

However, since we're talking about dates here, you need to think a little bit more about how you're going to handle this. The user is going to be working with character data containing values that "happen to look" like Dates. You're taking those character values and trying to store them into a database that will store Date values any way it chooses - and you can guarantee that the two are not going to be the same.

Ask yourself - in which month does this "date" fall? "05/11/2014"

IMHO, you have to have a translation layer between "dates" that the user works with and those held in your database - a pair of Format() and Unformat() functions is a simple way. Whenever you retrieve a Date, you format it for the user to see. When you take a date value from the user, you (validate and) "unformat" it back into something that makes consistent sense to the database.

// enteredDate contains, say, '05/11/2014' 
$formattedDate = reformatDateToYyyyMmDd( $enteredDate ); 

// formattedDate contains, say, '2014/11/05' 
sql = "insert into Invoices ( numberField1, dateField2 ) 
values ( 1, '$formattedDate' ) ";

Regards, Phill W.