Codeigniter“where”和“or_where”

Question

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 .... )

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 I need.

If I try any of the 2 querys separately, them work. But I'm not able to mix the 2 conditions.

How can I get this?? Thanks in advance.

Answer 1

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();

Doc : https://ellislab.com/codeigniter/user-guide/database/active_record.html