How to insert into multiple tables with CodeIgniter

Question

I have two tables that have (1:1) relationship between each others.

customers table:
 - customerID (PK)(AI)
 - customerName
 - phone

addresses table:
 - customerID (PK&FK)
 - address
 - city
 - zipcode

I tried to update them in the same CodeIgniter view form.

update_view.php

<th>Customer Name:</th>
<td><input type="text" name="customerName"/></td>
<tr>
<th>Customer Phone:</th>
<td><input type="text" name="phone"/></td>
<tr>
<th>Address:</th>
<td><input type="text" name="address"/></td>
<tr>
<th>City:</th>
<td><input type="text" name="city"/></td>
<tr>
<th>Zip Code:</th>
<td><input type="text" name="zipcode"/></td>

This is part of the my controller code:

    public function insert()
        {      
            $this->load->database();
            $this->load->model('my_model');
            $this->my_model->insert_entry();

            $custInsert=$this->my_model->get_all_customers();
            $this->load->view('main_view',array('customer'=>$custInsert));
..
}

Note: up to this point everything was working for process the one table (customers).

This is the part of my model file:

function insert_entry()
    {
        $this->customerName   = $_POST['customerName']; 
        $this->phone = $_POST['phone'];
        $this->db->insert('customers', $this); // up to here it was working

        //$customerID=$db->insert_id;
        $customerID=$this->db->query("SELECT MAX(customerID) FROM `customers`");
        $this->customerID;
        $this->address = $_POST['address'];
        $this->city = $_POST['city'];
        $this->zipcode = $_POST['zipcode'];
        $this->db->insert('addresses', $this);
}

As for me the problem is the 'addresses' table need to customerID, but I don't insert it handly (auto_increment). I tried to many way for get it after inserted to customers table but I cant do it. Is there anybody know different way or what should I do in that way?

Answer 1

Doing this is a bad idea... how is your app going to handle when new customers are being added at nearly the same exact time?

$customerID=$this->db->query("SELECT MAX(customerID) FROM `customers`");

You should get rid of that line and use the method that is preferred and works. If you do not, it can and will inevitably result in you fetching the wrong record for a customer at some point in time and associating an address with the wrong customer.

This is because two clients running the code at (nearly enough) the same time might encounter the MAX() at the same point in time, and thus each could get the same value. When they both try to save, only one will succeed, and the other will fail due to the primary key constraint. This situation is known as a race condition and should be guarded against.

Use this instead:

 $this->db->insert_id()

also this:

$this->customerID;

should be:

$this->customerID = $this->db->insert_id();

Answer 2

A cleaner way to achieve what you want is to use arrays instead of $this. You will have all the info you need and nothing more. There is a lot of stuff in your $this object than you don't need.

In your controller

public function insert()
{      
    $this->load->database();
    $this->load->model('my_model');

    $data_user = array(
      'customerName' => $this->input->post('customerName'),
      'phone'        => $this->input->post('phone')
    );

    $data_address = array(
      'address'    => $this->input->post('address'),
      'city'       => $this->input->post('city'),
      'zipcode'    => $this->input->post('zipcode')
    );    

    $this->my_model->insert_entry($data_user, $data_address);

    [...]
}

In your model

function insert_entry($data_user, $data_address) {

    $this->db->insert('customers', $data_user);

    $data_address['customerID'] = $this->db->insert_id();

    $this->db->insert('addresses', $data_address);
}

A good practice is also to use the Input Class of Codeigniter when you get your $_POST variables. Just use the following :

$this->input->post('VAR_NAME') instead of $_POST['VAR_NAME']