MYSQL - Date query and display(PHP) question

Question

I am currently trying to write a little program to track time-off requests for employees. I'm fairly new to MYSQL and PHP, so it's a learning project for me as well. I've run into this problem which I do not seem to be able to figure out.

I want to display time off requests for a given week (Mon-Fri). I've got the requests in a table 'requests', with a 'Starttime' and 'Endtime' in separate fields, both Date/Time.

I can currently easily search and retrieve requests that have either (or both) Starttime or Endtime values that fall within the given ISO week I am looking at (WEEKOFYEAR() ).

What I need to be able to do is search for requests that may include days in the ISO week I am displaying, but not have a Starttime or Endtime during that week.

Example: 1)Employee takes off Tuesday of Week 24 through Friday of Week 24.

Currently, I would correctly display that the employee was off starting Tuesday and show a return on that Friday, but on Wed and Thursday nothing would be entered.

2)Employee takes off Friday of Week 30 through Monday of Week 32.

Currently, I would show that employee as not being 'off' during Week 31 because the search would not show a Starttime or Endtime during that week even though they are actually off the entire week. Though the Starttime and Endtime would be noted on the correct days.

Right now, what I do to work around this is run 5 additional queries to check if the date for each day Mon-Fri during Week 31 is contained BETWEEN the Starttime and Endtime of each request in the db.

I hate to run a total of 6 queries to get this information, is there an easier way to get that information?

Thanks!

Answer 1

What format are these dates stored in the DB? Assuming they are using MySQL's DATE format, its pretty easy to do. You can just use comparison operators on the fields and MySQL will do the work for you in one query.

$sql = "SELECT * FROM table WHERE startdate <= $someday AND $someday <= enddate";

Answer 2

Sticking to your week of year way of doing it, you can run a check to see if the current week falls within the range of the starting week off and the ending week off.

SELECT * FROM table WHERE WEEKOFYEAR(Starttime) <= N AND WEEKOFYEAR(Endtime) >= N;

(where N is the week you're displaying)

What you'd want to do, once you get the rows, is parse each day in PHP to see if that day falls between the starttime and endtime.

A good method for that is using:

$start_timestamp = strtotime($row['Starttime']);
$end_timestamp = strtotime($row['Endtime']);

You can use a similar method to get a timestamp of the day you are displaying, and see if it falls between $start_timestamp and $end_timestamp to determine if that day is off.

Answer 3

I just wrote a calendar app for events and ran into this- how bout something like this:

SELECT * FROM Requests WHERE 
Start_Date BETWEEN <first_day_of_week> AND <last_day_of_week>
OR
End_Date BETWEEN <first_day_of_week> AND <last_day_of_week>
OR
<day_of_week_monday> BETWEEN Start_Date AND End_Date
OR
<day_of_week_tuesday> BETWEEN Start_Date AND End_Date
OR
<day_of_week_wedenesday> BETWEEN Start_Date AND End_Date
OR
<day_of_week_thursday> BETWEEN Start_Date AND End_Date
OR
<day_of_week_friday> BETWEEN Start_Date AND End_Date
GROUP BY ID ORDER BY Start_Date ASC, Date ASC

While < value_names > are generated with php via the currently viewed week requested. Should cover your bases.

Answer 4

since you mentioned PHP as the display:

$start=30; //Friday of Week 30
$end=32; //Monday of Week 32

foreach (range($start, $end) as $number) {
    echo $number;
}

You would expect to get 30,31,32.

You will have to verify that $start is < $end though as well for December to January weeks.

The range for WEEKOFYEAR() is 1-53. In this case, add 53 to the $end and display mod of $end if greater than 53:

$start=52; //Friday of Week 52, 2009
$end=2; //Monday of Week 2, 2010

If($start>$end) $end+=53;
foreach (range($start, $end) as $number) {
    if($number>53){
        echo $number%53;}
    else{
        echo $number;
    }
}

You would expect to get 52,53,1,2

Answer 5

Probably need the query to read either like the big one with all the ORs or something like this

WHERE Starttime <= AND Endtime >=

That should get all the dates you need that could fall in that week, then you parse the records you get with PHP to find the ones for that actual week... though there may be an easier way to do that...