Tarek Tarek - 4 months ago 46
MySQL Question

Codeigniter Transactions

I'm using Codeigniter transactions :

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->trans_complete();


This works fine , the problem I have is that inside the trans_start and trans_complete I'm calling other functions and those functions deals with database so they contains inserts and update and some deletes ... ex:

$this->db->trans_start();
$this->utils->insert_function($data);
$this->utils->update_function2($test);
$this->db->trans_complete();


Now if those functions are executed and some errors occurs codeigniter wont do a rollback.

What is the best way to deal with such issue ?

The only solution if i have in mind is to return an error from those functions and inside those function add (trans_stat and trans_complete) And if it returns an error test an do $this->db->trans_rollback

ex:

$this->db->trans_start();
$result = $this->utils->insert_function($data);
if($result === false){
$this->db->trans_rollback();
}
$this->db->trans_complete();


Is there a better way of doing this ?

Update 1:

As requested a sample of the external function i'm calling :

// insert_function contains

$rec = array(
'numero' => $numero,
'transaction_id' => $id,
'debit' => $product_taxes['amount_without_taxes'],
'date' => $data['date_transaction'],
);
$this->addExerciceAccountingRecords($rec);

and addExerciceAccountingRecords contains

function addExerciceAccountingRecords($records) {
$this->db->insert('transactions_exercices', $records);
}

Answer

Using transactions means support databases to insert data safely. So in Codeigniter we write every database related functions in the Model not in Controller.. And in your second code(which is not working)you have pointed model on there.(utils). So simple I'm sure this will not work. Because its not a insert data with model and Controller parallel. Transaction should be coded in the Model(I will write in Model in my answer).


Load this stuffs as well

  1. Database Library
  2. Model Class
  3. URL helper
  4. Session

Assumptions

In your code you have used $data and $test as array. So i assume there is two array for inserting and updating data.


Your data sets

$data = array(
   'title' => 'My title' ,
   'name' => 'My Name' ,
   'date' => 'My date'
);

$id = 007;
$test = array(
   'title' => $title,
   'name' => $name,
   'date' => $date
);

Your Code

$this->db->trans_start(); # Starting Transaction
$this->db->trans_strict(FALSE); # See Note 01. If you wish can remove as well 

$this->db->insert('table_name', $data); # Inserting data

# Updating data
$this->db->where('id', $id);
$this->db->update('table_name', $test); 

$this->db->trans_complete(); # Completing transaction

if ($this->db->trans_status() === FALSE) {
    # Something went wrong.
    $this->db->trans_rollback();
    return FALSE;
} 
else {
    # Everything is Perfect. 
    # Committing data to the database.
    $this->db->trans_commit();
    return TRUE;
}

Notes

  1. By default Codeigniter runs all transactions in Strict Mode. When strict mode is enabled, if you are running multiple groups of transactions, if one group fails all groups will be rolled back. If strict mode is disabled, each group is treated independently, meaning a failure of one group will not affect any others.