MySQL

What query do i need???

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2008

Posts: 9

Reputation: NoGood is an unknown quantity at this point

Solved Threads: 0

NoGood

Offline

Newbie Poster

What query do i need???

Jan 28th, 2009

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.

 Help with Code Tags 
 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]

•

Join Date: Jun 2008

Posts: 79

Reputation: varmadba is an unknown quantity at this point

Solved Threads: 8

varmadba

Offline

Junior Poster in Training

Re: What query do i need???

Feb 3rd, 2009

Use Left outer Join

 Help with Code Tags 
 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.

:- Varma

We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/