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| UnAvPersonB 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
