Hi there,
I know this probably isn’t the most practical way but can someone help me out with my following code. The below code is for an appointments system, I have time slots within a day and the below code is grabbing the time value and matching it with the the time slot.
So, if their is two records in the database of 09:00 and 09:15 times, it will show up on the time slot instructed and make the available link non-existent if the time exists. But here is where my problem is, FOR EACH record within the database, it will obviously echo out the two results, but due to no appointments being within the time slot of 09:30 and 09:45, these two slots echo twice because their is two records within the database when along with the result, I only want it to echo once within the time slot if the result matches the time.
Following code out puts the following result
09:00 - Appointment Booked - by name - View Appointment
09:15 - Available
09:30 - Available
09:45 - Available
09:00 - Available
09:15 - Appointment Booked - by name - View Appointment
09:30 - Available
09:45 - Available
- The above example outputs the two rows with the matching time slots due to the while loop
- This means time slots are echoed multiple times
Want the solution to display the result in the following form
09:00 - Appointment Booked - by name - View Appointment
09:15 - Appointment Booked - by name - View Appointment
09:30 - Available
09:45 - Available
- Would love for the time slots to echo once, and depending on the time, slot itself into its respective slot
- Without the multiple slots echoing
Is there a way to do this? and if so how?
<?php
include('connection.php');
$comment = "SELECT *
FROM profile
INNER JOIN appointments
ON profile.id=appointments.id WHERE date = '" .$_GET['date']."' ORDER BY date DESC LIMIT 5"; //MYSQL Query SELECT ALL FROM review WHERE id = row id
$commentresult = mysql_query($comment);
// If mysqlresult = false then query return fail
if ($commentresult == false)
{
die("Your Query isn't working correctly! :-( ");
}
// If mysqlresult = true then echo below information with results
if (mysql_num_rows($commentresult) >= 1)
{
while ($cr = mysql_fetch_array($commentresult)) //While Loop
{
$time = $cr['time'];
if($time=="09:00")
{
// Show Appointment if database matches 09:00
echo "<table border='0' width='800'>
<tr><td width='35'><div class='content'>".$cr['time']."</div></td>
<td width='100'><div class='content'>".$cr['status']."</div></td>
<td width='100'><div class='content'>".$cr['firstname']." ".$cr['secondname']."</div></td>
<td width='120'><div class='content'>View Appointment</div></td>
</tr></table>";
}
else
{
// Show time slot is available if no appointments
echo "<br><table border='0' width='800'>
<tr><td width='100'><div class='content'>09:00</div></td>
<td width='100'><div class='content'><a href='#'>Available</a></div></td>
<td><div class='content'></div></td></tr></table><br>";
}
if($time=="09:15")
{
// Show Appointment if database matches 09:15
echo "<table border='0' width='800'>
<tr><td width='35'><div class='content'>".$cr['time']."</div></td>
<td width='100'><div class='content'>".$cr['status']."</div></td>
<td width='100'><div class='content'>".$cr['firstname']." ".$cr['secondname']."</div></td>
<td width='120'><div class='content'>View Appointment</div></td>
</tr></table>";
}
else
{
// Show time slot is available if no appointments
echo "<br><table border='0' width='800'>
<tr><td width='100'><div class='content'>09:15</div></td>
<td width='100'><div class='content'><a href='#'>Available</a></div></td>
<td><div class='content'></div></td></tr></table><br>";
}
if($time=="09:30")
{
// Show Appointment if database matches 09:30
echo "<table border='0' width='800'>
<tr><td width='35'><div class='content'>".$cr['time']."</div></td>
<td width='100'><div class='content'>".$cr['status']."</div></td>
<td width='100'><div class='content'>".$cr['firstname']." ".$cr['secondname']."</div></td>
<td width='120'><div class='content'>View Appointment</div></td>
</tr></table>";
}
else
{
// Show time slot is available if no appointments
echo "<br><table border='0' width='800'>
<tr><td width='100'><div class='content'>09:00</div></td>
<td width='100'><div class='content'><a href='#'>Available</a></div></td>
<td><div class='content'></div></td></tr></table><br>";
}
if($time=="09:45")
{
// Show Appointment if database matches 09:45
echo "<table border='0' width='800'>
<tr><td width='35'><div class='content'>".$cr['time']."</div></td>
<td width='100'><div class='content'>".$cr['status']."</div></td>
<td width='100'><div class='content'>".$cr['firstname']." ".$cr['secondname']."</div></td>
<td width='120'><div class='content'>View Appointment</div></td>
</tr></table>";
}
else
{
// Show time slot is available if no appointments
echo "<br><table border='0' width='800'>
<tr><td width='100'><div class='content'>09:45</div></td>
<td width='100'><div class='content'><a href='#'>Available</a></div></td>
<td><div class='content'></div></td></tr></table><br>";
}
} // End Loop
?>
It depends on your application. You could create a new table, appointment_timeslots, with a row for each timeslot the appointment occupies. The query would become something like:
SELECT *
FROM timeslots
LEFT OUTER JOIN
(SELECT
appointment_timeslots.timeslot
, status
, firstname
, secondname
FROM appointments
INNER JOIN profile
ON profile.id=appointments.id
INNER JOIN appointment_timeslots
ON appointments.appointmentid = appointment_timeslots.id
WHERE appointments.DATE = '" .$_GET['date']."'
) AS a
ON a.timeslot >= timeslots.timeslot
Or you use a begintime and endtime field in your appointments table, and then you’d have to change the query that links the timeslots and the appointments into something like this:
SELECT *
FROM timeslots
LEFT OUTER JOIN
(SELECT
starttime
, endtime
, status
, firstname
, secondname
FROM appointments
INNER JOIN profile
ON profile.id=appointments.id
WHERE appointments.DATE = '" .$_GET['date']."'
) AS a
ON a.starttime >= timeslots.timeslot
AND a.endtime < timeslots.timeslot
I didn’t test anything, so these queries might be full of errors 
In the while loop, you can do an ‘if’ on $cr[‘status’]. If it’s empty, then the timeslot is available.
StarLion that wasn’t really suppose to be there, I’ve been copying similar queries from other pages to cut down on the workload and that’s all of a sudden not been removed but it hasn’t given me errors, but I will be rechecking all my queries just to make sure their isn’t that problem of unwanted extra’s in the queries! 
The timeslots table (in its simplest form) would consist of just one column: timeslot
It would contain all your timeslots:
09:00
09:15
ecc.
The appointments table wouldn’t change.
Your query would become something like:
SELECT *
FROM timeslots
LEFT OUTER JOIN
(SELECT *
FROM appointments
INNER JOIN profile
ON profile.id=appointments.id
WHERE appointments.date = '" .$_GET['date']."'
) AS a
ON a.time = timeslots.timeslot
ORDER BY a.date DESC, timeslots.timeslot
LIMIT 5"
Although I don’t understand why there’s a limit.
And by the way, date and time are reserved words in MySQL, so your query should give an error, unless you put these column names between backticks.
I see so just to re-iterate because I have never attempted a LEFT JOIN within a table, would the process of creating a new table called timeslots and relate it in relation with appointment on the time field. Then within the timeslots table would u for example have fields for 09:00, 09:15, 09:30, 09:45 then based on what time the appointment is just place an X within the field of the appointment time?
Then when querying the database on the times for that date, I would just echo with the LEFT JOIN in the query for timeslots, the full timeslots table and hopefully this will show up the full time slots which have been marked with an X if the appointment is a go ahead, and default value of “Available” if no X is within the database? Would this also stop the multiple echos?
Ahhhhh I see now Guido, tried this in my query browser and it’s coming up the way I would like it to display! Again thanks, If I have any more questions I will be more than happy to post in this thread regarding the time slots again! You’s guys are great help!
See if there is a begin time and end time for example an appointment started at 09:00:00which it was booked for, and ended in 45 minutes which = 09:45:00
Is there a way to tell the available slots within 09:00:00 - 09:45:00 to become unavailable? At this present moment, my diary system works perfectly, I have the appointments popping up in their time slots, and the other time slots becoming available, but im looking to further the functionality and make within the time duration of an appointment, it is unavailable to book within the duration of the appointment.
Heres a preview of the working outcome result - http://img181.imageshack.us/img181/9763/34456372.jpg
Come on jamie
You need a if-else construction: if status is empty, show the ‘available’ part, else show the ‘not available’ part.
Not entirely sure why you decided to order by date desc, if you’re only selecting a specific date?..
I would do it the way guido suggests, however if you really want to stick to hard-coding it…
Pull all the results into an array first.
while($cr = mysql_fetch_array($commentresult)) {
$appts[$cr['time']] = $cr;
}
Now, for each time slot…
if(isset($appts[$thetime])) { //There is an appointment for this time.
} else {
//There isnt.
}
You could make a timeslots table, and then join the two (LEFT JOIN) in your query, and then create the entire output dynamically (looping through the result set of the query), instead of hard coding the timeslots.
ON timeslots.timeslot >= a.starttime
AND timeslots.timeslot < a.endtime
Try this
Fixed the error Guido! now just need to replace the echoed same appointments with the word unavailable!
The second way works on a certain level lol, if I have a starttime of 12:00:00, and go backwards to 11:00:00 it will fill up those spaces with the same appointment which is the correct principle, but if I have a start time of 12:00:00 and endtime of 13:00:00 it doesnt show up the appointment, just available!
Oh crap solved the problem Guido, sometime’s I really do crack myself up with the most simplistic things. Thanks a lot dude!
Tried that Guido, my timeslots then echo twice because I put in the following code.
// If mysqlresult = true then echo below information with results
if (mysql_num_rows($commentresult) >= 1)
{
while($cr = mysql_fetch_array($commentresult))
{
{
$_GET['time'] = $cr['timeslot'];
$status = $cr['status'];
//Full Access Content
echo "<table border='0' width='800'>
<tr><td width='35'><div class='content'>".$cr['timeslot']."</div></td>
<td width='100'><div class='content'>".$cr['status']."</div></td>
<td width='100'><div class='content'>".$cr['firstname']." ".$cr['secondname']."</div></td>
<td width='120'><div class='content'></div></td>
</tr></table>";
}
if($status=="") {
echo "<table border='0' width='800'>
<tr><td width='35'><div class='content'>".$cr['timeslot']."</div></td>
<td width='100'><div class='content'><a href='book_slot.php?date=".$_GET['date']."&id=".$_GET['id']."&time=".$_GET['time']."'>Available</a></div></td>
<td width='100'><div class='content'></div></td>
<td width='120'><div class='content'></div></td>
</tr></table>";
}
}
}
P.S Here is my updated code that works perfectly but doesn’t show the available status when no appointment is booked, I have attached an image also to show you the way it looks!
Display of code outcome: http://img809.imageshack.us/img809/5829/22884494.jpg
<?php
include('connection.php');
$comment = "SELECT *
FROM timeslots
LEFT OUTER JOIN
(SELECT time,status,firstname,secondname
FROM appointments
INNER JOIN profile
ON profile.id=appointments.id
WHERE appointments.DATE = '" .$_GET['date']."') AS a
ON a.TIME = timeslots.timeslot"; //MYSQL Query SELECT ALL FROM review WHERE id = row id
$commentresult = mysql_query($comment);
// If mysqlresult = false then query return fail
if ($commentresult == false)
{
die("Your Query isn't working correctly! :-( ");
}
// If mysqlresult = true then echo below information with results
if (mysql_num_rows($commentresult) >= 1)
{
while($cr = mysql_fetch_array($commentresult))
{
{
//Full Access Content
echo "<table border='0' width='800'>
<tr><td width='35'><div class='content'>".$cr['timeslot']."</div></td>
<td width='100'><div class='content'>".$cr['status']."</div></td>
<td width='100'><div class='content'>".$cr['firstname']." ".$cr['secondname']."</div></td>
<td width='120'><div class='content'></div></td>
</tr></table>";
}
}
}
else
{
echo "<img src='info.png'> This user has no appointments! Please add an appointment if necessary by clicking <b>Add Appointment</b> above!";
}
// Close database connection
mysql_close($dbconnection);
/////////////////////////////
?>
Hey guys sorry about being a pain, im on the last aspect of my diary before completion. I am currently at the state where I have followed your advice in doing the left join, I have the booked appointments showing up within its respective time. But where their is no appointment it will just be an empty space because no specific if statement is within the code telling the left join what to do and I need a bit of help. Due to the fact I only have one field “timeslot” with my full list of times and nothing else, I am finding it hard to tell those times what to do if there is no appointments booked, where as when I had the time slots hard coded into the code, I could put if statements for each time telling it if there is no results, show as available!
If you can point me in the direction on how to do this then I would be greatful, If you need more information regarding the problem I can post a much better description of the problem at hand!
Thanks!
