2 Column Mysql Date Range Search in PHP

问题:

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..

回答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.

回答2:

It seems like a simple select query with a "where" clause can do the trick.

Peuso-code:

select sdate, name, edate 
from your_table 
where sdate >= '22 December 2008' and edate <= '30 December 2008'

回答3:

As an aside, to help with UI, I recommend using phps strtotime() method... it makes for entering dates very flexible

回答4:

First use mktime() on the input from the user

$time = mktime(format from user);

then do

SELECT Project_Name, sdate, edate FROM table WHERE 
  UNIX_TIMESTAMP(STR_TO_DATE(sdate, '%e %m %Y')) <= '$time'
 AND 
  UNIX_TIMESTAMP(STR_TO_DATE(edate, '%e %m %Y')) >= '$time'

That should work.

回答5:

SELECT project_name, sdate, edate FROM projects WHERE sdate <= $_POST['edate'] AND edate >= $_POST['sdate']

Gives you any project with start date and end date that overlap the Form start date and end date. (assuming the form sdate and edate are in the right format)