Code to prevent a double booking.

[Sarahpengie]

Hi

 

I am creating a website where a user can book a driving lesson. I am using mySQL and PHP.

 

I have coded a booking system where users can book a lesson for a desired date and time, however I can't seem to find code to prevent double bookings from occurring.

 

The code I have used in my booking form is as follows:

 

 

 

<?php

include('../config.php');

 

echo "<table border=1 >";

echo "<tr>";

 

echo "<td><b>LessonDate</b></td>";

echo "<td><b>StartTime</b></td>";

echo "<td><b>EndTime</b></td>";

echo "</tr>";

 

$result = mysql_query("SELECT * FROM booking") or trigger_error(mysql_error());

while($row = mysql_fetch_array($result)){

foreach($row AS $key => $value) { $row[$key] = stripslashes($value); }

 

echo "<tr>";

 

echo "<td valign='top'>" . nl2br( $row['LessonDate']) . "</td>";

echo "<td valign='top'>" . nl2br( $row['StartTime']) . "</td>";

echo "<td valign='top'>" . nl2br( $row['EndTime']) . "</td>";

echo "</tr>";

 

 

}

echo "</table>";

 

?>

 

If anyone could help me to prevent double bookings occurring that would be great

 

Thanks in advance for your help

 

Sarah.

[thara]

If I understood your question correctly you need to use mysql DISTINCT.

 

When querying data from a database table, you may get duplicate records. In order to remove duplicate records, you use the DISTINCT keyword along with the SELECT statements. The syntax of the MySQL DISTINCT is as follows:

 

SELECT DISTINCT columns
FROM table_name
WHERE where_conditions

Edited February 4, 2013 by thara

[Christian F.]

Unfortunately, DISTINCT won't prevent double-booking. The code posted at the top doesn't have anything to do with the problem either.

 

To prevent double-booking you have to check if the time slots are taken, before you INSERT the data into the database. Checking on display is too late.

You will also need to take racing conditions into consideration, as it would still be easy to double-book something if two people filled out the form at the same time. Transactions might help with this, or adding a "reserved" token when the user starts to fill out the form. This last solution requires that the user selects the time slot first, and then is redirected to a registration form. A bit more cumbersome for the user, but almost foolproof when it comes to race conditions.

 

[Barand]

The following query will find all slots that conflict with the proposed booking

 

If the proposed booking is on $bdate between $bstime and $betime

 

SELECT COUNT(*) as conflicts
FROM bookings
WHERE LessonDate = '$bdate' AND StartTime < '$betime' AND EndTime > '$bstime'

 

if "conflicts" is zero it's safe to book.

[Sarahpengie]

Thanks for all ye're help!

 

Barand, where in my code would I insert your code?

 

Sorry I'm a bit clueless!

 

Thanks again!

[Christian F.]

That would go in the file that's registering the bookings, after the user has submitted the proposed times and before you save the booking to the database.

 

As previously mentioned, the code you posted above isn't really related to this problem. As you're only showing what's already saved in the database with it. At which point it is too late to prevent the double bookings.

It's the same as with writing bookings into a book, really. Before you write the appointment, you first have to check (SELECT) to see if there is an appointment already in the desired time span. If there are 0 appointments, and thus 0 rows returned from Barand's query, you know that the slot is available. Otherwise, you have to show a warning, abort the saving process, and ask for a new time.

Edited February 5, 2013 by Christian F.

[Sarahpengie]

Thanks for your help Christian. I still a bit confused as to how to go about this.

 

So should I change my code to something like this:

 

 

 

<form action="../process-booking.php" method='POST'>

 

<p><b>Select a Date:</b><br /><input type="date" value="Now" name='LessonDate'/>

<p><b>Select a Start Time:</b><br /><input type='time' value="Now" name='StartTime'/>

<p><b>Select an End Time:</b><br /><input type='time' value="Now" name='EndTime'/>

 

<p><input type='submit' value='Book Now' /><input type='hidden' value='1' name='submitted' />

</form>

<br>

 

And in the process-booking.php form have some code which will check if the dates are taken?

 

Or how should I do it?

 

Thanks again for your help!

[Psycho]

I think you need to talk to your instructor. If we are simply telling you what code to use and where to put it you aren't learning anything.

[Sarahpengie]

Hi,

 

I am creating a website for a project where you are able to book driving lessons.

 

I have created code which (sort of) prevents double booking, however, I have I'm having a problem.

 

If there is a booking stored in the DB at 11.30-12.30, if i try to book a lesson from 11-12 it prevents me from doing so, however if I try book from 12-1, it allows me to go ahead with the booking.

 

The code I am using is below, I believe it has something to do with the line of code I have made "bold". Does anyone know how to solve this error? Thanks!

 

 

<?php

 

include('../config.php');

$Name = '';

$LessonDate = '';

$StartTime = '';

$EndTime = '';

if (isset($_POST['submit'])) {

if(isset($_POST['Name'])){ $Name = $_POST['Name']; }

if(isset($_POST['LessonDate'])){ $LessonDate = $_POST['LessonDate']; }

if(isset($_POST['StartTime'])){ $StartTime = $_POST['StartTime']; }

if(isset($_POST['EndTime'])){ $EndTime = $_POST['EndTime']; }

 

 

$result = mysql_query("SELECT * FROM booking WHERE LessonDate = '".mysql_real_escape_string($LessonDate)."' AND (StartTime >= '".mysql_real_escape_string($StartTime)."' AND StartTime <= '".mysql_real_escape_string($EndTime)."')");

if(!$result)

{

die(mysql_error());

}

 

if(mysql_num_rows($result) > 0)

{

die('<p>This date and time have already been booked. <a href="booking1.php">Please try another time</a>.</p>');

}

else

{

//insert new user data into Users table

$sql = "INSERT INTO `booking` ( `Name`, `LessonDate` , `StartTime` , `EndTime` ) VALUES( '{$Name}', '{$LessonDate}' , '{$StartTime}' , '{$EndTime}') ";

mysql_query($sql) or die(mysql_error());

echo "Booking Successful.<br />";

echo "<a href='text.php'> Send reminder text </a>";

}

 

} ?>

[bleured27]

please clean up your code it is messy this

("SELECT * FROM booking WHERE LessonDate = '".mysql_real_escape_string($LessonDate)."' AND (StartTime >= '".mysql_real_escape_string($StartTime)."' AND StartTime <= '".mysql_real_escape_string($EndTime)."')");

 

should be this

$LessonDate =mysql_real_escape_string($LessonDate);
$StartTime=mysql_real_escape_string($StartTime);
$EndTime=mysql_real_escape_string($EndTime);
("SELECT * FROM booking WHERE LessonDate = '$LessonDate' AND (StartTime >= '$StartTime' AND StartTime <= '$EndTime' ")");

*note i didn't looked @ the content yet so i did not took the errors out of it

btw plese put it between code taggs so i can read it properly [tag]

like this

<?php
include('../config.php');
$Name = '';
$LessonDate = '';
$StartTime = '';
$EndTime = '';
if (isset($_POST['submit'])) {
if(isset($_POST['Name'])){ $Name = $_POST['Name']; }
if(isset($_POST['LessonDate'])){ $LessonDate = $_POST['LessonDate']; }
if(isset($_POST['StartTime'])){ $StartTime = $_POST['StartTime']; }
if(isset($_POST['EndTime'])){ $EndTime = $_POST['EndTime']; }

$result = mysql_query("SELECT * FROM booking WHERE LessonDate = '".mysql_real_escape_string($LessonDate)."' AND (StartTime >= '".mysql_real_escape_string($StartTime)."' AND StartTime <= '".mysql_real_escape_string($EndTime)."')");
if(!$result)
{
die(mysql_error());
}
if(mysql_num_rows($result) > 0)
{
die('<p>This date and time have already been booked. <a href="booking1.php">Please try another time</a>.</p>');
}
else
{
//insert new user data into Users table
$sql = "INSERT INTO `booking` ( `Name`, `LessonDate` , `StartTime` , `EndTime` ) VALUES( '{$Name}', '{$LessonDate}' , '{$StartTime}' , '{$EndTime}') ";
mysql_query($sql) or die(mysql_error());
echo "Booking Successful.<br />";
echo "<a href='text.php'> Send reminder text </a>";
}
} ?>

Edited February 17, 2013 by bleured27

[Sarahpengie]

<?php


include('../config.php');
$Name = '';
$LessonDate = '';
$StartTime = '';
$EndTime = '';
if (isset($_POST['submit'])) {
if(isset($_POST['Name'])){ $Name = $_POST['Name']; }
if(isset($_POST['LessonDate'])){ $LessonDate = $_POST['LessonDate']; }
if(isset($_POST['StartTime'])){ $StartTime = $_POST['StartTime']; }
if(isset($_POST['EndTime'])){ $EndTime = $_POST['EndTime']; }



$result = mysql_query("SELECT * FROM booking WHERE LessonDate = '".mysql_real_escape_string($LessonDate)."' AND (StartTime >= '".mysql_real_escape_string($StartTime)."' AND StartTime <= '".mysql_real_escape_string($EndTime)."')");
if(!$result)
{
die(mysql_error());
}


if(mysql_num_rows($result) > 0)
{
die('<p>This date and time have already been booked. <a href="booking1.php">Please try another time</a>.</p>');
}
else
{
//insert new user data into Users table
$sql = "INSERT INTO `booking` ( `Name`, `LessonDate` ,  `StartTime` ,  `EndTime`  ) VALUES(  '{$Name}', '{$LessonDate}' ,  '{$StartTime}' ,  '{$EndTime}') ";
mysql_query($sql) or die(mysql_error());
echo "Booking Successful.<br />";
echo "<a href='text.php'> Send reminder text </a>";
}


} ?>

Edited February 17, 2013 by Sarahpengie

[bleured27]

 

 

 

CTRL + Q to Enable/Disable GoPhoto.it

i changed my last post you might want to read that and besides that delete your last reply and click eddit.

[Sarahpengie]

Hi,

 

Thanks, I've cleaned it up in my actual code, but when I try to edit it or delete it on this and using ctrl + Q it appears I don't have permission to make changes.

[bleured27]

in my browser there stands

in my browser there stands eddit in the bottom right of my posting.

when i eddid my posts

i dont know what prefrences you have on your accaunt.....

 

btw that control q was an mis click i didnt noticed it until now

Edited February 17, 2013 by bleured27

[Sarahpengie]

OK thanks

 

Do you know how I can fix my code to prevent the double bookings from occurring?

 

Thanks again.

[bleured27]

well i can look into it

while im doing 6 websites

@ the moment and i am only here becose i have an error so i will look into it as soon as possible.

[bleured27]

did you tried the mysql error?

Edited February 17, 2013 by bleured27

[PaulRyan]

<?PHP

 include('../config.php');

 if($_SERVER['REQUEST_METHOD'] == 'POST') {

   $Name       = isset($_POST['Name']) ? mysql_real_escape_string($_POST['Name']) : false ;
   $LessonDate = isset($_POST['LessonDate']) ? mysql_real_escape_string($_POST['LessonDate']) : false ;
   $StartTime  = isset($_POST['StartTime']) ? mysql_real_escape_string($_POST['StartTime']) : false ;
   $EndTime    = isset($_POST['EndTime']) ? mysql_real_escape_string($_POST['EndTime']) : false ;

   if(empty($Name) || empty($LessonDate) || empty($StartTime) || empty($EndTime)) {
     echo '1 or more of the required fields are missing.';
   } else {

     $checkBookingQuery = "SELECT `LessonDate` FROM `booking` WHERE `LessonDate` = {$LessonDate} AND (StartTime >= '{$StartTime}' AND StartTime <= '{$EndTime}')";
     $checkBooking      = mysql_query($checkBookingQuery);

     if(mysql_num_rows($checkBooking)) {
       echo 'This booking slot has already been taken. <a href="booking1.php">Please select a different slot</a>';
     } else {

       $insertBookingQuery = "INSERT INTO `booking` (`Name`, `LessonDate`, `StartTime`, `EndTime`) VALUES('{$Name}', '{$LessonDate}', '{$StartTime}', '{$EndTime}')";
       $insertBooking      = mysql_query($insertBookingQuery);

       if(!mysql_affected_rows()) {
         echo 'Unable to insert booking.';
       } else {
         echo 'Booking has been successfully place. <br> <a href="text.php">Send reminder text?</a>';
       }
     }    
   }
 }
?>

Edited February 17, 2013 by PaulRyan

[Christian F.]

Please keep your requests for help on thus subject in one thread. Do not double post!

[Sarahpengie]

Hi,

 

Sorry Christian.

 

Thanks Paul, I replaced my code with your code however I'm still having the same problem.

 

It is still ignoring the endtime and allowing me to book 12.00-13.00 when 11.30-12.30 is booked :/

[PaulRyan]

What is your table structure? Post it here, so I can have a play about.

[rguitar]

Your 12-1 timeslot does not satisfy the condition StartTime >= (12) as compared to the 11:30 in your post. The query is asking for both conditions to be satisfied with the AND. It does with 11-12 but not 12 -1.

[Barand]

in my browser there stands

in my browser there stands eddit in the bottom right of my posting.

 

when i eddid my posts

i dont know what prefrences you have on your accaunt.....

 

 

Are those haikus?