Stop Records Duplicating

[jamie2011]

Hi,

 

Was just wondering if I could get help with this programming problem, I am programming a diary system that people can use to book appointments within time slots on a current day. I have the below code reading from three database tables (one for appointment details, one for the profile of whom booked the appointment and lastly a LEFT JOIN table of times from 09:00:00 to 17:30:00). The functionality of this diary of time slots is where there is a booked slot, slip the appointment attendee into that slot so the avaible link is not seen so no one can book over the appointment.

 

This is where the problem comes into play, if there is an appointment expanding lets say 45 minutes which means other time slots will be taken, it just echos out the same appointment details so then I am getting it duplicating. for example down below;

 

09:00:00 - Booked Appointment (name) - Duration: 30 Minutes

09:15:00 - Booked Appointment (name) - Duration: 30 Minutes

09:30:00 - Booked Appointment (name) - Duration: 30 Minutes

 

Now what I would like the solution to is if there is a way to maybe stop the duplication by showing the first row of the start time of the appointment and then change the the next rows to unavailable? Or even collapsing the time rows so if an appointment is booked at 09:00:00 for 30 mnutes, then the next available time slot would be 09:45:00 so the record would not duplicate.

 

Here is a screenshot to help you a bit better - http://img185.imageshack.us/img185/7981/72314500.png

 

Here is my source code, would appreciate any suggestions on how I would do this!

 

<?php
include('connection.php'); 

//Query
$comment = "SELECT *
FROM timeslots
LEFT OUTER JOIN
  (SELECT time,endtime,status,firstname,secondname,duration
    FROM appointments
    INNER JOIN profile
    ON profile.id=appointments.id
    WHERE appointments.DATE = '" .$_GET['date']."') AS a
ON a.time <= timeslots.timeslot
AND a.endtime >= timeslots.timeslot";

$commentresult = mysql_query($comment);


// If commentresult = false then query return fail
if ($commentresult == false)
{
die("Your Query isn't working correctly! :-( ");

}

// If commentresult = true then echo below information with results
if (mysql_num_rows($commentresult) >= 1)
{ // Start Rows

while($cr = mysql_fetch_array($commentresult)) 
{ // Start While Loop
{ // Process While Loop

$_GET['time'] = $cr['timeslot'];
$status = $cr['status'];  

if($status=="") { // Start IF Status

// echo Available
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>";

} // End IF Status

else { // Start ELSE status

// echo Appointment within Time Slot
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='120'><div class='content'>".$cr['firstname']." ".$cr['secondname']."</div></td>
<td width='120'><div class='content'>".$cr['duration']." Minutes</div></td>
</tr></table>";
} // End ELSE Status


} // End Process While Loop
} // End Loop
} // End Row

// Else statement
else 
{ 
    echo "<img src='info.png'> There are no appointments for this day!"; 
}
// Close database connection
mysql_close($dbconnection);
/////////////////////////////

?>

[mentalist]

Not actually read your code, but... (well have now, but...)

 

I assume you put an Appointment ID (or similar) in to the Diary (as you say, in each Diary slot). Then you iterate through the Diary slots, by using a system which saves the 'last' Appointment ID in a var, you can then check it against the current Appointment ID, if their the same ignore, else close last appointment and start to display new.

 

Does that make sense?