how to write php mysql queries(having double parentheses) in codeignitor

[shashigowda]

Hi,
i am beginer to codeignitor,i know how to write basic active queries in CI.
i want to know how to write a query(having multiple conditions,double parentheses) in which supports CI.

query is:

$query="SELECT * FROM apply_special_leave WHERE ((from_date <= '$dt1' AND '$dt1' <= to_date) OR (from_date between '$dt1' AND '$dt2')) AND user_id='$user_id' AND status!='2'";

thank you.

[diafol]

Not sure about your uses of <= and between. Aren.t they the same thing?

[shashigowda]

No, in first condition iam checking values are greater r less than the var values then column form_date value is present in between var dt1,dt2

[diafol]

Probably something like this:

$this->db->select('*')->from('apply_special_leave')
        ->group_start()
                ->where('from_date <=',  $dt1)
                ->where('to_date >=',  $dt1)
                ->or_group_start()
                    ->where('from_date >=', $dt1)
                    ->where('from_date <=', $dt2)
                ->group_end()
        ->group_end()
        ->where('user_id', $user_id)
        ->where('status !=', 2)
->get();

Note that there is not special where/group for BETWEEN...AND. For this you need to look at >=min and <=max values.

However I don't see why you don't just write this as a string - it's not overly complicated.