[SOLVED] CodeIgniter query WHERE one column = one value AND another column is one of multiple qualifying values

CodeIgniter query WHERE one column = one value AND another column is one of multiple qualifying values

I'm trying to do the following SQL query with the active record of codeigniter:

SELECT *
FROM USERS_OPTIONS
WHERE user_id = 3
    AND (option_id = 2 OR option_id = 5 OR option_id = 108 OR ...etc)

For that, I have this code in codeigniter, but it's not working:

$this->db->where("user_id", $user_id);
foreach ($options as $option) {
    $this->db->or_where("option_id", $option["id"]);
}

$options is an array where I store all the options_id values I need.

If I try any of the 2 queries separately, they work, but I'm not able to mix the 2 conditions.

How can I solve this?

Solution

You can use $this->db->where_in() :

Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate

$names = array('Frank', 'Todd', 'James');
$this->db->where_in('username', $names);
// Produces: AND username IN ('Frank', 'Todd', 'James')

In your case, you will have :

$this->db->where_in('option_id', $options)
         ->get_where("your_table", array("user_id" => $user_id))->result();