Using a loop to perform multiple SQL inserts using Codeigniter/Activerecord

Question

I have an array that looking like this:

Array
(
[currency] => GBP
[shipping] => 3.5
[tax] => 0
[taxRate] => 0
[itemCount] => 3

[item_name_1] => Proletarian Sparrow
[item_quantity_1] => 2
[item_price_1] => 75

[item_name_2] => Guillemot Colony
[item_quantity_2] => 5
[item_price_2] => 20

[item_name_3] => Dandelion Clock
[item_quantity_3] => 2
[item_price_3] => 50
)

I'm trying to use a loop to extract the individual item details and insert a row in a database for each one. I'm using Codeigniter.

My model looks like this:

public function set_order($cust_id, $order_data)
{

    // Breaks up order information and creates a new row in the pn_orders tables for each item

    // Get the last row in the orders table so we can work out the next order_id
    $this->db->order_by('order_id', 'desc');
    $this->db->limit(1);
    $query = $this->db->get('pn_orders'); 

    $row = $query->row_array(); 

    // If the resulting array is empty, there are no orders so we can set the order_id to 1. If there is already an order_id, just add 1 to it.
    if (empty($row)) {
        $order_id = 1;
    } else {
        $order_id = $row['order_id'] + 1;
    }

    //echo "<pre>";
    //print_r($order_data);
    //echo "</pre>";

    // The delivery message input has a placeholder. if the user's input is different to this, assign it to $delivery_message.
    if ($this->input->post('delivery_message') == "e.g. if out, leave in porch") {
      $delivery_message = NULL;
    } else {
      $delivery_message = $this->input->post('delivery_message');
    }

    // Set today's date for insertion into the DB
    $date = date('Y-m-d');

    // The order information is clumped together in a single array. We have to split out single items by looping through the array before inserting them into the DB.
    for ($i = 1; $i <= $order_data['itemCount']; $i++) {

        $item = array(
            'order_id' => $order_id,
            'cust_id' => $cust_id,
            'date_ordered' => $date,
            'item_name' => $order_data["item_name_{$i}"],
            'item_quantity' => $order_data["item_quantity_{$i}"],
            'item_price' => $order_data["item_price_{$i}"],
            'payment_method' => $_POST['payment_method'],
            'delivery_message' => $delivery_message
        );

        $this->db->insert('pn_orders', $item);

     }

}

Everything seems to be in place, however, only 1 row is ever inserted and I can't work out why. It seems very simple.

Is it something to do with the Activerecord pattern?

Thanks.

Nil'z · Accepted Answer · 2013-08-02 10:31:48Z

4

First print out the array to see if the array structure is correct or not. If its Okay then just use insert_batch like this:

for ($i = 1; $i <= $order_data['itemCount']; $i++) {
    $items[] = array(
        'order_id' => $order_id,
        'cust_id' => $cust_id,
        'date_ordered' => $date,
        'item_name' => $order_data["item_name_{$i}"],
        'item_quantity' => $order_data["item_quantity_{$i}"],
        'item_price' => $order_data["item_price_{$i}"],
        'payment_method' => $_POST['payment_method'],
        'delivery_message' => $delivery_message
    );
}
//echo "<pre>";print_r($item);echo "</pre>";die;   uncomment to see the array structure
$this->db->insert_batch('pn_orders', $items);

answered Aug 2, 2013 at 10:31

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chris J Over a year ago

Thanks, I've tested your suggestion. The array you make prints out fine (print_r(items)). However, now no rows are being inserted. Just so you know, the database library is loaded.

Nil'z Over a year ago

Echo out this line, write after the insert statement echo $this->db->last_query();die;. Take that query and run in your phpmyadmin. Is it giving you any database error or something?

Nil'z Over a year ago

Happie to help :D @ChrisJ

Chris J Over a year ago

Got it! There was a duplicate entry on the order_id since it was set at the Primary Key in the Database. Wouldn't have worked that out without your help. You've also helped me learn a practical use of insert_batch in a very elegant manner. Many thanks.

Collectives™ on Stack Overflow

Using a loop to perform multiple SQL inserts using Codeigniter/Activerecord

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related