PHP Time Filtering and MYSQL Filtering

Question

I have 3 columns: Day, start_schedule and end_schedule.

   Day  |  start_schedule | end_schedule
 -------|-----------------|--------------
 Monday |     1:00        |   3:00
 Monday |     6:00        |   8:00
 Monday |     8:00        |  10:00

I'm still learning php. How will I filter if my input start schedule and end schedule is valid based on the stored times in the database?

For example if I want to add times

start_schedule = 3:00 and end_schedule = 7:00

since there is 6:00 - 8:00 you shouldn't be allowed to add this schedule.

start_schedule = 7:00 and end_schedule = 11:00

since there are 6:00 - 8:00 and 8:00 - 10:00 you shouldn't be allowed to add this schedule.

I edited your question. I think I understood what you were asking, but I'm maybe wrong. Are you talking about a MySQL database as well, or is it just PHP arrays? If so add the MySQL tag. — regilero, Dec 18 '11 at 09:49
Are we to assume the times are in the [24 hour clock](http://en.wikipedia.org/wiki/24-hour_clock)? Also, the [MySQL TIME type](http://dev.mysql.com/doc/refman/5.0/en/time.html) would also be useful. — Jared Farrish, Dec 18 '11 at 09:53
You got it sir that is the exact flow. Do you have a sample php code and sql query for this problem it is been weeks figuring out this. — jojo, Dec 18 '11 at 10:02
@ regileroThank you very much sir it is an early gift for christmas. Im gona try this now. I Hope everthing works — jojo, Dec 18 '11 at 10:14

regilero · Answer 1 · 2011-12-18T12:53:46.257

2

If it's a MySQL storage you will need to perform a SQL query to retreive records which overlaps your current new record, before you even try to insert the new record. Then if your overlap check doesn't return any row you can safely insert your row (if all this is done in a transaction, else someone could have inserted something between your check and your insert, do transactions and put locks on the table so that no one can insert whild your are checking and inserting).

Basically to find overlapping row you need a query like that (myday mystart and myend are the new values you want to insert):

SELECT count(*)
 FROM mytable
 WHERE Day=myday
  AND mystart < end_schedule
  AND myend > start_schedule

This query could be optimized if you have a lot of record to simply perform and EXISTS operation instead of a count (as 1 overlapping period is enought to decide you should not continue).

Now if you have some scheds overlapping tow days is more complex. Actually you cannot store theses scheds in your database model. If I want to start Sunday at 22:00 and end Monday at 04:00 or even thursday at 06:00 ... So maybe you are breaking your scheds to insert several scheds, one per day. Then you'll have tocheck carefully that all your scheds parts could be inserted, not just one of them. And of course still doing all theses things in a transaction.

EDIT:

overlap test:

     s------------------------e
  s1----------e1
                        s2----------e2
         s3----------e3

s < e1
s < e2
s < e3
e > s1
e > s2
e > s3

edited Dec 18 '11 at 12:53

answered Dec 18 '11 at 10:04

regilero

29,806
6
60
99

Wouldn't it be `AND ((mystart > start_schedule AND mystart < end_schedule) OR (myend < end_schedule AND myend > start_schedule))`? To check for those that fall between? – Jared Farrish Dec 18 '11 at 10:52
@refp - I don't follow your comment. – Jared Farrish Dec 18 '11 at 11:54
@refp - [`7-11`](http://codepad.org/YhEbhf7V), [`1-5`](http://codepad.org/dJjEH8na). – Jared Farrish Dec 18 '11 at 13:55
@Jared Farrish: all comment are removable. I'll remove mine. For readers, there was here some discussions about why it's really working. – regilero Dec 18 '11 at 14:32
Well, somehow we ended up figuring it out; my apologies if I came off rude or otherwise. I wasn't intending to, I just couldn't wrap my head around why that would work. It seemed simplistic, when it was really clever. The funny thing is, when I was putting my function together, I kept thinking there was some simple way to do it, but this didn't occur to me. – Jared Farrish Dec 18 '11 at 14:47
@regilero I'll remove my comments, to be honest I was sure I read the algorithm in question correct and I didnt even care to check it again when you made your remarks regarding my comments. I then took a second look at it and was like; "oh shit, I was wrong". Sorry about that regilero, regarding your down-vote on my post; since the post in question isn't wrong you shouldn't have downvoted it, I'd appreciate if you removed your downvote. I've upvoted your post. – Filip Roséen - refp Dec 18 '11 at 15:51

score 2 · Accepted Answer · edited May 23 '17 at 11:48

2

I would do most of the logic in mysql since it will handle these kinds of expressions very easy and straight forward approaches.

In PHP, before inserting a new entry we will need to check 1 thing:

Is end_time less than start_time? If so, abort.

In MySQL, when trying to insert a new entry we will need to check 4 things:

Is the start_time inside any existent entry? If so, abort.
Is the end_time inside any existent entry? If so, abort.
Is there any entry starting that would be inside our new entry? If so, abort.
Is there any entry ending that would be inside our new entry? If so, abort.

The above can actually be refactored into one steps:

Is there our start time less than any entries end time, AND is our end time larger than that entires start time? If so, abort.

mysql> describe sample;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| start | time        | YES  |     | NULL    |       |
| end   | time        | YES  |     | NULL    |       |
| day   | varchar(50) | YES  |     | Monday  |       |
+-------+-------------+------+-----+---------+-------+

mysql> SELECT * FROM sample;
+--------+----------+----------+
| day    | start    | end      |
+--------+----------+----------+
| Monday | 01:00:00 | 03:00:00 |
| Monday | 06:00:00 | 08:00:00 |
| Monday | 08:00:00 | 11:00:00 |
+--------+----------+----------+

Sample implementation in PHP querying the database:

Props to @regilero for posting a re-factored version of the where claus required.

$new_entries = array (
  array('Monday', '00:00:00', '23:59:00'),
  array('Monday', '12:00:00', '15:00:00'),
  array('Monday', '07:00:00', '10:00:00')
);

foreach ($new_entries as $new) {
  list ($day, $start, $end) = $new;

  $q_day   = "'$day'";
  $q_end   = "'$end'";
  $q_start = "'$start'";

  $sql = <<<EOQ
    INSERT INTO `sample` (day,start,end)
    SELECT $q_day, $q_start, $q_end FROM DUAL
    WHERE NOT EXISTS (
      SELECT * FROM `sample`
      WHERE day = $q_day AND (
        $q_start < end AND $q_end > start
      )
    )
EOQ;

  mysql_query ($sql) or die (mysql_error ());

  if (mysql_affected_rows () == 0)
    print join (' ', $new) . " was not inserted!\n";
  else
    print join (' ', $new) . " was inserted!\n";
}

This would output:

Monday 00:00:00 23:59:00 was not inserted!
Monday 12:00:00 15:00:00 was inserted!
Monday 07:00:00 10:00:00 was not inserted!

edited May 23 '17 at 11:48

Community

1
1

answered Dec 18 '11 at 10:14

Filip Roséen - refp

62,493
20
150
196

I added the check for the encapsulating time to my function. If there's any other time conditions you can think of, let me know. – Jared Farrish Dec 18 '11 at 13:24
lol: stating I'm wrong without showing any case where my query is wrong and building a more complex query... Sorry but your solution is too complex, the overlap problem is always solved with new_start < existing_end AND new_end > existing_start; find a case where it does not work and your solution is fixing it. – regilero Dec 18 '11 at 13:36
http://stackoverflow.com/questions/6571538/checking-a-table-for-time-overlap http://stackoverflow.com/questions/2545947/mysql-range-date-overlap-check – regilero Dec 18 '11 at 13:39
I still haven't quite comprehended how @regilero's `WHERE` works... It's bothering me. – Jared Farrish Dec 18 '11 at 14:15
@JaredFarrish It's very simple. If the `new_end` is larger than `start` and `new_start` is less than `end` it means that they are overlapping. It's easier if you scrabble down some data on a piece of paper and check for yourself, or query a database and see the result in real time. – Filip Roséen - refp Dec 18 '11 at 14:23
New and current schedule being compared: A new start time past the current schedule's end won't matter, and if it is before the current schedule's end, a new end time can't occur before the new start time, so if it's greater than the current schedule's start, it *has* to fall within that current schedule somehow. It was the second check that was puzzling me; all it does is verify whether the new end puts that interval into the current schedule being tested. And it's my opinion you owe regilero's answer an upvote. – Jared Farrish Dec 18 '11 at 14:42
thank you very much guys it is very big help for me specially Im very new in php . @refp the Codes works perfect. thank you very much but their something wrong when I integrate it do you have any idea to work with this. – jojo Dec 19 '11 at 00:56
here is the actual code -->1. http://codepad.org/zOxi06eW 2. http://codepad.org/p0WV08CH – jojo Dec 19 '11 at 01:01
$new_entries = array ( array('Monday', '00:00:00', '23:59:00'), array('Monday', '12:00:00', '15:00:00'), array('Monday', '07:00:00', '10:00:00') ); --> how to pass the value from test1.php which includes multiple array of records – jojo Dec 19 '11 at 01:06
@jojo no problem, please mark the answer as accepted to flag this thread as solved. What problems are you experiencing, trying to get the $_POST values into an array structured as `$new_entries`? – Filip Roséen - refp Dec 19 '11 at 01:14
Im sorry i'll mark it now. Im new here stackoverflow. you got it sir pass the $_POST to $new_entrries then execute the rest of it. Again sir thank you very much for such an early gift for christmas it's a big help for me specially im new in php. tnx again. – jojo Dec 19 '11 at 01:18
@jojo http://codepad.org/rVzYGCG4 something like that in "test2.php" should be sufficient, if you have further questions you should create a new question-thread here at stackoverflow. – Filip Roséen - refp Dec 19 '11 at 01:39

Jared Farrish · Answer 3 · 2011-12-18T13:51:33.240

I would recommend the MySQL TIME type method if you can. Here is a method in PHP, though, in case you need it.

Note, it relies on time being in the 24 hour clock, which means that 22:00 is 10pm. Also, I'm not handling 0:00 as being midnight with this code, and it doesn't span across days (ie, 22:00 and 5:00). This is simply a plain, same-day check, and it converts the times into integer representations and then compares the times as numbers.

You will also need to figure out how you're going to get your own $schedules array setup. You could pass it in as another argument, for instance.

EDIT

An approach based on the one Regilero uses:

function checkNewTime($day, $start, $end, &$error) {
    $_start = str_replace(':', '', $start);
    $_end = str_replace(':', '', $end);
    $error = '';
    $schedules = array(
        'Monday' => array(
            array('1:00', '3:00'),
            array('6:00', '7:00'),
            array('8:00', '10:00')
        )
    );

    if ($_end <= $_start) {
        $error = "The new start time ($start) cannot be after the end time ($end).";
        return false;
    }

    $schedules = $schedules[$day];
    $c_schedules = count($schedules);

    for ($i = 0; $i < $c_schedules; $i++) {
        $interval = "{$schedules[$i][0]} and {$schedules[$i][1]}";

        $interval_start = str_replace(':', '', $schedules[$i][0]);
        $interval_end = str_replace(':', '', $schedules[$i][1]);

        if ($_start < $interval_end && $_end > $interval_start) {
            $error = "The start is between schedules time $interval.";
            return false;
        }

    }

    return true;
}

http://codepad.org/hznpATft

The function:

function checkNewTime($day, $start, $end, &$error) {
    $_start = str_replace(':', '', $start);
    $_end = str_replace(':', '', $end);
    $error = '';
    $schedules = array(
        'Monday' => array(
            array('1:00', '3:00'),
            array('6:00', '8:00'),
            array('8:00', '10:00')
        )
    );

    if ($_end <= $_start) {
        $error = "The new start time ($start) cannot be after the end time ($end).";
        return false;
    }

    $schedules = $schedules[$day];
    $c_schedules = count($schedules);

    for ($i = 0; $i < $c_schedules; $i++) {
        $interval = "{$schedules[$i][0]} and {$schedules[$i][1]}";

        $interval_start = str_replace(':', '', $schedules[$i][0]);
        $interval_end = str_replace(':', '', $schedules[$i][1]);

        if ($_start > $interval_start && $_start < $interval_end) {
            $error = "The start is between schedules time $interval.";
        }

        if ($_end < $interval_end && $_end > $interval_start) {
            $error .= " The end is between schedule times $interval.";
        }

        if ($_start < $interval_start && $_end > $interval_end) {
            $error .= " The schedule encapsulates the schedule times $interval.";
        }

        if ($error != '') return false;
    }

    return true;
}

The testcase:

$times = array(
    array('5:00','8:00'),
    array('3:30','6:00'),
    array('4:30','5:00'),
    array('5:30','7:00'),
    array('9:00','10:00'),
    array('9:30','11:00'),
    array('21:30','12:00'),
    array('11:30','11:30'),
    array('12:30','20:00'),
    array('15:30','18:00'),
    array('12:30','19:00'),
    array('19:30','24:00'),
    array('17:30','23:45')
);

$c_times = count($times);
$error = '';

for ($i = 0; $i < $c_times; $i++) {
    if (checkNewTime('Monday', $times[$i][0], $times[$i][1], $error)) {
        echo "{$times[$i][0]}, {$times[$i][1]} does not conflict.\n\n";
    } else {
        echo "{$times[$i][0]}, {$times[$i][1]} does conflict.\nError: $error\n\n";
    }
}

http://codepad.org/pEq9Wdh4

Outputs:

5:00, 8:00 does conflict.
Error:  The schedule encapsulates the schedule times 6:00 and 7:00.

3:30, 6:00 does not conflict.

4:30, 5:00 does not conflict.

5:30, 7:00 does conflict.
Error:  The end is between schedule times 6:00 and 8:00.

9:00, 10:00 does conflict.
Error: The start is between schedules time 8:00 and 10:00.

9:30, 11:00 does conflict.
Error: The start is between schedules time 8:00 and 10:00.

21:30, 12:00 does conflict.
Error: The new start time (21:30) cannot be after the end time (12:00).

11:30, 11:30 does conflict.
Error: The new start time (11:30) cannot be after the end time (11:30).

12:30, 20:00 does not conflict.

15:30, 18:00 does not conflict.

12:30, 19:00 does not conflict.

19:30, 24:00 does not conflict.

17:30, 23:45 does not conflict.

PHP Time Filtering and MYSQL Filtering

3 Answers3

Sample implementation in PHP querying the database: