help with date compare

greedyisg00d

I am working on some sort of meeting arranger for employees. I have a table schedule:

I have 2 textbox with date picker namely date_from and date_to in my search page. Assuming employee can input as many schedules as they want. What I want is when the user selects a date range it will display the list of employees available/unavailable on that date for example the user picks:

Example 1:
date_from: 01/20/2010
date_to: 01/21/2010
search result: Jamie - Available

Example 2:
date_from: 01/22/2010
date_to: 01/25/2010
search result: Jamie - Unavailable - Meeting

Can you give me some idea or sample code on how can this be done. Thanks

johanafm

Do you actually store 01/25/2010 in your database? Unless you're using a DBMS that handles dates in non standard format, I'd recommend changing from varchar to date, which is on the form YYYY-MM-DD. Then you can simply

$query = 'SELECT fields, you, need FROM tblname WHERE NOT ('. $input_date_from .' < date_to AND '. $input_date_to .' > date_from)'

bradgrafelman

I'm wondering if you won't need a subquery for this one. johanafm's query will work assuming that each employee will only have 1 row in the table at any given time. Here's an example of what I'm talking about:

name | date_from | date_to |
+----------------------------------------+
Jamie | 2010-01-25 | 2010-01-28 | 
Jamie | 2010-01-04 | 2010-01-06 |

If your range is Jan. 22nd through the 25th, johanafm's suggested query will show Jamie as available since it will match the second row.

greedyisg00d

Yes sir bradgrafelman is correct actually I have a working query similar to sir johanafm but my actual problem is when it comes to single employee having multiple rows in the table as sir bradgrafelman stated. So can you post some sample codes that I will be need to solve my problem? Thanks

johanafm

Drop the not, then use WHERE NOT IN

SELECT name FROM t
WHERE name NOT IN (
	SELECT fname FROM tblname WHERE ('. $input_date_from .' < date_to AND '.
	$input_date_to .' > date_from)'