phpmysqlcodeigniteractiverecordquery-builder

Convert raw UPDATE SQL containing CASE statements in the SET clause into CodeIgniter query builder methods


Can anybody convert the normal mysql query into codeigniter update strcuture.

UPDATE game_rounds 
SET from_score = CASE WHEN from_id = 2 THEN 65 ELSE from_score END,
    to_score = CASE WHEN to_id = 2 THEN 65 ELSE to_score END 
WHERE round_id=5

I tried update_batch(), but I'm not able to find the correct solution.


Solution

  • $this->db
        ->set('from_score', 'CASE WHEN from_id = 2 THEN 65 ELSE from_score END', FALSE)
        ->set('to_score', 'CASE WHEN to_id = 2 THEN 65 ELSE to_score END', FALSE)
        ->where('round_id', 5)
        ->update('game_rounds');
    

    $this->db->set() enables you to set values for inserts or updates. It can be used instead of passing a data array directly to the insert or update functions.

    The optional third parameter ($escape), that will prevent data from being escaped if set to FALSE.

    CI Active Record Class