Query won't run using mysql between clause

[eluser]CI_Newb[/eluser]
Searching for stn: 4035903698 (which is confirmed in the database table)

Model

Code:

$start_date = $this->input->post('startDate');    
$end_date = $this->input->post('endDate');
$nsAgent = $this->session->userdata('username');
$manager_id = $this->input->post('manager_id');
$stn = $this->input->post('stn');
$notes = $this->input->post('notes');
$resolution = $this->input->post('resolution');
        
$dateRange = "tdate BETWEEN '%$start_date%' and '%$end_date%'";
        
$this->db->where($dateRange, NULL);
$this->db->where('username', $nsAgent);
$this->db->like('manager_id', $manager_id);
$this->db->like('stn', $stn);
$this->db->like('notes', $notes);
$this->db->like('resolution', $resolution);

$data = $this->db->get('NS_data');
        
        if($data->num_rows() > 0) {
            return $data;
                }

When I echo query, I get

Code:

SELECT * FROM (`NS_data`) WHERE `tdate` BETWEEN '%%' and '%%' AND `username` = 't815138' AND `manager_id` LIKE '%%' AND `stn` LIKE '@35903698%' AND `notes` LIKE '%%' AND `resolution` LIKE '%%'

Which doesn't produce a result.

If I select a startDate and endDate, it shows up.

Code:

SELECT * FROM (`NS_data`) WHERE `tdate` BETWEEN ' 10-10-01%' and ' 10-10-06%' AND `username` = 't815138' AND `manager_id` LIKE '%%' AND `stn` LIKE '@35903698%' AND `notes` LIKE '%%' AND `resolution` LIKE '%%'

If I just comment out

Code:

//$this->db->where($dateRange, NULL);

It works no problems.

What's going on?

[eluser]WanWizard[/eluser]
'%%' is interpreted as the literal %, and I assume there is nothing between % and %.

I suggest you change it to

Code:

if ( ! empty($start_date) && ! empty($end_date) )
{
    $dateRange = "tdate BETWEEN '%$start_date%' and '%$end_date%'";
    $this->db->where($dateRange, NULL);
}

[eluser]CI_Newb[/eluser]
omg thank you WanWizard! Working like a charm now

But I still don't really understand. Does using wildcards with mysql between have different rules than say using with a standard column where %% would just find anything?

[eluser]WanWizard[/eluser]
If tdate is an integer or a date, your between clause returns all rows. If tdate is a varchar or a datetime column, no rows are returned. So, unexpected behaviour.

A bit of digging reveals that BETWEEN doesn't really support wildcards like that. I think it's dangerous to really on this type of query.

[eluser]CI_Newb[/eluser]
Thanks for the information WanWizard, really appreciated!

What do you mean dangerous?

Just a bit of back info, this isn't a public app, its an internal program only used by users who apply to have access. Mainly managers, directors, etc. Not sure if thats what you were referring too.

[eluser]techgnome[/eluser]
I think what he means is that you may pick up more than you intend...
If I select records between % and % .... I could get everything from 12 to 20... and 112 and 120 and 121 and 99920000030 ... see the problem? Personally I'm a little surprised that using wildcards in a between like that doesn't throw an error.

-tg