phpmysqlcodeignitersql-updatedelimited

How to append a user id as a delimited value in one column and increment another column using CodeIgniter's update() method

In my application, every user can "like" every comment. Each "like" is stored as an item of a comma-separated column. The user id of each user's "like" is stored into a database table as 2,4,5..etc. I am trying to use CONCAT() in my model to append the user id.

public function shop_like($shop_id, $user_id, $logged_id)
{ 
    $this->db->where('user_id', $user_id);

    $this->db->where('shop_id', $shop_id);
    $this->db->set('review_like', 'review_like+1', FALSE);
    $where = "CONCAT('like_user_id', ',', '" . $logged_id . "')"; 
    // $where = CONCAT(like_user_id, '" . $logged_id . "');
    $this->db->set('like_user_id', $where);
    $this->db->set('review_like', 'review_like+1', FALSE);
    $this->db->update('shop_reviews', $data);
}

Each "like", the like_user_id field contains the inserted value like this CONCAT(like_user_id, ',','7') -- this is the problem.

I want the existing comma-separated values and the user id of liked user.

Solution

  • If you want to update the column you use set() and not in where clause:

    $this->db->set("like_user_id", "CONCAT( like_user_id, ',".$logged_id."' )", false);