1

So zero dates are deprecated in MySQL. J4 has replaced them with nulls.I am trying to update a J3 component to stop J setting zeros in the modified and checked_out_time fields.

For modified I am using table prepareTable() to set an empty modified date from the form to NULL for saving and table getItem() to replace nulls in modified with a zero date to keep the calendar control happy.

The problem is with checked_out_time which is set back to zero by joomla in a separate query when the item is closed (ie after save).

How can I override this to force Joomla to save a valid for MySQL date?

7
  • Hi Roger, I do this testing of blank date fields in the Bind step of the Table however I've not had a need for a blank date yet in J4. if(empty($array['depart_date']) || $array['depart_date'] == '0000-00-00 00:00:00' ) { $array['depart_date'] = $date->toSql(); } Aug 8, 2021 at 8:41
  • Thanks Glenn - the problem is Joomla clears the checked_out_time to "0000-00-00 00:00:00" after the call to bind() when saving or closing so even if you set checked_out_time to null in bind() it still sets it back to zeroes to clear the checked out flag. Setting it to NULL directly in the database doesn't cause any problems when loading the item, the checkin function simply stores the current time in the db as it should whilst the item is open.
    – RogerCO
    Aug 8, 2021 at 10:19
  • Interestingly in J3.10 a protected value has been added to the Table class called _supportNullValue which is used to check whether to write zero or null for an empty checked_out_time but it defaults to false and there seems to be no facility to set it :-( But that wouldn't solve the problem for 3.9.
    – RogerCO
    Aug 8, 2021 at 10:38
  • by the way bind() seems to get called once on load and twice on save - once to save the changes and then again if the save is successful in order to update the checked_out_time and user. It is called once on a simple close, at which point you might not want to make changes to the data.
    – RogerCO
    Aug 8, 2021 at 11:22
  • Ahh, I set a default in my components - checked_out_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' so I've not had this issue. Aug 9, 2021 at 0:53

3 Answers 3

2

With thanks to @glenn-arkell who pointed me in the right direction, for j3.9 this works to stop Joomla writing '0000-00-00 00:00:00' into the checked_out_time field. For other fields (eg modified and any of your own datetime fields) use bind() or prepareTable() and getItem() to set empty dates from a form control to null and null dates back to zero for use in a form control.

In your table class add the following function (copied from the parent table class and modified to write nulls for the checked_out_time field. The parent class is replaced.

    public function checkIn($pk = null) {
    $checkedOutField = $this->getColumnAlias('checked_out');
    $checkedOutTimeField = $this->getColumnAlias('checked_out_time');

    // If there is no checked_out or checked_out_time field, just return true.
    if (!property_exists($this, $checkedOutField) || !property_exists($this, $checkedOutTimeField))
    {
        return true;
    }

    if (is_null($pk))
    {
        $pk = array();

        foreach ($this->_tbl_keys as $key)
        {
            $pk[$this->$key] = $this->$key;
        }
    }
    elseif (!is_array($pk))
    {
        $pk = array($this->_tbl_key => $pk);
    }

    foreach ($this->_tbl_keys as $key)
    {
        $pk[$key] = empty($pk[$key]) ? $this->$key : $pk[$key];

        if ($pk[$key] === null)
        {
            throw new \UnexpectedValueException('Null primary key not allowed.');
        }
    }

    // Check the row in by primary key.
    $query = $this->_db->getQuery(true)
        ->update($this->_tbl)
        ->set($this->_db->quoteName($checkedOutField) . ' = 0' )
        ->set($this->_db->quoteName($checkedOutTimeField) . ' = NULL' );
    parent::appendPrimaryKeys($query, $pk);
    $this->_db->setQuery($query);

    // Check for a database error.
    $this->_db->execute();

    // Set table values in the object.
    $this->$checkedOutField     =  0;
    //we are putting an empty, not null, value here for compatibility with anything else using onAfterCheckin.
    $this->$checkedOutTimeField =  '';

    $dispatcher = \JEventDispatcher::getInstance();
    $dispatcher->trigger('onAfterCheckin', array($this->_tbl));

    return true;
}

This seems to work ok for getting rid of those pesky '0000-00-00 00:00:00' values in the checked_out_time field in the database and stops mysql throwing warnings and allows phpmyadmin to work without having to change the Strict mode.

1
  • of course this doesn't do anything for the core J3 components which are still using zero date values.
    – RogerCO
    Aug 9, 2021 at 6:38
1

Some added suggestions around null dates - I came upon the need to remove a date from a datetime field using the standard Table from the Model class. This makes the removal of a date nice and clean. This may be obvious to some.

$updateNulls = true;
$table = $this->getTable();
$table->load($id);

$table->proved_date = NULL;

$table->store($updateNulls);
0

A further add to this regarding comparing dates with nulls.

If a datetime can be set to null or a valid value and you want to update it if the new value is later (greater than) the existing value (null or a valid date) then you need to be able to compare the new date with null - which doesn't work.

So you can use the COALESCE() mysql function to replace the null with a valid value.

But since zero is no longer a valid date you can't use COALESCE(a.mydate,0) as I found suggested elsewhere on t'internet.

The minimum valid MySQL date is '1000-01-01 00:00:00' so my solution is this:

Assuming 'mytable' has a column 'last_seen' which I want to update a row with id = $id with a '$newdate' if $newdate is greater than the existing value in a.last-Seen. Assume newdate is a string as might be returned from a calendar form control.

$query = 'UPDATE `#__mytable`  AS a 
  SET `last_seen` =  '.$db->quote($newdate).'
  WHERE a.id  = $id 
  AND COALESCE(a.last_seen, STR_TO_DATE("1000-01-01","%Y-%m-%d"))
    < STR_TO_DATE("'.$newdate.'","%Y-%m-%d")';

In this case I'm not checking the time part. There may be a better way...

6
  • Why not quote() $newdate in all places? $id should be cast as an int if not quote()'ed. When writing a query that accesses only one table, you don't need to assign an alias -- there is no advantage. If you are going to backtick quote in a Joomla query, that is what quoteName() is for. Jun 3, 2022 at 7:58
  • because both should have already been validated in the preceding lines - this is only a fragment for illustration and I don't see the point of duplicating effort - but I take your point that a fragment should be complete in itself and illustrate good practice which would include quoting stuff in a query string that hasn't already been checked (eg if it could be coming direct from user input) - I suppose this begs the question of why I bothered to quote $newdate in the first instance - yep, no sensible answer to that :-)
    – RogerCO
    Jun 3, 2022 at 18:54
  • actually looking at it I see the second $newdate is wrapped in double quotes by the enclosing string anyway and if $newdate value was, eg,2022-06-02 then STR_TO_DATE(" ' 2022-06-02 ' " ...) which is what it would be if db quoted would give #1411 - Incorrect datetime value: ' ' 2021-06-02 ' ' for function str_to_date (NB spaces added between the quotes for clarity)
    – RogerCO
    Jun 3, 2022 at 19:03
  • Do you mean that this fails? $query = "UPDATE #__mytable  SET last_seen = " . $db->q($newdate) . "  WHERE id = " . (int) $id . "  AND COALESCE(last_seen, STR_TO_DATE('1000-01-01','%Y-%m-%d')) < STR_TO_DATE(" . $db->q($newdate) . ",'%Y-%m-%d')"; Jun 3, 2022 at 23:57
  • Nope, I'm using single quotes for the overall query string, the double quotes are embedded within the outer single quotes. Your version is using double quotes on the outside and $db->quote() within and is equivalent to what I have. If you simply replace the $newdate with $db->quote($newdate) in mine then you would get an error. I simply prefer mine because I try not to make unnecessary function calls and I think using double quotes where they within the query string is more readable. If $newdate is raw user input then yes, use $db->quote()
    – RogerCO
    Jun 4, 2022 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.