PHP中的2列Mysql日期范围搜索

2021-12-20 00:00:00 date range search php mysql

MySQL 列 > sdate、edate(它的第 2 列).

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

sdate 是项目开始的开始日期，edate 是项目结束的结束日期.

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

假设用户输入了 sdate(例如，2008 年 12 月 22 日)和 edate(例如，2008 年 12 月 30 日).

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

它应该显示

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

所以我需要一个 php 代码 sql 查询，它应该显示这两个日期之间的条目..

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

请帮帮我..

非常感谢..

推荐答案

假设你的 sdate 和 edate 是 MySQL 列类型 DATE您可以执行以下操作:

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'

或者你可以使用 DATEDIFF

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

第一个更有效，因为 MySQL 可以将表中的所有行与静态值进行比较.对于第二种解决方案，它需要计算表格中每一行的差异.

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.

如果您的 sdate 和 edate 列不是 DATE 列，那么您就不走运了，需要先更改它们.

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

相关文章