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.

Solution

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