Forums

Hello Everybody,

i have a problem adding and editing database entries with date / datetime fields.

Configuration:
Joomla 4.3.2
PHP 8.1.19
ChronoForms7 7.0.10
MySQL 5.7.41

I have to say, that the problem was not present with my old configuration (Joomla 3.10.11, PHP7.4, ChronoForms 7.0.10), every form worked until the upgrade to J4 and PHP8.

What happens:

- I insert entries into my DB vie a form and use the same form for editing. Testing with a simple form only containing some fields for create date and modified date for example plus 1 text field and one calender field.
- Trieed date or datetime as data format in the database.
- Default value for the date field is NULL or 0000-00-00
- When I add a database entry in PHPMyAdmin the default value is written (0000-00-00 or the field is NULL).
- When I add or modify an entry with Chronforms with a random date (e.g. 2023-06-07), everything works.

First Problem:

- Opening an existing entry with Chronoforms with the value 0000-00-00 shows 2001-01-01 and I have to klick a lot until the correct date.

Second Problem:

- Deleting an existing date value (empty field) or adding a record withou a selected date brings the error:
"22007 - 22007, 1292, Incorrect date value: '' for column 'test_date' at row 1"
(a similar problem was described with empty id, I had the same with the id and have to set it manually to 0 on insert although the database is set correct)
- When I try to write NULL, the same error occurs.
- When I enter 0000-00-00 in Value & Placeholder forr testing or set the read_data field by Custom Code to 0000-00-00 the date 2001-01-01 is written to the database.

Summary:

- I can not write empty field (NULL) to the database in a date or datetime field even when the tables default vaules are set to NULL.
- I can not write 0000-00-00 to the database, it's interpreted as 2001-01-01.
- I can not read 0000-00-00 without getting 2001-01-01 in the calendar field (avoided by changing 0000-00-00 to NULL in Custom Code after read action).

Everything has worked in the above mentioned old configuration.

Greetings

Nicolas

Hi Nicolas,

Make sure the date field does not exist in the data array to be saved if you want the default value (NULL) to be saved.

I think 2001-01-01 is stored because that's the empty date value of the calendar, you may unset the date field value before saving to save the default value

Hi Max,

that is, what I have tried. But unfortunately I was not able to unset the date field in the save data. To make it clear: 2001-01-01 is only shown and written when I try to read or write 0000-00-00. When I read a record from the database with the date field NULL, the field is not set in the read_data (field name: test_date) and the input in the form is empty as it should be:

Array
(
    [option] => com_chronoforms7
    [cont] => manager
    [chronoform] => test-input
    [cf_uid] => 617cd4ec-c066-41fe-b15d-77472ace32f7
    [reDimCookieHint] => 1
    [a0e7941a0f82a9714d2bbaa5664d548d] => e7a9e1fa18ba3360d5679a7ca5ca046d
    [joomla_user_state] => logged_in
    [945361bc890d9038cb28d8834893b026] => 70b9f5cc89cc500467ad64421ae100f2
    [test_data] => 
)

Array
(
    [read_data] => Array
        (
            [result] => Data read successfully!
            [log] => Array
                (
                    [0] => SELECT `test_data`.`cf_id` AS `test_data.cf_id`, `test_data`.`cf_uid` AS `test_data.cf_uid`, `test_data`.`cf_created` AS `test_data.cf_created`, `test_data`.`cf_modified` AS `test_data.cf_modified`, `test_data`.`cf_created_by` AS `test_data.cf_created_by`, `test_data`.`cf_modified_by` AS `test_data.cf_modified_by`, `test_data`.`cf_ipaddress` AS `test_data.cf_ipaddress`, `test_data`.`test_text` AS `test_data.test_text`, `test_data`.`test_date` AS `test_data.test_date` FROM `tsc_chronoforms7_data_test` AS `test_data` WHERE `test_data`.`cf_uid` != '' AND `test_data`.`cf_uid` = '617cd4ec-c066-41fe-b15d-77472ace32f7';
                )

            [var] => Array
                (
                    [test_data] => Array
                        (
                            [cf_id] => 2
                            [cf_uid] => 617cd4ec-c066-41fe-b15d-77472ace32f7
                            [cf_created] => 2023-06-06 21:01:16
                            [cf_modified] => 2023-06-16 17:41:11
                            [cf_created_by] => 43
                            [cf_modified_by] => 43
                            [cf_ipaddress] => 46.142.97.171
                            [test_text] => test_date is NULL
                        )

                )

        )

    [php_65] => Array
        (
            [returned] => NULL
            [var] => NULL
        )

)

When I try to save this form with empty date, the error "22007 - 22007, 1292, Incorrect date value: '' for column 'test_date' at row 1" occurs, no debug output. Disabling the save data action shows the debug data:

Array
(
    [option] => com_chronoforms7
    [cont] => manager
    [chronoform] => test-input
    [cf_uid] => 617cd4ec-c066-41fe-b15d-77472ace32f7
    [gpage] => save
    [test_data] => Array
        (
            [cf_id] => 2
            [cf_uid] => 617cd4ec-c066-41fe-b15d-77472ace32f7
            [cf_created] => 2023-06-06 21:01:16
            [cf_modified] => 2023-06-16 17:48:41
            [cf_created_by] => 43
            [cf_modified_by] => 43
            [cf_ipaddress] => 46.142.97.171
            [test_text] => test_date is NULL
            [test_date] => 
        )

    [test_data_cf_created] => 2023-06-06 21:01:16
    [test_data_cf_modified] => 2023-06-16 17:48:41
    [test_data_test_date] => 
    [reDimCookieHint] => 1
    [a0e7941a0f82a9714d2bbaa5664d548d] => e7a9e1fa18ba3360d5679a7ca5ca046d
    [joomla_user_state] => logged_in
    [945361bc890d9038cb28d8834893b026] => 70b9f5cc89cc500467ad64421ae100f2
    [__cf_token] => 153b1a62-e7b9-48bc-8bdb-2a1ec0ee0312
)

Array
(
    [save_data] => Array
        (
            [result] => Data save failed!
            [dataset] => Array
                (
                )

            [log] => Array
                (
                )

            [var] => false
        )

)

So on the save page the date field "test_date" is empty but present although it was not set in the read_data and no input was made. Database save leads to the above error. How can I unset this field in the save data? And why has this worked with CF7/J3.10/PHP7.4 and now with CF7/J4.3.2/PHP8.1 not?

But that's the correct behavior, when no date in the data array, it will not raise an error, but if there is a "date" value it must be in the correct format.

I do not understand what you mean.

1) in the read data the date is not set ([read_data][test_date] is not present)
2) I do not select a date in the calendar field and save the form
3) after saving the form [read_data][test_date] is present but empty -> error

So even when the field is not set in the read data, it reappers on the save page (empty) and produces an error. (the standard in MYSQL is NULL or tested with 0000-00-00).

How can I unset the field [test_date] before saving when I do not select a date in the calendar field?

By the way, this is a different behaviour than the same CF7 version under PHP7.4 and Joomla 3.10 (same form) and I whant to understand what I have to change and why.

And I think this is the same behaviour as described in this post (I had this problem too): https://www.chronoengine.com/forums/topics/view/110274/bug-hy000-1366-incorrect-integer-value-for-column-id-at-row-1. It was not nescessary to the the id = 0 when creating a new database entry even if the standard is set correct in MYSQL. But have no solution for this problem whith empty date fields. 0000-00-00 or NULL is not accepted.

Nicolas

Does the date field in the table have default "NULL" ? if not then please try that.

if that does not work then you will need to unset the date field value using PHP before the "Save data":

unset($this->data["test_date"]);

I think in J4 the database connection settings have changed, and now it requires some standard values available, that's why the id should not be empty.

Hi Max,

At first: Yes, as written in the first post, the default for the field in the table is NULL. When I add an empty database record in PHPMyAdmin, the field is NULL.

So I added a custom PHP before the save data action and tried unset. Different versions:

unset($this->data["test_date"]);
unset($this->data["test_data.test_date"]);
$this->set('test_date',NULL);
$this->set('test_data.test_date',NULL);

Result with deactivated save action:

Array
(
    [option] => com_chronoforms7
    [cont] => manager
    [chronoform] => test-input
    [cf_uid] => 617cd4ec-c066-41fe-b15d-77472ace32f7
    [gpage] => save
    [test_data] => Array
        (
            [cf_id] => 2
            [cf_uid] => 617cd4ec-c066-41fe-b15d-77472ace32f7
            [cf_created] => 2023-06-06 21:01:16
            [cf_modified] => 2023-06-16 17:48:41
            [cf_created_by] => 43
            [cf_modified_by] => 43
            [cf_ipaddress] => 46.142.97.171
            [test_text] => test_date is NULL
            [test_date] =>              field is still present!!
        )

Result with activated save action:
22007 - 22007, 1292, Incorrect date value: '' for column 'test_date' at row 1

What is my mistake? $this->set(...) works after the read data action but not before the save data action. unset seems to have no effect on the array or I have the wrong syntax.

Has no one else the case, loading a date field, clearing the field and saving a record with an empty date field?

Nicolas

OK, another Info. But still no solution:

Saving a calendar field (date or datetime in MySQL, default = NULL) only works with a valid date picked from the calendar. OK, thats obviously.
In the calendar field under "Value and Placeholder" I set {const:Null} (or leave it empty).

When I clear the calendar field and save the form, the above shown error occurs. 22007 - 22007, 1292, Incorrect date value: '' for column 'test_date' at row 1

This is because clearing the field leads to an empty string. I was not able to unset the date field in the save data (custom PHP) and the "Value and Placeholder" {const:Null} does also not have an effect on the field.

Bad workaround:
When I change the data type for the MySSQL table from date to varchar(10), the empty string is accepted and the date is "cleared". But of course also no default NULL is written.

But this can not be the solution. Clearing a field has to unset the field and not set it to an empty string. Writing an empty string only works with varchar but not with date/datetime.

I'm using CF for more than 10 years now since CF4 and I managed EVERY problem (also using this very helpful forum). But now I no longer know what to do. I think it's a bug in the combination Joomla4 / CF7 / PHP8. Same form has worked with all previous versions of Joomla, CF and PHP. But perhaps there is an easy solution or something I did not see yet...

Nicolas

Hi Nicolas,

if there is a field then it will send an empty value, and an empty value will cause the error you have, so the solution is to either send a valid date from the field or no value at all,.

here is the fix, in your calendar field, scroll down to Behaviors > Data, enable Alternative Data, then add a data set:
Data Scope: Override data
Add new Value,
Rule: =
Field Value: LEAVE EMPTY
New Value: {const:null}

That should remove the field value from the data array before saving when no date is chosen

Hello Nicolas,

It seems like you're facing some issues with adding and editing database entries with date/datetime fields in Joomla 4.3.2, PHP 8.1.19, and ChronoForms 7.0.10. Let's address each problem individually:

First Problem:
- When opening an existing entry with Chronoforms that has a value of 0000-00-00, it displays 2001-01-01, and you need to click several times to get the correct date.

This issue might be related to the default behavior of MySQL for invalid dates. MySQL uses the "zero" date '0000-00-00' as an invalid date representation and converts it to '2001-01-01' during retrieval. To handle this, you can try converting the '0000-00-00' date to NULL in your ChronoForms code after the read action.

Second Problem:
- Trying to write an empty field (NULL) to the database in a date/datetime field results in an error.
- Writing 0000-00-00 to the database is interpreted as 2001-01-01.
- Reading 0000-00-00 from the database results in 2001-01-01 in the calendar field.

This problem appears to be a combination of issues with the way NULL and '0000-00-00' are handled in the MySQL database and how ChronoForms interacts with the date fields.

To address these issues, you can try the following:

1. Handling NULL Values: Make sure that your database table allows NULL values for the date/datetime fields, and the default values for these fields are set to NULL.

2. Conversion in ChronoForms: In your ChronoForms custom code, when inserting data into the database, explicitly convert empty date fields to NULL before inserting them.

3. Avoid '0000-00-00': Instead of using '0000-00-00' as a placeholder value, use NULL to represent empty date/datetime fields.

4. Date Validation: Implement date validation in your form to ensure that only valid dates are entered.

Here's an example of how you could handle the date conversion in your ChronoForms custom code (replace 'test_date' with your actual date field name):

```php
// After the form submission, get the date value from the form
$dateValue = $form->data['test_date'];

// Convert empty date values to NULL
if (empty($dateValue)) {
$dateValue = null;
}

// Now use $dateValue to insert or update the database
// Your database query here...
```

Keep in mind that Joomla and its extensions, including ChronoForms, may have undergone changes and updates since your previous configuration (Joomla 3.10.11 and PHP 7.4). Therefore, some adjustments might be necessary to ensure compatibility with the newer versions.

Before implementing any changes, it's always a good idea to create a backup of your website and database, so you can revert to the previous state if needed. Additionally, if these problems persist despite trying the suggestions above, consider reaching out to ChronoForms support or the Joomla community to see if there are any specific solutions or updates related to the issues you've encountered.