CODEWITHSUNDEEP
phpmysql
Hussain Almalki
Hussain Almalki

Reputation: 177

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

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"];


    }
}

Upvotes: 7

Views: 3004

Answers (4)

Dave Anderson
Dave Anderson

Reputation: 12274

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.

Upvotes: 6

Kuldeep Dangi
Kuldeep Dangi

Reputation: 4422

try this

$sql = "SELECT * FROM curriculum where date = '" . $coursneededdate  . "' AND date between  '".$startdate."' and '" . $enddate . "'";

Upvotes: 0

azngunit81
azngunit81

Reputation: 1604

First you must trouble shoot the SQL and ensure what you have "coded" is equal to what is expected in SQL.

I would suggest to echo $sql; to see if the query is corresponding to mysql and then run it in the DB itself.

If that works then I would suggest to do what chris85 stated is to wrap your values and inside a single quote $sql = "SELECT * FROM curriculum where '".$coursneededdate."' between 'startdate' and 'enddate'";

Upvotes: 0

chris85
chris85

Reputation: 23892

The issue is probably that you aren't quoting the variable which is a string when it goes to your DB. Also don't quote the column names.

$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"];
    }
}

The issue also could be that you are checking for a date 5 years in the future. Do you have data that far ahead?

Upvotes: 3

Related Questions