1
$result=mysql_query("SELECT * FROM attendance WHERE date Between '$start_date' and '$end_date' order by date asc") or die(mysql_query);

Above mysql query is to get all data in the database between two dates selected by the user. Let say table attendance in my database as shown below

2009-07-01    3
2009-07-03    4
2009-07-04    5
2009-07-06    7
2009-07-07    6
2009-07-10    8

  1. if the $start_date = "2009-07-01" and $end_date = "2009-07-10", it will display all the data between two selected dates 

    2009-07-01    3
2009-07-03    4
2009-07-04    5
2009-07-06    7
2009-07-07    6
2009-07-10    8

( I noticed that this is successful because, both dates are exist in the database).

  1. if the $start_date = "2009-07-02" and $end_date = "2009-07-09", what i expected it to be displayed is 

    2009-07-03    4
2009-07-04    5
2009-07-06    7
2009-07-07    6

Unfortunately, the output never goes out like what I've expected. This is happened because both dates doesn't exist in the database. I know that something wrong with my query. It would be great if someone can help me to figure it out. Thanks in advanced!

jenny
  • 61
  • 3
  • 9
  • So what does get output in your in your final case? Nothing? Everything? – andrewsi Sep 14 '13 at 14:15
  • Nothing.... @andrewsi – jenny Sep 14 '13 at 14:16
  • What happens if you have a start date of 2009-07-01 and an end date of 2009-07-09? And what is the definition of `date` in your database table? – andrewsi Sep 14 '13 at 14:20
  • I got an error that says variable for date 2009-07-09 ($end_date) is undefined. column name:date ; data types: date; @andrewsi – jenny Sep 14 '13 at 14:26
  • Does your working query return `2009-07-26 7`? That definitely shouldn't be returned based on your query. – andrewsi Sep 14 '13 at 14:30
  • Sorry! I've edited the quest. I wanted to write 2009-07-06. @andrewsi – jenny Sep 14 '13 at 14:36
  • Possible duplicate of [MySQL compare DATE string with string from DATETIME field](https://stackoverflow.com/questions/2758486/mysql-compare-date-string-with-string-from-datetime-field) – Dharman Jul 05 '19 at 21:09

1 Answers1

-1
$result=mysql_query("SELECT * FROM attendance WHERE DATE(date) Between '$start_date' and '$end_date' order by date asc") or die(mysql_query);

OR

$result=mysql_query("SELECT * FROM attendance WHERE DATE(date) >='$start_date' and DATE(date) <='$end_date' order by date asc") or die(mysql_query);
Shemeer M Ali
  • 1,030
  • 15
  • 39
  • It doesn't work. I tried both of it. But Still got the same error message. – jenny Sep 14 '13 at 14:38
  • It said that variable for $start_date and $end_date are undefined if both selected date are not in the database. – jenny Sep 14 '13 at 14:51
  • $start_date and $end_date undefined its not related to database. its php error. Can just echo those dates before the sql query echo $start_date ."----". $end_date; – Shemeer M Ali Sep 14 '13 at 15:03
  • Please check part where assign value to $start_date varibale – Shemeer M Ali Sep 14 '13 at 15:11
  • $result=mysql_query("SELECT * FROM attendance WHERE DATE(date) >='2009-07-02' and DATE(date) <='2009-07-10' order by date asc") or die(mysql_query); – User123456 Mar 04 '19 at 09:06