How to use codeigniter table update using mysql syntax like if condition?

Question

I am trying to update records using mysql IF condition, is this supported as records are not updating

$this->db->update(
    'elections',
        array(
            'status'=>'IF(status=="Active","Inactive","Active")'
        ),
        array(
            'election_id'=>$election_id
        )
    );

I am getting following query on print:

UPDATE `elections` SET `status` = 'IF(status==\"Active\",\"Inactive\",\"Active\")' WHERE `election_id` = '8'

Zaragoli · Accepted Answer · 2016-04-25 10:26:01Z

2

It seems CodeIgniter escapes your IF statement, but you can use it as string:

$this->db->query('UPDATE `elections` SET `status`= IF(`position`=?,?,?) WHERE `election_id` = ?', array('Active','Inactive','Inactive', $election_id));

Or
You can disable the auto escaping function (with set to false the third parameter of the set method):

$this->db->set('status', "IF(status='Active','Inactive','Active')", false)
->where(array('id' => $election_id))
->update('elections');

(Please keep in mind: in this case if your parameters can contain untrusted values you should escape them. e.g.: $this->db->escape)

edited Apr 25, 2016 at 10:26

answered Mar 17, 2016 at 12:49

Zaragoli

6864 silver badges7 bronze badges

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

1 Comment

justnajm Over a year ago

My question is about db->update method, is there any way to perform same query using update method ?? writing queries is messy code

jarlh · Accepted Answer · 2016-03-17 12:32:03Z

0

Use a single equal (=) sign after status:

$this->db->update(
    'elections',
        array(
            "status"=>"IF(status='Active','Inactive','Active')"
        ),
        array(
            'election_id'=>$election_id
        )
    );

edited Mar 17, 2016 at 12:32

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

answered Mar 17, 2016 at 12:27

JBMagallanes

2711 gold badge3 silver badges4 bronze badges

Collectives™ on Stack Overflow

How to use codeigniter table update using mysql syntax like if condition?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related