[RESOLVED] MYSQL CASE statement in CodeIgniter Active Record Query« Back to Questions List

I have query which I want Codeigniter Active Record friendly but I'm not sure how to go about adding case statements to the set part of the plain mysql query.
 
UPDATE attendance_time_index ati
JOIN users s ON ati.time_index_user_id = s.user_id
JOIN attendance_code_groups acg ON s.user_attendance_code_group = acg.group_id
SET ati.time_index_default = CASE WHEN ati.time_index_value = 'PM' THEN acg.group_pm_default ELSE acg.group_am_default END
WHERE s.user_id = 123
Posted by aliraza170@gmail.com
Asked on September 8, 2016 9:20 am
1

You can convert it to active record easily, Try this code

$this->db->set('ati.time_index_default', "CASE WHEN ati.time_index_value = 'PM' THEN acg.group_pm_default ELSE acg.group_am_default END", FALSE);
$this->db->where('s.user_id', 123);
$this->db->update('attendance_time_index ati
join users s on ati.time_index_user_id = s.user_id
join attendance_code_groups acg  on s.user_attendance_code_group = acg.group_id
');

.

Posted by aliraza170@gmail.com
Answered On September 8, 2016 9:22 am
1

You can convert it to active record easily, Try this code

$this->db->set('ati.time_index_default', "CASE WHEN ati.time_index_value = 'PM' THEN acg.group_pm_default ELSE acg.group_am_default END", FALSE);
$this->db->where('s.user_id', 123);
$this->db->update('attendance_time_index ati
join users s on ati.time_index_user_id = s.user_id
join attendance_code_groups acg  on s.user_attendance_code_group = acg.group_id
');

.

Posted by aliraza170@gmail.com
Answered On September 8, 2016 9:22 am