A DW CC form, MySQL database, and date fields

Report · Mar 15, 2016

HI folks,

I'm wondering if my issue has something specifically to do with rules regarding MySQL databases. I have a Web form (built in DW CC; end user uses a browser) I maintain for a client that we use to keep info current about our members. Not only name, address, etc., but whether they are in arrears on their dues, have been sent reminders, and finally, have been dropped from membership, and the date they were dropped. In that regard I have two columns: date_removed, and year_removed. Both are "date" fields; the first one configured as XXXX-XX-XX and the second one as XXXX.

Obviously, for members who are current in their dues, we don't want a date or a year to populate these fields, so that when we do searches their names won't come up. But whenever we empty out the field using our form, then hit Save, the date_removed field later defaults back to 1969-12-31 and the year_removed field defaults back to 2000! It's the strangest thing, kind of spooky too! Similar things happen if we set the field to NULL.

So my question is, is it a no-no to have blank fields in a MySQL database (at least fields that are set to be date fields)? Has anyone else ever seen this bizarre substitution take place? We can always spot the 1969 bogus entry because the organization wasn't in existence then, but the 2000 entry really messes us up.

Any insight would be greatly appreciated!

Gail

‌

Report · Mar 16, 2016

You can default your date fields to 000-00-00 in the database. On your form processing you can specify that if there is no value for the date. The following would do it:

$date_removed = empty($date_removed)? "0000-00-00" : $date_removed;

UNIX type operating systems return 1969-12-31 when they can't interpret a date or if none is given.