2

We got 2 tables where one table is parent and another table as a child with primary and foreign key reference.

Assume table1 is the parent and table2 is the child as below.

table1:

id  <- primary key of table1
name  <- name field

table2:

id  <- primary key of table2 auto increment
par_id  <- foreign key referring table1 default to NULL
position  <- posiiton field

The code snippet below is to update an existing record in the child table with par_id as NULL

$recObj = new stdClass ();
$recObj->id = '1';
$recObj->par_id=null
$recObj->position="test position"

$db->updateObject('#__table2', $recObj, 'id');

When the above code executes, it is not updating the par_id to null. Instead the old value remains.

2 Answers 2

2

Found a way by adding an another parameter as below:

$db->updateObject('#__table2', $recObj, 'id', true);

The 4th parameter determines whether null values can be used or not. Default this will be set as false.

https://docs.joomla.org/API17:JDatabase::updateObject

1

The way it should be done

For now (until they introduce full entities support somewhere in Joomla! 4.x) it is better to use JTable to store "entities". In that way you can simply set $updateNulls parameter in JTable::store() method o true. It also simplifies a lot of CRUD and validate operations making your programming faster. You can read more in a JTable documentation or look for tables usage in core components.

How to perform nulls update in updateObject()

The second method is to provide the fourth parameter to JDatabase::updateObject() method the way you did. As far as I remember it is used in JTable::store() internaly.

If there is no API method for your case

As a last resort you can always run a direct query:

use Joomla\CMS\Factory;

$db = Factory::getDbo();
$q = $db->getQuery(true);
$q
    ->update('#__table2')
    ->set('b = NULL')
    ->where('id=1');
$db->setQuery($q);
$db->execute();
2
  • "The way it should be done" does not work in all scenarios. In my case, setting $updatenulls to true does not update the value in the database (Maria 10.4). Joomla's (3.10) JTable uses the updateObject function of JDatabaseDriver. This function sets the null values to a 'NULL' String and passes the query to the execute function of the used db driver (JDatabaseDriverMysqli for me). The driver calls mysqli_query which does the actual update. Maybe some further configuration of the db server is needed to automatically convert 'null' to null. But I don't know that for sure.
    – aProgger
    Oct 12, 2022 at 11:45
  • @aProgger I have extensions working on MariaDB for years already and I did not have such issue with null values. So it might be the case it's your particular server configuration that's causing the issue. Oct 12, 2022 at 15:24

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.