Hi I need help. I know what I want to accomplish, but I do not know how to do it. WHAT I NEED HELP ACCOMPLISHING: How to do I insert data into a table for a date range of two or more months, where every second or third week should be able to have different events/appointments. POSSIBLE APPROACH: I would like to choose a 7 day cycle/template, or to allow making the weeks different with a 14 day or 21 day or 28 day cycle/template. For example the 14 days cycle represents two weeks where every second week can be different. Day# Day 1 2 Monday (meetings 1:00pm to 3:00pm) 3 Tuesday 4 Wednesday 5 Thursday (breakfast meeting 8:00AM to 9:00AM) 6 Friday 7 8 9 Monday (breakfast meeting 8:00AM to 9:00AM) 10 Tuesday 11 Wednesday (meetings 1:00pm to 3:00pm) 12 Thursday 13 Friday 14 The current code works well for a week or if every week is the same in the date range. See below. The availablity table store different event/appointment types using date and time range. This event/appointment type information is then displayed to the user using a daily schedule format. HOW THE 7 DAY CYCLE DATA IS STORED: Example: Meetings(event_type_code) between 2:30 PM and 4:30 PM for Monday to Friday this is stored in the "availablity" table as seen below. Note: A template group holds the different appointment types for the days of the week as selected. |group_id|start_time|end_time| start_date|end_date | week_day|type_code |26 |14:30:00 |16:30:00| 2007-12-03|2007-12-07| 550 | 201 |26 |14:30:00 |16:30:00| 2007-12-03|2007-12-07| 551 | 201 |26 |14:30:00 |16:30:00| 2007-12-03|2007-12-07| 552 | 201 |26 |14:30:00 |16:30:00| 2007-12-03|2007-12-07| 553 | 201 |26 |14:30:00 |16:30:00| 2007-12-03|2007-12-07| 554 | 201 CODE FOR ONE WEEK CYCLE <? $group_seg = array(); /*get group templates data to apply to schedule. This data contains event/appointment types*/ $query = "SELECT distinct(s.seg_id), s.model_id, w.group_id, s.event_type_code, s.time_from, s.time_to, w.weekday FROM cal_group_week w, cal_day_segment s WHERE s.model_id = w.model_id AND w.group_id = '$group_id' AND s.deleted = 'N' AND w.deleted = 'N'"; $result = mysqli_query ($mysqli, $query); while($row = mysqli_fetch_array($result)) { $group_seg[] = $row; } //loop through segment start and end time foreach($group_seg as $group_segment) { //database stored time from daily model segments $start_time = $group_segment['time_from']; $end_time = $group_segment['time_to']; $group_id = $group_segment['group_id']; $event_type_code = $group_segment['event_type_code']; $day = $group_segment['weekday']; /**-----------------------insert event type/appointment---------------------**/ $cal_query = "INSERT INTO availablity( time_id, group_id, start_time, end_time, start_date, end_date, week_day, type_code) VALUES( null, '$group_id', '$start_time', '$end_time', '$start_date', '$end_date', '$day', '$event_type_code')"; mysqli_query($mysqli, $cal_query)or die(mysqli_error($mysqli)); }//apply group ?> Code (markup):
could u explain it a bit different as i am not understanding what u are trying to accomplish, you mean recurring events that are defined in a cycle?
Hi I hope this better explains what I am trying to accomplish. The events are recurring every month, but not recurring at the same time or on the same day of the week. Thanks
Hi After some time I think I have done it. Let nme know if you have any suggestions how to refine the code. The result below is for a three week (7 day) cycle for the month of January. Note: week days Monday (550) to Friday (554) Result Columns: date range(start date, end date), day of week, time range(start time and end time) inner loop)1 2008-01-01 2008-01-07 550 09:00:00 12:00:00 2008-01-22 2008-01-28 550 09:00:00 12:00:00 2008-01-01 2008-01-07 551 09:00:00 12:00:00 2008-01-22 2008-01-28 551 09:00:00 12:00:00 2008-01-01 2008-01-07 552 09:00:00 12:00:00 2008-01-22 2008-01-28 552 09:00:00 12:00:00 2008-01-01 2008-01-07 553 09:00:00 12:00:00 2008-01-22 2008-01-28 553 09:00:00 12:00:00 2008-01-01 2008-01-07 554 09:00:00 12:00:00 2008-01-22 2008-01-28 554 09:00:00 12:00:00 (inner loop)2 2008-01-08 2008-01-14 550 10:00:00 13:00:00 2008-01-29 2008-01-31 550 10:00:00 13:00:00 2008-01-08 2008-01-14 551 10:00:00 13:00:00 2008-01-29 2008-01-31 551 10:00:00 13:00:00 2008-01-08 2008-01-14 552 10:00:00 13:00:00 2008-01-29 2008-01-31 552 10:00:00 13:00:00 2008-01-08 2008-01-14 553 10:00:00 13:00:00 2008-01-29 2008-01-31 553 10:00:00 13:00:00 2008-01-08 2008-01-14 554 10:00:00 13:00:00 2008-01-29 2008-01-31 554 10:00:00 13:00:00 (inner loop)3 2008-01-15 2008-01-21 550 16:00:00 20:00:00 2008-01-15 2008-01-21 551 16:00:00 20:00:00 2008-01-15 2008-01-21 552 16:00:00 20:00:00 2008-01-15 2008-01-21 553 16:00:00 20:00:00 2008-01-15 2008-01-21 554 16:00:00 20:00:00 <? $max_week = '3'; $start_date = '2008-01-01'; $end_date = '2008-01-31'; /**-------------loop through number of weeks------------**/ for($i = 1; $i <= $max_week; $i++) { //format to two characters $week_num = "0$i"; //detemine start date interval if($i == 1) { $start_date = $start_date;//week 1 } else { //week 2, 3 and 4 $wk_start = strtotime($start_date); $start_date = date("Y-m-d", strtotime("+7 days", $wk_start)); } //flush previous array contents unset ($group_seg); /**------------get group module data to apply to schedule------------**/ $query = "SELECT distinct(s.seg_id) w.group_id, s.time_from, s.time_to, w.weekday FROM group_week w, day_segment s WHERE s.model_id = w.model_id AND w.group_id = '$group_id'"; $result = mysqli_query ($mysqli, $query); while($row = mysqli_fetch_array($result)) { $group_seg[] = $row; } /**------------------- event type info to insert-----------------------**/ //loop through segment start and end time foreach($group_seg as $group_segment) { //database stored time from daily model segments $start_time = $group_segment['time_from']; $end_time = $group_segment['time_to']; $group_id = $group_segment['group_id']; $day = $group_segment['weekday']; //more than one week cycle used interval date if($max_week > 1) { //determine date start incrementation using max_week switch($max_week) { case '1': //1 week $cycle_days = 7; break; case '2': //2 weeks $cycle_days = 14; break; case '3': //3 weeks $cycle_days = 21; break; case '4': //4 weeks $cycle_days = 28; break; } /**----------increment using $cycle_days from above--------**/ for($f = $start_date; $f <= $end_date; $f = date("Y-m-d", strtotime($f . "+ $cycle_days day"))) { //set start date $startdate = strtotime($f); $type_start_date = date("Y-m-d", $startdate); //set end date with addtional 6 days $wk_start = strtotime($type_start_date); $wk_end_date = date("Y-m-d", strtotime("+6 days", $wk_start)); //check incremented end date does not exceed selected end date if($wk_end_date <= $end_date) { $type_end_date = $wk_end_date;//incremented end date } else { $type_end_date = $end_date;//selected end date } /**INSERT STATEMENT GOES HERE**/ } } else { // single week cycle insert selected start and end dates no manipulation needed $type_start_date = $start_date; $type_end_date = $end_date; /**INSERT STATEMENT GOES HERE**/ } }//foreach }//for ?> PHP: