What query do i need???

[NoGood]

Hi all,

I'm creating an appointments scheduling system. I have a bookings table and a time table among others in a MySQL database. Each entry in the bookings table has a slotId value. In the time table each slotId has a startTime and an endTime.

This is what i want to do.

I want a sql query to run so that when you enter a date and stylist into a form it runs a query and brings back all timeSlots available on that date with that stylist.

I have this query which is bringing back all the appointments on the date specified.

<?php
if ($_POST["newSearch"]=="yes") {

    $thisDate=$_POST["date"];
	$dd=substr($thisDate,0,2);
	$mm=substr($thisDate,3,2);
	$yyyy=substr($thisDate,6,4);
	$date="$yyyy-$mm-$dd";
	$stylist=$_POST["stylist"];

include("dbVariables.php");

 // connect to database
 $db = mysql_connect($dbHost, $dbUsername, $dbPassword);
 mysql_select_db($databasename,$db);

$dbQuery = "SELECT * FROM bookings,time WHERE bookings.date='$date' AND bookings.stylist='$stylist' AND bookings.slotId=time.slotId ORDER BY time.startTime";
$result = mysql_query($dbQuery, $db);
$num=mysql_numrows($result);
mysql_query($dbQuery) or die('Error, insert query failed');

echo "<table width='100%' border='solid'  bordercolor='darkcyan'> <tr><td>Time<td><td>Stylist</td></tr>\n";
	echo "</table>\n";
					
    while ($dbRow=mysql_fetch_array($result)) {
	
echo"<form style=\"display:inline\" action=\"book.php\" method=\"post\">\n " .
           
"<table width='100%' border='solid' bordercolor='darkcyan'>\n " .
 "<tr><td>" . $dbRow['startTime'] . " - " . $dbRow['endTime'] . </td>\n " .
"<td>" . $dbRow['stylist'] . </td>\n " .
 "</form>\n " ;		
	} 
    echo "</table><p>&nbsp;</p>\n";
  }
  ?>

But i want it to do the opposite and bring back all the slotId's on the specified date that have not been allocated an appointment.

Any suggestions??

Thanks :)

[varmadba]

Use Left outer Join

SELECT bookings.*,times.slotId  FROM bookings
left outer join time 
 on bookings.slotId=time.slotId
WHERE bookings.date='$date' AND bookings.stylist='$stylist' 
ORDER BY time.startTime"
having time.slotId is null

[NoGood]

Excellent thank you :)