A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2008 year2 Column Mysql Date Range Search in PHP - page 1
User InfoPosts
2 Column Mysql Date Range Search in PHP#1
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..

posted date: 2008-12-26 01:09:00


Re: 2 Column Mysql Date Range Search in PHP#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2008-12-26 01:09:01


Re: 2 Column Mysql Date Range Search in PHP#3
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'

posted date: 2008-12-26 01:34:00


Re: 2 Column Mysql Date Range Search in PHP#4
As an aside, to help with UI, I recommend using phps strtotime() method... it makes for entering dates very flexible

posted date: 2008-12-26 01:39:00


Re: 2 Column Mysql Date Range Search in PHP#5
assuming that your sdate and edate are of MySQL columns type DATE you could do the following:SELECT Project_Name, sdate, edateFROM your_table WHERE sdate <= '2008-12-26' AND edate >= '2008-12-26'or you could use DATEDIFFSELECT Project_Name, sdate, edateFROM your_table WHERE DATEDIFF(sdate, '2008-12-26') <= 0 AND DATEDIFF(edate, '2008-12-26') >= 0The 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.

posted date: 2008-12-26 03:55:00


Re: 2 Column Mysql Date Range Search in PHP#6
First use mktime() on the input from the user$time = mktime(format from user);then doSELECT 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.

posted date: 2008-12-26 07:07:00


Re: 2 Column Mysql Date Range Search in PHP#7
MySQL supports only YYYY-MM-DD or YY-MM-DD in date literals. To use formats like those above, you'd have to parse it using the STR_TO_DATE() function.

posted date: 2008-12-26 12:10:00


Re: 2 Column Mysql Date Range Search in PHP#8
looks like the OP is using a varchar column for the date, though...

posted date: 2008-12-26 12:11:00


Re: 2 Column Mysql Date Range Search in PHP#9
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)

posted date: 2008-12-30 09:12:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.