Showing posts with label innodb. Show all posts
Showing posts with label innodb. Show all posts

Tuesday, November 22, 2016

Codeigniter Transaction queries creation


Introduction on Transaction


A transaction is a logical unit of work that contains one or more SQL statements.
Transactions are atomic units of work that can be committed or rolled back.
When a transaction makes multiple changes to the database, either all the changes succeed
when the transaction is committed, or all the changes are undone when the transaction is rolled back.

A transaction begins with the first executable SQL statement.
A transaction ends when it is committed or rolled back, either explicitly
with a COMMIT or ROLLBACK statement or implicitly when a DDL (Data Definition Language (DDL) is used to manage table and index structure and CREATE, ALTER, RENAME, DROP and TRUNCATE statements are to name a few data definition elements) statement is issued.

Achieve the same in codeigniter using below code


$this->db->trans_begin();


//insert transaction
$i_data = array(
      'user_id'     => $app['opp_id']
    , 'amount'      => $app['total']
    , 'type'        => 1
    , 'feb_bal'     => 1
    , 'note'        => 'Earnings'
);

$this->db->insert('transaction', $i_data);
$tran_id = $this->db->insert_id();

//insert 2
$c_data = array(
      'oppt_opp_id' => $app['id']
    , 'status'      => 0 //closed
);
$this->db->insert('progress', $c_data);

//insert 3
$e_data = array(
      'tran_id' => $tran_id
    , 'app_id'  => $app['id']
    , 'type'    => 1
    , 'status'  => 2
);
$this->db->insert('earn_spend', $e_data);


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

Note: To achieve the transaction statements all tables engine should be in innodb