Hotel room booking/reservation

Multi tool use
Multi tool use





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







1















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!










share|improve this question

























  • 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




















1















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!










share|improve this question

























  • 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
















1












1








1








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!










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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 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





















  • 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



















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














1 Answer
1






active

oldest

votes


















2














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.






share|improve this answer


























  • 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












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
});


}
});














draft saved

draft discarded


















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









2














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.






share|improve this answer


























  • 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
















2














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.






share|improve this answer


























  • 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














2












2








2







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.






share|improve this answer















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.







share|improve this answer














share|improve this answer



share|improve this answer








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 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



















  • 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

















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




















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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
oSXNtw4,ush1 Kqeho8NBr5dXnKyy,KqDqe53MFtHQIbG92V

Popular posts from this blog

Monofisismo

Angular Downloading a file using contenturl with Basic Authentication

Olmecas