MySQL, using BETWEEN-clause with Active Record

[eluser]Vitsaus[/eluser]
How can I use simple sql queries with Between-clause if I use Active record? Manual didn't give any clear guide with that or maybe I'm just blind.

I want to do something like this:

Code:

SELECT some, random, things FROM pages WHERE lft BETWEEN 2 AND 11 ORDER BY lft ASC

[eluser]Armchair Samurai[/eluser]

Code:

$this->db->select('some, random, things');
$this->db->where('`lft` BETWEEN 2 AND 11', NULL, FALSE);
$this->db->order_by('lft', 'asc');
$query = $this->db->get('pages');

I'd also escape any variables used in the BETWEEN clause before inserting them - I've found active record to be a little spotty when escaping custom strings.

[eluser]CI_Newb[/eluser]
I hate to dust off this old thread but this is the same issue i'm having.

Model

Code:

$start_date = $this->input->post('start_date');    
$end_date = $this->input->post('end_date');

$this->db->where('`tdate` BETWEEN `$start_date` and `$end_date`', NULL, FALSE);

What am I doing wrong?

EDIT: error it gives

Code:

Unknown column '$start_date' in 'where clause'

[eluser]CI_Newb[/eluser]
Just so the next person might benefit, solved the issue.

Used custom string.

Code:

$dateRange = "tdate BETWEEN '$start_date%' AND '$end_date%'";
$this->db->where($dateRange, NULL, FALSE);

[eluser]Unknown[/eluser]
It may help to understand why @CI_Newb was able to fix his query the way he did. Specifically, there are a couple of problems with the way the original query was written.

First, PHP doesn't replace variable names with their values if the string is single-quoted ('). You have a few options in this case for fixing the issue. Here are two:

Code:

$this->db->where('`tdate` BETWEEN `' . $start_date . '` and `' . $end_date . '`', NULL, FALSE);

Code:

$this->db->where("`tdate` BETWEEN `$start_date` and `$end_date`", NULL, FALSE);

Using double-quotes (") is probably easier to read and understand. Note that there are some tricky problems with embedding variable names in a string - specifically, PHP needs a way to know when the variable name ends and the rest of the string begins. One way is to use a character that isn't valid in a variable name immediately after the variable name. The other way is to enclose variable names in curly braces ({}):

Code:

$this->db->where("`tdate` BETWEEN `{$start_date}` and `{$end_date}`", NULL, FALSE);

This method is really handy when you want to include object members or array values in a string, since it tells PHP to keep treating characters as part of the variable name until it reaches the closing brace. Ultimately, in this case, using or not using braces to escape variable names is up to personal preference.

The other major issue is that MySQL interprets anything inside backticks (`) as database, table, or column names, depending on where they are used. This means that our dates are being treated like columns to reference, which isn't what we're after. So we need to change them to something else - specifically, we need to enclose them in quotes. Single and double quotes are treated the same way in MySQL, so the simplest solution is to use the opposite quote character from the one we're quoting the string with.

Said another way, since our string is double-quoted, we want to use single quotes. This avoids needing to escape the quote character so PHP doesn't treat it as the end of the string. If our string was single-quoted, we would want to use double quotes here instead.

Code:

$this->db->where("`tdate` BETWEEN '$start_date' and '$end_date'", NULL, FALSE);

This query should work, but if we're storing a full date and time in `tdate`, and the user can only submit dates, we still have trouble. The answer is to tell MySQL that we only want it to check the beginning of the values, and let the rest of the value be anything. For this, we use the SQL wildcard character, the percent sign (%).

Code:

$this->db->where("tdate BETWEEN '$start_date%' AND '$end_date%'", NULL, FALSE)

Another matter of personal preference comes up at this point. Right now we have a fully working query. But the single-line version of this code runs a tad long. If you prefer to see the entire line of code without having to scroll sideways, and your screen is small enough (or your font large enough), you might want to split this into two lines. That's essentially what @CI_Newb did with his final fix. But note that the code works the same either way. The only real difference is that another variable is created in the process.

Hopefully this helps.