Codeigniter transaction doesn't roll back

[eluser]alex646[/eluser]
Hey guys,


I would appreciate if somebody could clarify this for me:

I have a simple transaction:

Code:

$id = $data['id'];
        $name = $data['name'];
        $value = $data['value'];
        $apikey = $data['apkey'];
        $now = date("Y-m-d H:i:s");
      
  //test transactions here
        $this->db->trans_start();
        $this->db->query("UPDATE data SET feed_name ='$name', feed_value='$value' WHERE feed_id = '$id'");
        $this->db->query("UPDATE feeds SET date_updated ='$now' WHERE feed_id = '$id' AND apikey = '$apikey'");
        
        $this->db->trans_complete();
       return $this->db->trans_status() === FALSE ? FALSE : TRUE ;
        //test transactions here
    }

The problem with this transaction is that data is updated in data table but fails to update in feeds table. Should this transaction rollback and not update only one of the tables?

Thanks!!!

[eluser]jonez[/eluser]
When you say the second query fails do you mean 0 rows were updated or the query produces an error? I don't believe transactions will catch an empty update since it's considered a successful query. A query inside the transaction has to fail and throw and error to trigger the rollback.

What you could do instead is either do a select check above the updates and only apply them if you know there are records. Or do both queries inside a transaction, check rows effected after and if it's 0 call $this->db->trans_rollback( ) to undo the first query. Transactions use a bit of overhead so it's probably better to check first and only use them to catch errors vs applying conditional logic.

You should use Active record or parametrized queries, your code is susceptible to SQL injections.

Active record:

Code:

$this->db->where( 'feed_id', $id );
$this->db->update( 'data', array( 'feed_name' => $name, 'feed_value' => $value ) );

$this->db->where( array( 'feed_id' => $id, 'apikey' => $apikey ) );
$this->db->update( 'feeds', array( 'date_updated' => $now ) );

Parametrized:

Code:

$this->db->query( 'UPDATE data SET feed_name = ?, feed_value= ? WHERE feed_id = ?', array( $name, $value, $id ) );
$this->db->query( 'UPDATE feeds SET date_updated = ? WHERE feed_id = ? AND apikey = ?', array( $now, $id, $apikey ) );

[edit]
Just noticed this, could it be the problem?

Code:

$apikey = $data['apkey']; //apkey not apikey?

[eluser]alex646[/eluser]
Thanks for the info!

Actually apkey is correct, I just wanted to name them different : )

I appreciate your suggestions, will try them now!


Thanks!