php - 此查询的更简单方法 : If {current date} is a {weekly/fortnightly/monthly} repeat/multiple of {start_date}

Question

我们有一个scheduled_jobs表，该表包含以下两列:

重复类型:每周/每两周/每月

开始日期:重复的起始日期，即如果重复类型为每月且开始日期设置为 2015-12-05，则它将在每个月的 5 号重复。

以下查询是当前正在使用的代码片段:

<?php
    foreach(range(1,100) as $num)
    {
        $numtimestwo = $num * 2;

        $query = "SELECT * FROM scheduled_jobs WHERE deleted != TRUE AND ((DATE_ADD(DATE(beginning_date), INTERVAL $num MONTH) = CURDATE() AND repeat_type = 'monthly') OR (DATE_ADD(DATE(beginning_date), INTERVAL $numtimestwo WEEK) = CURDATE() AND repeat_type = 'fortnightly') OR (DATE_ADD(DATE(beginning_date), INTERVAL $num WEEK) = CURDATE() AND repeat_type = 'weekly'))";

        // If result is not empty, process the scheduled job
    }
?>

但是我注意到一些问题，随着 begin_date 变得越来越旧，这最终会停止工作，似乎应该有一个更好的解决方案。

Answer 1

我使用 PHP's diff method 解决了该问题对于 DateTime 对象。

下面的函数采用 $type(“每日/每周/每两周/每月/六个月/每年”)和 $beginning_date(日期字符串)。

例如。如果您有一个类型为“每月”的日期“2010-01-01”，并且您希望每个月的第一天返回 true，那么这将起作用。类型为“每周”的日期“2015-06-05”将在该日期之后每 7 天返回 true。

function checkInterval($type, $beginning_date)
{
    // Get DateTime for today, and DateTime object for $beginngin_date input
    $today = date("Y-m-d");
    $today = new DateTime($today);
    $beginning_date = new DateTime($beginning_date);
    // Diff the two dates, and get the save variables for the difference in days, months, years, as well as total days passed ($interval->days)
    $interval = $beginning_date->diff($today);
    $difference_d = $interval->d;
    $difference_m = $interval->m;
    $difference_y = $interval->y;
    $difference_total = $interval->days;
    // If beginning date is today, do not proceed
    if($difference_total < 1)
    {
            return false;
    }
    // If daily, return true all days
    if($type == "daily")
    {
            return true;
    // If weekly, Return true on the day difference being a multiple of 7 days or 0
    } else if($type == "weekly") {
            if($difference_d == "7" || $difference_d == "14" || $difference_d == "21" || $difference_d == "28" || $difference_d == "0")
            {
                    return true;
            }
    // If fortnightly, Return true on the day difference between a multiple of 14 days or 0
    } else if ($type == "fortnightly") {
            if($difference_d == "14" || $difference_d == "28" || $difference_d == "0")
            {
                    return true;
            }
    // If monthly, only return true when it is same day every month (0)
    } else if ($type == "monthly") {
            if($difference_d === 0)
            {
                    return true;
            }
    // If biannually, return true when there's a 6 month difference, and it's the same day of the month
    } else if ($type == "six months") {
            if($difference_m == "6" && $difference_d == "0")
            {
                    return true;
            }
    // Return true when year difference is greater than 0, and day and month are equal (0)
    } else if ($type == "yearly") {
            if($difference_d == "0" && $difference_m == "0" && $difference_y > 0)
            {
                    return true;
            }
    // Else no matches, return false
    } else {
            return false;
    }
    return false;
}