7

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


    }
}
Hussain Almalki
  • 177
  • 1
  • 9

4 Answers4

6

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.

Dave Anderson
  • 11,836
  • 3
  • 58
  • 79
  • 1
    Good answer but no need for `STR_TO_DATE` if the format is standard mysql date format, which it apparently is. Still get 1 up :) – Hanky Panky Apr 17 '15 at 04:19
  • Thank you sir, can I do this condition in the query $coursneededdate >= startdate AND $coursneededdate <= enddate – Hussain Almalki Apr 17 '15 at 04:31
  • 1
    @HussainAlmalki yes you can but that is what the BETWEEN statement already does for you https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between – Dave Anderson Apr 17 '15 at 04:34
  • @Hanky good point. I was going to +1 your answer for mentioning SQL injection but it's now deleted so I've updated this answer. – Dave Anderson Apr 17 '15 at 04:48
3

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?

chris85
  • 23,846
  • 7
  • 34
  • 51
  • It worked thanks, but I need to check all start and end date to bring all the data that is between these dates. Now it bring only one row? – Hussain Almalki Apr 17 '15 at 04:20
  • I'm not sure about that issue it should be returning all records between those date ranges. If you run it on the DB there is more than one returned? Maybe try taking out the `if ($result->num_rows > 0) {` – chris85 Apr 17 '15 at 04:26
  • Many thanks it worked my bad I put a date that is only between one row start and end date. Can I do it with condition like $coursneededdate >= startdate AND $coursneededdate <= enddate – Hussain Almalki Apr 17 '15 at 04:30
  • That should be valid but why not use the between? You also may want to group that in parenthesis, if you go that route, so if you append other things they aren't grouped wrong. – chris85 Apr 17 '15 at 04:32
  • I did this and it worked $sql = "SELECT * FROM curriculum where '".$coursneededdate."' >= startdate AND '".$coursneededdate."' <= enddate"; Just to have the benfits of using = – Hussain Almalki Apr 17 '15 at 04:44
  • Between includes the equals as well, per mysql doc and the link from @Dave Anderson. `If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1`. – chris85 Apr 17 '15 at 04:49
  • Thats great. thank you for your informative help and other people who posted a great comments as well. – Hussain Almalki Apr 17 '15 at 04:53
0

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

azngunit81
  • 1,574
  • 2
  • 20
  • 38
  • Can I put condition in the query like $coursneededdate >= startdate AND $coursneededdate <= enddate – Hussain Almalki Apr 17 '15 at 04:24
  • 1
    that is what between does for you. but yes. don't forget to single quote your variable and also column name comes first not the variable. @HussainAlmalki – azngunit81 Apr 17 '15 at 04:56
0

try this

$sql = "SELECT * FROM curriculum where date = '" . $coursneededdate  . "' AND date between  '".$startdate."' and '" . $enddate . "'";
Kuldeep Dangi
  • 4,126
  • 5
  • 33
  • 56