assgar Posted December 29, 2007 Share Posted December 29, 2007 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 ?> Quote Link to comment Share on other sites More sharing options...
PHP_PhREEEk Posted December 29, 2007 Share Posted December 29, 2007 To be honest, I didn't go through your code... That being said, let's discuss the logic of what you're trying to accomplish. Only you know the details of the 'larger picture' of this project, but I'll ask, wouldn't it be easier (logically and programming) to use each EVENT as an individual record with a time stamp system? Most scheduling scripts I've run across do it in this manner... PhREEEk Quote Link to comment Share on other sites More sharing options...
assgar Posted December 29, 2007 Author Share Posted December 29, 2007 Hi thanks replying 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. You point is a good one. The events/appointments are individual inserts eg. 9:00 AM to 10:00 AM. The event/appointment type is used to display colour and labeling (ie: Vacation) for the event/appointment time. This is to indicate the type of appointment permited during specific hours. To reduce the amount of data stored I have opted for time and date range. Quote Link to comment Share on other sites More sharing options...
assgar Posted January 29, 2008 Author Share Posted January 29, 2008 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 ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.