2

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?

  • Of your 25 questions on JSX, you have only accepted 2. Please endeavor to progress your questions toward a system-recognized solution. This will "turn off" the Community page bumping and inform future researchers that a working solution has been delivered. – mickmackusa Jul 12 '18 at 11:30
2

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.

  • @dev-m is there anything that you would like me to explain further? – mickmackusa Jun 24 '18 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.