How do I get the MySQL affected rows using the Magento resource?

Question

$conn  = Mage::getSingleton('core/resource')->getConnection('core_write');
$sql = "UPDATE ...";
$result = $conn->query($sql);

How do I get the number of affected rows - i.e. mysql_affected_rows();

Answer 1

Instead of using the $conn->query() you could use $conn->update(). This takes the following parameters:

1. The table that you want to update,
2. An array of column value pairs,
3. The where clause of your statement

It will return the number of affected rows. This is also true for the delete and different insert functions. You can find the basics of the functions under Varien_Db_Adapter_Interface. Or for a good example have a look at decrementProducts in Mage_Tag_Model_Resource_Tag

EDIT

If you want to perform an insert on duplicate key update then use the function insertOnDuplicate. This takes the following parameters:

1. The table that you want to insert into,
2. An array of column value pairs,
3. An array of columns to update,

A good example of this can be found in the saveSelectionPrice function under Mage_Bundle_Model_Resource_Selection

Answer 2

Try with this.

  <?php
    $sql = "UPDATE ...";
    $result = $read->fetchAll($sql);
    $total_rows = count($result);
    ?>