how to use transactions in codeigniter for commit and rollback based on data change?

Question

I want to learn how I could use MYSQL TRANSACTIONS.

I have use case where I have two tables. Let say Table1 and Table2. Now I insert some data in Table1 and the insert id I get from that table I want to insert it into table 2.

If values are successfully inserted in Table1, and during the insertion of Table2 values if any error occurs I want to delete the values from Table1 as well, as the query for my Table2 was not successful.

I just want to understand how this could be done using Codeigniter. How to COMMIT or ROLLBACK as per need.

Please help me with some sample code to understand.

Update

I also referred the Codeigniter UserGuide. But I did not understood the concept of Running Transactions manually What does this mean ? As mentioned above I want to do something like trigger which is automatic, I mean if my query fail I want it to ROLLBACK whatever it did, using Codeigniter.

Code:

$this->db->trans_begin();
$data = $this->Product_m->array_from_post(array('name','description'));
$this->Product_m->save($data,$id);
$pid = $this->db->insert_id();

$num_of_license = $_POST['license'];

$this->Product_m->create_product($pid,$num_of_license);
    if ($this->db->trans_status() === FALSE)
    {
            $this->db->trans_rollback();
    }
    else
    {
            $this->db->trans_commit();
    }

Now in this case I tried doing this:

This statement $this->Product_m->create_product($pid,$num_of_license); inserts data based on the previous save() method, Now suppose some error occurs during create_product() method. Then I want to rollback. I want to delete the record that save() method did.

Possible duplicate of [Codeigniter Transactions](http://stackoverflow.com/questions/15224826/codeigniter-transactions) — dhruv jadia, Feb 09 '17 at 05:09
it's not a duplicate he just asked for a reference of transaction done in codeigniter — Vimal, Feb 09 '17 at 05:14
@dhruvjadia that question i have already referred but it is specifically regarding his code, I just wanted to understand how i could use it in different ways, Also i have mentioned I want to "Learn" — Rajan, Feb 09 '17 at 06:00

Hikmat Sijapati · Answer 1 · 2017-02-09T05:08:41.400

18

Use the following steps.

1.Begin your transaction using $this->db->trans_begin();.

2.Performs queries.

3.Check Transaction status using $this->db->trans_status().

4.If status is true commit transaction using $this->db->trans_commit();.

5.If status is false rollback transaction using $this->db->trans_rollback();.

$this->db->trans_begin();

$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');

if ($this->db->trans_status() === FALSE)
{
        $this->db->trans_rollback();
}
else
{
        $this->db->trans_commit();
}

For more see docs Codeigniter Transaction

edited Feb 09 '17 at 05:08

answered Feb 09 '17 at 05:03

Hikmat Sijapati

6,869
1
9
19

could you explain me with this use case scenario where i insert a record and based on that record insert another record, but while inserting my second record in second table if error occurs i want to delete even the first record that i inserted – Rajan Feb 09 '17 at 06:05
Will $this->db->trans_rollback(); delete all records under this transaction? – Rajan Feb 09 '17 at 06:07
yea ..all the queries performed after `trans_begin` will be roll backed. Did you try anything according to above steps. – Hikmat Sijapati Feb 09 '17 at 06:10
could please check the updated question I have tried using this but when My second query fails it does not roll back the save method's query! How do i do it – Rajan Feb 09 '17 at 06:13
Yes i did try, But the rollback is not working suppose i have a db error in my second method, so i want to roll back, i want to delete what save() method inserted – Rajan Feb 09 '17 at 06:15
rather than calling model for saving data.use queries at controller. – Hikmat Sijapati Feb 09 '17 at 06:17
OKay let me try – Rajan Feb 09 '17 at 06:24

how to use transactions in codeigniter for commit and rollback based on data change?

1 Answers1