phpcodeigniterjoinsql-updatequery-builder

trying to make update data into multiple table in CI


I'm trying to make update data into multiple tables that have some join relationship, here's my table.

Table1: input_pen

no_form | no_lahan | t_utama

Table2: lahan

no_lahan | jenis

here's my controller called c_read

function upAksi(){
    $no_form = $this->input->post('noform');
    $no_lahan = $this->input->post('nolahan');
    $t_utama = $this->input->post('tutama');
    $jenis = $this->input->post('jlahan');

    $this->m_read->upAksi($no_form, $no_lahan, $t_utama, $jenis);
    redirect('c_read');
}

and here's my model called m_read

function upAksi($no_form, $no_lahan, $t_utama, $jenis){
    $this->db->set('input_pen.t_utama', $t_utama);
    $this->db->set('lahan.jenis', $jenis);

    $this->db->where('input_pen.no_form', $no_form);
    $this->db->where('input_pen.no_lahan', $no_lahan);
    $this->db->update('input_pen JOIN lahan ON input_pen.no_lahan= lahan.no_lahan');
}

did i do wrong? that code made some error like
enter image description here

I already check the query in phpmyadmin, and it running well

UPDATE input_pen JOIN lahan ON input_pen.no_lahan = lahan.no_lahan
SET input_pen.t_utama = 'PADI', lahan.jenis = 'Lahan Rawa'
WHERE input_pen.no_form = 42 AND input_pen.no_lahan = 71

Solution

  • Codeigniter active record doesn't allow to update a joined tables.

    After trying the various method and searching the solution. By using the following method, you can update multiple tables using Codeigniter active record.

    sample code:

    $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');
    

    After seeing your final comment I will suggest you do it separately.

    your Update Querys

    //First Table Update
    $this->db->set('a.t_utama', $t_utama);
    $this->db->where('a.no_form',  $no_form);
    $this->db->where('a.no_lahan', $no_lahan);
    $this->db->update('input_pen as a');
    // Second Table Update
    $this->db->set('b.jenis', $jenis);
    $this->db->where('b.no_lahan', $no_lahan);
    $this->db->update('lahan as b');
    

    And event this not help the use your working query in $this->db->query( your query );.

    That's all i can suggest. Hope it will help.