editing appointment info in data table. Hi I am having problems how to proceed in editing appointment info. In this example I have a meeting (code 201) on Monday (day code 550) from 09:00:00 to 12:00:00. This is stored in the data table as: start_time end_time start_date end_date week_day type_code 09:00:00 12:00:00 2008-01-01 2008-01-01 550 201 If I decided need to change the time range for the appointments to meeting 09:00 to 10:00, break 10:00 to 11:00 and meeting 11:00 to 12:00. What is the best way to edit the data table to refelect that I am taking a break from 10:00 to 11:00? 1) I am having trouble determining how to extract the 10:00 to 11:00 time range from the existing 09:00 to 12:00 time range found in the data table to get these results. start_time end_time start_date end_date week_day type_code 09:00:00 10:00:00 2008-01-01 2008-01-01 550 201 10:00:00 11:00:00 2008-01-01 2008-01-01 550 202 11:00:00 12:00:00 2008-01-01 2008-01-01 550 201 2) To get the above results should I copy the existing data and insert two new entries (10 to 11 and 11 to 12) and update the existing entry to reflect 9 to 10? 3) What formulas/functions can I use to allow changes to any part of the time range? Note: This is a dynamic system. Appointment time is displayed in 15 minute interval usiing the start and end time eg. 09:00 - 09:15 09:15 - 09:30 09:30 - 09:45 09:45 - 10:00 10:00 - 10:15 10:15 - 10:30 10:30 - 10:45 and so on to 11:45 - 12:00.
just developed a 'availability' page for a website where you can see the availability of a nice holiday chalet in France (PM me if you like to have more info about the Chalet ) The availability there is always from Saturday 15:00 until next week Saturday 10:00am. To create the MySQL date/time from only a begin-date "dd-mm-yyyy" I check the begin date to be on a Saturday. Then I use the following to get the next week Saturday date and no need to care about month/year changes. Note the $day+7 in he second line $from = strftime("%Y-%m-%d 15:00:00",mktime(15,0,0,$month,$day,$year)); $to = strftime("%Y-%m-%d 10:00:00",mktime(10,0,0,$month,$day+7,$year)); Code (markup): In your case, adding 2 new entries and change the existing might be the easiest way, as handling dates and times can give a big headache with respect to regional settings The above functions allow changing dates and times. More detailed information on http://nl2.php.net/manual/en/function.strftime.php
Hi Thanks for the response. This is the solution I arrived at. I am open to changing it <?php /**NOTE: Where editing of time/date range is between existing start and end time/date range. The existing range is split into 3 portions: beginning (update), middle (insert1) and end (insert2). update: a)time: use existing time range, b)date: existing start to new start date(insert1) minus 1 (previous day) insert1: a)time: new time range b)date: new date range insert2: a)time: use existing time range b)date: new end date plus 1 (following day) to existing end date //edited time range is before/after existing end time range update: a)time: use existing time range, b)date: existing start to edition(insert1) start date minus 1 (previous day) insert1: a)time: new b)date: new **/ /**-----------------------variables--------------------------**/ //table data being edited: $old_start_date = "2008-01-01"; $old_end_date = "2008-01-10"; $old_type = 201; $old_start_time = "09:00:00"; $old_end_time = "12:00:00"; //selected from current form: $new_start_date = "2008-01-01"; $new_end_date = "2008-01-04'; $new_type = 202; $new_start_time = "10:00:00"; $new_end_time = "11:00:00"; /**--------------values for update and insert query---------------**/ //for spliting date range into three: range 1 end date (- one day gives prevoius day) $old_sec = strtotime($old_start_date); $new_sec = strtotime($new_start_date); $diff_day = floor((($new_sec - $old_sec) - 86400)/86400); $mod_end = date("Y-m-d", strtotime("+$diff_day days",$old_sec)); //for spliting date range into three: range 3 start date (+ one day gives next day) $end_sec = strtotime($new_end_date); $start_sec = strtotime($new_start_date); $diff_day = floor(((($end_sec - $start_sec)) + 86400)/86400); $mod_start = date("Y-m-d", strtotime("+$diff_day days",$start_sec)); if($new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time == $old_start_time && $new_end_time == $old_end_time) { //updates existing time range: $update_start_time = $new_start_time; $update_end_time = $new_end_time; $update_start_date = $new_start_date; $update_end_date = $new_end_date; $update_type = $new_type; } elseif($new_start_date > $old_start_date && $new_end_date < $old_end_date) { //updates existing time range: $update_start_time = $old_start_time; $update_end_time = $old_end_time; $update_start_date = $old_start_date; $update_end_date = $mod_end; $update_type = $old_type; //values for insert1 middle time and date range $insert1_start_time = $new_start_time; $insert1_end_time = $new_end_time; $insert1_start_date = $new_start_date; $insert1_end_date = $new_end_date; $insert1_type = $new_type; //values for insert2 end time and date range $insert2_start_time = $old_start_time; $insert2_end_time = $old_end_time; $insert2_start_date = $mod_start; $insert2_end_date = $old_end_date; $insert2_type = $old_type; } elseif($new_start_date == $old_start_date && $new_end_date == $old_end_date || $new_start_date == $old_start_date && $new_end_date < $old_end_date || $new_start_date > $old_start_date && $new_end_date == $old_end_date) { if($new_start_date == $old_start_date && $new_end_date < $old_end_date) { //updates existing time range: $update_start_time = $old_start_time; $update_end_time = $old_end_time; $update_start_date = $mod_start; $update_end_date = $old_end_date; $update_type = $old_type; //values for insert1 middle time and date range $insert1_start_time = $new_start_time; $insert1_end_time = $new_end_time; $insert1_start_date = $new_start_date; $insert1_end_date = $new_end_date; $insert1_type = $new_type; } elseif($new_start_date > $old_start_date && $new_end_date == $old_end_date) { //updates existing time range: $update_start_time = $old_start_time; $update_end_time = $old_end_time; $update_start_date = $old_start_date; $update_end_date = $mod_end; $update_type = $old_type; //values for insert1 middle time and date range $insert1_start_time = $new_start_time; $insert1_end_time = $new_end_time; $insert1_start_date = $new_start_date; $insert1_end_date = $new_end_date; $insert1_type = $new_type; } elseif($new_start_date == $old_start_date && $new_end_date == $old_end_date) { /**Note: the abreviations represent $ust (update start time) $uet (update end time) $ist1 (insert1 start time) $iet1 (insert1 end time) $ist2 (insert2 start time) $iet2 (insert2 end time) **/ //make easy to follow start and end time $A_start = $old_start_time; $A_end = $old_end_time; $C_start = $new_start_time; $C_end = $new_end_time; //new start and end time in existing range if($new_start_time > $old_start_time && $new_end_time < $old_end_time) { //update and 2 inserts, start and end time beginning, //middle and end $ust = $A_start; $uet = $C_start; $ist1 = $C_start; $iet1 = $C_end; $ist2 = $C_end; $iet2 = $A_end; } //new start time begin in and end at end at existing //range end elseif($new_start_time > $old_start_time && $new_end_time == $old_end_time) { //update and insert start and end time $ust = $A_start; $uet = $C_start; $ist1 = $C_start; $iet1 = $A_end; } elseif($new_start_time == $old_start_time && $new_end_time < $old_end_time) { $ust = $C_end; $uet = $A_end; $ist1 = $C_start; $iet1 = $C_end; } elseif($new_start_time < $old_start_time && $new_end_time <= $old_start_time) { //start and end time $ust = $A_start; $uet = $A_end; $ist1 = $C_start; $iet1 = $C_end; } elseif($new_start_time < $old_start_time && $new_end_time > $old_start_time && $new_end_time < $old_end_time) { $ust = $C_end; $uet = $A_end; $ist1 = $C_start; $iet1 = $C_end; } elseif($new_start_time < $old_start_time && $new_end_time > $old_end_time) { //start and end time $ust = $C_start; $uet = $C_end; } elseif($new_start_time >= $old_end_time && $new_end_time > $old_end_time) { //start and end time $ust = $A_start; $uet = $A_end; $ist1 = $C_start; $iet1 = $C_end; } //updates existing time range: $update_start_time = $ust; $update_end_time = $uet; $update_start_date = $old_start_date; $update_end_date = $old_end_date; $update_type = $old_type; //values for insert time and date range $insert1_start_time = $ist1; $insert1_end_time = $iet1; $insert1_start_date = $new_start_date; $insert1_end_date = $new_end_date; $insert1_type = $new_type; //values for insert time and date range $insert2_start_time = $ist2; $insert2_end_time = $iet2; $insert2_start_date = $old_start_date; $insert2_end_date = $old_end_date; $insert2_type = $old_type; } /**--------------------update existing table entry--------------------**/ if($new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time == $old_start_time && $new_end_time == $old_end_time || $new_start_date > $old_start_date && $new_end_date < $old_end_date || $new_start_date == $old_start_date && $new_end_date < $old_end_date || $new_start_date > $old_start_date && $new_end_date == $old_end_date || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time > $old_start_time && $new_end_time < $old_end_time || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time > $old_start_time && $new_end_time == $old_end_time || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time == $old_start_time && $new_end_time < $old_end_time || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time < $old_start_time && $new_end_time <= $old_start_time || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time < $old_start_time && $new_end_time > $old_start_time && $new_end_time < $old_end_time || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time < $old_start_time && $new_end_time > $old_end_time || $new_start_time >= $old_end_time && $new_end_time > $old_end_time && $new_start_date == $old_start_date && $new_end_date == $old_end_date) { //update existing entry (start) //add update query here } /**----------------insert1 for edited data (middle or end range)---------------**/ if($new_start_date > $old_start_date && $new_end_date < $old_end_date || $new_start_date == $old_start_date && $new_end_date < $old_end_date || $new_start_date > $old_start_date && $new_end_date == $old_end_date || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time > $old_start_time && $new_end_time < $old_end_time || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time > $old_start_time && $new_end_time == $old_end_time || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time == $old_start_time && $new_end_time < $old_end_time || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time < $old_start_time && $new_end_time <= $old_start_time || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time < $old_start_time && $new_end_time > $old_start_time && $new_end_time < $old_end_time || $new_start_time >= $old_end_time && $new_end_time > $old_end_time && $new_start_date == $old_start_date && $new_end_date == $old_end_date) { //new insert event $type (end) //add insert query here } /**--------------------insert2 for edited data (end range)-----------------**/ if($new_start_date > $old_start_date && $new_end_date < $old_end_date || $new_start_date == $old_start_date && $new_end_date == $old_end_date && $new_start_time > $old_start_time && $new_end_time < $old_end_time) { //second new insert with existing entry (end) //add insert query here } ?> PHP: