-1

Hi as my question states I was wondering if it possible to get the a PK from two tables and then insert them into another table where they are already set in as FK. I currently have a form that also inserts data that the user inputs into that same table. For example I want, the user to choose the rooID they want from drop down menu, input checkindate, checkoutdate, contactnumber, and booking extras. This then gets put into my booking table along with the two FK. Below is my booking table columns to help understand the inputs.

  • bookingID (PK)
  • checkindate
  • checkoutdate
  • contactnumber
  • bookingextras
  • customerID (FK) from customer table
  • roomID (FK) from room table

bookproccess.php:

<?php

include_once 'config.php';
$conn = mysqli_connect("127.0.0.1", DBUSER, DBPASSWORD, DBDATABASE);
if(isset($_POST['submit']))
{    
 $roomID = $_POST['rooID'];
 $checkindate = $_POST['checkindate'];
 $checkoutdate = $_POST['checkoutdate'];
 $contactnumber = $_POST['contactnumber'];
 $bookingextras = $_POST['bookingextras'];

     
 $query = "SELECT booking.bookingID, customer.customerID, room.roomID FROM ((booking
 INNER JOIN customer ON booking.customerID = customer.customerID)
 INNER JOIN room ON booking.roomID = room.roomID) WHERE roomID = ".$roomID;

 $result = $conn->query( $query );
 if( $result ) {
 
 $sql = "INSERT INTO booking (roomID, checkindate, checkoutdate, contactnumber, bookingextras, customerID)
 VALUES ('$roomID''$checkindate','$checkoutdate','$contactnumber','$bookingextras','$customerID')";
 if (mysqli_query($conn, $sql)) {
    echo "New record created successfully !";
 } else {
    echo "Error: " . $sql . "
" . mysqli_error($conn);
 }
 mysqli_close($conn);
}}
?>

makeabooking.php:

<h1>Booking</h1>
<h2><a href='menu.php'>[Return to the main page]</a></h2>

<form method = "post" action = "bookproccess.php">
<p>
<label for = "rooID">Room: (name, type, beds): </label>
<select id = "rooID" name = "rooID" required>
<option name = "" value = "" disabled selected>Select</option>
<option name = "1" value = "1">Kellie, S, 5</option>
<option name = "2" value = "2">Herman, D, 2</option>
<option name = "3" value = "3">Scarlett, D, 2</option>
<option name = "4" value = "4">Jelani, S, 5</option>
<option name = "5" value = "5">Sonya, S, 4</option>
<option name = "6" value = "6">Miranda, S, 2</option>
<option name = "7" value = "7">Helen, S, 2</option>
<option name = "8" value = "8">Octavia, D, 3</option>
<option name = "9" value = "9">Bernard, D, 5</option>
<option name = "10" value = "10">Dacey, D, 1</option>
</select>
</p> 

<p>
<label for="checkindate">Check in date: </label>
<input type="date" name="checkindate"required> 
</p>  
<p>
<label for="checkout">Check out date: </label>
<input type="date" name="checkoutdate"required> 
</p>  
<p>  
<label for="contactnumber">Contact number: </label>
<input type="text" name="contactnumber" required> 
</p>
<p>
<label for="bookingextras">Booking extras: </label>
<input type="text" name="bookingextras" size="100" minlength="5" maxlength="200"  required> 
</p> 

<input type="submit" name="submit" value="submit">
<a href="menu.php">[Cancel]</a>

 </form>
 </body>
 </html>
Ty Bown
  • 17
  • 4

1 Answers1

0

Of course it is possible because that's what you should do.

It seems you have a many to many relationship between Customers and Rooms which is resolved with an associative table Bookings. Bookings has 2 foreign keys - one to Customers and one to Rooms.

Typically what you want is to be on a customer master detail page which gives you access to the customer id - the detail being the booking.

To add a booking you introduce a row which is already aware of the master customer id and then in the booking context, you introduce a fixed list of rooms in some fashion (which come with room ids). This will depend on if a customer (a party/family) can occupy one room or multiple rooms.

By the time you submit the booking to be saved in the database, it has CustomerID, Booking ID and any other booking level details you need.

Chuma
  • 714
  • 3
  • 7
  • Thank you @Chuma but what do I need to do to my code to change this? – Ty Bown Jan 29 '21 at 00:44
  • Right. I just answered your question on the possibility because that's what you asked for. – Chuma Jan 29 '21 at 01:33
  • First things first. Your question should include what you've done, what isn't working so that you can get the help you need. Right now, it's kinda open-ended and folks may not be inclined to drip drip draw out information from you. – Chuma Jan 29 '21 at 01:34
  • What I can do is give you some tips here and there and you knowing the whole complete picture, complete it. – Chuma Jan 29 '21 at 01:35
  • To achieve what you want, both RoomID and CustomerID have to exist on the page. For example,do you have a CustomerID that gets passed in from another place? If so it should be there like Why do you have a RooID instead of a RoomID? You have RoomID as a dropdown, if you don't have the customer id from another page you need to make a dropdown so a CustomerID can be selected – Chuma Jan 29 '21 at 01:35
  • If you need to know how populate dropdowns from the database in php look here https://stackoverflow.com/questions/43334180/populating-php-dropdown-list-from-mysql-database – Chuma Jan 29 '21 at 01:36
  • Lastly, you need to consider stored procedures you can call to insert your rows because you never want to show your sql in a page because you invite sql injection but that can be done later once you solve your main problem. – Chuma Jan 29 '21 at 01:38