Solved

Overlapping Times MySQL

Posted on 2013-06-19
4
2 solutions
251 Views
Last Modified: 2013-06-21
I have a table that holds conference room reservations.

I put in an event in next week from 8am Monday until 4:30pm on Friday.

Even though I thought I was checking for overlapping times, I was able to enter an event for next Wednesday from 10am until 12...which is right in the middle of the first event that lasts one week.

How can I structure my query to avoid this from happening?

$checkTime = mysql_query("SELECT id FROM fm_calendar_resourceRequests WHERE  ((`startDate` BETWEEN '".date("Y-m-d", strtotime($firstDay))."' AND '".date("Y-m-d", strtotime($returnDay))."') OR (`endDate` BETWEEN '".date("Y-m-d", strtotime($firstDay))."' AND '".date("Y-m-d", strtotime($returnDay))."')) AND  ((`startTime`  BETWEEN '".$startTime."' AND '".$endTime."') OR (`endTime` BETWEEN '".$startTime."' AND '".$endTime."'))") or die("Check Times: " . mysql_error());

Open in new window


The query actually produces:

SELECT id FROM fm_calendar_resourceRequests WHERE  ((`startDate` BETWEEN '2013-06-27' AND '2013-06-27') OR (`endDate` BETWEEN '2013-06-27' AND '2013-06-27')) AND  ((`startTime`  BETWEEN '10:00' AND '12:30') OR (`endTime` BETWEEN '10:00' AND '12:30'))

Open in new window


The explanation of my table is as attached as an image.
db-structure.png
0
Comment
Question by:t3chguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 2
4 Comments
 
LVL 1

Author Comment

by:t3chguy
ID: 39260886
Sample Data can be seen in the following image...these are the two rows that conflict.
sample-data.png
0
 
LVL 9

Assisted Solution

by:COANetwork
COANetwork earned 250 total points
ID: 39260927
sample data not necessary, if you actually look at the statement you labeled as "The query actually produces:".  Your code will work fine if events OVERLAP (meaning, if either the start date or the end date of one event is outside the range of the other event).  But your query does not check to see if the range of one event fully contains the other event.
The statement your code has produced checks to see if any of other events start or stop during that 2.5 hours, but it does not check if there are any events where start date and time < current start date and time and end date and time > current end date and time.
You need a statement that will "actually produce" this query: 
	SELECT id 
	FROM fm_calendar_resourceRequests 
	WHERE  
			(
				(
					(`startDate` BETWEEN '2013-06-27' AND '2013-06-27') 
				OR 
					(`endDate` BETWEEN '2013-06-27' AND '2013-06-27')
				) 
			AND  
				(
					(`startTime`  BETWEEN '10:00' AND '12:30') 
				OR 
					(`endTime` BETWEEN '10:00' AND '12:30')
				)
			)
		OR
			(
				(`startDate` <= '2013-06-27' AND `startTime` <= '10:00')
			AND
				(`endDate` >= '2013-06-27' AND `endTime` >= '12:30')
			)

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39262388
You probably want to restructure the table in a way that incorporates the date and time columns together into DATETIME columns.  It will make your programming simpler.  This article tells some of the theory and practice.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

And since you have to make a data base change anyway, now would be a good time to get off MySQL.  PHP is doing away with MySQL.  This article explains why and tells what you must do to keep your scripts running.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 39263919
I will update the Date-and-Time article with this example, but I thought I would post it here first.  It shows the general way that a resource scheduling calendar works.  The locking mechanism might make more sense in a transaction, etc., but the overall design and the queries are the same for all resource schedulers.

<?php // RAY_mysqli_resource_calendar.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// DEMONSTRATE A RESOURCE-SCHEDULING CALENDAR


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    trigger_error("CONNECT FAIL: $mysqli->connect_errno $mysqli->connect_error", E_USER_ERROR);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_calendar
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, rname VARCHAR(24) NOT NULL DEFAULT ''                    # RESOURCE NAME
, alpha DATETIME    NOT NULL DEFAULT '0000-00-00 00:00:00' # BEGINNING OF COMMITMENT
, omega DATETIME    NOT NULL DEFAULT '0000-00-00 00:00:00' # END OF COMMITMENT
)
"
;

// CREATE THE TABLE OR LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// PRE-LOAD SOME SCHEDULED RESOURCES INTO THE CALENDAR
$mysqli->query("INSERT INTO my_calendar (rname, alpha, omega) VALUES ( 'One', '2013-06-20T08:00:00', '2013-06-20T09:59:59' )");
$mysqli->query("INSERT INTO my_calendar (rname, alpha, omega) VALUES ( 'Two', '2013-06-20T08:00:00', '2013-06-20T09:59:59' )");
$mysqli->query("INSERT INTO my_calendar (rname, alpha, omega) VALUES ( 'Two', '2013-06-20T11:00:00', '2013-06-20T12:59:59' )");


// SHOW THE CALENDAR
$res = $mysqli->query("SELECT * FROM my_calendar");
while ($row = $res->fetch_object()) { print_r($row); }


// SCHEDULE A RESOURCE FROM 1:00pm TO 3:00pm
$rname = 'One';
$alpha = date('c', strtotime('June 20, 2013 1:00pm'));
$omega = date('c', strtotime('June 20, 2013 3:00pm - 1 SECOND'));

$res = $mysqli->query("LOCK TABLES my_calendar");
$res = $mysqli->query("SELECT id FROM my_calendar WHERE rname = '$rname' AND ( (alpha BETWEEN '$alpha' AND '$omega') OR (omega BETWEEN '$alpha' AND '$omega') )  LIMIT 1");
$num = $res->num_rows;
if ($num == 0)
{
    $res = $mysqli->query("INSERT INTO my_calendar (rname, alpha, omega) VALUES ( '$rname', '$alpha', '$omega' )");
    echo "SCHEDULED $rname FROM $alpha TO $omega" . PHP_EOL;
}
else
{
    echo "CONFLICT! $rname FROM $alpha TO $omega" . PHP_EOL;
}
$res = $mysqli->query("UNLOCK TABLES my_calendar");


// TRY TO SCHEDULE THE SAME RESOURCE FROM 2:00pm TO 4:00pm
$rname = 'One';
$alpha = date('c', strtotime('June 20, 2013 2:00pm'));
$omega = date('c', strtotime('June 20, 2013 4:00pm - 1 SECOND'));

$res = $mysqli->query("LOCK TABLES my_calendar");
$res = $mysqli->query("SELECT id FROM my_calendar WHERE rname = '$rname' AND ( (alpha BETWEEN '$alpha' AND '$omega') OR (omega BETWEEN '$alpha' AND '$omega') )  LIMIT 1");
$num = $res->num_rows;
if ($num == 0)
{
    $mysqli->query("INSERT INTO my_calendar (rname, alpha, omega) VALUES ( '$rname', '$alpha', '$omega' )");
    echo "SCHEDULED $rname FROM $alpha TO $omega" . PHP_EOL;
}
else
{
    echo "CONFLICT! $rname FROM $alpha TO $omega" . PHP_EOL;
}
$res = $mysqli->query("UNLOCK TABLES my_calendar");


// TRY TO SCHEDULE A DIFFERENT RESOURCE FROM 2:00pm TO 4:00pm
$rname = 'Two';
$alpha = date('c', strtotime('June 20, 2013 2:00pm'));
$omega = date('c', strtotime('June 20, 2013 4:00pm - 1 SECOND'));

$res = $mysqli->query("LOCK TABLES my_calendar");
$res = $mysqli->query("SELECT id FROM my_calendar WHERE rname = '$rname' AND ( (alpha BETWEEN '$alpha' AND '$omega') OR (omega BETWEEN '$alpha' AND '$omega') )  LIMIT 1");
$num = $res->num_rows;
if ($num == 0)
{
    $mysqli->query("INSERT INTO my_calendar (rname, alpha, omega) VALUES ( '$rname', '$alpha', '$omega' )");
    echo "SCHEDULED $rname FROM $alpha TO $omega" . PHP_EOL;
}
else
{
    echo "CONFLICT! $rname FROM $alpha TO $omega" . PHP_EOL;
    while ($row = $res->fetch_object()) { var_dump($row); }
}
$res = $mysqli->query("UNLOCK TABLES my_calendar");


// SHOW THE RESULTING CALENDAR
$res = $mysqli->query("SELECT * FROM my_calendar");
while ($row = $res->fetch_object()) { print_r($row); }

Open in new window

HTH, ~Ray
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Free Trial
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Simple Vote Counting in PHP and MySQL
Article by: Ray
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Register_Globals (a bad idea from day one)
Article by: Ray
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Matching Tagged Data with PHP Regular Expressions
Video by: Terry
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
How to Add a Watermark to an Image Using PHP (Part 2)
Video by: Marco
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Advertise Here

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here