-1

Hi All,
These days I am implimenting a Hotel Room Reservation system.
I have a small problem in Check Availability' page.

below I listed my database structure.

rooms

rid     int(11) <--- AI
name    varchar(255)
ppn     varchar(50)
facilities  mediumtext

reservations

resid   int(11) <--- AI
rid     int(10)
arrival date
departure   date

System >> You will enter a arrival and departure. System will search & list the available rooms within user entered data range.

Please help me to write sql query for search and list availble rooms.

Thank You

4
Contributors
9
Replies
71
Views
3 Years
Discussion Span
Last Post by diafol
0

Hi jKidz - I would love to help you here but it would be great to see you have a go first. Also, tell us more about the technologies you're using. MySQL? MySQLi? PDO?

Have a go yourself here - no one is going to judge you or your work. Once you've had a go we can make some adjustments and corrections if required and you will learn a lot from the discussion.

I'll watch this discussion and post back when you do :) Good luck!

Michael

1

Here's an idea...

$searchStartDate = '2014-07-04';
$searchEndDate = '2014-07-05';

/*Search for collisions
=======================
Scenarios:

1) where both of the above are between DB values
2) where the above envelop the DB values
3) where one of the above is between the DB values

So we need to check if either one of the above is 
between the DB values or whether there's an 'envelop'
 - in ther words 3) takes care of 1)

... WHERE $searchStartDate BETWEEN `startDate` AND `endDate` 
    OR $searchEndDate BETWEEN `startDate` AND `endDate` 
    OR ($searchStartDate <= `startDate` AND $searchEndDate >= `endDate`)

For 'non-collisions', do something similar

Edited by diafol

0

MySQL

Hi jKidz - I would love to help you here but it would be great to see you have a go first. Also, tell us more about the technologies you're using. MySQL? MySQLi? PDO?

Have a go yourself here - no one is going to judge you or your work. Once you've had a go we can make some adjustments and corrections if required and you will learn a lot from the discussion.

I'll watch this discussion and post back when you do :) Good luck!

Michael

Thank You Michael for your lovly comment.
I have posted this question in one more leading technology site.
But only thing I got is bad words and -rep.
But in this site you are the one who posted very first comment in my very first thread.
So, I would like to give my thanks to you.

Michael,
I am using PHP & MySQL.
I have completed full system but 'check availability' page.
I have created variables for 'adults' (number of adults), 'children'(number of children), 'checkin date' and 'checkout date' which passes data from previous page's form.
But I don't know how to, search within checkin date and checkout date and display the available rooms.
Please help me

0

For 'non-collisions', do something similar

Hi diafol,
Thank you for your valuable reply.
Now I am going to try your code.
However, I got an idea that what is happening in this query.
Thank You.

(My English is not so good. Please never mind)

0

Dear diafol,
Can I use database table name instead of $searchStartDate?
If can't please tell me what is $searchStartDate

WHERE $searchStartDate

0

$searchStartDate is the start date from your search form.

$searchStartDate = '2014-07-04';
$searchEndDate = '2014-07-05';

I just hard coded them, but you'd get them from $_GET superglobals

$searchStartDate = $_GET['startdate'];
$searchEndDate = $_GET['enddate'];
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.