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.
checked_out_time
DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' so I've not had this issue.