Mark Alan Mark Alan - 1 year ago 35
MySQL Question

Between clause is returning 0 recods

I have 3 records in the database matching the criteria but when I run query with between clause it is getting 0 records can any one help me out

$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 Source

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.