[SOLVED] MySQL LOGIC PROBLEM

[darryldoo]

Hi Everyone,

 

This is my first post so i just want to say Hi to all and congrats to the contributors on such a great forum/message board.

 

I have only recently started to learn PHP and MYSQL and so am still a little wet behind the ears (bit of a novice) so my apolgies if i am asking anything stupid in this question.

 

Before i start, my MYSQL Server version is: 5.0.51b

 

My problem is one of Logic (i think). This is related to a particular "date search" that i would like to carry out on a Database.

 

On the database, I have a table "bookings" which contains the two relevant fields, these are "arrival" and "departure"

 

The "arrival" field holds the guests arrival date and the "departure" field holds the guests departure date.

 

What i would like to be able to do is search both of these fields to check that no bookings exist for a specified date range.

 

I figured that the best way to approach this would be by using a Select Query to extract any dates in these fields that fell between the two dates entered by the user "$reqArrive" and "$reqDepart" and then after the query check to see if any results were returned.

 

 

 

If results are returned then obviously a booking exisits so the property would not be available

If no results are returned then no booking exisits so the property would be available

 

I originally thought this would be quite simple however i am having an absolute nightmare with the logic behind it.

 

This morning i had another attempt at this and actually thought i had cracked it however i was wrong again, here is what i was thinking

 

This is not a SQL query just a logic statement:

 

SELECT from bookings WHERE

'arrival' BETWEEN '$reqArrive' AND '$reqDepart'

OR

'departure' BETWEEN ''$reqArrive' AND '$reqDepart'

 

however this is not catching all of the bookings, here is an example of how this fails:

 

if a booking exists in the databse between 10 August through to 20 August (arrival/departure) and i were to search for the dates 12 August through to 15 August ($reqArrive/$reqDepart) then based on my logic no results would be returned however a booking obviously exists in the database that spans these dates.

 

I am unsure as to how the logic would work in order to catch any existing bookings

 

I would be most greatfull if anyone could advise me further regarding how i could approach this problem

 

Thanks in advance of any help or advice

 

Jacob

 

[revraz]

Sounds like you need to forget about arrival/departure and just go with when they will actually be there.  If someone is staying from 10 Aug through 20 Aug, then mark it as such.

[php_dave]

Hello mate,

 

I have just solved a similar problem on a project I am currently working on. I didnt put the logic in SQL however - I pulled a wide result and then pushed the results in PHP - obviously you can choose how you do it but the following captures all possibilities.

 

IF ((ReqArr < arrival) && (reqdep > arrival) ||
IF (ReqArr >= arrival) && (reqdep <= departure) ||
IF (ReqArr > arrival) && (ReqArr < departure))

 

Not sure whow you would translate that logic to SQL but will hopefully push you in the right direction.

 

Dave

 

 

 

 

[darryldoo]

Sounds like you need to forget about arrival/departure and just go with when they will actually be there.  If someone is staying from 10 Aug through 20 Aug, then mark it as such.

 

 

Hi, Thanks for the quick reply..

 

Im not to sure what you mean by that, do you mean two dates in one field (stupid question i know)

 

or are you refering to having an entry for each date of the years, maybe something like flagging the dates ouccupied?

 

Jay

[darryldoo]

Hello mate,

 

I have just solved a similar problem on a project I am currently working on. I didnt put the logic in SQL however - I pulled a wide result and then pushed the results in PHP - obviously you can choose how you do it but the following captures all possibilities.

 

IF ((ReqArr < arrival) && (reqdep > arrival) ||
IF (ReqArr >= arrival) && (reqdep <= departure) ||
IF (ReqArr > arrival) && (ReqArr < departure))

 

Not sure whow you would translate that logic to SQL but will hopefully push you in the right direction.

 

Dave

 

 

 

 

 

 

Dave, i may well owe you a beer

 

I will have a look at this and run it through on my test database (my peice of scrap paper)

 

I will give this ago and attempt to apply the logic to my problem and let you know how i get on

 

thanks again,

 

Jay

[darryldoo]

Here is an update to my attempt to rectify the problem by applying the logic supplied by dave,

 

Here is the logic:

 

IF ((ReqArr < arrival) && (reqdep > arrival) ||

IF (ReqArr >= arrival) && (reqdep <= departure) ||

IF (ReqArr > arrival) && (ReqArr < departure))

 

Here is my attempt to convert this into a Mysql statement (dont laugh)

 

key to fields and variables used:

$ReqArriveDate (requested arrival date submitted from form)

$ReqDepartDate (requested departure date submitted from form)

arriveDate (arrival date field in database of existing bookings)

departDate (Departure date field in database of existing bookings)

 

My attempted statement

 

"SELECT * FROM reservation WHERE ($ReqArriveDate < 'arriveDate' && $ReqDepartDate > 'departDate') || ($ReqArriveDate >= 'arriveDate' && $ReqDepartDate <= 'departDate') || ($ReqArriveDate > 'arriveDate' AND $ReqArriveDate < 'departDate')";

 

However the statement is not working, it is executing with the two dates in, i can see this as im echoing the query however it is not picking out any of the dates of existing bookings from the database.

 

Any help would be great,

 

Thanks,

 

Jacob

[fenway]

Are those single-quotes?

[darryldoo]

Hi everyone,

First of all, in reply to “Fenway” and the post above, yes they were single quotes...

**Update on progress with this problem**

After my problems I decided to give up on this method, go back to the drawing board and rethink the problem through and what I came up with is a different approach.

Let me know what you think of this, I do have it working now and it seems to be OK (ish) however if you can see any downfalls then please, as I say, let me know.

 

Ok, first off we have a table in our database called reservation with 4 fields:

 

 

 

 

This table has an entry for every date over a two year period, now quite simply when a booking is made the ‘resID’ and ‘booked’ fields on the table are updated on the specific dates booked by the user.

Its a lot easier to search this table for existing bookings, I did initially use the MySql BETWEEN command as in,

 

The booked field has only two values 0 = available : 1 = booked (default value is 0)

 

 

SELECT date FROM reservation WHERE booked = 0 AND (date BETWEEN '$ReqArrDate' AND '$ReqDepDate')

 

However I found that when using this it was selecting the departure date as a full day as its inclusive where as in effect the departure day would be the day a guest left and so the property would not need to be booked for that night.

So I then went on to use

 

SELECT date FROM reservation WHERE booked = 0 AND (date >= '$ReqArrDate' AND date <'$ReqDepDate')

 

This seems to work well as it finds all unbooked dates which I then place into an array.

 

The resID field has a default value of 0000 however when the booking is made the resID field is updated with the new value that links to a customer table with the auto incrementing field resID in it.

 

Any comments / alternative suggestions are more than welcome,

 

Thanks for everones help

Jacob