PHP时间过滤和MYSQL过滤

I have 3 columns: Day, start_schedule and end_schedule.

我有3列:Day、start_schedule和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?

我仍然学习php。如何根据数据库中的存储时间过滤输入开始计划和结束计划是否有效?

For example if I want to add times

例如，如果我想增加时间

• start_schedule = 3:00 and end_schedule = 7:00
• start_schedule = 3:00, end_schedule = 7:00

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

因为有6点到8点，你不应该被允许添加这个时间表。

• start_schedule = 7:00 and end_schedule = 11:00
• start_schedule = 7:00, 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.

因为有6点到8点和8点到10点，你不应该被允许添加这个时间表。

3 个解决方案

#1

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

我将在mysql中执行大部分的逻辑，因为它将处理这些类型的表达式，非常简单和直接的方法。

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

在PHP中，在插入新条目之前，我们需要检查一件事:

1. Is end_time less than start_time? If so, abort.
2. end_time是否小于start_time?如果是这样,中止。

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

在MySQL中，当试图插入新条目时，我们需要检查4件事:

1. Is the start_time inside any existent entry? If so, abort.
2. start_time是否存在任何条目?如果是这样,中止。
3. Is the end_time inside any existent entry? If so, abort.
4. 在任何存在的条目中是否有end_time ?如果是这样,中止。
5. Is there any entry starting that would be inside our new entry? If so, abort.
6. 是否有进入我们新条目的条目?如果是这样,中止。
7. Is there any entry ending that would be inside our new entry? If so, abort.
8. 在我们的新条目中有任何条目结尾吗?如果是这样,中止。

The above can actually be refactored into one steps:

以上内容可以重构为一个步骤:

1. 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.
2. 是否我们的开始时间比任何条目的结束时间都要短，并且我们的结束时间是否大于它的开始时间?如果是这样,中止。

---

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.

@regilero的道具，用于发布克劳斯需要的重构版本。

$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!

#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).

如果是MySQL存储，那么您需要执行SQL查询，以便在尝试插入新记录之前重新生成与当前新记录重叠的记录。然后如果你重叠检查不返回任何行你可以安全地插入行(如果这样做是在一个事务,否则有人会插入一些检查和插入,之间做交易,把锁放在桌子上,这样没有人可以插入whild你检查和插入)。

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

要找到重叠的行，你需要这样的查询(myday mystart和myend是你想要插入的新值):

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.

现在如果你有一些scheds重叠两天会更复杂。实际上，您不能在数据库模型中存储这些scheds。如果我想在周日晚上22:00开始，周一下午4:00，甚至周四早上6点…所以，也许你正在打破你的scheds插入几个scheds，每天一个。然后你必须仔细检查你所有的scheds部件是否可以被插入，而不仅仅是其中的一个。当然，在交易中仍然要做所有这些事情。

EDIT:

编辑:

overlap test:

重叠测试:

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

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

#3

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

如果可以的话，我推荐MySQL的时间类型方法。不过，这里有一个PHP方法，以防您需要它。

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.

注意，它依赖于24小时的时间，这意味着22:00是晚上10点。另外，我不把0:00当作午夜使用这个代码，它不会跨越几天(即22:00和5:00)。这只是一个简单的、同日检查，它将时间转换为整数表示，然后将时间作为数字进行比较。

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.

您还需要了解如何获得自己的$schedule数组设置。例如，你可以将它作为另一个参数传入。

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

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:

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

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.