Reoccurrance row's with php/sql ?

[Skylight_lady]

Hi Guys,

 

I have a schedule table in mysql as shown below:

 

CREATE TABLE IF NOT EXISTS `schedule` (
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL DEFAULT '0000-00-00',
  `time` varchar(20) NOT NULL DEFAULT '',
  `desc_type` varchar(24) NOT NULL DEFAULT '',
  `occur` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`sID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

and a code:

<select name="occur" id="occur">
<option value="Once">Once</option>
<option value="Weekly">Weekly</option>
<option value="Bi-Weekly">Bi-Weekly</option>
<option value="Monthly">Monthly</option>
<option value="Bi-Monthly">Bi-Monthly</option>
<option value="Yearly">Yearly</option>
</select>

 

which posts the form into the table via:

$insert = mysql_query("INSERT INTO schedule SET date = '".$date."', time = '".$time."', desc_type = '".$desc_type."', occur = '".$occur."'") or die(mysql_error());

 

Now, this inserts the row into one line. What i want to try and do is to add a few rows depending on the occur option selected via the select box. If it occur's weekly then what i want to do is add a weekly schedule for the full year by adding all those rows in the table for the year. The same will go for the monthly, bi-monthly or yearly.

 

How do i do this?

[redixx]

Well you wouldn't want to insert 52 rows that all have the same data in them.

 

Instead, insert it once and then when you want to output it, just build an array where each element has a date of X number of days added (7 for weekly, 30 for monthly, etc).

 

Maybe something like this? (not tested)

$row = mysql_fetch_assoc($query);

$schedule = array();

$date = strtotime($row['date']);

// if occurance is weekly
for($i=1;$i<=52;$i++)
{
$schedule[] = array('date'=>$date,'time'=>$row['time'],'desc_type'=>$row['desc_type']);

$date = $date + 604800;
}

[Psycho]

Hmm . . . I've never thought about a scenario such as this. My first instinct would be to just create one record and save the occurrence as a parameter. Then just determine the the dates at run-time. But, that may be less efficient than creating multiple records. But, you have the problem that you could only create the multiple instances for a specific time period. If the user creates a meeting to occur each week with no end date, you can't create an infinite amount or records - which is why I assume you asked how to do this for one year. But, if that is the case the "yearly" occurrence has no purpose.

 

So, let's assume you do create individual records for some period into the future. You probably still want some way to tie all of the occurrences together. When I delete a recurring meeting in Outlook it gives me the option of deleting that occurrence or all occurrences.

 

Here is my suggestion:

 

1. When saving the records for a schedule that has multiple occurrences, have an additional field to store an occurrence ID. You could just use a unique random number (for example a timestamp of the submission and the user ID concatenated should do).

 

Then, when the request is submitted and you see there is an occurrence selected, you could use PHP to generate the records needed programmatically and then do a single INSERT of all the records. I'll slap some sample code together and post it here shortly.

 

EDIT: After further thought, I'm going to go back to my original idea of having only one record for the series. I need to look at how I would query and determine the dates for a given period, but it shouldn't be too difficult. But, if you want to delete certain occurrences within a schedule you would probably have to maintain an exception table.

[Psycho]

OK, to use just a single record for each series, I would suggest having an "endOccurence" field int he table. If the user selects anything other than "Once" for the occur value you should store a value for the endOccurence. This should be a date or NULL if it does not have an end date. Then you can use the following query to get all the schedules that fall between a start and end date.

 

SELECT *
FROM schedule
WHERE date <= $endDate
  AND (
date >= $startDate
        OR (occur <> 'Once'
            AND (endOccurence = NULL OR endOccurence >= $startDate)
           )
      )

 

The next step would be to process the records returned to determine the actual dates of the schedules that fall within the time period.

[Skylight_lady]

Hi,

 

Thanks. For your help. I forgot about an end date as it makes sence to have one. What i was mainly looking for is an INSERT statement that adds these records to the database.

 

At the moment, what i can only think that is the best solution, is to use CronJobs to add these INSERT statements once the start dates comes around so it will add one row to the table each week if it is a weekly schedule until the end date is reached.

[Psycho]

What i was mainly looking for is an INSERT statement that adds these records to the database.

 

Yes, I know that was what you asked for, but what I was proposing is that it might not be the best approach. It is a fairly straightforward process to either create all the instances up front (as you asked) or to determine them dynamically at run-time. redixx and I were suggesting the latter. But, I really am ambivalent on which would be the better approach as it really depends on many variables on how it will be used. So, I'll provide some mock code to create all the records to insert (as you requested). You do NOT need a cron job, you can do it when the user submits the form.

 

I left a lot of necessary validation logic out of this code (ensuring dates are actual dates, escaping for DB input, etc.) but this code has the logic for creating all the records based upon the occurrence selection.

//Test input
$_POST['date'] = '2011-3-15';
$_POST['time'] = '12:30 PM';
$_POST['desc'] = 'Meeting';
$_POST['occur'] = 'Weekly';
$_POST['end_date'] = '2011-5-15';

$date = strtotime($_POST['date']);
$time = $_POST['time'];
$desc = $_POST['desc'];
$occur = $_POST['occur'];
$end_date = ($occur!='Once') ? strtotime($_POST['end_date']) : $date;

function nextDate($date, $occur, $end_date)
{
    switch($occur)
    {
        case 'Yearly':
            $date = strtotime(date('Y-m-d', $date) . " +1 Year");
            break;

        case 'Bi-Monthly':
            $date = strtotime(date('Y-m-d', $date) . " +1 Months");
            break;

        case 'Monthly':
            $date = strtotime(date('Y-m-d', $date) . " +1 Month");
            break;

        case 'Bi-Weekly':
            $date = strtotime(date('Y-m-d', $date) . " +2 Weeks");
            break;

        case 'Weekly':
            $date = strtotime(date('Y-m-d', $date) . " +1 Week");
            break;

        case 'Once':
        default:
            //return false if no occurances
            return false;
    }
    //return false if no more valid occurances
    if($date > $end_date) { return false; }
    return $date;
}


$values = array();
while($date!==false)
{
    //Create insert values for current date
    $date_str = date('Y-m-d', $date);
    $values[] = "('$date_str', '$time', '$desc', '$occur')";
    //Get next date based upon occurance
    $date = nextDate($date, $occur, $end_date);
}

$query = "INSERT INTO schedule
            (`date`, `time`, `desc_type`, `occur`)
          VALUES " . implode(", \n", $values);
$result = mysql_query($query) or die(mysql_error())

 

With the test values in the code above, the following would be the resulting query:

INSERT INTO schedule
            (`date`, `time`, `desc_type`, `occur`)
          VALUES ('2011-03-15', '12:30 PM', 'Meeting', 'Weekly'), 
                 ('2011-03-22', '12:30 PM', 'Meeting', 'Weekly'), 
                 ('2011-03-29', '12:30 PM', 'Meeting', 'Weekly'), 
                 ('2011-04-05', '12:30 PM', 'Meeting', 'Weekly'), 
                 ('2011-04-12', '12:30 PM', 'Meeting', 'Weekly'), 
                 ('2011-04-19', '12:30 PM', 'Meeting', 'Weekly'), 
                 ('2011-04-26', '12:30 PM', 'Meeting', 'Weekly'), 
                 ('2011-05-03', '12:30 PM', 'Meeting', 'Weekly'), 
                 ('2011-05-10', '12:30 PM', 'Meeting', 'Weekly')

[Skylight_lady]

Your way is the best solution if i'm not going to use CronJobs. Thanks a million for your help!! I'll go with your solution as it does sound better.

[Psycho]

You know I just thought of a WAY better solution. Change the values of your select list as follows:

<option value="0">Once</option>
<option value="+1 Weeks">Weekly</option>
<option value="+2 Weeks">Bi-Weekly</option>
<option value="+1 Months">Monthly</option>
<option value="+2 Months">Bi-Monthly</option>
<option value="+1 Year">Yearly</option>

 

Then use this replacement function:

function nextDate($date, $occur, $end_date)
{
    switch($occur)
    {
        case '+1 Year':
        case '+2 Months':
        case '+1 Months':
        case '+2 Weeks':
        case '+1 Weeks':
            $date = strtotime(date('Y-m-d', $date) . " $occur");
            break;

        default:
            //return false if no occurances
            return false;
    }
    //return false if no more valid occurances
    if($date > $end_date) { return false; }
    return $date;
}

 

[Skylight_lady]

Thank you. It works perfectly !!