1

I need to do multiple insert/update so i came up with transaction to roll back if anything goes wrong. Besides, my application should update the record if already exists, and insert if not.

So first of all i try to update a record using a unique id, if it return affected_rows=0 i continue wih insert.

Probably i miss something within transaction/affected rows, it always return affected_rows=0.

Below is the code:

$this->db->trans_start();

   $this->db->where('order_id', $order_id);
   $this->db->where('order_status', 'Active');
   $this->db->update('orders', $order);

   $this->db->where('order_id', $order_id);
   $this->db->where('sku', $item_sku);
   $this->db->update('order_items', $order_items);

$this->db->trans_complete();

if ($this->db->affected_rows()==0){
   $this->db->trans_start();
       $this->db->insert('orders', $order);
       $this->db->insert('order_items', $order_items);
   $this->db->trans_complete();
}

Thanks in advance!

Luciano
  • 1,455
  • 8
  • 22
  • 52

1 Answers1

2

I use MySQL's ON DUPLICATE KEY UPDATE to handle that case. However, CodeIgniter's ActiveRecord implementation does not support this natively, and the accepted answer in this thread:

How would I use ON DUPLICATE KEY UPDATE in my CodeIgniter model?

Seems to be dead. So, consider just using raw MySQL instead of the ActiveRecord pattern; this is fairly common amongst CI developers (I do not use their implementation of AR).

Community
  • 1
  • 1
Michael B
  • 1,743
  • 4
  • 21
  • 35
  • Thanx for your help Kyle! The dead thread could be visited here: [link](http://web.archive.org/web/20090221091226/http://codeigniter.com/forums/viewthread/80958/). Anyway straight query does the trick, so i'll use this solutions! But what if i want that ON DUPLICATE KEY UPDATE runs only on some rows, for example WHERE foo='1'? – Luciano Aug 08 '11 at 17:01
  • 1
    No clue on the conditional statement, sorry. – Michael B Aug 08 '11 at 17:58