how to remove second while loop to avoid double data

[dragon_sa]

I have a query that gets staff on a particular day then performs a while loop selecting each member that works on that day and the times they work between.

I have a for loop in that while loop that lists apointment times for each staff member every 15mins between the times they work, this works fine.

I then have in the for loop a while loop that checks for existing appontments in the appointment table and changes the appointment slot for times that are booked to booked.

I have found though that the second while loops causes doubling of results when an appointment is found, so I need to work out how to restructure this code so that i dont get the doubling effect of result.

 

you can see the result of the script here

 

http://justrelax.net.au/datetest.php

 

and the code that causes the problem is

 

// convert 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/>";
		}
	}
}

 

could really use some help on this I have been trying different things for a few days now.

[denno020]

Can you please give me some SQL so I can create the table on my end and test out changes? You should be able to just export the staff table and post the data here. You can delete all values, I just want the structure of it .

 

Denno

[dragon_sa]

CREATE TABLE IF NOT EXISTS `appointment` (
  `appointmentID` int(10) NOT NULL auto_increment,
  `services` varchar(100) NOT NULL,
  `totalTIME` int(10) NOT NULL,
  `date` varchar(10) NOT NULL,
  `dateTIME` int(100) NOT NULL,
  `staffCONFIRM` varchar(3) NOT NULL default 'no',
  `clientCONFIRM` varchar(3) NOT NULL default 'no',
  `reminderSENT` varchar(3) NOT NULL default 'no',
  `clientID` int(10) NOT NULL,
  `staffID` int(10) NOT NULL,
  PRIMARY KEY  (`appointmentID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

[denno020]

sorry I can't understand your code. I don't know where onLEAVE or ACTIVE has come from to be put in your SQL query, there is no row called those names.

 

Also, the date stuff at the start didn't work at all for me. It's too hard to follow your code, sorry.

 

Denno

[dragon_sa]

I finnally got it to work 

I put the booked appointment times into an array then checked if the current time slot of the appointment time 15min loop was in the array like this

 

// get working staff from staff table
putenv("TZ=Australia/Adelaide");
$staffSQL=mysql_query("SELECT * FROM staff WHERE ".$weekday." != 'off' AND onLEAVE='no' AND ACTIVE='yes' ORDER BY staffID ASC");
if (mysql_num_rows($staffSQL)>=1) {
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/>";
// get appointments for staff
$existSQL=mysql_query("SELECT * FROM appointment WHERE date='$sqlDATE' AND staffID='$staffID'");
if (mysql_num_rows($existSQL)>=1) {
	$booked="booked";
	${$booked.$staffID}=array();
	while ($appointV=mysql_fetch_array($existSQL)) {
		$resSTART=$appointV['dateTIME'];
		$length=$appointV['totalTIME'];
		$resEND=$resSTART+($length*60);
		for ($book=$resSTART;$book<=$resEND;$book+=900) {
			${$booked.$staffID}[]=$book;
		}
	}
}
	for ($inc=$in2;$inc<=$stop;$inc+=900) {
		if (isset(${$booked.$staffID}) && in_array($inc, ${$booked.$staffID})) {
			echo "Appointment time is ".date("g.i a", $inc)." <font color='red'><< $first BOOKED</font><br/>";
		} else if (!isset(${$booked.$staffID}) || !in_array($inc, ${$booked.$staffID})) {
			echo "Appointment time is ".date("g.i a", $inc)." <font color='green'><< $first AVAILABLE</font><br/>";
		}
	}
}
} else { echo "No staff availablen today"; }

[dragon_sa]

onLEAVE and ACTIVE come from the staff table not the appointment table

 

and the date comes from an input variable at the top of the page which will be from a calendar selection but just declared for now and then formatted the way I want it

 

// get requested date
$input="17/1/2011";
// convert date to variables
list($day, $month, $year)=explode("/", $input);
// date fix for single digit day or month
if (strlen($day)=='1') $day="0".$day;
if (strlen($month)=='1') $month="0".$month;