What query do i need???

Expand Post »

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.

MySQL Syntax (Toggle Plain Text)
 
<?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";
  }
  ?>
<?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

Last edited by peter_budo; Jan 30th, 2009 at 2:36 pm. Reason: Correcting closing tag from [code] to [/code]

Similar Threads

Removing Query Strings in ASP.NET
Sql Query in VB.NET
Double MySQL Query in PHP
Dynamic Query in JSP
MySQL nested query / joined query conversion help in MySQL

NoGood

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Nov 2008

Permalink

Feb 3rd, 2009

Re: What query do i need???

Use Left outer Join

sql Syntax (Toggle Plain Text)
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
 
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

Last edited by peter_budo; Feb 3rd, 2009 at 2:03 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

varmadba

Reputation Points: 22

Solved Threads: 9

Junior Poster in Training

Offline

83 posts
since Jun 2008

Permalink

Feb 3rd, 2009

Re: What query do i need???

Excellent thank you

NoGood

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Nov 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Need Suggestion

Next Thread in MySQL Forum Timeline: Move mysql colum to another table

DaniWeb Message

Cancel Changes

User Name
Password