webdeveloper123

hmm....I think I might go for if the price changes during the year. Because in that Hotel project, I set the same price all year round. Seems like a natural step up Thanks!

Flat rate, all year round, same price. In that case, would it go in movie table?

Hey Barand, I just realised - I don't have price anywhere. Should I add it?

Thanks No, I didn't really want to do that, I've practiced lots on INSERT queries forms. I just load the data straight into the database. I've done it now. To change the date I got a little work around: Copy and paste all the data into a new file, and use find and replace all on the date!

Would the unique constraint be on: screen_on, screen_at and screen_id ?

Do you mean write a script that will generate it for me?

Would it be a good idea to make a Unique Constraint so I can't double book screenings? So for example do it so that I can't show different movies in the same screen at the same time on the same day?

Hey guys, I went with screening.screen_on in my data model as per Barands advice. Thing is, If I create a record for screening entity, I would have to put the date that movie is being screened on. So, say I put todays date, by tomorrow It won't be showing in cinema anymore, unless I Create a new record with tomorrows date. Now I know it's going to take me more than 1 day to finish this project, so everyday I have to load new data in. Would it better if I went for screen_from to screen_until that way I could create a long date range and not have to load new data everyday? Or should I do something like: if (screen_on < 2024/12/31) display listings else dont display and then when I finish the code and everything is ok, change it to: if (screen_on == Today) display listings else dont display Thanks

Ahh I think I get what you mean. Just use row and seat_number from seat instead

The thing is I have some JS in conjunction with some php that generates the cinema screen map on the fly based on rows and columns. So 6 Rows, it does it alphabetically so it would be rows A to F, and then say 10 columns would be 10 seats in the row. So You would get A1, A2 etc all the way up to A-10, for each row etc. Then that same screen map you just select the seats you want then press book, and it enters it into the DB. Here is a screen shot: A few things going on here. The red seats are unavailable because they have already been booked, the blue seats are the ones I have just selected and then I press "Book selected Seats" then I get a JS alert saying "seats booked" then when I ok that, the blue seats turn red. Here is a snippet of code: $primaryKeyValue = 5; $stmt = $conn->prepare("SELECT row AS numRows, numbercols AS numCols FROM seats WHERE seating_id = ?"); $stmt->execute([$primaryKeyValue]); $seatMapData = $stmt->fetch(PDO::FETCH_ASSOC); Obviously this is a stand alone example and it will require further code to integrate it into the system. $seatMapData then goes into the JS and it builds the map as show on screen shot. I am sort of modeling my system on the Vue cinema chain (myvue.com) If you go there and mess around with the website you'll get an idea of what I am trying to do. Got it. I'll be honest with you I wanted to integrate a Payment API into this project. So I went off to Stripe API & signed up and got my API keys and I was going to run it in Test Mode. I did the webhooks successfully and thought I was doing well. But then when I tried to simulate a transaction - I just didn't get it. Maybe I have to get some more programming experience, go up a notch of 2 then do it. It was all in OOP (which is ok because I learnt that in Uni) but haven't revisited it since. I know you can use the Stripe API procedurally, but it looks very complicated. So with the "book selected seats", I am just going to Pretend a transaction took place and let the booking go through. Thanks Barand

Hey Barand, Thanks for all your help. Sorry, just for clarity, is this the final data model? Also, For the booking table, create a unique index on screening_id, seat_id, and at that point I am protected from double booking for a screening.

Hey Guys, Sorry for the late reply. I think I will take advice from here and go with Barand's data model. I'll post back later with ERD.

Yes, that Is what I was after. That's a good idea. I was thinking originally to put screening_id in the seat table, but didn't think it made much sense. Better to create a new entity. Yes, that was the job of rows and columns in the screen entity You mean don't fill seat entity with all seats manually. eg A-1, A-2 etc? Different screens will be different sizes. What would I do instead? Got you. @Barand Thanks for the data model. I too was thinking of splitting movie into 2 entities with the other being genre. I will digest this and post back later. Thanks guys!

Hey Guys, Just looking for some feedback/critique on this data model. It's for a cinema booking system. Main thing I'm concerned about is that (I think) I won't be able to reuse seats. So I'm worried I haven't got my data model correct in the sense that I can reuse seats across different movies/days/screenings. (But I may have, but I have a lingering doubt) So I got this feeling in the seat entity: Seat Id ScreenID Row Number Status 1 4 A 1 booked So say ScreenId 4 relates to screen number 4, and seat A1 gets booked, is that it - has A1 gone forever or am I able to release the seat for the next showing in the same screen id Other options for Status are: Available and reserved. Or is it just a simple case of changing the status from booked to available after the movie finishes? Any feedback would be welcome. Thanks

Thanks Barand, I'll give it a try

Hey Barand, Thanks for the reply. This is the original code you gave me to search for available rooms: SELECT rs.description , rs.sleeps , rs.image , rs.price , substring_index(GROUP_CONCAT(f.description separator ' &bull; '), ' &bull; ', 5) as rmfac , rt.description as rmtype , r.room_id , r.room_number , GROUP_CONCAT(DISTINCT f.description SEPARATOR '<br>') AS rmfac1 FROM room as r JOIN roomtype as rt ON r.roomtype_id = rt.roomtype_id JOIN roomsize as rs ON r.size_id = rs.size_id JOIN room_facility as rf ON rf.size_id = rs.size_id JOIN facility as f ON rf.facility_id = f.facility_id LEFT JOIN room_booking as rb ON r.room_id = rb.room_id AND rb.departureDate > :arrival AND rb.arrivalDate <= :departure WHERE rb.room_id IS NULL GROUP BY room_id; When implementing the revised room_booking table above, would it change to this?: SELECT rs.description , rs.sleeps , rs.image , rs.price , substring_index(GROUP_CONCAT(f.description separator ' &bull; '), ' &bull; ', 5) as rmfac , rt.description as rmtype , r.room_id , r.room_number , GROUP_CONCAT(DISTINCT f.description SEPARATOR '<br>') AS rmfac1 FROM room as r JOIN roomtype as rt ON r.roomtype_id = rt.roomtype_id JOIN roomsize as rs ON r.size_id = rs.size_id JOIN room_facility as rf ON rf.size_id = rs.size_id JOIN facility as f ON rf.facility_id = f.facility_id LEFT JOIN room_booking as rb ON r.room_id = rb.room_id AND rb.occupied > :arrival AND rb.occupied <= :departure WHERE rb.room_id IS NULL GROUP BY room_id;

I got that PRG Running, thank mac_gyver

If I do that, can I isolate the change to just the room_booking table and tweak my Insert Statement to the table or will I have to change a lot more?

It's going to be a nightmare to go back and fix it isn't it?

arrrrgghhh

Hey mac_gyver, I added: <?php header('Location: booking.php'); ?> right at the bottom of the document (just before closing body tag) and I put it just underneath the closing form tag and it gave me this error both times: Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 4 in /var/www/vhosts/booking.php:162 Stack trace: #0 /var/www/vhosts/avbus.uk/neilk.avbus.uk/hotel/booking.php(162): PDO->prepare() #1 {main} thrown in /var/www/vhosts/booking.php on line 162 line 162 is: $place_holders = implode(',', array_fill(0, count($reservedrooms), '?')); $sth = $pdo->prepare("SELECT DISTINCT r.room_id, rs.description FROM room as r JOIN roomsize as rs ON r.size_id = rs.size_id JOIN room_booking as rb ON rb.room_id = r.room_id WHERE rb.room_id IN ($place_holders)"); $sth->execute($reservedrooms); $confirmroomdesc = $sth->fetchAll(); and the entire code is: <!DOCTYPE html> <html> <head> <link rel="stylesheet" href="css/style.css"> <link rel="preconnect" href="https://fonts.googleapis.com"> <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin> <link href="https://fonts.googleapis.com/css2?family=Cormorant+Garamond:wght@500&display=swap" rel="stylesheet"> <meta charset="UTF-8"> <title>Booking</title> </head> <body class="bgcolour"> <?php include 'includes/db.php'; include 'includes/functions.php'; $reservedrooms = []; $today = new DateTime(); if ($_SERVER['REQUEST_METHOD'] == 'POST') { $reservedrooms = $_POST['reservedrooms'] ?? []; $arrivalDate = $_POST['arrivaldate'] ?? ''; $departureDate = $_POST['departuredate'] ?? ''; $totaladults = $_POST['numofadults'] ?? ''; $totalchild = $_POST['numofchild'] ?? ''; $serializedArray = serialize($reservedrooms); $arrivalObj = new DateTime($arrivalDate); $departureObj = new DateTime($departureDate); $difference = $arrivalObj->diff($departureObj); $formattedInterval = $difference->format('%d'); $sqlbookquery = "SELECT r.room_id, rs.price FROM room AS r INNER JOIN roomsize AS rs ON r.size_id = rs.size_id;"; $statement1 = $pdo->query($sqlbookquery); $bookquery = $statement1->fetchAll(); $bookquery = array_filter($bookquery, fn($v)=>in_array($v['room_id'], $reservedrooms)); $totalprice = array_sum(array_column($bookquery, 'price')); $alltogether = $totalprice * $formattedInterval; $alltogether = number_format($alltogether); $totalprice = number_format($totalprice); if (!$reservedrooms) { $zerorooms = "<p class='bookoutput'>You must select a room</p>"; if (isset($zerorooms)) { echo "$zerorooms"; } exit(); } if ($formattedInterval == 0) { $pricenight = "<p class='bookoutput'>Price for the night is: &pound;$totalprice</p>"; } else { $pricenight = "<p class='bookoutput'>Total Price: &pound;$alltogether</p>"; } if ($arrivalObj == $departureObj) { $samedate = "<p class='bookoutput'>You will be charged for a minimum of 1 night</p>"; } else { $samedate = $difference->format('%d nights'); } if ($totaladults == 1) { $adultnum = "<p class='bookoutput'>$totaladults Adult</p>"; } else { $adultnum = "<p class='bookoutput'>$totaladults Adults</p>"; } if ($totalchild == 1) { $childnum = "<p class='bookoutput'>$totalchild Child</p>"; } elseif ($totalchild == 0) { $childnum = ''; } else { $childnum = "<p class='bookoutput'>$totalchild Children</p>"; } $countrooms = count($reservedrooms); if ($countrooms == 1) { $countmsg = "<p class='bookoutput'>$countrooms Room</p>"; } else { $countmsg = "<p class='bookoutput'>$countrooms Rooms</p>"; } } $place_holders = implode(',', array_fill(0, count($reservedrooms), '?')); $sth = $pdo->prepare("SELECT DISTINCT r.room_id, rs.description FROM room as r JOIN roomsize as rs ON r.size_id = rs.size_id JOIN room_booking as rb ON rb.room_id = r.room_id WHERE rb.room_id IN ($place_holders)"); $sth->execute($reservedrooms); $confirmroomdesc = $sth->fetchAll(); ?> <div class="completedetails"> <div> <form action="confirm.php" method="post"> <label for="title">Title:</label><br> <select id="title" name="title" required> <option value="Mr">Mr</option> <option value="Mrs">Mrs</option> <option value="Ms">Ms</option> <option value="Miss">Miss</option><br> </select> <br><label for="fname">First name:</label><br> <input type="text" id="fname" name="fname" required><br> <label for="lname">Last name:</label><br> <input type="text" id="lname" name="lname" required><br> <label for="phone">Phone number:</label><br> <input type="tel" id="phone" name="phone"><br> <label for="email">Email:</label><br> <input type="email" id="email" name="email" required> <br><label for="address">Address</label><br> <input type="text" id="address" name="address" required><br> <label for="postcode">Zip/Post Code</label><br> <input type="text" id="postcode" name="postcode" required><br> <label for="city">City</label><br> <input type="text" id="city" name="city"><br> <label for="country">Country</label><br> <input type="text" id="country" name="country" required><br> <label for="message">Special request</label><br> <textarea id="message" name="message" rows="10" cols="60" placeholder="Enter your special request"></textarea> <input type="hidden" name="totaladults" value="<?=$totaladults?>"> <input type="hidden" name="totalchildren" value="<?=$totalchild?>"> <input type="hidden" name="dateofarrival" value="<?=$arrivalDate?>"> <input type="hidden" name="dateofdeparture" value="<?=$departureDate?>"> <input type="hidden" name="dataArray" value="<?= htmlspecialchars($serializedArray) ?>"> <br> <input type="submit" value="Confirm" class="confirm"> </div> </form> <div> <div><img src="images/website_images/arrival.jpg"></div> <p class="headline">The London Hotel, a Group Hotel<p> <p><?= htmlspecialchars($arrivalObj->format('D, jS M Y')) ?> to <?= htmlspecialchars($departureObj->format('D, jS M Y')) ?> </p> <p><?php if (isset($samedate)) { echo "$samedate"; } ?></p> <p><?php if (isset($adultnum)) { echo "$adultnum"; } ?></p> <p><?php if (isset($childnum)) { echo "$childnum"; } ?></p> <p><?php if (isset($countmsg)) { echo "$countmsg"; } ?></p> <p><?php if (isset($pricenight)) { echo "$pricenight"; } ?></p> <p class="roombookedheadline">Rooms Booked:</p> <?php foreach ($confirmroomdesc as $rd) { ?> <p><?= htmlspecialchars($rd['description']) ?></p> <?php } ?> </div> </div> <?php header('Location: booking.php'); ?> </body> </html> Thanks

Hi Guys, I'm building a online room reservation system. Nearly done, everything is fine - just doing the confirmation. Here's the problem: user searches between 2 dates, available rooms returned, user can book multiple rooms through a checkbox, user clicks on book, takes you to a guest details form(name, address, etc), fills out the form, if the form goes through the booking is complete and then I want to land on a confirmation booking page. Now in my create table statement for the booking it looks like this: CREATE TABLE room_booking ( room_booking_id int(11) NOT NULL AUTO_INCREMENT, room_id int(11) DEFAULT NULL, booking_id int(11) DEFAULT NULL, arrivalDate date DEFAULT NULL, departureDate date DEFAULT NULL, PRIMARY KEY (room_booking_id), FOREIGN KEY (booking_id) REFERENCES booking(booking_id), FOREIGN KEY (room_id) REFERENCES room(room_id), CONSTRAINT rb_unq UNIQUE (room_id,arrivalDate,departureDate) ); I am referring to the CONSTRAINT, which is working fine but causes another problem. When the guest form is filled out and user presses the submit button, I want the confirmation to appear on the next page - which it does - but the thing is obviously I want to use CSS for the confirmation and I would just like to press CTRL-Refresh to see css edits as and when I add/remove css as this is much quicker. But because of the Constraint it gives me a fatal pdo error that I have broke a unique constraint. Which is fine, because that's what it's supposed to do, but then to see any CSS edits I have to go through the whole process again, fill in the form again, then see the effects of the updated css. So I'm basically looking for a way to just refresh the page and see the css edits. Because if I CTRL-refresh it on the error page, I will just get the same error. So I used this code to handle the fatal error: // ERROR AND EXCEPTION HANDLING FUNCTIONS // Convert errors to exceptions set_error_handler('handle_error'); function handle_error($error_type, $error_message, $error_file, $error_line) { throw new ErrorException($error_message, 0, $error_type, $error_file, $error_line); // Turn into ErrorException } // Handle exceptions - log exception and show error message (if server does not send error page listed in .htaccess) set_exception_handler('handle_exception'); function handle_exception($e) { error_log($e); // Log the error http_response_code(500); // Set the http response code echo "<h1>Sorry, a problem occurred</h1> The site's owners have been informed. Please try again later."; } // Handle fatal errors register_shutdown_function('handle_shutdown'); function handle_shutdown() { $error = error_get_last(); // Check for error in script if ($error !== null) { // If there was an error next line throws exception $e = new ErrorException($error['message'], 0, $error['type'], $error['file'], $error['line']); handle_exception($e); // Call exception handler } } Which works just fine, but I'm still in the same situation- instead of refreshing the Fatal PDO error message, I am just refreshing a friendly error message. So I tried doing this: header('Location: confirmbooking.php'); I put this at the bottom of the confirm page (which is basically just 3 Insert table statements) so It redirects me to another page and I thought I'd do my confirmation page on that instead. Redirects me just fine. But I need all the variables/arrays from the confirm.php page - so I do this in the confirmbooking.php page: include 'includes/confirm.php'; I thought I'd be able to use all the variables/arrays from there as if it were on the same page - but I can't even echo variables - it gives me an undefined variable error. Can someone help please? Thanks

Thanks! 😁

You mean go for option a? Sorry I just wanted to be clear

Hey Guys, Looking for some advice/logic here. I am making a hotel room reservation system. I have got to the booking stage. I have 3 tables which I need to insert data into. Guest table (guest details), booking and room_booking. The data dictionary looks like this: Guest(title, fname, lname, phone, email, address, pcode, city, country) Booking(booking_id, guest_id, time_booked, numOfAdults, numOfChildren, specialRequest) Room_booking(room_booking_id, room_id, booking_id, arrivalDate, departureDate) Now I have to insert the data in that order because the booking table requires a guest_id, so I have to generate that first and then thirdly the room_booking table requires a booking_id. I have made a sql insert form for guest details which works fine but I'm a little stuck on the next step. Shall I just keep it to 3 insert statements, grabbing the required id's as I go along and insert them or should I use Insert Into Select? Thanks