affected_rows not compatible with transactions

[eluser]kylehase[/eluser]
[url="http://ellislab.com/codeigniter/user-guide/database/helpers.html"]The user guide query helper page[/url] says this about affected_rows()

In MySQL "DELETE FROM TABLE" returns 0 affected rows. The database class has a small hack that allows it to return the correct number of affected rows.

This doesn't seem to be the case when using transactions.

Here's a simplified version of my code:

Code:

$this->db->trans_begin();
$this->db->where('companyID', $companyID);
$this->db->delete($tables);
if ($this->db->trans_status() === FALSE)
{
      log_message('error', "xmlrpc::_delete_company\tDB error: ".$this->db->_error_message());
      $this->db->trans_rollback();
      return $this->xmlrpc->send_error_message('010706',"DB delete error.");
}
$this->db->trans_commit();
$affectedRows = $this->db->affected_rows();
// at this point $affectedRows is always 0.

Anyone else notice or confirm this?

[eluser]gtech[/eluser]
hello,

Code:

<?php
class Home extends Controller   {
  function Home(){
    parent::Controller();
  }

  function dbtest() {
    $this->db->trans_begin();
    $this->db->where('CustomerName', 'sdfsdf');
    $this->db->delete('Customers');

    //works here
    $affectedRows = $this->db->affected_rows();
    echo $affectedRows;

    if ($this->db->trans_status() === FALSE)
    {
      echo "DB error: ".$this->db->_error_message();
      $this->db->trans_rollback();
    }
    $this->db->trans_commit();

    // does not work here
    /*
    $affectedRows = $this->db->affected_rows();
    echo $affectedRows;
    */
  }
}
?>

yep confirmed.. It looks like affected rows only works before the commit. the above code works for my database.

I have a table called customers which I inserted 4 entries in with the CustomerName set to 'sdfsdf' and the affected rows count is set to '4' inside the transaction and '0' after the commit.

if you have multiple queries that affect rows within a transaction it looks like you will need to keep a counter and reset it if the transaction rollsback.

[eluser]kylehase[/eluser]
Thanks gtech. I added a counter. Not the best solution but it works.