$this->db->between()

[eluser]Unknown[/eluser]
Hi guys,
New user, really enjoying the flexibility that CI provides so far. I do have a question though, I'm trying to perform an SQL BETWEEN query on a mysql database. Is there a $this->db->between() helper that can be used? If there is, would the syntax go something like:

$this->db->between('field name','lower value');
$this->db->between('field name','higher value');

? According to the user guide, multiple 'where' helpers are chained with an 'AND' between them, so presumably the query would result in:

SELECT 'whatever' FROM 'tablename' WHERE 'field name' BETWEEN 'lower value' AND 'higher value';

I can't find any mention of such a helper anywhere online, but it would make sense to port it to CI, right? Failing this, is there a way to achieve a between otherwise?
Thanks!

[eluser]LifeSteala[/eluser]
If you are using Active Record class, then you can do a BETWEEN clause like so:

Code:

$this->db->select('field1, field2');
$this->db->from('tablename');
$this->db->where('age BETWEEN ' . $age1 . ' AND ' . $age2);

// SELECT field1, field2 FROM tablename WHERE age BETWEEN $age1 AND $age2

This code is untested, but should theoretically work.

[eluser]Unknown[/eluser]
Thanks, I'll give it a go and report back.

[eluser]jedd[/eluser]
You may need to use the third parameter to stop the library trying to escape the whole thing - though reading the manual it looks like this isn't so much a problem with where(). I don't use AR, though, so experimentation is the order of the day.

If you're happy to wear the slight performance cost (which may in fact be zero, if MySQL's parser is pretty smart - which it usually is) you could do this:

Code:

$this->db->where('age >', $age1);
$this->db->where('age <', $age2);

[eluser]kiedis[/eluser]
[quote author="jedd" date="1259307443"]You may need to use the third parameter to stop the library trying to escape the whole thing - though reading the manual it looks like this isn't so much a problem with where(). I don't use AR, though, so experimentation is the order of the day.

If you're happy to wear the slight performance cost (which may in fact be zero, if MySQL's parser is pretty smart - which it usually is) you could do this:

Code:

$this->db->where('age >', $age1);
$this->db->where('age <', $age2);

[/quote]
^ this or else this: $myinterval = array('field_name >' => $lowvalue, 'field_name <' => $highvalue);

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