Hotel room booking/reservation

Multi tool use
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm working on a reservation/booking system for a small hotel. I'm pretty good with PHP but not so good with SQL... I made a form where you enter your information, number of rooms and select arrival date and check-out date using a calendar.
Now everything went good until I got to the point where you have to check which rooms are available and it's giving me a headache. There are 10 rooms you can book.
I currently have one table in MySQL storing the information, dates, booking-ID and room-ID/number.
How would you make the SQL for checking which rooms that are available and not?
Should it look something like
"SELECT * FROM bookings WHERE checkinDate >= '$formCheckin'
AND checkoutDate <= '$formCheckout' "
and then get the roomID and count them?
Any help is very appreciated!
php mysql sql
add a comment |
I'm working on a reservation/booking system for a small hotel. I'm pretty good with PHP but not so good with SQL... I made a form where you enter your information, number of rooms and select arrival date and check-out date using a calendar.
Now everything went good until I got to the point where you have to check which rooms are available and it's giving me a headache. There are 10 rooms you can book.
I currently have one table in MySQL storing the information, dates, booking-ID and room-ID/number.
How would you make the SQL for checking which rooms that are available and not?
Should it look something like
"SELECT * FROM bookings WHERE checkinDate >= '$formCheckin'
AND checkoutDate <= '$formCheckout' "
and then get the roomID and count them?
Any help is very appreciated!
php mysql sql
You should really make a rooms table and assign an index which marks them as occupied or not, this would really ease up everything for you.
– Akshay
Oct 10 '15 at 14:52
1
Have you looked at some of theRelated
questions on the right hand side of this page? They may give you some ideas?
– Ryan Vincent
Oct 10 '15 at 14:53
syntax error herecheckinDate =>
you need to reverse those symbols.=>
doesn't do what you think it does.
– Funk Forty Niner
Oct 10 '15 at 14:56
add a comment |
I'm working on a reservation/booking system for a small hotel. I'm pretty good with PHP but not so good with SQL... I made a form where you enter your information, number of rooms and select arrival date and check-out date using a calendar.
Now everything went good until I got to the point where you have to check which rooms are available and it's giving me a headache. There are 10 rooms you can book.
I currently have one table in MySQL storing the information, dates, booking-ID and room-ID/number.
How would you make the SQL for checking which rooms that are available and not?
Should it look something like
"SELECT * FROM bookings WHERE checkinDate >= '$formCheckin'
AND checkoutDate <= '$formCheckout' "
and then get the roomID and count them?
Any help is very appreciated!
php mysql sql
I'm working on a reservation/booking system for a small hotel. I'm pretty good with PHP but not so good with SQL... I made a form where you enter your information, number of rooms and select arrival date and check-out date using a calendar.
Now everything went good until I got to the point where you have to check which rooms are available and it's giving me a headache. There are 10 rooms you can book.
I currently have one table in MySQL storing the information, dates, booking-ID and room-ID/number.
How would you make the SQL for checking which rooms that are available and not?
Should it look something like
"SELECT * FROM bookings WHERE checkinDate >= '$formCheckin'
AND checkoutDate <= '$formCheckout' "
and then get the roomID and count them?
Any help is very appreciated!
php mysql sql
php mysql sql
edited Oct 16 '15 at 20:16
j08691
168k20198215
168k20198215
asked Oct 10 '15 at 14:49
grANDgrAND
447
447
You should really make a rooms table and assign an index which marks them as occupied or not, this would really ease up everything for you.
– Akshay
Oct 10 '15 at 14:52
1
Have you looked at some of theRelated
questions on the right hand side of this page? They may give you some ideas?
– Ryan Vincent
Oct 10 '15 at 14:53
syntax error herecheckinDate =>
you need to reverse those symbols.=>
doesn't do what you think it does.
– Funk Forty Niner
Oct 10 '15 at 14:56
add a comment |
You should really make a rooms table and assign an index which marks them as occupied or not, this would really ease up everything for you.
– Akshay
Oct 10 '15 at 14:52
1
Have you looked at some of theRelated
questions on the right hand side of this page? They may give you some ideas?
– Ryan Vincent
Oct 10 '15 at 14:53
syntax error herecheckinDate =>
you need to reverse those symbols.=>
doesn't do what you think it does.
– Funk Forty Niner
Oct 10 '15 at 14:56
You should really make a rooms table and assign an index which marks them as occupied or not, this would really ease up everything for you.
– Akshay
Oct 10 '15 at 14:52
You should really make a rooms table and assign an index which marks them as occupied or not, this would really ease up everything for you.
– Akshay
Oct 10 '15 at 14:52
1
1
Have you looked at some of the
Related
questions on the right hand side of this page? They may give you some ideas?– Ryan Vincent
Oct 10 '15 at 14:53
Have you looked at some of the
Related
questions on the right hand side of this page? They may give you some ideas?– Ryan Vincent
Oct 10 '15 at 14:53
syntax error here
checkinDate =>
you need to reverse those symbols. =>
doesn't do what you think it does.– Funk Forty Niner
Oct 10 '15 at 14:56
syntax error here
checkinDate =>
you need to reverse those symbols. =>
doesn't do what you think it does.– Funk Forty Niner
Oct 10 '15 at 14:56
add a comment |
1 Answer
1
active
oldest
votes
If you want to know if a room is available during a period, then the logic looks like:
SELECT r.*
FROM rooms r
WHERE NOT EXISTS (SELECT 1
FROM bookings b
WHERE b.roomid = r.roomid AND
b.checkinDate <= $formCheckOut AND
b.checkoutDate >= '$formCheckIn
);
I'm not sure if the equality comparison is needed for both of these. The logic is that a room is available if there are no bookings that start before the checkout date and that end after the check in date.
However, for ten rooms, I might suggest that you just keep a table of each room by day, say for the next ten years (add another year once per year). Such a table isn't very big and it is probably easier to understand how to use it. Plus, you can handle things like a couple reserve the room for a week, but only one person is in the room for the first 3 days.
I think you're missing something along the lines ofAND b.roomId = r.Id
in the subquery
– FuzzyTree
Oct 10 '15 at 15:04
@FuzzyTree . . . Thank you.
– Gordon Linoff
Oct 10 '15 at 15:05
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f33055270%2fhotel-room-booking-reservation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you want to know if a room is available during a period, then the logic looks like:
SELECT r.*
FROM rooms r
WHERE NOT EXISTS (SELECT 1
FROM bookings b
WHERE b.roomid = r.roomid AND
b.checkinDate <= $formCheckOut AND
b.checkoutDate >= '$formCheckIn
);
I'm not sure if the equality comparison is needed for both of these. The logic is that a room is available if there are no bookings that start before the checkout date and that end after the check in date.
However, for ten rooms, I might suggest that you just keep a table of each room by day, say for the next ten years (add another year once per year). Such a table isn't very big and it is probably easier to understand how to use it. Plus, you can handle things like a couple reserve the room for a week, but only one person is in the room for the first 3 days.
I think you're missing something along the lines ofAND b.roomId = r.Id
in the subquery
– FuzzyTree
Oct 10 '15 at 15:04
@FuzzyTree . . . Thank you.
– Gordon Linoff
Oct 10 '15 at 15:05
add a comment |
If you want to know if a room is available during a period, then the logic looks like:
SELECT r.*
FROM rooms r
WHERE NOT EXISTS (SELECT 1
FROM bookings b
WHERE b.roomid = r.roomid AND
b.checkinDate <= $formCheckOut AND
b.checkoutDate >= '$formCheckIn
);
I'm not sure if the equality comparison is needed for both of these. The logic is that a room is available if there are no bookings that start before the checkout date and that end after the check in date.
However, for ten rooms, I might suggest that you just keep a table of each room by day, say for the next ten years (add another year once per year). Such a table isn't very big and it is probably easier to understand how to use it. Plus, you can handle things like a couple reserve the room for a week, but only one person is in the room for the first 3 days.
I think you're missing something along the lines ofAND b.roomId = r.Id
in the subquery
– FuzzyTree
Oct 10 '15 at 15:04
@FuzzyTree . . . Thank you.
– Gordon Linoff
Oct 10 '15 at 15:05
add a comment |
If you want to know if a room is available during a period, then the logic looks like:
SELECT r.*
FROM rooms r
WHERE NOT EXISTS (SELECT 1
FROM bookings b
WHERE b.roomid = r.roomid AND
b.checkinDate <= $formCheckOut AND
b.checkoutDate >= '$formCheckIn
);
I'm not sure if the equality comparison is needed for both of these. The logic is that a room is available if there are no bookings that start before the checkout date and that end after the check in date.
However, for ten rooms, I might suggest that you just keep a table of each room by day, say for the next ten years (add another year once per year). Such a table isn't very big and it is probably easier to understand how to use it. Plus, you can handle things like a couple reserve the room for a week, but only one person is in the room for the first 3 days.
If you want to know if a room is available during a period, then the logic looks like:
SELECT r.*
FROM rooms r
WHERE NOT EXISTS (SELECT 1
FROM bookings b
WHERE b.roomid = r.roomid AND
b.checkinDate <= $formCheckOut AND
b.checkoutDate >= '$formCheckIn
);
I'm not sure if the equality comparison is needed for both of these. The logic is that a room is available if there are no bookings that start before the checkout date and that end after the check in date.
However, for ten rooms, I might suggest that you just keep a table of each room by day, say for the next ten years (add another year once per year). Such a table isn't very big and it is probably easier to understand how to use it. Plus, you can handle things like a couple reserve the room for a week, but only one person is in the room for the first 3 days.
edited Oct 10 '15 at 15:05
answered Oct 10 '15 at 14:57
Gordon LinoffGordon Linoff
794k37318421
794k37318421
I think you're missing something along the lines ofAND b.roomId = r.Id
in the subquery
– FuzzyTree
Oct 10 '15 at 15:04
@FuzzyTree . . . Thank you.
– Gordon Linoff
Oct 10 '15 at 15:05
add a comment |
I think you're missing something along the lines ofAND b.roomId = r.Id
in the subquery
– FuzzyTree
Oct 10 '15 at 15:04
@FuzzyTree . . . Thank you.
– Gordon Linoff
Oct 10 '15 at 15:05
I think you're missing something along the lines of
AND b.roomId = r.Id
in the subquery– FuzzyTree
Oct 10 '15 at 15:04
I think you're missing something along the lines of
AND b.roomId = r.Id
in the subquery– FuzzyTree
Oct 10 '15 at 15:04
@FuzzyTree . . . Thank you.
– Gordon Linoff
Oct 10 '15 at 15:05
@FuzzyTree . . . Thank you.
– Gordon Linoff
Oct 10 '15 at 15:05
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f33055270%2fhotel-room-booking-reservation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
gIgj,ELx5TevhaG6TF,VanrVth uX,2f69avGpRk3ls Js 6k RCz7YDhqHpljy8Idfz4A2AgrmHqxnD,L1t0h5,4FkoZ
You should really make a rooms table and assign an index which marks them as occupied or not, this would really ease up everything for you.
– Akshay
Oct 10 '15 at 14:52
1
Have you looked at some of the
Related
questions on the right hand side of this page? They may give you some ideas?– Ryan Vincent
Oct 10 '15 at 14:53
syntax error here
checkinDate =>
you need to reverse those symbols.=>
doesn't do what you think it does.– Funk Forty Niner
Oct 10 '15 at 14:56