Hi,
I am wanting to come up with a simple php/mysql time slot booking calendar that I can intergrate with my client database… and see at a glance which slots are free when selecting calendar dates… Does anyone know of a tutorial for this kind of calendar or can help me get on the right track?
Cheers
I have allready programmed my calendar with clickable dates etc… Im just after some insite on the best way to create and store the data below.
There will be half hour slots from 8am - 5pm… When a day gets selected, below the calendar have check boxes apear for each time slot… when a time slot is selected and submited I want to put add a client and insert the taken time slot with the date and userid into the database. When the same day is selected again the time slot taken will be selected allready shown the slot is taken.
I allready have the customer database built. So wondering what the best plan of attack to create the above solution?
Cheers
a) Someone picks a day where there are no bookings yet.
PHP queries the DB, finds no matching date, so creates and array of time_slots showing as ‘free’ ready to be used.
b) Someone picks a day where there is one or more bookings for that day so fetches those as an array, then merging that array with the time_slots array, then either displays ‘booked’ or ‘free’.
23, 2011-05-25 10:30:00, 44
What about that idea?
An alternative I have seen suggested on here before is to pre-fill a database with free time-slots, but I found that idea a bit off.
// sql
select concat( HOUR(updated) , ':', MINUTE(updated)) as daytime from
mytable where DATE(updated) = '2011-05-25';
// you can also look up how to use this function
select DATE_FORMAT( ' xxxxxx', daytime) as daytime from ....
Trivial test:
```php
$times = array(
'10:00' => 'free',
'10:30' => 'free',
'11:00' => 'free',
'11:30' => 'free',
);
$booked = array(
'10:30' => 'booked',
);
$times = array_merge($times, $booked);
var_dump( $times);
/** /
array
'10:00' => string 'free' (length=4)
'10:30' => string 'booked' (length=6)
'11:00' => string 'free' (length=4)
'11:30' => string 'free' (length=4)
/**/
Thanks Cups…
Ok I have made some progress… Following the basis of what you posted. I have now got the calendar and booking side of it working… using a table setup as above.
I have created the radio boxes from an array
$timeslots = array(“8:00”, “8:30”, “9:00”, “9:30”,…
then using foreach loop.
Now stuck on checking the daytime field for times booked so I can mark the corosponding radio buttons readonly
My array $booked takes the place of the sql result I posted.
the array you posted differs from mine as you have not set up
$key = ‘10:00’ $value = ‘free’
but effectively have:
$key = 0 $value=‘10:00’
Now in my example showing times ‘booked’ or ‘free’ is trivial:
foreach( $times as $key=>$value ) {
echo "<li><input type=checkbox value='$key'> $key is $value </li>" . PHP_EOL;
}
No fun having limited understanding of how functions work 
I must be over thinking it.
I am storing appointment times in the database as datetime
2011-05-25 10:30:00
2011-05-25 11:30:00
etc
I guess I need to make an array of the current date selected within the calendar and ‘all’ the apointment times somehow formated as above, then check against the array of dates in the database for a match using below to mark the booked radio button as checked.
if(in_array($timeslots, $timeslot)) $checked = 'checked'; else $checked = '';
But not sure how to make an array of selected date and all apointment times to check against.
if that even makes sence?
Going back to my example I posted earlier,
$times is a PHP array you create and is permanent, written as PHP.
$booked is an array which is generated from the sql select I posted for you, you’d perhaps normally call it $rows in this manner:
(code lifted from the manual for mysql_fetch_array)
mysql_connect("localhost", "mysql_user", "mysql_password") or
die("Could not connect: " . mysql_error());
mysql_select_db("mydb");
$qry = "select concat( HOUR(updated) , ':', MINUTE(updated)) as daytime from
mytable where DATE(updated) = '2011-05-25'";
$result = mysql_query($qry);
$rows = mysql_fetch_array($result, MYSQL_NUM))
// but use:
// $booked = mysql_fetch_array( ... );
Then join the 2 arrays as I showed you.
And, yeah, of course add ‘checked’, like so:
foreach( $display_times as $key=>$value ) {
$checked = ($value == 'booked') ? 'checked=checked':'';
echo "<li><input type=checkbox value='$key' $checked /> $key is $value </li>" . PHP_EOL;
}
Sorry, just realised that I am using the var name $times in two places, how very confusing that must be. Change it to this:
$times = array(
'10:00' => 'free',
'10:30' => 'free',
'11:00' => 'free',
'11:30' => 'free',
);
$booked = array(
'10:30' => 'booked',
);
$display_times = array_merge($times, $booked);
So $display_times is the 3rd array, the sum of $times and $booked, I have amended the original code for this particular post.
Warning: I amended my last post slightly to try and avoid some confusion. Sorry 'bout that.
I’d do it -slightly- differently than Cups, if slightly less verbose.
Instead of using a third array, just use the two ($times and $booked) .
foreach( $times as $key=>$value ) {
echo "<li><input type=checkbox value='$key' ".((isset($booked[$key])) ? "disabled" : "")."/> $key</li>" . PHP_EOL;
}
All depends really on what format you want your output to have.
Sorry I cant make it work… here is the basis of what I have so far without the option of checking for booked dates…
echo '<form action="'.$_SERVER['PHP_SELF'].'" method="post" name="Booking">';
$timeslots = array("8:00", "8:30", "9:00", "9:30", "10:00", "10:30", "11:00", "11:30", "12:00", "12:30", "1:00", "1:30", "2:00", "2:30", "3:00", "3:30", "4:00", "4:30", "5:00", "5:30", "6:00", "6:30");
echo 'Make apointment for: <b>'.$shdate.'</b><br /><br />
<select name="customer">
<option value="">Select Customer</option>';
$sql = mysql_query("SELECT customerid, firstname, lastname FROM customers ORDER BY lastname ASC");
while ($row = mysql_fetch_array($sql)) {
echo '<option '.( $firstname==$row['firstname'] ? 'selected' : '' ).' value="'.$row['customerid'].'">'.$row['firstname'].' '.$row['lastname'].'</option>';
}
echo '</select><br /><br />
<table width="750" cellspacing="0" cellpadding="3" align="center">';
$column = 0;
foreach($timeslots as $timeslot) {
if ($column == 0) {
echo '<tr>';
}
echo '<td align="right"><input type="radio" name="$timeslot[]" value="'.$timeslot.'" '.$checked.'></td><td align="left">'.$timeslot.'</td>';
$column++;
if ($column == 5)
{
echo '</tr>';
$column = 0;
}
}
echo '</table>
<input name="slot" type="hidden" value="'.$slot.'" />
<br /><br /><center><input type="submit" name="submit" value="Submit"></center>
</form>';
where are you setting $checked?
Hey,
Its not set yet… that is where I am strugling… have tried several ways of making the 2 arrays and checking with in array…
So, what you need to do is get the set of already booked times (entries in your table) for this day, and then as you walk through your times, see if the current time has already been booked. (Which… is pretty much what posts 2-9 of this thread were 
)
What was wrong with my suggestion then, why did you not use it?
Its not that there is anything wrong with your solution… I just personally couldnt get it to work… lack of understanding of how it works.
Did you manage to get dates out of your database into an array?
If not, show us what your table looks like enter the sql statement.
DESCRIBE <table-name>
Na just get mysql error using
$sql = "(SELECT concat( HOUR(timeslot) , ':', MINUTE(timeslot)) as times from timeslots where DATE(timeslots) = '2011-05-25')";
Table layout is:
timeslots
timeslotid, customerid, timeslot
Ahh I see where I went wrong… type I have put the records into an array now… so flag the above post…
One part of the sql I dont understand is the 2x , in there… what is the job of them?
the array populated from the database looks like:
Array ( [0] => 9:30 [1] => 9:0 [2] => 1:30 [3] => 12:0 [4] => 5:30 )
any time on the hour is displayed as 9:0, when I am lookin in array I am looking for 9:00 so im not getting a match… how come the array created from the query is droping the last 0 for on the hour?
Go and read up on the [google]mysql concat function[/google] and you will see what the commas (,) are used for.
Well that is looking more hopeful at least!
Now THIS bit I do not understand at all.
Can you show me the result of var_dump($array) on that array?
ps remove the opening and closing brackets on your sql string - they are not needed.
$sql = “(SELECT concat(… )”;
Hi,
I found the problem I think, on my system (win32, mysql5.0) the times were missing leading 0s eg “9.00”.
The second idea I posted earlier was to use date_format(), I did not think there was any difference, (which shows you just how wrong you can be).
This should work:
select DATE_FORMAT( timeslot, '%H:%i') as times from <your-table>;
That returns the full 00:00 format you want.
(watch your typing of timeslot/ timeslots they seem to be field/table name, Yes?)
Read up on the very useful [google]mysql date_format function[/google] and all the different formatting strings you can use.
Of course, this formatting could also be done by PHP prior to iterating through the array, but I thought it would be easier to do it in the database on this occasion (which shows just how wrong can you be? #2)
Brilliant, I learned something!