Welcome Guest, Not a member yet? Register   Sign In
Codeigniter transactions with multiple functions
#1

I need to add a product in the "products" table, and I get the added product ID and then add the details of that product in the table "product details"

In the official page of Codeigniter they show how to do this but without using functions, eg:

PHP Code:
$this->db->trans_start(TRUE); // Query will be rolled back
$this->db->query('AN SQL QUERY...');
$this->db->trans_complete(); 

I need to use functions and still maintain integrity that if one of the two queries fails then the records are not added, I have prepared the following code and I want to know if in this way Codeigniter will be able to detect if the query fails or not to undo the records Or if there is another best practice or other way of doing it.


PHP Code:
// Instead of using the $this->db->query('AN SQL QUERY...'); I am using a function:
public function init_product()
{
 
   $this->db->trans_begin();
 
   $this->new_product();
 
   $this->new_product_details();

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


PHP Code:
// Adding the product and returning the aggregate product ID
public function new_product()
     
    $data 
= array(            
        
'name' => $this->input->post('name'),
 
       'details' => $this->input->post('details'),
 
       'price' => $this->input->post('price')
 
   );
 
   $this->db->insert('products'$data);
 
   if($this->db->affected_rows()) {            
        return $this
->db->insert_id();
 
   }
 
   return false;


PHP Code:
// Adding Product Details
public function new_product_details()
     
    $data 
= array(            
        
'product_id' => $this->new_product(),
 
       'user_id' => $this->session->id_user
    
);
 
   $this->db->insert('products'$data);
 
   if($this->db->affected_rows()) {            
        return true
;
 
   }
 
   return false;



As you specify, I need to know if this way is functional even though I do not follow the example as done in Codeigniter, and if using these functions Codeigniter can detect whether or not queries or insertions in the database fail, also if they can give me Some better example.

Thanks for your help
Reply
#2

Looks like it should work fine. CodeIgniter doesn't care where you start and finish your transaction.
Reply
#3

(06-21-2017, 07:59 AM)skunkbad Wrote: Looks like it should work fine. CodeIgniter doesn't care where you start and finish your transaction.

What does it mean, from the moment the transaction starts until it ends, regardless if I use functions, CI is able to detect that?

I've seen this example in a POST comment, it looks like it may also be an option, but I want to be sure to do this.

Example and the code:

http://www.itgo.me/a/9149152029127615409...ansactions

PHP Code:
$this->db->trans_begin();
 
 $rst1 $this->utils->insert_function($data);
 
 $rst2  $this->utils->update_function2($test);
if(
$this->db->trans_status() === FALSE || !isset($rst1) || !isset($rst2)){
 
  $this->db->trans_rollback();
}else{
 
  $this->db->trans_commit();

Reply
#4

Yes. Think about code execution. Just because code execution goes in and out of functions doesn't mean that the DB class isn't going to handle what it's told to do. Functions/methods do limit variable scope, help organize your code, and have other benefits. Functions/methods aren't going to interfere with your transactions.
Reply
#5

(06-21-2017, 09:04 AM)skunkbad Wrote: Yes. Think about code execution. Just because code execution goes in and out of functions doesn't mean that the DB class isn't going to handle what it's told to do. Functions/methods do limit variable scope, help organize your code, and have other benefits. Functions/methods aren't going to interfere with your transactions.

Perfect, thank you very much.
Reply
#6

(06-21-2017, 09:04 AM)skunkbad Wrote: Yes. Think about code execution. Just because code execution goes in and out of functions doesn't mean that the DB class isn't going to handle what it's told to do. Functions/methods do limit variable scope, help organize your code, and have other benefits. Functions/methods aren't going to interfere with your transactions.

I have performed the tests but if an insert fails all queries are executed and does not undo the queries that have to be undone in order to maintain the integrity of the data.

PHP Code:
<?php

public function init_invoice()
{
 
   $products self::products_related_invoice();
 
   $total '0';

 
   if(!$products) {
 
       return;
 
   }

 
   foreach ($products as $value) {
 
       $total $total $value['price'];
 
   }

 
   $this->db->trans_start();
 
   $id_invoice             self::add_new_invoice($total);
 
   $add_invoice_details    self::add_invoice_details($id_invoice$products);

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

public function 
add_new_invoice($total)
{
 
  $data = array(            
        
'id_user'           => $this->session->user_id,
 
       'id_invoice_status' => '1'
 
       'total' => $total
 
       'auto_create' => 
    
);
 
   $this->db->insert('bill'$data);
 
   if($this->db->affected_rows()) {
 
       $id_invoice $this->db->insert_id();       
        return $id_invoice
;
 
   }
 
   return false;
}

public function 
add_invoice_details($id_invoice$productos)
{
 
   /**
     * Default var
     */
 
   $status false;

 
   foreach ($productos as $key => $value) {
 
       $query $this->db->simple_query('
            INSERT INTO '
.$this->db->dbprefix('bill_details').'
                   (`id_invoice`,`id_product`,`created_by`,`modified_by`) 
            VALUES (
                '
.$this->db->escape($id_invoice).',
                '
.$this->db->escape($value['id']).' ,
                '
.$this->db->escape($this->session->user_id).'
                '
.$this->db->escape($this->session->user_id).'
            )
        '
);

 
       if(!$query) {
 
           $status true;
 
       }
 
   }

 
   if($status == false) {
 
       return true;
 
   }

 
   return false;
}

public function 
products_related_invoice()
{
 
   $query $this->db->select('id, price')
 
                     ->from('products')
 
                     ->where('status''1'
 
                     ->where('related_invoice''1'
 
                     ->get();
 
   if($query->num_rows() > 0) {
 
       
        $data 
$query->result_array();           
        return $data
;
 
   }
 
   return false;

Reply
#7

Could calling this be causing the problem?

PHP Code:
$this->db->affected_rows() 

Maybe the call to affected rows is causing the query to be commited?
Reply
#8

Also make sure your tables are InnoDB. If they are MyISAM then transactions don't work.
Reply
#9

(06-21-2017, 02:22 PM)skunkbad Wrote: Also make sure your tables are InnoDB. If they are MyISAM then transactions don't work.

All the tables are InnoDB and still Codeigniter transactions are confusing, because even when I provoke the CI error you do not register them and the queries are made.
Reply
#10

You can use TRANSACTION or INSERT_LAST_ID. When you want to insert data in multiple tables those two are useful. I can't provide a sample snippet for better understanding but I am sure you can find huge samples when you do a research. The difference between the 2 that I don't know.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB