Hi I am trying to display appointments in a database and would like to display all appointments for a week. The time of the day appears on the left and vertical columns with the names of appointments on the right. The code below only generates a single column of names. Would Time Mon Tue Wed Thu Fri Sat Sun ---------------------------------------------------------------------- 07:00AM name name name name name name name ---------------------------------------------------------------------- 07:15AM name name name name name name ------------------------------------------------------------ to ------------------------------------------------------------ 04:00PM name name <? function calendar_event_list_play($date $db_host, $db_user, $db_password, $db_id) { //connnect to database script here /**---------------get first day of the week for date ---------------**/ /**subtract the difference between monday of the week and start date from start_date for the start_date**/ $new_start = "2008-03-04"; /**-------------------------week day dates--------------------------**/ //monday $date_day1 = date("Y-m-d",strtotime($new_start)); $week_day1 = "Mon";//550 //tuesday $wk_day2 = strtotime($new_start); $date_day2 = date("Y-m-d",strtotime("+1 days",$wk_day2)); $week_day2 = "Tue";//551 //wednesday $wk_day3 = strtotime($new_start); $date_day3 = date("Y-m-d",strtotime("+2 days",$wk_day3)); $week_day3 = "Wed";//552 //thursday $wk_day4 = strtotime($new_start); $date_day4 = date("Y-m-d",strtotime("+3 days",$wk_day4)); $week_day4 = "Thu";//553 //friday $wk_day5 = strtotime($new_start); $date_day5 = date("Y-m-d",strtotime("+4 days",$wk_day5)); $week_day5 = "Fri";//554 //saturday $wk_day6 = strtotime($new_start); $date_day6 = date("Y-m-d",strtotime("+5 days",$wk_day6)); $week_day6 = "Sat";//555 //sunday $wk_day7 = strtotime($new_start); $month_day7 = date("F d",strtotime("+6 days",$wk_day7)); $date_day7 = date("Y-m-d",strtotime("+6 days",$wk_day7)); $week_day7 = "Sun";//556 /************************configuration*************************/ $add_time = 900; //15 min appointment time interval $start_time = "08:00:00"; $end_time = "16:00:00"; $status = A; /*************** this section displays the appointments***********/ //search area display area layer and table echo "<table width=\"100%\" border=\"0\">"; echo"<tr align=\"center\" bgcolor=\"#FFFFFF\" height=\"\">"; echo" <td width=\"100%\" > <div id=\"Layer2\" style=\"position:absolute;\"> <div id=\"pat-dash-scroll-box2\" style=\"overflow: \">\n"; //table begins echo "<table width=\"98%\" height=\"332\" left =\"4\" \">\n"; /**-------------------declare arrays-----------------**/ //Storing the rows rather than outputting them immediately //declare arrays for availablility $avail_day1 = array();//monday $avail_day2 = array();//tuesday $avail_day3 = array();//wednesday $avail_day4 = array();//thursday $avail_day5 = array();//friday $avail_day6 = array();//saturday $avail_day7 = array();//sunday //declare arrays for events $event_day1 = array();//monday $event_day2 = array();//tuesday $event_day3 = array();//wednesday $event_day4 = array();//thursday $event_day5 = array();//friday $event_day6 = array();//saturday $event_day7 = array();//sunday //loop through the 7 days of the week to load arrays for($i = 0; $i < 7; $i++) { //start with Monday: $day = 550; $day = $day + $i;//increment day code //increment day to the end date of week $wk_start = strtotime($new_start); $event_date = date("Y-m-d", strtotime("+$i days", $wk_start)); /**-------get availability config and event type info----**/ $query = "SELECT DISTINCT(a.time_id), a.start_time, a.end_time, c.colour FROM available a, type_display c WHERE a.type_code = c.type_code AND '$event_date' BETWEEN a.start_date AND a.end_date AND a.week_day = '$day' GROUP BY a.start_time"; $result = mysqli_query($mysqli, $query) or die('Error, query failed'); while($row = mysqli_fetch_array($result)) { SWITCH($i) { case 0: $avail_day1[] = $row; break; case 1: $avail_day2[] = $row; break; case 2: $avail_day3[] = $row; break; case 3: $avail_day4[] = $row; break; case 4: $avail_day5[] = $row; break; case 5: $avail_day6[] = $row; break; case 6: $avail_day7[] = $row; break; } } /**----------------appointment search by date-------------------**/ $query = "SELECT a.event_id, a.event_date, a.event_time, a.duration, a.event_type, p.last_name FROM cal_appointment a, pat_patient p WHERE a.patient_id = p.patient_id AND a.status = '$status' AND a.event_date = '$event_date' GROUP BY a.event_id, a.event_date, a.event_time, ORDER BY a.event_time, p.last_name "; $result = mysqli_query($mysqli, $query) or die('Error, query failed'); while($row = mysqli_fetch_array($result)) { SWITCH($i) { case 0: $event_day1[] = $row; break; case 1: $event_day2[] = $row; break; case 2: $event_day3[] = $row; break; case 3: $event_day4[] = $row; break; case 4: $event_day5[] = $row; break; case 5: $event_day6[] = $row; break; case 6: $event_day7[] = $row; break; } } } for($i = 0; $i < 7; $i++) { //Loop to display the work hours for($time = $start_time; $time <= $end_time; $time += $add_time) { //format 24 hour time interval for passing via url $interval_24hr = date("H:i:s", $time); //loop through array to diaplay event type colour and labeling foreach ($avail_day1 as $group_segment) { /**---------------event type display-------------------------**/ //diaplay event type colour and labeling to event end time if($interval_24hr >= $group_segment['start_time'] && $interval_24hr <= $end_time) { $seg_colour = "#".$group_segment['colour']; $time_id = $group_segment['time_id']; } //limit diaplay event type colour and labeling to event end time elseif($interval_24hr > $end_time) { $seg_colour = ""; } } /**-----------------------event time listing------------------------**/ echo "<tr>"; //Output the time interval label echo"<td width=\"8%\" height=\"15\" bgcolor=\"\" align=\"center\"> <div id=\"cal-number\" style =\"\"> <ul> <li>".date("h:i A", $time)."</li> </ul> </div> </td>"; //loop to display patient appointments foreach ($event_day1 as $event) { list($event_hr,$event_min,$event_sec) = split(":",$event['event_time']); //convert event time for comparison $event_time = mktime($event_hr, $event_min, $event_sec); //Event falls into this hour if($event_time >= $time && $event_time < ($time + $add_time)) { //event id $event_id = $event['event_id']; //patient id $patient_id = $event['patient_id']; //format patient name if(empty($event['last_name'])) { $patient_name = $seg_desc; } else { $patient_name = $event['last_name']; } //format date $db_event_date = $event['event_date']; foreach ($avail_day1 as $group_segment) { if($interval_24hr >= $group_segment['start_time'] && $interval_24hr <= $end_time) { $seg_colour = "#".$group_segment['colour']; $time_id = $group_segment['time_id']; } } //appointment type colour if(empty($patient_name)) { $bgcolor = $seg_colour; } echo "<td width=\"12%\" height=\"10\" bgcolor=\"$seg_colour\" align =\"left\"> <a href =\"../calendar_form.php? u_time=$interval_24hr&u_date=$db_event_date\"> $name </a></div></td>\n"; }//end if }//end foreach echo "</tr>"; }//end for } echo "</table>"; echo "</td>"; echo "</tr>"; echo "</div>"; } ?> PHP:
What is your required output look like? Print the 2nd query and run it in MySQL to see what sort of output it gives.
Hi Thanks for responding. I ran both quries. Note: The example uses 15 min interval but the database is set for 10 minutes. first query time_id |start_time |end_time |colour 803 08:00:00 08:10:00 FFCC33 746 08:10:00 08:40:00 00CC66 747 08:40:00 09:10:00 FFCC33 724 10:00:00 10:15:00 FFCC33 732 10:10:00 10:20:00 FFCC33 725 10:15:00 10:30:00 00CC66 727 10:30:00 10:40:00 FFCC33 728 10:40:00 11:00:00 00CC66 729 11:00:00 11:10:00 FFCC33 730 11:10:00 11:30:00 00CC66 733 11:30:00 11:40:00 FF001A 734 11:40:00 13:00:00 00CC66 723 12:00:00 13:15:00 FF6600 742 13:00:00 13:10:00 FFCC33 736 13:10:00 13:20:00 00CC66 741 13:20:00 13:40:00 FFCC33 737 13:30:00 13:40:00 00CC66 740 13:40:00 13:50:00 FFCC33 738 13:50:00 14:00:00 00CC66 743 14:00:00 14:10:00 FFCC33 744 14:10:00 14:20:00 00CC66 739 14:20:00 14:30:00 FFCC33 second query: event_id | event_date |event_time |duration |event_type |last_name ---------------------------------------------------------------- 6170 |2008-03-18 |08:00:00 |00:10:00 |E |Adams ---------------------------------------------------------------- 6171 |2008-03-18 |08:10:00 |00:10:00 |E |Armstrong ---------------------------------------------------------------- I have any experience with multi dementional arrays. I am playing around them to see if that is the solution.
You have it exactly. Just take out the first Sunday. Thanks for responding and the offer. a) I am unemployed at present and cannot afford $70. b) I enjoy learning and I won't if get you to complete the task. c) This is preparation for working on a monthly calendar that display availablity. Display the number of appointments on each available day by using differrent colours. I will learn how to use hover an click on daily cells to access that day of the month. Note: This is a project for my self that I started play with 3 years ago