Database Date type error

datrim

Hi,

If you create a database table with a 4 digit DATE field, the CMS will not accept any entry other than '0000'. The problem seems to be in the line 'if (strncmp($value, '0000', 4) !== 0)' in the 'auto_validate_field' function in MY_Model.php. I have replaced it with 'if (strlen($value) !== 4' but perhaps a 'preg_match' call would be a better solution.

Regards,
Chris.

admin

Thanks for the report. I'm not quite sure I follow when you say a 4 digit DATE field. Could you explain that a bit more? The '0000' is there to catch empty date field values (e.g. '0000-00-00').

datrim

Sorry, I meant a YEAR field which can be either 2 or 4 digits.

Chris.

admin

Thanks for clarifying. I'll look into that issue.

datrim

This seems to work for YEAR fields without affecting the error checking for other DATE types:

case 'date':
$valid_date = TRUE;
if ($field_data['type'] == 'year')
{
$reg_exp = $field_data['max_length'] == 4 ? '/(19|20)\d\d$/' : '/\d\d$/';
$valid_date = preg_match($reg_exp, $value);
}
else
{
$valid_date = (strncmp($value, '0000', 4) === 0);
}

if ($valid_date == FALSE)
{
$this->validator->add_rule($field, 'valid_date', "Invalid date for ".$field_name, $value);
if ($field_data['type'] == 'datetime') $this->validator->add_rule($field, 'valid_time', "Invalid time for ".$field_name, $value);
}
break;

admin

Great!... Thanks for the input. Would the above fix be an issue for dates before 1900 or after 2099?

datrim

No, at present it works only for 1900 - 2099, but wouldn't take too much effort to make it a little more flexible

admin

I just posted a fix for this. The change was made to the fuel/application/libraries/MY_Model.php and made some additions to fuel/modules/fuel/language/english/model_lang.php

datrim

Sorry, but the fix doesn't work. The $this->field_type($field) will still be a DATE type so the 'year' case in the switch statement is never hit. The 'date' case needs to then check the $field_data['type'] to determine whether the type of date is a 'year'. It should also be aware that a year field can be either 2 or 4 digits.

datrim

Please disregard last comment - my aplologies, didn't spot the change to the field_type function as well. It is now working for me. Thank you.

Chris.

admin

Is your comment regarding years being 2 or 4 in the database still relevant?

datrim

According to the MYSQL documentation a YEAR type can be either a YEAR(2) or YEAR(4). The default is YEAR(4). Heres a link to the docs:
http://dev.mysql.com/doc/refman/5.1/en/year.html

admin

I'll need to modify the code I think to account for the 2 digit year.

admin

That has been updated to account for 2 digit years.

datrim

Great, thanks.

Chris.

EcotypeS

I downloaded Fuel in June, so I had that version of the date-time code. I have two fields. One defined as date, the other defined as time. In the original, the date worked. Time semi-worked, but obviously semi- isn't good enough..

Given that it was "semi" I tried to update using the GitHUB code. Now if one updates the time field, the date field gets zero'd out. I assume that this is the bug described above. You don't have to change it to get it zero'd -- it always happens

It looks as though the time field now works.

I've added a datetime field since I need a fix. It appears to work --I did at least basic testing.

So three observations that might help:
1. June date code worked, June time code had issues
2. 7 months ago update works for time but not date
3. Datetime seems to be OK
4. June version of date and time is in general less fatal than the 7 months ago version

I'm recoding to datetime field ... but I know not everyone has that option...

EcotypeS

Same "updated" version -- causes an error in file uploads -- My_model, likely line 411 hard-codes the upload directory and over-rides the directory parameter that went to form_builder.

I've gone back to the older code -- it appears to have been cleaner -- just needed the time field fixed.