Query won't run using mysql between clause

Query won't run using mysql between clause - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Query won't run using mysql between clause (/showthread.php?tid=34693)

Query won't run using mysql between clause - El Forum - 10-06-2010

[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?

Query won't run using mysql between clause - El Forum - 10-07-2010

[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);

}

Query won't run using mysql between clause - El Forum - 10-07-2010

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

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?

Query won't run using mysql between clause - El Forum - 10-07-2010

[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.

Query won't run using mysql between clause - El Forum - 10-08-2010

[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.

Query won't run using mysql between clause - El Forum - 10-08-2010

[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