Using normal SQL with Active Records in Code Igniter

I have a single normal SQL statement in the middle of my Active Records query. Is there any way I can 'add' this query to the AR one without having two independent queries? The SQL is as follows if someone would help me 'convert' it into Active Records-style in case it doesn't work.

SELECT birthday
FROM usertable
WHERE STR_TO_DATE(bir开发者_开发知识库thday, '%d.%m.%Y')
BETWEEN $minimumDate AND $maximumDate

You can simply do this:

$this->db
    ->select('birthday')
    ->where("STR_TO_DATE(birthday, '%d.%m.%Y') BETWEEN $minimumDate AND $maximumDate")
    ->get('usertable');

But you will have to escape the variables yourself in this case.

You could also do this:

$this->db
    ->select('birthday')
    ->where("STR_TO_DATE(birthday, '%d.%m.%Y') >", $minimumDate)
    ->where("STR_TO_DATE(birthday, '%d.%m.%Y') <", $maximumDate)
    ->get('usertable');

This will automatically escape the second parameter of where(), but now we're running STR_TO_DATE() twice and have a slightly less elegant query (not using BETWEEN).

Active Record can be difficult to use MySQL functions with. Sometimes it's best to just do the query manually if you are not building it dynamically. CI offers some methods of doing this easily while escaping your input properly for you.

See the section in the user guide about Query Bindings:

$sql = "SELECT birthday
FROM usertable
WHERE STR_TO_DATE(birthday, '%d.%m.%Y')
BETWEEN ? AND ?";

$this->db->query($sql, array($minimumDate, $maximumDate));

You can do this -

SELECT birthday
FROM usertable
WHERE STR_TO_DATE(birthday, '%d.%m.%Y')
BETWEEN $minimumDate AND $maximumDate

by chaining like this -

$this->db->select('birthday')->from('usertable')->where('STR_TO_DATE', '%d.%m.%Y')->limit($minimumDate, $maximumDate);