listing schedule time interval and client appointment

**assgar** · Jul 2, 2007 12:09

listing schedule time interval and client appointment
Hi

I am php newbie and I don't know if I am approaching this properly.
I need to creat a scheduler that list the appointment time on the left
and client for a specific appointment time on the left.

NOTE: The appointments are stored in a Mysql database

This is what I am trying to acomplish:
Time interval for appointments are 10 min or 15 min or 30 min, for
this example it is 60 mins

Time | client Name
-------------------------------------------
09:00 AM |
10:00 AM | John Smith
11:00 AM |
to
09:00 PM | Mike Smith

I am getting the time interval on the right to display,
The problem I am having is getting the client name to line up with the
appointment time intervals on the right.

This is what I am getting with the code below:

Time | client Name
------------------------------------------------
09:00 AM | John Smith
10:00 AM | Mike Smith
11:00 AM |
to
09:00 PM |

PHP Code:


<?

  /**--------------------------time interval--------------------**/

  //array with time interval listing found on the left side of the page

  $time_60min_array = array(

  '06:00 AM', '07:00 AM', '08:00 AM', '09:00 AM', '10:00 AM', '11:00 AM', 

  '12:00 PM', '01:00 PM', '02:00 PM', '03:00 PM', '04:00 PM', '05:00 PM', 

  '06:00 PM', '07:00 PM', '08:00 PM', '09:00 PM');

  

  /**-------------select appointment from database-----------------**/

  // select by date to match to time interval listing

  $query = "SELECT distinct(event_id), event_date, event_time, 

                         event_am_pm, first_name, last_name, 

              FROM cal_appointment 

          WHERE event_date = '$event_date'

          ORDER BY event_date, event_am_pm, event_time";



   $result = mysqli_query($mysqli, $query) or die('Error, query failed');

 

   /**-------------------------------title--------------------------**/    

  //Note:<html> open layers and <tables> tags and title goes here

     

    

 /**--------------------------display------------------------**/



     $result = mysqli_query($mysqli, $query) or die('Error, query failed');



     $row_count = count($time_60min_array);//count time interval in array



     for($i=0; $i < $row_count; $i++)

    {

        $row = mysqli_fetch_array($result); 

 

         list($event_id, $event_date, $event_time, 

                                $event_am_pm,$first, $last) = $row;

         

         

           //format time

          $appoint_time = $event_time." ".$am_pm;

        

          //time interval

          $interval =  $time_60min_array[$i];

 

              //match appointment time on right to time listing on left          

             if(in_array($appoint_time,  $interval))

          {

            $s_event_id = $event_id; 

                   $client_name = $last.", ".$first;

          }

        

        //display list

         echo"<tr height=\"10\">

        <td width=30% height=\"10\" bgcolor=\"$bgcolor\" 

                               align=\"center\">$interval</td>

        <td width=\"70%\"  height=\"10\" bgcolor=\"$bgcolor\"> 

                               <span class=\"style20\">$client_name</td>\n";

              echo"</tr>\n";

          }

          

          

  //Note: </html> end layers and </table>

?>

**Dan Grossman** · Jul 2, 2007 14:01

Read your event rows into an array rather than outputting them in the loop through the result set. The way you're doing it now doesn't make sense; you're calling mysqli_fetch_array a certain number of times regardless if there are that many rows to fetch.

Loop over your time intervals instead, and in the second cell of your table, print out an event if one exists in the array you created earlier for that time.

**assgar** · Jul 3, 2007 00:15

Hi

Thanks for the response and suggestion.
I am having a little difficulty interpreting your suggestion.

Can you show me what you mean?

**Dan Grossman** · Jul 3, 2007 00:27

One thing first. Why do you have event_date, event_time and event_am_pm columns in this table? Store a datetime (that's a data type) instead of fudging it with these strings or whatever they are.

A little untested pseudocode...

PHP Code:


$result = mysql_query("SELECT 
    UNIX_TIMESTAMP(event_time) AS `time`, first_name, last_name 
    FROM cal_appointment ...");
//Storing the rows rather than outputting them immediately
while ($row = mysql_fetch_array($result)) {
  $events[] = $row;
}

$start_time = mktime(9, 0, 0); //9AM
$end_time = mktime(18, 0, 0); //6PM
//Loop over the hours from 9AM to 6PM
for ($time = $start_time; $time <= $end_time; $time += 3600) {

  echo "<tr>";
  // Output the time label
  echo "<td>" . date("h:i A", $time) . "</td>";
  echo "<td>";
  foreach ($events as $event) {
    //Event falls into this hour
    if ($event['time'] >= $time && $event['time'] < ($time + 3600)) {
      echo $event['first_name'] . " " . $event['last_name'];
    }
  }
  echo "</td>";
  echo "</tr>";

}

**assgar** · Jul 6, 2007 10:36

Hello
Thanks for the info its was the best way to make this work.

This is the solution, this final code allows you to double book appointments.

PHP Code:


 <? 

 

 /**---------------------------appointment interval--------------------------**/

     SWITCH($time_interval)

      {

           case 10:

             $add_time = 600; //10 min appointment

         break;

                 

         case 15:

             $add_time = 900; //15 min appointment

         break;

 

         case 20:

         $add_time = 1200; //20 min appointment

         break;

 

         case 30:

         $add_time = 1800; //30 min appointment

         break;

 

         case 60:

         $add_time = 3600; //60 min appointment

         break;

 

         default:

         $add_time = 900; //15 min appointment

         break;

    }

     /**-------------------------------search by date-----------------------------------**/

     $query = "SELECT  event_id, event_date, event_time, 

               first_name, last_name

           FROM cal_appointment 

           WHERE a.event_date = '$event'

           GROUP BY a.event_id, a.event_date, a.event_time, 

                    first_name, last_name

           ORDER BY event_time, last_name, first_name

           ";

           

     $result = mysqli_query($mysqli, $query) or die('Error, query failed');



   

   /************************** this section displays events********************************/



    /**-Note: <table> tags etc. go here**/

 

    //declare array

    $events = array();

    

     //Storing the rows rather than outputting them immediately

     while($row = mysqli_fetch_array($result))

          {

              $events[] = $row;

          }



   //Loop over the hours from 9AM to 6PM

   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);



        

          echo "<tr>";

             //Output the time interval label

           echo"<td width=\"10%\" height=\"15\" bgcolor=\"#e6e8fa\" align=\"center\">".date("h:i A", $time)."</td>";

        



      //start of next cell

      foreach ($events 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))

                {

                  //format client name

             $client_name = $event['last_name'].", ".$event['first_name'];

                         

               //looping data diaplayed            

               echo "<td width=\"20%\"  height=\"10\" bgcolor=\"$bgcolor\"><span class=\"style20\"><div style = \"margin-left:10;\">$client_name</td>";

         }//end if

 

         }//end foreach

            

     echo "</tr>";

  }//end for

  

  /**Note: end of </table> etc. goes here**/

}

?>

User Tag List

Thread: listing schedule time interval and client appointment

Thread Tools

Display

listing schedule time interval and client appointment

suggestion

Bookmarks

Bookmarks

Posting Permissions