logic question

Q_Prime

this is specifically a php/mysql question but really it can be answered by anyone with computer programming experience. i'm writing a website for a friend, and i need to validate a reservation for a room, the user is entering their requested arrival and depareture dates. and i am checking if a room is avaliable

visStartDate is the start of a reservation already entered in the database
visEndDate is the end date of reservation already in the database
arDate is the requested arrival date of the user
depDate is the requested departure date of the user

i figured there were four situations i needed to look for

when the end of the requested date overlaps with the beginning of an existing reservation
(((mktime(visStartDate) >= $arDate) AND (mktime(visStartDate) <= $depDate))

when the requested dates overlaps an existing reservation entirely
OR ((mktime(visStartDate) >= $arDate) AND (mktime(visEndDate) <= $depDate))

when the existing reservation overlaps the requested date entirely
OR (($arDate >= mktime(visStartDate)) AND ($depDate <= mktime(visEndDate)))

when the requested arrival date overlaps the end of an existing reservation
OR (($arDate>=mktime(visStartDate)) AND ($arDate<=mktime(visEndDate))))

the mktime function was my attempt at comparing unix timestamps of the dates so it'd just be integer comparison but that didn't work so i figure my logical statements are the flaw.

no error is returned for the code so it's not a syntax issue.

Geod

I've thought about it - I could be completely wrong, but you can even simplify it down further than 4, into 2 situations:

- When $arDate is before visStartDate AND $depDate is after visStartDate
- When $arDate is after visStartDate AND $arDate is before visEndDate

Easy to use graphic below. I see things better visually. A for arrival, D for departure.

Anyways, to make a long story, short, your situations would seem to work in my mind. I don't know how you're comparing the dates, but you might want to look into the mySQL function DATEDIFF. Good luck, let me know if I'm completely off track!

Q_Prime

i might not be understanding your picture completely, i quickly drew up one to show you what i thought about when i was writing the statements. starting from first to last they correspond in order to my statements up above.



if yours still works please explain why. because i haven't done this kind of stuff for a while and i don't understand. haha

Q_Prime

also can i use datediff to compare the php variable to a mysql feild?

Geod

I understand your picture, and that works fine as well. Basically you can have 4 situations, these correspond from left to right in your picture as well:

1. The arrival time of the requesting party is before the database's party arrives, but the departure time of the requesting party is during the stay of the database's party.
2. The arrival time of the requesting party is before the database's party arrives, but the departure time of the requesting party is after the arrival and departure date of the database's party.
3. The arrival time of the requesting party is after the database party arrives, but the departure of the requesting party is before the departure of the database's party. (Essentially #3 backwards)
4. The arrival time of the requesting party is after the database party arrives, but the departure of the requesting party is after the departure of the database's party. (Essentially #1 backwards)

These are all true, but you can even simplify it more if you wish. You don't have to. Consider #1 and #2:

What do they have in common?

1. The requesting arrival date is before the DB arrival date.
2. The requesting departure date is after the DB arrival date.

Where do they differ?

1. The requesting departure time of #1 is before the DB departure time. In #2, the requesting departure time is after the DB departure time.

What does this tell us?
The differences don't MATTER! You'd only have to check for the common attributes, the different attributes can be ignored. Basically you can check for common attribute #1 as normal, but instead of checking for the different attribute #1, you can substitute it for common attribute #2 which covers both cases.

You can apply this to situation #3 and #4. This is all optional though, since your initial situations should work as well.

I've never personally used DATEDIFF, but if I have some time tonight I'll experiment with it.

Q_Prime

ah okay that makes sense, but then if my statements aren't the problem. then there has to be a problem with the variables i'm comparing.

Vrtra Theory

You should actually be able to simplify the whole question down to just one statement (with two expressions).

Given: "Arrival" and "Departure" dates (these are the dates you want to stay.);
"Reserved Start" and "Reserved End" dates (the dates already in the database.)

If ("Reserved End" AFTER "Arrival") AND ("Reserved Start" BEFORE "Departure")
  The dates don't work.
Else
  The dates are OK.
End

To explain why, here's a chart of the 6 possible scenarios. The first four are bad, the last two are OK. The red lines are the existing reserved dates.

#1-#4 match both of the expressions above, so the dates aren't valid. For #5, the first expression is false, so the dates are OK. For #6, the second expression is false, so the dates are OK.

As far as how you compare your dates, you have two options:

A) Do this comparison in SQL on the database side. This means you'll want to format your PHP variables a certain way when you put them in the SQL string.

Do this comparison in PHP, in your code. This means you'll want to pull the dates out of the database formatted in a certain way (or use PHP-specific date comparisons on them.)

I haven't written PHP in quite a while, so I'm sure someone else can give better advice on what specific comparison to use, but you'll want to decide between A and B first. It boils down to whether you're saying "get all the rows from the table, then use a for loop and find any conflicting reservations" or "get all the rows from the table that conflict, then see if the number of rows returned is more than zero."

Q_Prime

the problem was i was using the wrong fields from the database, i was using fields 2 and 3 instead of 3 and 4. thanks for the logic help too tho, its good seeing how my complex statement can be broken down into a simple one.