Welcome Guest, Not a member yet? Register   Sign In
How should I use DB Transactions
#1

I would like to be able to use Codeigniter Database Transactions. But I am new to this and I am unsure if have set it correct to what I am after.

What I am after if there is a error while trying to insert data into my insert_userdata() model function then will roll back data from my insert_user() and also insert_userdata() model functions

I have use the Database Transactions on my controller but not sure if that is correct.

Would like some guidance on this thanks.

Controller


PHP Code:
<?php

class Add extends MX_Controller {

    private $error = array();

    public function __construct() {
        parent::__construct();
        $this->load->library('form_validation');
        $this->load->library('upload');
        $this->load->model('admin/user/add_user_model');
    }

    public function index() {
        $this->form_validation->set_rules('username''Username''trim|required');
        $this->form_validation->set_rules('firstname''Firstname''trim');
        $this->form_validation->set_rules('lastname''Lastname''trim');
        $this->form_validation->set_rules('email''Email''trim|valid_email');
        $this->form_validation->set_rules('password''Password''trim');
        $this->form_validation->set_rules('confirm_password''Confirm Password''trim|matches[password]');

        if (($this->form_validation->run()) && $this->upload())  {

            $upload_data $this->upload->data();

            $this->db->trans_start();

            $insert_user = array(
                'status' => $this->input->post('status'),
                'date_added' => date('Y-m-d')
            );

            $set_user_id $this->add_user_model->insert_user($insert_user);

            if ($this->checkinsertedtodb($set_user_id)) {

                $insert_userdata = array(
                    'user_id' => $set_user_id,
                    'username' => ($this->input->post('username')) ? $this->input->post('username') : '',
                    'password' => '',
                    'firstname' => ($this->input->post('firstname')) ? $this->input->post('firstname') : '',
                    'lastname' => ($this->input->post('lastname')) ? $this->input->post('lastname') : '',
                    'email' => ($this->input->post('email')) ? $this->input->post('email') : '',
                    'image' => ($upload_data['file_name']) ? $upload_data['file_name'] : ''
                );

                $userdata_inserted $this->add_user_model->insert_userdata($insert_userdata);

                if (($this->checkinsertedtodb($userdata_inserted)) && $this->db->trans_status()) {
                    
                    redirect
('admin/user/users');
                    
                    $this
->db->trans_complete();
                
                
} else {

                    $this->db->trans_rollback();

                }
            }
            
        
}

        if (isset($this->error['warning'])) {
            $data['error'] = $this->error['warning'];
        } else {
            $data['error'] = '';
        }

        $data['header'] = Modules::run('admin/common/header/index');
        $data['footer'] = Modules::run('admin/common/footer/index');

        $this->load->view('user/add_view'$data);

    }

    public function upload($field 'userfile') {
        $users_dir $this->input->post('username');
        $directory FCPATH 'uploads/users/' $users_dir;

        if (isset($_FILES[$field]) && $_FILES[$field]['size'] > 0) {

            if (is_dir($directory)) {
                $this->error['warning'] = 'This ' $directory ' is all ready created!';
            }

            if (mkdir($directory)) {

                $config['upload_path'] = $directory '/';
                $config['allowed_types'] = 'gif|jpg|png';
                $config['max_size'] = 3000;
                $config['max_width'] = 0;
                $config['max_height'] = 0;
                $config['overwrite'] = true;

                $this->upload->initialize($config);

                if (!$this->upload->do_upload($field)) {    
                    $this
->error['warning'] = $this->upload->display_errors();
                }

            }

            return !$this->error;
            
    
}

    public function checkinsertedtodb($inserted) {
        if (!$inserted) {
            $this->error['warning'] = 'Opps somthing has gone wrong could not insert data!';
        }

        return !$this->error;
    }



Model


PHP Code:
<?php

class Add_user_model extends CI_Model {

    public function insert_user($data) {
        $this->db->set($data);
        $this->db->insert($this->db->dbprefix 'user');
        return $this->db->insert_id();
    }

    public function insert_userdata($data) {
        $this->db->set($data);
        $this->db->insert($this->db->dbprefix 'user_data');
    }




Attached Files
.php   Add_user_model.php (Size: 342 bytes / Downloads: 123)
.php   Add.php (Size: 3.36 KB / Downloads: 97)
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#2

PHP Code:
$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();

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

(09-01-2016, 02:12 AM)wolfgang1983 Wrote: I would like to be able to use Codeigniter Database Transactions. But I am new to this and I am unsure if have set it correct to what I am after.

What I am after if there is a error while trying to insert data into my insert_userdata() model function then will roll back data from my insert_user() and also insert_userdata() model functions

I have use the Database Transactions on my controller but not sure if that is correct.

Would like some guidance on this thanks.

Controller


PHP Code:
<?php

class Add extends MX_Controller {

    private $error = array();

    public function __construct() {
        parent::__construct();
        $this->load->library('form_validation');
        $this->load->library('upload');
        $this->load->model('admin/user/add_user_model');
    }

    public function index() {
        $this->form_validation->set_rules('username''Username''trim|required');
        $this->form_validation->set_rules('firstname''Firstname''trim');
        $this->form_validation->set_rules('lastname''Lastname''trim');
        $this->form_validation->set_rules('email''Email''trim|valid_email');
        $this->form_validation->set_rules('password''Password''trim');
        $this->form_validation->set_rules('confirm_password''Confirm Password''trim|matches[password]');

        if (($this->form_validation->run()) && $this->upload())  {

            $upload_data $this->upload->data();

            $this->db->trans_start();

            $insert_user = array(
                'status' => $this->input->post('status'),
                'date_added' => date('Y-m-d')
            );

            $set_user_id $this->add_user_model->insert_user($insert_user);

            if ($this->checkinsertedtodb($set_user_id)) {

                $insert_userdata = array(
                    'user_id' => $set_user_id,
                    'username' => ($this->input->post('username')) ? $this->input->post('username') : '',
                    'password' => '',
                    'firstname' => ($this->input->post('firstname')) ? $this->input->post('firstname') : '',
                    'lastname' => ($this->input->post('lastname')) ? $this->input->post('lastname') : '',
                    'email' => ($this->input->post('email')) ? $this->input->post('email') : '',
                    'image' => ($upload_data['file_name']) ? $upload_data['file_name'] : ''
                );

                $userdata_inserted $this->add_user_model->insert_userdata($insert_userdata);

                if (($this->checkinsertedtodb($userdata_inserted)) && $this->db->trans_status()) {
                    
                    redirect
('admin/user/users');
                    
                    $this
->db->trans_complete();
                
                
} else {

                    $this->db->trans_rollback();

                }
            }
            
        
}

        if (isset($this->error['warning'])) {
            $data['error'] = $this->error['warning'];
        } else {
            $data['error'] = '';
        }

        $data['header'] = Modules::run('admin/common/header/index');
        $data['footer'] = Modules::run('admin/common/footer/index');

        $this->load->view('user/add_view'$data);

    }

    public function upload($field 'userfile') {
        $users_dir $this->input->post('username');
        $directory FCPATH 'uploads/users/' $users_dir;

        if (isset($_FILES[$field]) && $_FILES[$field]['size'] > 0) {

            if (is_dir($directory)) {
                $this->error['warning'] = 'This ' $directory ' is all ready created!';
            }

            if (mkdir($directory)) {

                $config['upload_path'] = $directory '/';
                $config['allowed_types'] = 'gif|jpg|png';
                $config['max_size'] = 3000;
                $config['max_width'] = 0;
                $config['max_height'] = 0;
                $config['overwrite'] = true;

                $this->upload->initialize($config);

                if (!$this->upload->do_upload($field)) {    
                    $this
->error['warning'] = $this->upload->display_errors();
                }

            }

            return !$this->error;
            
    
}

    public function checkinsertedtodb($inserted) {
        if (!$inserted) {
            $this->error['warning'] = 'Opps somthing has gone wrong could not insert data!';
        }

        return !$this->error;
    }



Model


PHP Code:
<?php

class Add_user_model extends CI_Model {

    public function insert_user($data) {
        $this->db->set($data);
        $this->db->insert($this->db->dbprefix 'user');
        return $this->db->insert_id();
    }

    public function insert_userdata($data) {
        $this->db->set($data);
        $this->db->insert($this->db->dbprefix 'user_data');
    }



I suggest that you use a try catch block around the method calls that you want to include in the transaction. That way you can roll back if any kind of error occurs. Also you should check the success of your database calls for success/failure inside the methods and raise an exception on failure.

PHP Code:
{
 
   try {
 
       $this->db->trans_start();
 
       $set_user_id $this->add_user_model->insert_user($insert_user);
 
       
       $userdata_inserted 
$this->add_user_model->insert_userdata($insert_userdata);

 
      $this->db->trans_commit();
 
    catch (Exception $e) {
 
       $this->db->trans_rollback();
 
       log_message('error'$e->getMessage();
 
   }
 
   $this->load->view('user/add_view'$data);
}

{
 
   /**
      * @throws Exception
      */
    public function insert_user($data) {
        // this one may fail too, so you could do the same as below
 
       $this->db->set($data);
        if (!$this->db->insert($this->db->dbprefix 'user')) {
 
           $error $this->db->error();
 
           throw new Exception('message ' $error['code'] . ' ' $error['message']);
 
       }
        return $this->db->insert_id();
    }

Reply
#4

(This post was last modified: 09-02-2016, 12:55 AM by wolfgang1983.)

I have now done it this way but if there is a error on my file upload section how to roll it back also.

PHP Code:
<?php

class Add_user_model extends CI_Model {

    public function 
insert_user($file_name = array()) {
        
$this->db->trans_begin();

        
$user = array(
            
'status' => $this->input->post('status'),
            
'date_added' => date('Y-m-d')
        );

        
$this->db->set($user);
        
$this->db->insert($this->db->dbprefix 'user');

        
$user_id $this->db->insert_id();

        
$options = [
         
   'cost' => 12,
        ];

        
$hash password_hash($this->input->post('password'), PASSWORD_BCRYPT$options);

        
$userdata = array(
            
'user_id' => $user_id,
            
'username' => $this->input->post('username'),
            
'password' => $hash,
            
'firstname' => $this->input->post('firstname'),
            
'lastname' => $this->input->post('lastname'),
            
'email' => $this->input->post('email'),
            
'image' => ($file_name) ? $file_name '',
            
'country_code' => $this->input->post('country'),
            
'timezone' => $this->input->post('timezone')
        );

        
$this->db->set($userdata);
        
$this->db->insert($this->db->dbprefix 'user_data');

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

        } else {

         
   $this->db->trans_commit();
        } 
    }


Controller

PHP Code:
<?php

class Add extends MY_Controller {

    private 
$error = array();

    public function 
__construct() {
        
parent::__construct();
        
$this->load->library('form_validation');
        
$this->load->library('countries');
        
$this->load->library('upload');
        
$this->load->model('admin/user/add_user_model');
    }

    public function 
index() {
        
$data['timezones'] = DateTimeZone::listIdentifiers(DateTimeZone::ALL);

        
$this->form_validation->set_rules('username''Username''trim|required');
        
$this->form_validation->set_rules('firstname''Firstname''trim|required');
        
$this->form_validation->set_rules('lastname''Lastname''trim');
        
$this->form_validation->set_rules('email''Email''trim|valid_email');
        
$this->form_validation->set_rules('password''Password''trim');
        
$this->form_validation->set_rules('confirm_password''Confirm Password''trim|matches[password]');
        
$this->form_validation->set_rules('status''Status''trim|required|callback_status[status]');
        
$this->form_validation->set_rules('country''Country''trim|required|callback_country[country]');
        
$this->form_validation->set_rules('timezone''Time Zone''trim|required|callback_timezone[timezone]');

        
$this->form_validation->set_message('required''<b>{field}</b> must be set!');

        if (
$this->form_validation->run($this)) {

            if (isset(
$_FILES['userfile']) && $_FILES['userfile']['size'] > 0) {

                if (!
is_dir(FCPATH 'uploads/users/' $this->input->post('username') . '/')) {
                    
mkdir(FCPATH 'uploads/users/' $this->input->post('username') . '/');
                }
            
                
$config['upload_path'] = './uploads/users/' $this->input->post('username') . '/';
 
               $config['allowed_types'] = 'gif|jpg|png';
 
               $config['max_size'] = 3000;
 
               $config['max_width'] = 0;
 
               $config['max_height'] = 0;

 
               $this->upload->initialize($config);

 
               if (!$this->upload->do_upload('userfile')) {
 
                   $this->error['warning'] = $this->upload->display_errors();
 
               }

 
               $upload_data $this->upload->data();
            }

            
$this->add_user_model->insert_user($upload_data['file_name']);
        }

        if (
validation_errors() != false) {
            
$this->error['warning'] = validation_errors('<div class="alert alert-danger">''</div>');
        }

        if (isset(
$this->error['warning'])) {
            
$data['warning_error'] = $this->error['warning'];
        } else {
            
$data['warning_error'] = '';
        }

        
$data['countries'] = $this->countries->get();

        
$data['header'] = Modules::run('admin/common/header/index');
        
$data['footer'] = Modules::run('admin/common/footer/index');

        
$this->load->view('user/add_view'$data);
    }

    public function 
country($str) {
        if (
$str) {
            return 
true;
        } else {
            
$this->form_validation->set_message('country''<b>{field}</b> must be set!');
            return 
false;
        }
    }

    public function 
timezone($str) {
        if (
$str) {
            return 
true;
        } else {
            
$this->form_validation->set_message('timezone''<b>{field}</b> must be set!');
            return 
false;
        }
    }

    public function 
status($str) {
        if (
$str) {
            return 
true;
        } else {
            
$this->form_validation->set_message('status''<b>{field}</b> must be set!');
            return 
false;
        }
    }

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#5

(09-02-2016, 12:54 AM)wolfgang1983 Wrote: I have now done it this way but if there is a error on my file upload section how to roll it back also.

PHP Code:
<?php

class Add_user_model extends CI_Model {

 public function 
insert_user($file_name = array()) {
 
$this->db->trans_begin();

 
$user = array(
 
'status' => $this->input->post('status'),
 
'date_added' => date('Y-m-d')
 );

 
$this->db->set($user);
 
$this->db->insert($this->db->dbprefix 'user');

 
$user_id $this->db->insert_id();

 
$options = [
 
   'cost' => 12,
 ];

 
$hash password_hash($this->input->post('password'), PASSWORD_BCRYPT$options);

 
$userdata = array(
 
'user_id' => $user_id,
 
'username' => $this->input->post('username'),
 
'password' => $hash,
 
'firstname' => $this->input->post('firstname'),
 
'lastname' => $this->input->post('lastname'),
 
'email' => $this->input->post('email'),
 
'image' => ($file_name) ? $file_name '',
 
'country_code' => $this->input->post('country'),
 
'timezone' => $this->input->post('timezone')
 );

 
$this->db->set($userdata);
 
$this->db->insert($this->db->dbprefix 'user_data');

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

 } else {

 
   $this->db->trans_commit();
 } 
 }


Controller

PHP Code:
<?php

class Add extends MY_Controller {

 private 
$error = array();

 public function 
__construct() {
 
parent::__construct();
 
$this->load->library('form_validation');
 
$this->load->library('countries');
 
$this->load->library('upload');
 
$this->load->model('admin/user/add_user_model');
 }

 public function 
index() {
 
$data['timezones'] = DateTimeZone::listIdentifiers(DateTimeZone::ALL);

 
$this->form_validation->set_rules('username''Username''trim|required');
 
$this->form_validation->set_rules('firstname''Firstname''trim|required');
 
$this->form_validation->set_rules('lastname''Lastname''trim');
 
$this->form_validation->set_rules('email''Email''trim|valid_email');
 
$this->form_validation->set_rules('password''Password''trim');
 
$this->form_validation->set_rules('confirm_password''Confirm Password''trim|matches[password]');
 
$this->form_validation->set_rules('status''Status''trim|required|callback_status[status]');
 
$this->form_validation->set_rules('country''Country''trim|required|callback_country[country]');
 
$this->form_validation->set_rules('timezone''Time Zone''trim|required|callback_timezone[timezone]');

 
$this->form_validation->set_message('required''<b>{field}</b> must be set!');

 if (
$this->form_validation->run($this)) {

 if (isset(
$_FILES['userfile']) && $_FILES['userfile']['size'] > 0) {

 if (!
is_dir(FCPATH 'uploads/users/' $this->input->post('username') . '/')) {
 
mkdir(FCPATH 'uploads/users/' $this->input->post('username') . '/');
 }
 
 
$config['upload_path'] = './uploads/users/' $this->input->post('username') . '/';
 
               $config['allowed_types'] = 'gif|jpg|png';
 
               $config['max_size'] = 3000;
 
               $config['max_width'] = 0;
 
               $config['max_height'] = 0;

 
               $this->upload->initialize($config);

 
               if (!$this->upload->do_upload('userfile')) {
 
                $this->error['warning'] = $this->upload->display_errors();
 
               }

 
               $upload_data $this->upload->data();
 }

 
$this->add_user_model->insert_user($upload_data['file_name']);
 }

 if (
validation_errors() != false) {
 
$this->error['warning'] = validation_errors('<div class="alert alert-danger">''</div>');
 }

 if (isset(
$this->error['warning'])) {
 
$data['warning_error'] = $this->error['warning'];
 } else {
 
$data['warning_error'] = '';
 }

 
$data['countries'] = $this->countries->get();

 
$data['header'] = Modules::run('admin/common/header/index');
 
$data['footer'] = Modules::run('admin/common/footer/index');

 
$this->load->view('user/add_view'$data);
 }

 public function 
country($str) {
 if (
$str) {
 return 
true;
 } else {
 
$this->form_validation->set_message('country''<b>{field}</b> must be set!');
 return 
false;
 }
 }

 public function 
timezone($str) {
 if (
$str) {
 return 
true;
 } else {
 
$this->form_validation->set_message('timezone''<b>{field}</b> must be set!');
 return 
false;
 }
 }

 public function 
status($str) {
 if (
$str) {
 return 
true;
 } else {
 
$this->form_validation->set_message('status''<b>{field}</b> must be set!');
 return 
false;
 }
 }


If you include the file upload section inside the try catch block then any errors should go the catch portion where you roll back the transaction. I say should but you will want to test by making deliberate errors in the file uploads and in the database inserts.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB