Add OR WHERE statement in codeigniter using foreach loop

Question

I am using codeigniter framework and i want to create a OR WHERE statement using foreach loop,

Below is my code,

$wh_q = array('ec_date >' => $start, 'ec_date <=' => $end);

This will produce query as WHERE ec_details.date > '2016-11-01 00:00:00' AND ec_details.date <= '2017-01-07 00:00:00'

Now i want to append few more criteria in abover query string with AND ec_details.user_id = '5' OR ec_details.user_id = '6' and so on.

Now user_id, 5 and 6 are coming from array results,

$user_string = '5,6';
$users_array = explode(",",$user_string);

foreach ($users_array as $user) {
   $query = array('ec_details.user_id' => $user);
}

Then i am merging above $query array in original $wh_q array

$wh_q = array_merge($wh_q, $query);

And finally i select where as below,

$this->db->where($wh_q);

But this will not produce my required query, but will just create like below,

`WHERE `ec_details`.`date` > '2016-11-01 00:00:00' AND `ec_details`.`date` <= '2017-01-07 00:00:00'`AND `ec_details`.`user_id` = '6'`

How can i add AND OR WHERE statements using foreach loop in codeigniter?

Thank you,

I assume you want to get details from ec_detail table for user_id 5 and 6 between start and end range, is that it? — Mudassar Khani
– Mudassar Khani, Commented Jan 8, 2017 at 6:59

Hikmat Sijapati · Accepted Answer · 2017-01-08 11:13:27Z

1

Try as below...

$wh_q = array('ec_date >' => $start, 'ec_date <=' => $end);//condition first

$user_string = '5,6';
$users_array = explode(",",$user_string);

foreach ($users_array as $user) {
   $query []= array('ec_details.user_id' => $user);//second condition
}

//print_r($query) //multidimensional array

Query

$this->db->where($wh_q);

$total = count($query);
for($i=0;$i<$total;$i++){
    $this->db->or_where($query[$i]);
    }

edited Jan 8, 2017 at 11:13

answered Jan 8, 2017 at 6:56

Hikmat Sijapati

6,9691 gold badge11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

rjcode Over a year ago

but this will not add value 5, it will just generate -- OR ec_expensedetails.user_id = '6', where as i need -- AND ec_expensedetails.user_id = '5' OR ec_expensedetails.user_id = '6'

Mudassar Khani Over a year ago

what if we use $query.= array('ec_details.user_id' => $user); no, that will create 2 OR statements

rjcode Over a year ago

@Hikmat, is same key allowed in array? no its not. I think we can only achieve with custom string..

rjcode Over a year ago

@HikmatSijapati, no it will not work, will produce OR 0 = Array OR 1 = Array

Mudassar Khani Over a year ago

That will still produce two OR statements @Hikmat, but I guess it should get the required results

|

Rahul · Accepted Answer · 2017-01-08 06:46:07Z

1

You can try like this, create a complete where string like this

$where = "name='Joe' AND status='boss' OR status='active'";
$this->db->where($where);

And check, this will work.

EDIT

OR you can give or condition like this

$this->db->where('name !=', $name);
$this->db->or_where('id >', $id);  // Produces: WHERE name != 'Joe' OR id > 50

answered Jan 8, 2017 at 6:46

Rahul

18.6k7 gold badges43 silver badges64 bronze badges

1 Comment

rjcode Over a year ago

@V for Vendetta, this can work if we do as string, but we need to pull values from array and create conditional statement.

Jigar Patel · Accepted Answer · 2017-01-09 07:57:56Z

If you you need to check only user id then better you can also user IN like below.

$user_string = '5,6'; $users_array = explode(",",$user_string); 
$this->db->where_in('id', $users_array);

and If you want to use only OR and And statement

then use below code

$this->db->where('ec_date >',$start,false);
$this->db->where('ec_date <=', $end,false);
$user_string = '5,6';
$users_array = explode(",",$user_string);
$userqry = array();
foreach ($users_array as $user) {
   $userqry[] = "ec_details.user_id ='".$user."'";
}
$userqrystr = implode(" OR ",  $userqry);
$this->db->where($userqrystr,false,false);

This query look like

WHERE `ec_details`.`date` > '2016-11-01 00:00:00' AND `ec_details`.`date` <= '2017-01-07 00:00:00' AND (`ec_details`.`user_id` = '6' OR `ec_details`.`user_id` = '5')

Collectives™ on Stack Overflow

Add OR WHERE statement in codeigniter using foreach loop

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related