Update a database field with Joomla UpdateObject method with a calculated field from same table

Question

Right to the point.

I need to update a field in the database using the field to calculate the new value first.

E.g of fields: https://i.sstatic.net/FADH6.jpg

Now I am using the Joomla updateObject function. my goal is to take the "spent" value from the DB table without using a select statement.

Then I need to calculate a new value with it like (spent + 10.00) and update the field with the new value. Check out the code below:

// Create an object for the record we are going to update.
$object = new stdClass();

// Must be a valid primary key value.
$object->catid = $item['category'];
$object->spent = ($object->spent - $item['total']);

// Update their details in the users table using id as the primary key.
$result = JFactory::getDbo()->updateObject('#__mytable', $object, 'catid'); 

The bit which i need to make the calculation on is

$object->spent = ($object->spent - $item['total']);

I realise I can use a seperate insert statement but I am wondering if there is a better way. Any help is much appreciated.

It needs to work like this, WITHOUT THE SELECT (working example)

$query = $db->getQuery(true);
$query->select($db->quoteName('spent'));
$query->from($db->quoteName('#__mytable'));
$query->where($db->quoteName('catid')." = ". $item['category']);

// Reset the query using our newly populated query object.
$db->setQuery($query);
$oldspent = $db->loadResult();

// Create an object for the record we are going to update.
$object = new stdClass();

// Must be a valid primary key value.
$object->catid = $item['category'];
$object->spent = ($oldspent - $item['total']);

// Update their details in the users table using id as the primary key.
$result = JFactory::getDbo()->updateObject('#__mytable', $object, 'catid');  

Answer 1

The sticking point with trying to use updateObject('#__mytable', $object, 'catid'); is that your query logic needs to reference the column name in the calculation to assign the "difference" as the new value. The raw mysql query syntax to update a column value with the value minus another value is like:

"`spent` = `spent` - {$item['total']}"

updateObject() will convert spent - {$item['total']} to a literal string, the database will expect a numeric value, so UPDATE results in a 0 value recorded. In other words, $db->getAffectedRows() will give you a positive count and there will be no errors generated, but you don't get the desired mathematical action.

The workaround is to discard updateObject() as a tool and build an UPDATE query without objects -- don't worry it's not too convoluted. I'll build in some diagnostics and failure checking, but you can remove whatever parts that you wish.

I have tested the following code to be successful on my localhost:

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->update($db->quoteName('#__mytable'))
                ->set($db->quoteName("price") . " = " . $db->qn("price") . " - " . (int)$item['total'])
                ->where($db->quoteName("catid") . " = " . (int)$item['category']);
    echo $query->dump();  // see the generated query (but don't show to public)
    $db->setQuery($query);
    $db->execute();
    if ($affrows = $db->getAffectedRows()) {
        JFactory::getApplication()->enqueueMessage("Updated. Affected Rows: $affrows", 'success');
    } else {
        JFactory::getApplication()->enqueueMessage("Logic Error", 'error');
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // never show getMessage() to public
}

Here is a StackOverflow page discussing the mysql subtraction logic: update a column by subtracting a value