Correct ways to verify update query is successful under conditions

Question

I was trying to find out correct ways to verify db update query is successful but some problems i am fetching. Even if update query($db->execute()) is not done/row does not exists.... but it returns true meaning it was successful which is wrong. $db->getaffectedrows() method has incomplete results (only o and 1). I found a way while debugging the joomla code is that there is a info property in connection object in database object ($db = jfactory:getdbo()), this property tells whether any row matched(data row found in table) by the update query and whether any row changed(updated with new value) or not. So are there any better ways/queries to find if update was indeed successful considering all the conditions that there were rows found, row found but no column value changed, and row found and column value changed....under joomla cms framework?

Answer 1

Here is a basic structure for isolating each outcome.

$id = 2286;    // you can jinput this or whatever
$found = 0;    // establish default value
$affrows = 0;  // establish default value
$db = JFactory::getDBO();
try {
    $select_query = $db->getQuery(true)
                       ->select("COUNT(*)")
                       ->from("#__users")
                       ->where("id = " . (int)$id);
    $db->setQuery($select_query);
    if ($found = $db->loadResult()) { // if a positive count
        $update_query = $db->getQuery(true)
                           ->update("#__users")
                           ->set("block = 1")
                           ->where("id = " . (int)$id);
        $db->setQuery($update_query);
        $db->execute();
        if ($affrows = $db->getAffectedRows()) {  // if a positive count
            JFactory::getApplication()->enqueueMessage("Update Successful: Found: $found; Updated: $affrows");
        } else {
            JFactory::getApplication()->enqueueMessage("Fruitless Update (No Changes): Found: $found; Updated: $affrows", "notice");
        }
    } else {
        JFactory::getApplication()->enqueueMessage("Update Ignored - No Qualifying Rows: Found: $found; Updated: $affrows", "notice");
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage('Query Syntax Error (Select or Update Failed): Found: $found; Updated: $affrow', 'error');
    // $query->dump() . "<br>" . $e->getMessage()  // <-- not to be displayed publicly
}

Above uses no $db->qn() or $db->q() calls because the table name and columns are single words and not "Reserved" mysql keyword words AND the $id value is an integer which can be hardcast with (int) for security reasons.

This assumes you don't want to perform an INSERT when the row does not exist and check those outcomes.