What query do i need???
 

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 :)

Re: What query do i need???
 

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

Re: What query do i need???
 

Excellent thank you :)