使用PHP从SQL数据库显示重复日期 [英] Display recurring dates from SQL Database using PHP

问题描述

我正在访问另一个网络公司创建的数据库，以检索当前客户的事件信息.我的客户输入事件的信息，并记下该日期是否重复.我正在尝试显示所有重复日期.到目前为止，我已经能够显示所有内容，包括定期日期和重复发生的日期.

I'm accessing a database created by another web company to retrieve event information for my current client. My client enters info for events and notes whether the date is recurring or not. I'm trying to display all the recurring dates. So far I have been able to get everything to display, regular dates as well as recurring.

表的布局如下:

• 事件
• Events_Recurring

这是事件"表的一部分更大的图片

Here is part of the Events table BIGGER PICTURE

这是Events_Recurring表的外观

This is what the Events_Recurring table looks like

当客户端将其检查为重复发生时，events_recurring表会创建一个包含事件ID和其他信息的行，例如事件在一周中的哪一天或一周中的某个月发生.

When the client checks it as recurring, the events_recurring table creates a row with the Event ID and other information like what day of the week or month the event is recurring on.

我只是不确定如何显示重复出现的特定ID的倍数.我有一个可以访问的开始日期和结束日期，以及该日期是在星期几.

I'm just not sure how to display multiples of that certain ID that is recurring. I have a start date, and end date I can access, as well as what day of the week it is recurring on.

例如:如果此事件在每个星期四再次发生.而且我知道它是从1月1日开始到1月31日结束的，我能经历一下并在1月的每个星期四的日期进行4个不同的事件吗?

So for example: If this event reoccured every thursday. and I knew it started on Jan 1st and ended Jan 31st, can I run through that and spit out 4 different events all with the date of every Thursday in January?

这是我正在使用的完整代码，试图弄清楚这一点有些混乱.我正在检查底部的复发情况

Here is the full code I am working with, it's a little messy while trying to figure this out. I'm checking for the recurrence towards the bottom

// Access external database
$events_db = new wpdb(TOP SECRET CREDENTIALS HERE);
$events_db->show_errors();

if ($events_db) :
    // Query Events Database
    $events = $events_db->get_results(
        "
        SELECT ID, RequestDateStart, RequestDateEnd, Ministry, RequestTimeStart, EventName, CoordinatorName, EventDescription, Location
        FROM gc_events
        WHERE PrivateEvent = 0
        AND Ministry = 15
        AND date(RequestDateStart)>=date(NOW())
        ORDER BY RequestDateStart
        "
    );

    // Create the event data that will be displayed
    foreach ($events as $event) :

        // Store Event ID in a variable
        $masterID = $event->ID;

        echo '<div class="col-12">';

        echo '<strong>ID:</strong> ' . $event->ID . '<br /><strong>Event Name:</strong> ' . $event->EventName . '<br /><strong>Leader:</strong> ' . $event->CoordinatorName . '<br /><strong>Date:</strong> ' . date('l, F j',strtotime($event->RequestDateStart)) . '<br /><strong>Start Time:</strong> ' . date('g:i a',strtotime($event->RequestTimeStart));

        // CHECK IF RECURRING

        $recurring_events = $events_db->get_results(
            "
            SELECT gc_event_id, period, day
            FROM gc_event_recurring
            WHERE gc_event_id = '$masterID'
            "
        );

        foreach ($recurring_events as $recurring_event) :
        if ($recurring_event->period === 'week') {

        echo '<div class="col-12"><strong>&uarr; WEEKLY</strong><br />';

        echo $recurring_event->day;

        echo '</div>';

        }
        endforeach;

        echo '</div>';

    endforeach;
endif;

我现在得到的结果(带有重复发生的事件)是

事件:每周祈祷
日期:2013年2月1日

Event: Weekly Prayer
Date: Feb 1, 2013

我想要的结果是

事件:每周祈祷
日期:2013年2月1日

Event: Weekly Prayer
Date: Feb 1, 2013

事件:每周祈祷
日期:2013年2月8日

Event: Weekly Prayer
Date: Feb 8, 2013

事件:每周祈祷
日期:2013年2月15日

Event: Weekly Prayer
Date: Feb 15, 2013

事件:每周祈祷
日期:2013年2月22日

Event: Weekly Prayer
Date: Feb 22, 2013

这将是开始日期为2月1日，结束日期为2月28日.

This would be if the start date was Feb 1st and end date was Feb 28th.

推荐答案

        foreach ($events as $event) :

                // Store Event ID in a variable
                $masterID = $event->ID;

                echo '<div class="col-12">';

                echo '<strong>ID:</strong> ' . $event->ID . '<br /><strong>Event Name:</strong> ' . $event->EventName . '<br /><strong>Leader:</strong> ' . $event->CoordinatorName . '<br /><strong>Date:</strong> ' . date('l, F j',strtotime($event->RequestDateStart)) . '<br /><strong>Start Time:</strong> ' . date('g:i a',strtotime($event->RequestTimeStart));

                // CHECK IF RECURRING

                $recurring_events = $events_db->get_results(
                    "
                    SELECT gc_event_id, period, day
                    FROM gc_event_recurring
                    WHERE gc_event_id = '$masterID'
                    "
                );

                foreach ($recurring_events as $recurring_event) :
                if ($recurring_event->period == 'week') {
                $StartDate = strtotime($event->RequestDateStart);
                $EndDate = strtotime($event->RequestDateEnd);
$TotalDays = round(($EndDate-$StartDate)/(60*60*24*7));
                for($i = 0 ;$i<($TotalDays-1);$i++)
                {
                $StartDate += (60*60*24*7);
                echo '<div class="col-12">';

                echo '<strong>ID:</strong> ' . $event->ID . '<br /><strong>Event Name:</strong> ' . $event->EventName . '<br /><strong>Leader:</strong> ' . $event->CoordinatorName . '<br /><strong>Date:</strong> ' . date('l, F j',$StartDate) . '<br /><strong>Start Time:</strong> ' . date('g:i a',strtotime($event->RequestTimeStart));


                }


                }
                endforeach;

                echo '</div>';

            endforeach;

尝试一下，告诉我是否可行

try this and tell me if it works

这篇关于使用PHP从SQL数据库显示重复日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！