Handling Fatal error/using includes

[webdeveloper123]

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 

[mac_gyver]

when you successfully complete the post method form processing code, you should perform a redirect to the exact same URL of the current page, the page the form processing code and the form is on, to cause a get request for that page. this is called - Post, Redirect, Get (PRG) - https://en.wikipedia.org/wiki/Post/Redirect/Get this will prevent the browser from resubmitting the form data should that page get reloaded or browsed away from and back to.

to display a one-time success message/content, store a value in a session variable, then test for the session variable, display the variable/content, and clear that session variable, at the appropriate location in the html document on that page.

to allow the visitor to go to any other page, provide navigation links.

[webdeveloper123]

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

[Barand]

Not sure that constraint is very useful.

If you have Room 1 - 1st - 5th then no one else can book the same room from 1st to 5th, however it would allow 2nd to 4th (or any other overlap) which would still be a double booking.

[webdeveloper123]

arrrrgghhh 

[webdeveloper123]

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

[Barand]

I'd opt for

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,
  `occupied` date DEFAULT NULL,
  PRIMARY KEY (`room_booking_id`),
  UNIQUE KEY `room_id_2` (`room_id`,`occupied`),
  KEY `booking_id` (`booking_id`)
) ENGINE=InnoDB

so a booking for 1st to 5th would be stored as

room_id   | date_occupied
----------+---------------
    1     |  2023-11-01 
    1     |  2023-11-02 
    1     |  2023-11-03 
    1     |  2023-11-04 
    1     |  2023-11-05 

then the unique constraint on (roomid, occupied) does work. More records but safer.

[webdeveloper123]

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?

[webdeveloper123]

I got that PRG Running, thank mac_gyver

[Barand]

1 hour ago, webdeveloper123 said:

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 has a potential effect on any process that uses that table. For example, a search for rooms already booked now becomes a simple search for a date between X and Y  instead of looking for overlapping date ranges.

[webdeveloper123]

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 ' • '), ' • ', 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 ' • '), ' • ', 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;

 

[Barand]

My earlier post, showing 5 night occupied, was wrong. If one arrives on the 1st and departs on the 5th then the room is occupied on only 4 nights (1st, 2nd, 3rd, 4th) so you would ...

LEFT JOIN ON r.room_id = rb.room_id AND occupied BETWEEN arrival AND departure - INTERVAL 1 DAY

[webdeveloper123]

Thanks Barand, I'll give it a try