CODEWITHSUNDEEP
phpmysqlsqlcodeigniteractiverecord
Vahn
Vahn

Reputation: 542

How to fix this query in CI Active records?

I'd like to do update for a joined table. But, I have problem with the single quote.

My CI active record: 

function upd_pic_hps_drtu($i_lamp,$i_id_req){
    $this->db->set("pm_rapd.STATUS",1);
    $this->db->where("pm_ap.UPL_FILENAME", $i_lamp);
    $this->db->where("pm_rapd.ID_REQUIREMENT_ASSIGN_PIC", $i_id_req);

    $this->db->update("pm_requirement_assign_pic_d pm_rapd INNER JOIN pm_activity_project pm_ap ON pm_rapd.RECEIVED_BY = pm_ap.NIP"); 
}

This is the query looks like: 

UPDATE `pm_requirement_assign_pic_d pm_rapd INNER JOIN pm_activity_project pm_ap ON pm_rapd`.`RECEIVED_BY =` `pm_ap`.`NIP` SET `pm_rapd`.`STATUS` = 1 WHERE `pm_ap`.`UPL_FILENAME` = 'condition_1' AND `pm_rapd`.`ID_REQUIREMENT_ASSIGN_PIC` = 'condition_2'

The desired and correct query should be: 

UPDATE pm_requirement_assign_pic_d pm_rapd INNER JOIN pm_activity_project pm_ap ON pm_rapd.RECEIVED_BY = pm_ap.NIP SET `pm_rapd`.`STATUS` = 1 WHERE `pm_ap`.`UPL_FILENAME` = 'condition_1' AND `pm_rapd`.`ID_REQUIREMENT_ASSIGN_PIC` = 'condition_2'

Upvotes: 0

Views: 61

Answers (2)

Atural
Atural

Reputation: 5439

i'm not sure but did you try something like this

function upd_pic_hps_drtu($i_lamp,$i_id_req)
{
    $this->db
        ->set("pm_rapd.STATUS",1)
        ->join("pm_activity_project pm_ap","pm_rapd.RECEIVED_BY = pm_ap.NIP", "inner")
        ->where("pm_ap.UPL_FILENAME", $i_lamp)
        ->where("pm_rapd.ID_REQUIREMENT_ASSIGN_PIC", $i_id_req)
        ->update("pm_requirement_assign_pic_d pm_rapd");
}

if that works - i would go with that way.

Imho the query binding feature should only be used in exceptional situations because the query builder simplifies your queries really nice... (beside the fact that this is a database independent layer)

Upvotes: 0

Brian Gottier
Brian Gottier

Reputation: 4582

I'm sure there is more than one way to do what you are trying to achieve, but I like to use CodeIgniter DB's query bindings feature. Anytime I'm doing some query that is more than just a simple one, I use the query bindings. For more information see the user guide: https://www.codeigniter.com/user_guide/database/queries.html#query-bindings

function upd_pic_hps_drtu( $i_lamp, $i_id_req )
{
    $this->db->query('
        UPDATE pm_requirement_assign_pic_d pm_rapd 
        INNER JOIN pm_activity_project pm_ap 
        ON pm_rapd.RECEIVED_BY = pm_ap.NIP 
        SET `pm_rapd`.`STATUS` = 1 
        WHERE `pm_ap`.`UPL_FILENAME` = ? 
        AND `pm_rapd`.`ID_REQUIREMENT_ASSIGN_PIC` = ?
    ', array( $i_lamp, $i_id_req ) );
}

Notice how I used your desired query, but substituted the values with question marks, and then added the values in an array as the second parameter of the query method.

Upvotes: 1

Related Questions