1

I'm inserting an object in a database table using joomla 4, as per joomla docs:

$toInsert = new stdClass();
$toInsert->update_timestamp = 'NOW()';
$toInsert->id = null;

$db = Factory::getContainer()->get('DatabaseDriver');
$db->insertObject('table', $toInsert, 'id');

However, that results in

Incorrect datetime value: 'NOW()' for column 'update_timestamp' at row 1

How do I use NOW() using insertObject()?

2 Answers 2

3

If you are going to insert a row into the database using an object, you are going to need to pass in values that do not leverage SQL functions. insertObject() will treat the object's payload as literal values for security/stability.

Another bit of magic is that you must not try to declare a null value in your object's payload. Instead, you should omit the property from the object entirely if you wish NULL to be added for a given database table column.

I don't see anywhere in the insertObject() method's argument signature to allow SQL keywords/functions to be rendered. Unless someone else knows better than me, this means you should use PHP's datetime capabilities to populate the current time.

That said, I think my best advice is to set the column's default value to the current datetime. This way you never need to mention the column when you build an insert object.

A "TRIGGER" can be helpful if you want update update_timestamp after every change made.

Maybe these will inspire:

1

I ended up using arrays to compose the sql query, as per this alternative mentioned in the joomla docs, which works fine:

$columns = array('update_timestamp');
$values = array('NOW()');
$db = Factory::getContainer()->get('DatabaseDriver');
$query = $db->getQuery(true); 
$query->insert($db->quoteName('table'))
      ->columns($db->quoteName($columns))
      ->values(implode(',', $values));
2
  • Since you are imploding a hardcoded, single-element array, you can just write ->values('NOW()') Commented Mar 3, 2023 at 21:14
  • @Jimmy if this has resolved your original question please be sure to mark it as the accepted answer.
    – Terry Carter
    Commented Mar 13, 2023 at 18:22

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.