Tuesday, June 11, 2013

How to update a JOINed tables using Codeigniter's Active Record?

Codeigniter active record doesn't allow to update a joined tables.
After trying various method and searching the solution, I have found the following solution which does the exactly same thing i.e. update the multiple join tables. By using following method, you can update multiple table using codeigniter active record.
$this->db->set('a.firstname', 'Pekka');
$this->db->set('a.lastname', 'Kuronen');
$this->db->set('b.companyname', 'Suomi Oy');
$this->db->set('b.companyaddress', 'Mannerheimtie 123, Helsinki Suomi');

$this->db->where('a.id', 1);
$this->db->where('a.id = b.id');
$this->db->update('table as a, table2 as b');


9 comments:

  1. This method will not work with CI3. I know this because I used it often in CI2. I do not have a replacement yet except maybe update_batch()

    ReplyDelete
  2. Is it work for more than two table ?

    ReplyDelete
    Replies
    1. It should work with many tables but you should correctly use join.

      Delete
  3. Can I use '$data' array for '$this->db->set()' method?

    ReplyDelete
  4. how to use in controller and model? i'm confused, update data in 3 tables.
    can you help me please?

    ReplyDelete
  5. public function entry_update1($id, $data) {

    $status=$data['status'];
    $follow_up_date=$data['follow_up_date'];
    $comments=$data['comments'];
    $id=$data['enquiry_id'];





    $this->db->set('a.status', $status);
    $this->db->set('a.comments', $comments);
    $this->db->set('b.status', $status);
    $this->db->set('b.follow_up_date', $follow_up_date);

    $this->db->where('a.enquiry_id', $id);
    $this->db->where('b.enquiry_id', $id);
    $this->db->where('a.enquiry_id = b.enquiry_id');
    $this->db->update('tbl_enquiry as a, tbl_enquiry_followup as b');
    }

    getting on error could you please solve this.


    tbl_enquiry as a, tbl_enquiry_followup' doesn't exist

    UPDATE `tbl_enquiry as a, tbl_enquiry_followup` as `b` SET `a`.`status` = 'followup', `a`.`comments` = 'werew', `b`.`status` = 'followup', `b`.`follow_up_date` = '2018-10-29' WHERE `a`.`enquiry_id` = '133' AND `b`.`enquiry_id` = '133' AND `a`.`enquiry_id` = `b`.`enquiry_id`

    Filename: C:/xampp/htdocs/Alfalah.ver.1.0.0/system/database/DB_driver.php

    Line Number: 691

    ReplyDelete