2 Column Mysql Date Range Search in PHP

Question

MySQL column > sdate, edate ( its 2 column).

sdate is start date for project starting and edate is end date for project ending.

so i need to make search between them..

<strong>Search</strong><br />
<form method="post" action="search.php">
  Start Report Date : <input type="text" name="sdate" />
  End Report Date : <input type="text" name="edate" />
  <input type="submit" name="Submit" value="Search" />
</form>

This is example data in mysql
sdate             Project Name      edate
22 December 2008  project 1         23 December 2008
25 December 2008  project 2         26 December 2008
24 December 2008  project 3         27 December 2008
1  January 2008   project 4         20 January 2008
10 December 2008  project 5         12 December 2008

so let say a user entered sdate ( eg, 22 December 2008 ) and edate ( eg, 30 December 2008 ).

It should display

22 December 2008  project 1         23 December 2008
25 December 2008  project 2         26 December 2008
24 December 2008  project 3         27 December 2008

So i need a php code sql query which should display entries lies between those 2 dates..

Please help me..

Thanks very much..

Answer 1

assuming that your sdate and edate are of MySQL columns type DATE you could do the following:

SELECT  
  Project_Name
, sdate
, edate
FROM your_table 
WHERE 
  sdate <= '2008-12-26'
 AND 
  edate >= '2008-12-26'

or you could use DATEDIFF

SELECT  
  Project_Name
, sdate
, edate
FROM your_table 
WHERE 
  DATEDIFF(sdate, '2008-12-26') <= 0
 AND 
  DATEDIFF(edate, '2008-12-26') >= 0

The first one is more efficient because MySQL can compare all the rows in your table to a static value. For the second solution it needs to calculate the difference for every row in your table.

If your sdate and edate columns are not DATE columns, you are out of luck and need to change them first.