Between子句返回0条记录

Question

I have 3 records in the database matching the criteria but when I run query with between clause it is getting 0 records.

$current_date  = date('m-d-Y', strtotime('monday this week'));
$upcoming_date = date('m-d-Y', strtotime('monday next week'));
$sql   = mysqli_query($connection, "SELECT * FROM result WHERE test_date BETWEEN $current_date AND $upcoming_date AND login = '".$_SESSION['uid'] ."'");
$total_check = mysqli_num_rows($sql);

Here is my database

result_id`, `login`, `test_id`, `test_date`, 
(1,           '2',      6,      '08-03-2016', 
(2,           '2',      5,      '08-03-2016', 
(3,           '2',      3,      '08-03-2016',

Please let me know where and what I am doing wrong as I am getting 0 results and $_SESSION['uid'] is 2

Answer 1

You are storing your dates incorrectly if you want to use them in range searches like BETWEEN. You must store them in DATE columns. Storing them in VARCHAR() columns is a bad idea.

You could use something like this, using STR_TO_DATE() to work around your misdesigned table.

 $current_date  = date('Y-m-d', strtotime('monday this week'));
 $upcoming_date = date('Y-m-d', strtotime('monday next week'));
 $sql = mysqli_query($connection, "SELECT * FROM result WHERE STR_TO_DATE(test_date,'%d-%m-%Y') BETWEEN '$current_date' AND '$upcoming_date' AND login = '".$_SESSION['uid'] ."'");

To ask MySQL to compare the strings 01-01-2016 and 12-31-2015, and determine that the latter comes before the former is unreasonable. String comparison is lexical. However, 2015-12-31 obviously comes before 2016-01-01.

This is a little tricky to get right, because the text string 08-08-2016 does come before 08-15-2016, by accident. But at the end of year, things collapse.