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 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 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 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 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 at 0:53
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 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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