-1

Hi,

I have been trying to code an timetable which allows timeslots to be booked. What I am finding difficult is the schedule settings. So three things, "schedule settings", "find common slots" and "make booking". The "find common slots" and "make booking" seems to almost work. But "schedule settings" is more difficult.

The idea: The timetable is a typical one with days of the week on the top and hours of the day on the left side. Ofcourse bookings should be possible by choosing month and year also. There are three paricipants. Two of these have a schedule. The third one is the person arranging a date for the meeting. Each user must setup his "schedule settings" by indicating those slots' availability for booking.

<style type="text/css">
td { width: 150px; font-size: medium; }
td.open  { background-color: lime; }
td.close { background-color: pink; }
td.maybe { background-color: yellow; }
td.time  { width: 75px; }
</style>

... stuff ...

<table border=1 cellpadding=3>
<tr>
    <td>Hour</td>
    <th>Monday</th>
    <th>Tuesday</th>
    <th>Wednesday</th>
    <th>Thursday</th>
    <th>Friday</th>
</tr>
<?php
//make db connection
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("book", $con);

$first_of_week = "2009-09-28"; // naturally, these would come...
$last_of_week  = "2009-10-02"; // from a form where the person would pick a week


// We join the booking stable TO ITSELF in order to combine 
// the two records for each date and time into a single row in result:
$sql = "SELECT B1.*, B2.Type AS B2Type, B2.booked AS B2Booked "
      . " FROM Bookings AS B1, Bookings AS B2 "
      . " WHERE B1.bookDate BETWEEN '$first_of_week' AND '$last_of_week' "
      . " AND B1.bookDate = B2.bookDate AND B1.bookTime = B2.bookTime "
      . " AND B1.bookPerson < B2.bookPerson " 
      . " ORDER BY bookTime, bookDate ";

$result = mysql_query($sql); // should have error checking here

$priorTime = "no";
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
    // get info for 
    $date    = $row["bookDate"]; // if all goes well, don't need this value
    $time    = $row["bookTime"];
    $avail1  = $row["bookType"];
    $avail2  = $row["B2Type"];
    $booked1 = $row["booked"];
    $booked2 = $row["B2booked"];
    // now pick colors and words
    $booked  = "";
    $avail   = "Available";
    $style   = "open";
    if ( $booked1 != 0 || $booked2 != 0 ) 
    {
        $booked = "Booked";
        $style  = "close";
    }
    if ( $avail1 == "U" || $avail2 == "U" ) 
    {
        $avail = "Unavailable"; 
        $style  = "close";
    } else if ( $avail1 == "M" || $avail2 == "M" ) {
        $avail = "Possible"; 
        $style  = "maybe";
    }
    if ( $priorTime != $time )
    {
        if ( $priorTime != "no" ) echo "</tr>\n"; // close off prior row
        // may need to format the $time value to look pretty here:
        echo '<tr><td class="time">' . $time . "</td>\n";
        $priorTime = $time;
    }
    // now output a cell with right color and word:
    echo '<td class="' . $style . '">' . $avail . "</td>\n";

} // and loop on next date/time

mysql_free_result($result);

?>
</tr>
</table>



<?php
//make db connection
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


$startat = strtotime('2009-09-28'); //I am not sure is this compliant with date_add()?
// build data for 200 weeks (about 4 years)?
for ( $weeknum = 0; $weeknum < 200; ++$weeknum )
{
    // started on a monday, so this will do 5 days, mon to fri
    for ( $daynum = 0; $daynum < 5; ++$daynum )
    {
        // and then for as many hours each day as needed
        for ( $hour = 9; $hour < 18; ++$hour )
        {  // 7 days per week plus the offset within the week
            $interval = "P" . (7 * $weeknum + $daynum) . "D";
            // so add that many days to our start date and then format
            // it into the YYYY-mm-dd string that MySQL insists on:
            $thedate = date_add( $startat, new DateInterval($interval))->format("YY-m-d");//I am not sure is this compliant with date_add()?
            // and the hour is easy:  just append :00:00 and MySQL will like it
            $thetime = ( $hour . ":00:00" );
            // so insert one record for that date and time for the first person...
            // initially, all time slots are marked as Unavailable
            $sql = "INSERT INTO bookings (bookDate,bookTime,bookPerson,`Type`,booked) "
                   . "VALUES('$thedate','$thetime','Jo','U',0)";
            mysql_query( $sql );
            // and same query repeated for the other person
            $sql = "INSERT INTO bookings (bookDate,bookTime,bookPerson,`Type`,booked) "
                   . "VALUES('$thedate','$thetime','Xy','U',0)";
        } // next hour
    } // next day
} // next week
?>

Errors:
Warning: date_add() expects parameter 1 to be DateTime, integer given in C:\.......\timetable.php on line 119
Fatal error: Call to a member function format() on a non-object in C:\.........timetable.php on line 119

Sql script:

--
-- Database: `book`
--
CREATE DATABASE `book` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `book`;

-- --------------------------------------------------------

--
-- Table structure for table `bookings`
--

CREATE TABLE IF NOT EXISTS `bookings` (
  `bookDate` date DEFAULT NULL,
  `bookTime` time DEFAULT NULL,
  `bookPerson` char(2) DEFAULT NULL,
  `Type` char(1) DEFAULT NULL,
  `booked` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bookings`
--

INSERT INTO `bookings` (`bookDate`, `bookTime`, `bookPerson`, `Type`, `booked`) VALUES
('2009-10-10', '09:00:00', 'John', 'U', 0),
('2009-10-10', '09:00:00', 'Mike', 'U', 0);

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

CREATE TABLE IF NOT EXISTS `person` (
  `pid` varchar(3) DEFAULT NULL,
  `First_Name` varchar(50) DEFAULT NULL,
  `Last_Name` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `person`
--

INSERT INTO `person` (`pid`, `First_Name`, `Last_Name`) VALUES
('001', 'John', 'Smith'),
('007', 'James', 'Bond');

The idea as I mentioed is for the timetable with rows showing each persons schedule (i.e. perhaps some form of filter could be use here, the where clause maybe)

I am confused about this. So personA could look at his schedule settings its default values "unavailable". PersonA setsup his timeslots which he will be "available/unavailable/maybe". On this schedule setup page, there are three radiobuttons in the corner. He selects (available/unavailable/maybe) and then clicks a rectangular timeslots on the timetable. So it will probably be like this:

1- Press radio button of choice
2- Click on a time slot
3- Repeat 1,2

Hours ||  Mon | Tue  |  Wed |  Thu |  Fri
==========================================
8:00  || Avail| Avail| Mayb| UnAv|  Mayb
------------------------------------------
9:00  || Mayb | Avail| Mayb| UnAv|  Mayb
------------------------------------------
10:00 || UnAv | Avail| Mayb| UnAv|  Mayb
------------------------------------------
11:00 || Mayb | Avail| Mayb| UnAv|  Mayb
------------------------------------------
12:00 || Avail | UnAv| Avail|UnAv|  UnAv

PersonB will also need to do his schedule settings which will be a different setting(but exactly same idea). This would look better with colour instead of(Mayb/Avail/UnAv).

Once both participants have set up their schedule settings, third participant can "arrange meeting". The code goes through both schedule and comes up with the common one. These common ones are listed and this third participant selects one from the list of dates and makes bookings.

The selection of dates from the list I think can be done quite easily. But what I am trying is this schedule setting up.

Regards,

Mike

Edited by MikeGore: n/a

2
Contributors
8
Replies
36
Views
8 Years
Discussion Span
Last Post by MikeGore
0

Looks like you've spent a lot of time on this Mike! I still don't get what you're having a problem with. I recently created a room booking app for our school, but just had a timetabled table (lessons blocked out - no bookings possible), a bookings table (for teachers wanting to book a particular lesson period) and the usual extras (rooms, users). I won't go into the intricacies, but I assume your solution will be similar to mine, although yours has the added complexity of multiple checks.

1) No need for millions of dates and times to be created as placeholders. Just use a 'schedule' table so that participants only add 'busy' at periods when they are unavailable. Fields will be id/user_id/dated/hourstart/status. The status can be Not Available or Maybe (although I don't understand the use of 'Maybe').

2) A meeting table should have: id/user_id (booker)/dated/hourstart/status (show 1 for OK, 0 for problem - say one of the participants has to cancel). A link table for this should also exist: id/meeting_id/user_id

3) Your schedule page can then have radiobuttons in each day-time cell, or 'ajaxified' links (as in my version) to update the cell - no form sending! This way the schedule is repainted (or just that cell) with updated info (db as well) including a different colour.

4) Your meeting page will be similar, except that it will have a list of users (checkboxes?) to select. Hit a button and the sql query lists the common 'available' periods for that week, for those users. Then - and only for those periods, does a link appear in order for the third (or fourth...) user to book a meeting. Once a meeting has been booked, the colour changes for that cell and a cancel option appears.

Only the original booker (and possibly participants) see the cancel link. You could set it up that an email is fired to all participants when the meeting is booked. In addition, if an user changes the schedule to NOT available at that time, a warning should sound and then possibly another email fired to participants announcing the failure of one of them to attend. The meeting table should then show a 0 in the status field, indicating a problem.

There are loads of little extras you could push into this app, e.g. agenda documents attached to the meeting id.

Sorry if this wasn't what you wanted.

1

Hi ardav,

Yes, exactly the description of your program seems quite similar too what I am trying to accomplish..plz don't leave :-)

I still don't get what you're having a problem with.

I don't either and this code is giving me headaches and I think also carpel tunnel syndrome. Even if I somehow get this part working , there so much I still need to code. Thanks for your informative description, but I would really really appreciate it if you could help me out here. And you're explanation of the alerts via e-mail and bookings etc is exactly what I am trying to do. You said you have recently created a room booking app. I don't wanna impose, but I would really really be so happy if I could see that code regarding this timetable and e-mail. I'd be so grateful if you could send me the app or at least the timetable code(where the settings are done).

Warm regards,

Mike

Edited by MikeGore: n/a

0

Hmm, I'll have to think about that - my code is made with general functions for other bits of the 'admin area'. I'll see what I can do. It won't fit your needs, but may give you an idea. Here's a snapshot, the grid comprises of lessons down the side and room numbers across the top. The red blocks show bookings, the black block show timetabled lessons and the green are free with 'book now" links. IF a red block was my booking, it would have a 'clear me' link below my name.

I'm off to work now, but I'll see what I can do tonight (approx 6-7pm BST)

Attachments tt.png 143.56 KB
1

@ Mike - haven't forgotten. I'm at it at the moment. I've managed to simplify the code and apply it to your needs.

Don't worry I ain't gonna do it all for you, but it's pretty useful.

2 pages: timetable.php (for viewing/changing schedules and for creating meetings) and changedata.php (handles all the db updates).

Attachments tt1.png 252.9 KB tt2.png 111.79 KB
0

Ardav... you can't believe how HAPPY you have made me. I really really APPRECIATE this. Your post has been the best thing I have heard in a long time. THANK YOU!...VERY MUCH :-)

Warm Regards,

Mike

THANKS AGAIN!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.