Member Avatar for jKidz

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
  • 5K Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for mmcdonald

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

Member Avatar for diafol

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
Member Avatar for jKidz

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

Member Avatar for jKidz

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)

Member Avatar for jKidz

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

WHERE $searchStartDate

Member Avatar for diafol

$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'];
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.