current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a site devloped in codeigniter where I'd want to create a query with some condition and a condition inside with OR like this:

Select * from users where username = 'user' and nation_id = 90 and active = 1 and (rate = 1 OR rate = 2 OR rate = 3);

Now I have create this code but isn't working because is like writing this:

Select * from users where username = 'user' and nation_id = 90 and active = 1 and rate = 1 OR rate = 2 OR rate = 3;

I don't want this query but the first. This is my code:

$this->db->from('users');
$this->db->where('username', 'user');
$this->db->where('nation_id', 90);
$this->db->where('active', 1);

for ($i = 1; $i<=3; $i++){
     $this->db->or_where('rate', $i);
}

$query = $this->db->get();

Please don't tell me other way like write the query manually because I have simplified it and is where huge to make a manual query.
The cycle is important that is in that way because I have to cycle an array for example.
I only want to insert my or condition inside a () is possible?

share|improve this question
    
What do you get from $this->db->last_query() ? And, you do notice that you're selecting * from service...not from users, right? –  jcorry Aug 7 '13 at 15:52
    
error of copy because I have many query similar.. @jcorry –  Alessandro Minoccheri Aug 7 '13 at 15:54
    
Looks like you could use BETWEEN –  Brewal Aug 7 '13 at 16:01
add comment

3 Answers

up vote 2 down vote accepted

You can use $this->db->where_in(), like for example:

$opts = array();
for ($i = 1; $i <=3; $i++) {
    $opts[] = $i;
}
$this->db->where_in('rate', $opts);
share|improve this answer
    
Thanks this is what I'd wanted –  Alessandro Minoccheri Aug 7 '13 at 16:02
add comment
up vote 2 down vote

You can use the where_in method for this:

$this->db->from('users');
$this->db->where('username', 'user');
$this->db->where('nation_id', 90);
$this->db->where('active', 1);
$this->db->where_in('rate' array(1, 2, 3))
$query = $this->db->get();

Or, you can do the same thing with the and_where method and explicitly setting the brackets:

$this->db->from('users');
$this->db->where('username', 'user');
$this->db->where('nation_id', 90);
$this->db->where('active', 1);
// This produces: AND (rate = 1 OR rate = 2 OR rate = 3)
$this->db->where('(rate = 1 OR rate = 2 OR rate = 3)')
$query = $this->db->get();
share|improve this answer
    
No I have to use a cycle for the or_where condition –  Alessandro Minoccheri Aug 7 '13 at 15:58
    
Accidentally used the or_where method rather than and_where. –  Chris White Aug 7 '13 at 16:01
    
Sorry but I have to use cycle.. but your function can works in other case +1 –  Alessandro Minoccheri Aug 7 '13 at 16:02
    
Just curios: why so much upvotes with even loop requirment not met? –  Cthulhu Aug 7 '13 at 16:07
    
@Cthulhu Perhaps because upvoters didn't expect writing a simple loop is needed to mark the answer as valid, this is obvious, at least for a 5k user. –  Hashem Qolami Aug 7 '13 at 16:12
show 1 more comment
up vote 1 down vote

With the BETWEEN operator, you don't have to have a loop : 

$this->db->where("rate BETWEEN 1 AND 3");

This method is cleaner, since if you make it between 1 and 150, you won't have a huge sql query : rate IN (1, 2, 3, 4, 5, ... , 149, 150) but just rate BETWEEN 1 AND 150. That seems much more fitted.

share|improve this answer
    
No I need the condition to use with a cycle –  Alessandro Minoccheri Aug 7 '13 at 16:10
    
I really don't understand why here, unless you do some testing in your loop that would give a query like rate IN (1, 3, 7, 9) –  Brewal Aug 7 '13 at 16:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.