How to select rows if given date is between two dates?

Question

I'm trying to select rows if the given date only falls between two dates in the table In the curriculum table I have startdate and enddate.

If it is possible I need also to do condition inside the query

$coursneededdate >= startdate AND $coursneededdate <= enddate

Here is my code, any help would be highly appreciated.

$coursneededdate = '2020-08-27';
$sql = "SELECT * FROM curriculum where ".$coursneededdate." between  'startdate' and 'enddate'";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {


    while($row = $result->fetch_assoc()) {

echo $row["curriculum_id"];


    }
}

Answer 1

Your SQL statement evaluates to SELECT * FROM curriculum where '2020-08-27' between 'startdate' and 'enddate' which contains all string values and no dates.

The supplied parameter starts as a string so you will need to convert this to a date value using STR_TO_DATE. The columns names from the table should not be in quotes. You will sometime see the back quote used to specify column names.

Your query should be something like

SELECT * FROM curriculum WHERE STR_TO_DATE('2020-08-27','%Y-%m-%d') BETWEEN `startdate` AND `enddate`;

IMPORTANT NOTE

If the supplied string date values comes from user generated input, creating a SQL query with string concatenation makes the code vulnerable to SQL Injection.