CODEWITHSUNDEEP
phpmysql
Mark Alan
Mark Alan

Reputation: 455

Between clause is returning 0 records

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

Upvotes: 0

Views: 56

Answers (2)

O. Jones
O. Jones

Reputation: 108641

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.

Upvotes: 1

Tamil
Tamil

Reputation: 1203

You should use ' for $current_date AND $upcoming_date

"SELECT * FROM result WHERE test_date BETWEEN '$current_date' AND '$upcoming_date' AND login = '".$_SESSION['uid'] ."'"

Use prepare statement like below to avoid SQL Injection

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // do something with $row
}

Upvotes: 3

Related Questions