Using JFactory::getDbo()->insertObject with on duplicate key update

Question

How to use:

JFactory::getDbo()->insertObject('#__card_bonus', $object);

with on duplicate key update ?

tvanc · Answer 1 · 2015-12-28T01:21:47.537

You have a few options:

1) Check for an entity id. This is my preferred option, because it only uses a single query, is reusable for any object, and is database agnostic - meaning it will work on whichever DBMS you choose, whereas the other two options are exclusive to MySQL.

if (isset($object->id)) {
  $db->updateObject('#__card_bonus', $object);
}
else {
  $db->insertObject('#__card_bonus', $object, 'id');
}

I often create an abstract model with a save(stdClass $object) method that does this check so I don't have to duplicate it.

2) Write your own query using the MySQL ON DUPLICATE KEY UPDATE syntax, which is a proprietary extension to the SQL standard, that you have demonstrated understanding of.

3) Write your own query using MySQL's proprietary REPLACE INTO extension.

Rudolf Pipopulo · Answer 2 · 2017-08-11T06:42:48.423

<?php

$jarticle                   = new stdClass();
$jarticle->id               = 1544;
$jarticle->title            = 'New article';
$jarticle->alias            = JFilterOutput::stringURLSafe($jarticle->title);
$jarticle->introtext        = '<p>re</p>';
$jarticle->state            = 1;
$jarticle->catid            = 13;
$jarticle->created_by       = 111;
$jarticle->access           = 1;
$jarticle->language         = '*';

$db = JFactory::getDbo();
try {
    $query = $db->getQuery(true);
    $result = JFactory::getDbo()->insertObject('#__content', $jarticle);
}
catch (Exception $e){
    $result = JFactory::getDbo()->updateObject('#__content', $jarticle, 'id');
}

I use this method - are not fully satisfied, but ...

or for not object method:

    $query = $db->getQuery(true);
    $columns = array('username', 'password');
    $values = array($db->quote($username), $db->quote($password));
    $query
        ->insert($db->quoteName('#__db_name'))
        ->columns($db->quoteName($columns))
        ->values(implode(',', $values));
    $query .= ' ON DUPLICATE KEY UPDATE ' . $db->quoteName('password') . ' = ' . $db->quote($password);
    $db->setQuery($query);

The problem with this approach is that in the case of an update, you are performing two queries where only one is needed. See my answer for alternative solutions. — tvanc, Dec 28 '15 at 01:23
No one should ever, ever, ever store unencrypted passwords in a database... ever. — mickmackusa, Sep 17 '18 at 12:04

score 0 · Accepted Answer · answered Sep 18 '14 at 06:13

0

JFactory::getDbo()->insertObject('#__card_bonus', $object, $keyName);

The name of the primary key. If provided the object property is updated. Joomla doc ...

answered Sep 18 '14 at 06:13

Rudolf Pipopulo

110
1
10

1

This will update the field of $object with the name $keyName - this WILL NOT update instead of insert in the case of a duplicate key. – tvanc Oct 23 '15 at 02:48
What about the version below? – Rudolf Pipopulo Dec 27 '15 at 23:12
I can confirm what turibe wrote: only the field specified in $keyname (or fields in case $keyname is an array) will be updated in case of duplicate. – giovannim Jan 13 '16 at 10:03
This answer is incorrect. Following is from `insertObject()` documentation `@param string $key The name of the primary key. If provided the object property is updated.` – Ejaz Jul 25 '17 at 15:19

Using JFactory::getDbo()->insertObject with on duplicate key update

3 Answers3