Single transaction on multiple model function with CodeIgniter

Question

I need to insert into 2 tables if anything goes wrong while inserting in any of the table I want to rollback commited queries.

I wrote queries inside controller for example:

   $this->db->trans_start();
   $this->db->insert_batch('market_users_mapping', $marketData);
   $this->db->insert_batch('maincategory_users_mapping', $maincategoryData);
   $this->db->trans_complete();
   if ($this->db->trans_status() === FALSE) {
       throw error
   }

This works perfectly. But I think it's not good practice to write queries inside controller. So I did this, called model function and I wrote those insert_batch queries in respective model function.

$this->db->trans_start();
$this->maincategory_model->function_name()
$this->market_model->function_name(); 
$this->db->trans_complete();
if ($this->db->trans_status() === FALSE) {
    throw error
    `enter code here`
}

but this didnt work as expected

ya its not working as expected i.e no roll back on committed queries. — bhanushalimahesh3, Sep 17 '15 at 09:47

Tpojka · Accepted Answer · 2015-09-17T12:37:31.920

5

You changed places of queries in those examples regarding names in case it matters. I think that you can't have tied transactions between different methods (your second example). But you can and should set your DB related code to model.

So make those queries in model:

// controller code
$this->db->trans_start();
$this->maincategory_model->first_function($maincategoryData);
$this->market_model->second_function($marketData); 
$this->db->trans_complete();
if ($this->db->trans_status() === FALSE) {
    throw error
        `enter code here`
} 


// Maincategory_model code

public function first_function($maincategoryData)
{
    return $this->db->insert_batch('maincategory_users_mapping', $maincategoryData);
}

// Market_model code

public function second_function($marketData)
{
    return $this->db->insert_batch('market_users_mapping', $marketData);
}

edited Sep 17 '15 at 12:37

answered Sep 15 '15 at 22:44

Tpojka

6,996
2
29
39

I want to put both the insert queries in different models and if any insert query fails in any of the model the other model's committed queries should be rolled back – bhanushalimahesh3 Sep 17 '15 at 10:14
Make return in model method: `public function second($data) { return $this->db->insert_batch('table_name', $data);}` and iclude it this way in controller or where you make transaction (i.e. `$this->other_model->second($maincategoryData);` inside transaction). – Tpojka Sep 17 '15 at 10:33
tpojka:@tpojka Thanks it worked :). I didnt wrote return in model function previously. But can you explain it how return change the flow?? – bhanushalimahesh3 Sep 17 '15 at 11:59
Return means you are dedicating that value to function (in this case of OOP to method) or that method/function will end with anything returned in it. It's like you are adding the value to the variable but variable is function name(with parameters if mandatory or how ever it need to look like). [Basics](http://php.net/manual/en/function.return.php). Check [this explanation](http://stackoverflow.com/questions/9387765/what-is-the-difference-between-php-echo-and-php-return-in-plain-english) too. – Tpojka Sep 17 '15 at 12:22
Does this code also works with create, alter and drop function. Because I heard that this way doesn't work for stored function. – user254153 Oct 04 '15 at 05:30
@user254153 Heard? Be kind and test so you could give us info about it. – Tpojka Oct 04 '15 at 05:35
Its misconcept. I tried and it didn't worked and found the problem in this link . https://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html – user254153 Oct 04 '15 at 05:38

Keval Rathi · Answer 2 · 2015-09-17T10:10:28.753

0

First shift your db related operation in module and then start transaction.

Sample code in module,

 First module :
function insert_data_market_maincategory($marketData,$maincategoryData)
{
   $status = TRUE;
   $this->db->trans_start();
   $this->db->insert_batch('market_users_mapping', $marketData);
   $this->second_module_name->maincategoryData($maincategoryData)
   $this->db->trans_complete();
   if ($this->db->trans_status() === FALSE) {
     $status = FALSE;
   }
   return $status;
 }


  second module :
  function maincategoryData($data)
  {
    $this->db->insert_batch('table_name', $data);
  }

and your controller call this function

sample code in controller,

function inser_user_data()
{
   $result = $this->module_name->maincategoryData($marketData,$maincategoryData)
   if($result == FALSE)
   {
       throw error
       `enter code here`
   }
   else
   {
      //data inserted successfully
   }
}

edited Sep 17 '15 at 10:10

answered Sep 16 '15 at 04:59

Keval Rathi

978
6
21

keval-rathi:@keval-rathi can we put both queries in different model as I asked in question. Maincategory's insert query in maincategory model and market's insert in market model. – bhanushalimahesh3 Sep 17 '15 at 09:52
Yes you can put both query in different module – Keval Rathi Sep 17 '15 at 09:55
You have to load second module in first module. – Keval Rathi Sep 17 '15 at 09:55
in second model too there will be a transcation ??? Can you give me sample code or eg. – bhanushalimahesh3 Sep 17 '15 at 10:02
keval-rathi I think your way and my second way are same ,only the difference is You are calling model from model and me from controller. I just wrote return in model as suggested by tpojka with my second method and it worked. Thanks :) – bhanushalimahesh3 Sep 17 '15 at 12:06

score 0 · Answer 3 · answered Oct 09 '16 at 20:05

I am using CI 3 and I can use single transaction on multiple model in Controller. I have tried to insert error data to test if rollback or not, the transaction is successfully rollback.

I did not use Tpojka's anwser but my model methods return true or false. Everything seems okay.

Single transaction on multiple model function with CodeIgniter

3 Answers3