保留没有任何时间重叠

Question

I am writing php script which checks and allocates available free slot to reserve a certain machine. The table looks like this:

    machine      start_time    duration(mins)
      1             11:00       30 
      1             12:00       40
      1             13:00       30
      1             14:00       40
      2             11:00       10
      2             12:10       30
      2             13:00       10

when the user gives start time the system should automatically assign machine and max possible duration the user can use. No partial nor total overlap on the same machine.

Here is part of the script I tried:

    $time = $_POST['starttime'];
    $duration = $_POST['duration'];

    $query1 = "SELECT machine_id FROM machinetable";
    $res1 = mysql_query($query1$query1,$conn);

    while($row1 = mysql_fetch_array($res1)) {
        $y = $row1['machine_id'];
        $qry = "SELECT * FROM tableabove where machine_id = '$y' Order By start_time DESC";

        $result = mysql_query($qry,$conn);
        while($row = mysql_fetch_array($result)) {
         // WHAT SHALL I ADD HERE.....JUST SOME HINT OR ANOTHER MEANS THAT HELPS
        }
    }

Answer 1

Is this what you are looking for?

select machine_id, duration
from table t
where starttime = USERSTARTTIME
order by duration desc
limit 1;

This returns the machine and duration. Your php code can then do the "reservation".

EDIT:

Try this instead:

select machine_id, duration - timediff(USERSTARTTIME, starttime) as durationForUser
from table t
where timediff(USERSTARTTIME, starttime) between 0 and duration
order by duration - timediff(USERSTARTTIME, starttime) desc
limit 1;