Database Date type error

edited February 2011 in Bug Reports
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.

Comments

  • edited 7:09AM
    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').
  • edited 7:09AM
    Sorry, I meant a YEAR field which can be either 2 or 4 digits.

    Chris.
  • edited 7:09AM
    Thanks for clarifying. I'll look into that issue.
  • edited 7:09AM
    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;
  • edited 7:09AM
    Great!... Thanks for the input. Would the above fix be an issue for dates before 1900 or after 2099?
  • edited 7:09AM
    No, at present it works only for 1900 - 2099, but wouldn't take too much effort to make it a little more flexible
  • edited 7:09AM
    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
  • edited 7:09AM
    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.
  • edited 7:09AM
    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.
  • edited 7:09AM
    Is your comment regarding years being 2 or 4 in the database still relevant?
  • edited 7:09AM
    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
  • edited 7:09AM
    I'll need to modify the code I think to account for the 2 digit year.
  • edited 7:09AM
    That has been updated to account for 2 digit years.
  • edited 7:09AM
    Great, thanks.

    Chris.
  • edited 7:09AM
    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...
  • edited 7:09AM
    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.
Sign In or Register to comment.