CODEWITHSUNDEEP
phpmysqlcodeigniter
user2722043
user2722043

Reputation: 53

Inserting to two tables using Codeigniter transaction

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.

Upvotes: 0

Views: 1344

Answers (1)

Nassim
Nassim

Reputation: 2876

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

Upvotes: 1

Related Questions