使用php的员工的可用性

Question

In my system I have two tables. One for staff and one for jobs.

Jobs table contain data such as: job_id (int) staff_id (int) starttime (datetime) endtime (datetime)

And staff is simply: staff_id (id) name (varchar)

However, I need to come up with a way to see if staff is available or not for a job. Meaning are they already scheduled. For instance a staff_id with data saved for: starttime: 2016-01-11 12:00 enddtime: 2016-01-11 17:00

Can not take a new job starting at 2016-01-11 16:00. Also they should not be able to take a job that ends at 2016-01-11 13:00.

I have tried to search for some PHP calendar logic but havent found any good so far.

EDIT What I did was to check if the dates where within a certain block using the below code. However this dosent't work if both userstart and userend are outside of the daterange, startdate and enddate.

$starttime = "2016-01-11 20:00";
$endtime = "2016-01-11 22:00";
$userstart = '2016-01-11 20:10';
$userend = '2016-01-12 01:10';


function check_time($starttime, $endtime, $userstart, $userend){
    $start_ts = strtotime($starttime);
    $end_ts = strtotime($endtime);
    $userstart_ts = strtotime($userstart);
    $userend_ts = strtotime($userend);

    if(($userstart_ts >= $start_ts) && ($userstart_ts <= $end_ts) OR ($userend_ts >= $start_ts) && ($userend_ts <= $end_ts)){
        return true;
    }else{
        return false;
    }
}

if(check_time($starttime, $endtime, $userstart, $userend)){
    echo 'Staff work';
}else {
    echo 'Staff free';
}

Above will output "Staff work". However, if I do change to below it will print out Staff free

$userstart = '2016-01-11 19:10';
$userend = '2016-01-12 01:10'

Answer 1

Finally I ended up with this code. The last problem was to handle events that did overlap.

$starttime = "2016-01-11 20:00";
$endtime = "2016-01-11 22:00";
$userstart = '2016-01-11 19:10';
$userend = '2016-01-11 21:10';

function check_time($starttime, $endtime, $userstart, $userend){
    $start_ts = strtotime($starttime);
    $end_ts = strtotime($endtime);
    $userstart_ts = strtotime($userstart);
    $userend_ts = strtotime($userend);

    if(($userstart_ts >= $start_ts) && ($userstart_ts <= $end_ts) OR ($userend_ts >= $start_ts) && ($userend_ts <= $end_ts)){
        return true;
    }else{
        if(($userstart_ts <= $start_ts) && ($userend_ts >= $end_ts)){
            return true;
        }else{
            return false;
        }
     }
}

if(check_time($starttime, $endtime, $userstart, $userend)){
    echo 'Staff work';
}else {
    echo 'Staff free';
}

However, in order to use this function I would be forced to loop through all entries in DB. I dont think that would be to efficient

EDIT:

Below is the final SQL-query I came up with. It will show the staff currently working based on userstart and userend in case someone looking for something similar.

SELECT staff FROM staff WHERE (startdate BETWEEN '2016-01-11 22:00' AND '2016-01-11 23:00') OR (enddate BETWEEN '2016-01-11 22:00' AND '2016-01-11 23:00') OR (startdate < '2016-01-11 22:00' AND enddate > '2016-01-11 23:00');