I’m using codeigniter to query a database, and I need to conditionally compare arrays in the where clause.
So basically I want to do: where_in OR where_in depending on what array has data in it. So I will have either an array of user_id OR an array of area_id’s.
I’m not sure how to combine the where_in comparison. This is the query i need to amends:
public function get_checks_test($org_id,$users,$areas) { // get todays date $todays_date = date("Y-m-d"); // build where array $check_array = array('c.org_id =' => $org_id, 'c.status !=' => '0', 'c.due <=' => $todays_date); // build query $this->db->select('c.*,a.check_area_id,b.check_user_id,d.area_name,u.first_name,u.last_name'); $this->db->order_by('c.due', 'asc'); $this->db->where($check_array); $this->db->where_in('user_id', $users); $this->db->or_where_in('area_id', $areas); $this->db->join('check_assigned_areas a','a.check_id = c.check_id','left'); $this->db->join('check_assigned_users b','b.check_id = c.check_id','left'); $this->db->join('areas d','d.area_id = a.check_area_id','left'); $this->db->join('users u','u.id = b.check_user_id','left'); $check_object = $this->db->get('checks c'); return $check_object->result(); }
I don’t think there is a or_where_in function that I can find.
Can I do it like this or do I need a rethink?
Thanks
Advertisement
Answer
Yes, there is a or_where_in function in Codeigniter.
Example
$arr_price=array(20,40,80,30); $this->db->or_where_in('price', $arr_price); //SQL Query: OR price IN(20,40,80,30);
Syntax:
or_where_in([$key = NULL[, $values = NULL[, $escape = NULL]]])
Parameters:
$key (string) – The field to search $values (array) – The values searched on $escape (bool) – Whether to escape values and identifiers
Returns:
DB_query_builder instance
Return type:
object
Generates a WHERE field IN(‘item’, ‘item’) SQL query, joined with ‘OR’ if appropriate. Generates a WHERE field IN(‘item’, ‘item’) SQL query, joined with ‘OR’ if appropriate.
If more help needed, I’m happy to help. Happy Coding.