Update table rows based on limited and randomized qualifying ids using CodeIgniter's query builder methods

Question

I am trying to select ids then pass them into an update query using the following code:

$query = $this->db->query("
    SELECT GROUP_CONCAT(a.sponsor_id) as sponstr
    FROM (
          select sponsor_id
          from sponsor
          WHERE (pay_success = 'yes')
                AND (end_date_time > NOW())
                and (
                     (country_id = 1 and state_id = 24)
                     or city_id = 123
                )
          order by rand()
          limit 0,10
    ) a
");

if ($query->num_rows() > 0) {
    foreach ($query->result() as $sponsorids) {
        $data['se_count'] = 0;
        $this->db->where_in('sponsor_id',$sponsorids->sponstr);
        $this->db->update('sponsor',$data);
    }
}

but all the ids do not update, only the first one does.

the where_in produces a single string as:

WHERE sponsor_id IN ('5,4,2,3,1')

which I think it should be individual values like:

WHERE sponsor_id IN (5,4,2,3,1)

How do I correctly implement the desired effect?

Answer 1

You should pass an array there. So pass not $sponsorids->sponstr but explode(',', $sponsorids->sponstr)

Also it's seems like a bad DB design decision, take some time and have a look on many-to-many concept