Inserting to two tables using Codeigniter transaction

Question

I am using Codeigniter, the following code is working and inserting into the two InnoDB table, but I can't get Insert_ID inserted into the second table Here is part of my code so far.

Controller:

 //Create Customer Data Array $data1 = array( 'first_name' => $this->input->post('first_name'), 'last_name' => $this->input->post('last_name'), 'phone' => $this->input->post('phone'), 'email' => $this->input->post('email'), 'address' => $this->input->post('address'), 'zipcode' => $this->input->post('zipcode'), 'refferedby' => $this->input->post('refferedby') ); //$insert_id =$this->Ticket_model->insert_data($data1); $data2 = array( //'user_id' => $this->Ticket_model->insert_id(), 'section' => $this->input->post('section'), 'make' => $this->input->post('make'), 'problem' => $this->input->post('problem'), 'notes' => $this->input->post('notes'), 'assets' => $this->input->post('assets'), 'password' => $this->input->post('password'), 'imei' => $this->input->post('imei'), 'cost' => $this->input->post('cost'), 'assignedto' => $this->input->post('assignedto'), 'created' => $this->input->post('created'), 'createdby' => $this->input->post('createdby'), 'promisedate' => $this->input->post('promisedate'), 'status' => $this->input->post('status') ); $data['insert'] = $this->Ticket_model->insert_data($data1,$data2); //View $data['main_content'] = 'admin/orders/addtick'; $this->load->view('admin/layouts/main',$data);

Model:

 public function insert_data($data1,$data2){ $this->db->trans_start(); //$this->db->query("INSERT INTO customers($data1) VALUES ()"); $this->db->insert('customers', $data1); $user_id=$this->db->insert_id(); //$this->db->insert('tickets', $data2); $this->db->insert('tickets', $data2); $this->db->trans_complete(); return $this->db->insert_id(); }

Database:Customers Table

 CREATE TABLE IF NOT EXISTS `customers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `phone` int(15) NOT NULL, `email` varchar(50) NOT NULL, `address` varchar(50) NOT NULL, `zipcode` int(5) NOT NULL, `refferedby` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

Tickets Table:

 CREATE TABLE IF NOT EXISTS `tickets` ( `id` int(11) NOT NULL AUTO_INCREMENT, `section` varchar(25) NOT NULL, `user_id` int(11) NOT NULL, `make` varchar(255) NOT NULL, `problem` text NOT NULL, `notes` text NOT NULL, `assets` varchar(100) NOT NULL, `password` varchar(25) NOT NULL, `imei` varchar(25) NOT NULL, `cost` double NOT NULL, `assignedto` varchar(25) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `createdby` varchar(50) NOT NULL, `promisedate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `status` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

Thank you for your help.

Nassim · Accepted Answer · 2015-05-24 08:59:18Z

I would use two methods in the model it simplifies troubleshooting queries

 public function insert_data1($data1){ $this->db->trans_start(); //$this->db->query("INSERT INTO customers($data1) VALUES ()"); $this->db->insert('customers', $data1); $user_id=$this->db->insert_id(); $this->db->trans_complete(); return $user_id; }

and

public function insert_data2($data2){ $this->db->trans_start(); //$this->db->insert('tickets', $data2); $this->db->insert('tickets', $data2); $ticket_id=$this->db->insert_id(); $this->db->trans_complete(); return $ticket_id; }

and here is the controller

public function foo() { //Create Customer Data Array $data1 = array ( 'first_name' => $this->input->post('first_name'), 'last_name' => $this->input->post('last_name'), 'phone' => $this->input->post('phone'), 'email' => $this->input->post('email'), 'address' => $this->input->post('address'), 'zipcode' => $this->input->post('zipcode'), 'refferedby' => $this->input->post('refferedby') ); $this->load->model("tickets_model"); $last_user_id = $this->tickets_model->insert_data1($data1); $data2 = array ( //$last_user_id is the value of the foreign key to link the tables 'user_id' => $last_user_id, 'section' => $this->input->post('section'), 'make' => $this->input->post('make'), 'problem' => $this->input->post('problem'), 'notes' => $this->input->post('notes'), 'assets' => $this->input->post('assets'), 'password' => $this->input->post('password'), 'imei' => $this->input->post('imei'), 'cost' => $this->input->post('cost'), 'assignedto' => $this->input->post('assignedto'), 'created' => $this->input->post('created'), 'createdby' => $this->input->post('createdby'), 'promisedate' => $this->input->post('promisedate'), 'status' => $this->input->post('status') ); $last_ticket_id = $this->tickets_model->insert_data2($data2); $data['insert'] = $last_ticket_id; $data['main_content'] = 'admin/orders/addtick'; //View $this->load->view('admin/layouts/main',$data); }

and here is the full model, controller and view code under runnable.com

http://runnable.com/VWGMqHsM0SpKc7Ir/using-database-transactions-in-ci-for-codeigniter-hello-world-and-php

the controller is ticket.php
the model is tickets_model.php
the view is admin/layouts/main.php

hope that helps

Thank you, I already loaded the model. and its working but I can't get the user_id inserted to the tickets tables.
but still we will have the same problem, how can we insert $user_id to the tickets table??
it was a typo, the $ in "return $user_id;" and i forgot to complete the transaction "$this->db->trans_complete();" in the insert_data2() method, everything should be alright now in the code above, I also created a Runnable version ,see Answer. hope that helps

Collectives™ on Stack Overflow

Inserting to two tables using Codeigniter transaction

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related