dragon_sa Posted January 12, 2011 Share Posted January 12, 2011 I have the following code which gets staff working on a selected day and their start and finish times. The code then lists out the day for every 15min of time for each staff member. During the loop of each staff member I then check an appointment table to see if a user has any appointments, and lists the booked time slots, if no appointments lists user as available for all the time slots. My problem is when a user has appointments it only shows the booked times and not the available times. I know the problem lies here else if ($inc<$resSTART && $inc>$resEND) { and the complete code that is from is here // convet to US format $input="$month/$day/$year"; // readable date $readDATE="$day/$month/$year"; // convert for mysql date $sqlDATE="$year-$month-$day"; // get day of the week $weekday=date(l, strtotime($input)); echo $weekday."<br/>"; // get working staff from staff table $staffSQL=mysql_query("SELECT * FROM staff WHERE ".$weekday." != 'off' AND onLEAVE='no' AND ACTIVE='yes' ORDER BY staffID ASC"); // return results putenv("TZ=Australia/Adelaide"); while ($staff=mysql_fetch_array($staffSQL)) { $name=$staff['staffNAME']; $avail=$staff[$weekday]; $staffID=$staff['staffID']; list($first, $last)=explode(" ", $name); list($in, $out)=explode("-", $avail); $firstTIME="$sqlDATE $in:00"; $lastTIME="$sqlDATE $out:00"; echo "$first is available between $avail on $weekday<br/>"; $in2=strtotime($firstTIME); $out2=strtotime($lastTIME); $stop=$out2-900; echo $in2." - time start - $firstTIME<br/>"; echo $out2." - time finish - $lastTIME<br/>"; for ($inc=$in2;$inc<=$stop;$inc+=900) { $existSQL=mysql_query("SELECT * FROM appointment WHERE date='$sqlDATE' AND staffID='$staffID'"); if (mysql_num_rows($existSQL)>=1) { while ($appointV=mysql_fetch_array($existSQL)) { $resSTART=$appointV['dateTIME']; $length=$appointV['totalTIME']; $resEND=$resSTART+($length*60); if ($inc>=$resSTART && $inc<=$resEND) { echo "Appointment time is ".date("g.i a", $inc)." <font color='red'><< $first BOOKED</font><br/>"; } else if ($inc<$resSTART && $inc>$resEND) { echo "Appointment time is ".date("g.i a", $inc)." <font color='green'><< $first AVAILABLE</font><br/>"; } } } if (mysql_num_rows($existSQL)==0) { echo "Appointment time is ".date("g.i a", $inc)." <font color='green'><< $first AVAILABLE</font><br/>"; } } } any ideas how to restructure that if statement to work properly would be very much appreciated Quote Link to comment Share on other sites More sharing options...
dragon_sa Posted January 12, 2011 Author Share Posted January 12, 2011 just realised I cant have a value both less than and greater than at the same time so instead of && I used || which works problem now is that because it is in the while loop when there are appointments, it will display each time twice if 2 appointments, 3 times if three appointments etc etc, how do I get around the multiple echoes of each 15mins Quote Link to comment Share on other sites More sharing options...
dragon_sa Posted January 13, 2011 Author Share Posted January 13, 2011 Any ideas how I can change this so I dont get multiple echoes of each time slot for each appointment in the appointment table, I am just checking to see what appointments there are and then mark the used time slots as booked. heres what I have now // convet to US format $input="$month/$day/$year"; // readable date $readDATE="$day/$month/$year"; // convert for mysql date $sqlDATE="$year-$month-$day"; // get day of the week $weekday=date(l, strtotime($input)); echo $weekday."<br/>"; // get working staff from staff table $staffSQL=mysql_query("SELECT * FROM staff WHERE ".$weekday." != 'off' AND onLEAVE='no' AND ACTIVE='yes' ORDER BY staffID ASC"); // return results putenv("TZ=Australia/Adelaide"); while ($staff=mysql_fetch_array($staffSQL)) { $name=$staff['staffNAME']; $avail=$staff[$weekday]; $staffID=$staff['staffID']; list($first, $last)=explode(" ", $name); list($in, $out)=explode("-", $avail); $firstTIME="$sqlDATE $in:00"; $lastTIME="$sqlDATE $out:00"; echo "$first is available between $avail on $weekday<br/>"; $in2=strtotime($firstTIME); $out2=strtotime($lastTIME); $stop=$out2-900; echo $in2." - time start - $firstTIME<br/>"; echo $out2." - time finish - $lastTIME<br/>"; for ($inc=$in2;$inc<=$stop;$inc+=900) { $existSQL=mysql_query("SELECT * FROM appointment WHERE date='$sqlDATE' AND staffID='$staffID'"); if (mysql_num_rows($existSQL)>=1) { while ($appointV=mysql_fetch_array($existSQL)) { $resSTART=$appointV['dateTIME']; $length=$appointV['totalTIME']; $resEND=$resSTART+($length*60); if ($inc>=$resSTART && $inc<=$resEND) { echo "Appointment time is ".date("g.i a", $inc)." <font color='red'><< $first BOOKED</font><br/>"; } else if ($inc<$resSTART || $inc>$resEND) { echo "Appointment time is ".date("g.i a", $inc)." <font color='green'><< $first AVAILABLE</font><br/>"; } } } if (mysql_num_rows($existSQL)==0) { echo "Appointment time is ".date("g.i a", $inc)." <font color='green'><< $first AVAILABLE</font><br/>"; } } } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.