How To: Insert into column where the value is on another table

Question

Summarize the Problem:

I want to write a booking code. I had a little problem, that I wanted to insert a value into the booked table that is in another table called house_info.

The detail of booked & house_info database is below:

Booked Table

 ID_Booking | House_Name | House_ID | House_No | House_Qty | House_Price |
          1 | Rose House |        1 |     RH01 |         1 |             |
          2 | Rose House |        1 |     RH02 |         1 |             |

House Info Table

 House_ID | HouseState_Name | House_Qty | House_Price |
        1 |     Garden City |         8 |        40000|
        2 | Electronic City |        10 |      1000000|

I want to insert the House_Price value on the house_info table into the House_Price column on booked table every time users input on the Booking Form.

Background you've already tried:

I already tried this using a trigger on booked table like below:

Trigger on Booked Table (Before Insert)

IF NEW.House_ID= '1' THEN SET
NEW.House_Price = 40000;
ELSEIF NEW.House_ID= '2' THEN SET
NEW.House_Price = 1000000;

But I realize this is not dynamic because when the company want to change the price of each HouseState_Name he needs to change it from the trigger. So I think what I needed is a query from PHP that can calls the value of each HouseState_Name and hold it on an array and place it or insert it when the Book Query passed (I hope my logic is true, I'm sorry if it's false).

I already tried to search too for the query's to use. But I didn't know how am I going to use the query.

Some Codes:

Booking.php

require 'Connection.php';

//Check Connection
if ($conn->connect_error){
    die("Connection Failed: ". $conn->connect_error);
}

//Check for Submit
if (filter_has_var(INPUT_POST, 'Submit')) {
    //Get Form Data
    $CustomerEmail= htmlspecialchars($_POST["Email"], ENT_QUOTES);
    $House_Name= htmlspecialchars($_POST["HouseName"], ENT_QUOTES);
    $House_ID = htmlspecialchars($_POST["HouseID "], ENT_QUOTES);
    $House_No = htmlspecialchars($_POST["HouseNo "], ENT_QUOTES);

    //Validate the data fields
    if (!empty($CustomerEmail) && !empty($House_Name)) {
        //Passed
        if (filter_var($CustomerEmail, FILTER_VALIDATE_EMAIL) === false) {
            //Failed
            $msg = 'Please use a valid email';
            header("location: ../GardenCity.php?error=PleaseUseValidEmail");
        } else {
            //Passed
            echo "Creating a Booking.<br>";
            //Inserting the Booking Data into Database
            $sql = "INSERT INTO `booked`(`ID_Booking`, `CustomerEmail`, `House_Name`, `House_ID`, `House_No`) 
            VALUES (NULL, '$CustomerEmail', '$House_Name', '$House_ID ', '$House_No', '', '')";
            if ($conn->query($sql) === TRUE) {
                header("location: ../GardenCity.php");
            } else {
                echo "Error: " . $sql . "<br><br>" . $conn->error;
            }
        }

    } else {
        header("location: ../GardenCity.php?error=EmptyFields");
    }

}

$conn -> close();

Expected Results:

Before Update the price

Database Looks

ID_Booking | House_Name | House_ID | House_No | House_Qty | House_Price | 1 | Rose House | 1 | RH01 | 1 | | 2 | Rose House | 1 | RH02 | 1 | | 3 | Rose House | 1 | RH03 | 1 | 40000|

House_ID | HouseState_Name | House_Qty | House_Price | 1 | Garden City | 7 | 40000| 2 | Electronic City | 10 | 1000000|

After Update the price

Database Looks

ID_Booking | House_Name | House_ID | House_No | House_Qty | House_Price | 1 | Rose House | 1 | RH01 | 1 | | 2 | Rose House | 1 | RH02 | 1 | | 3 | Rose House | 1 | RH03 | 1 | 40000| 4 | Rose House | 1 | RH04 | 1 | 200000|

House_ID | HouseState_Name | House_Qty | House_Price | 1 | Garden City | 6 | 200000| 2 | Electronic City | 10 | 1000000|

I hope this is well explained. Please let me know if there's any confusing statements or questions. I will say many thanks to you all if this is answered because I'm so stuck at this and my brain won't work.

I don't understand what House Name and House No are doing in the booking table !?!? — Strawberry, Aug 13 '19 at 11:22
Why would you duplicate informations ? The price can easily be retrieved from `house_info` to `booked` using a `JOIN`. [DRY](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself) — Cid, Aug 13 '19 at 11:25
@Cid It can't (as presently designed), because the price might change. — Strawberry, Aug 13 '19 at 11:53
Then why storing the price in `house_info`, instead of using a default value in `booked.House_Price` ? — Cid, Aug 13 '19 at 12:13
It's not really duplicate informations, it's a snapshot of the price at the time of booking — Pepper, Aug 13 '19 at 12:16
Also I think your "Before/After Update the price" titles are a bit misleading, It's an `INSERT` you're doing (Well, you're doing an `UPDATE` to the house table followed by an `INSERT` in booked, if I understand it correctly) — Pepper, Aug 13 '19 at 12:39
Thank you guys for your clarifications, I appreciate it so much to take you'r time on my question. I'll try to answer all of you: 1.) `House_Name` and `House_No` are on another table (@Strawberry). 2.) It's not duplication informations because the price might change sometimes that's only the habit of the company so we will make it more dynamic to them (@DRY & @Cid). 3.) I still don't know what and how using the default value :) (@Cid). 4.) Sorry if I'm misleading I just want to try describe what it looks like after user input booking form (Pepper). — Faj, Aug 13 '19 at 22:59

Pepper · Accepted Answer · 2019-08-13T12:37:15.993

I think this could work, basically using a subquery just to fetch the price, that should achieve the same result as your insert trigger, but without using fixed prices.

INSERT INTO `booked` (
   `ID_Booking`, 
   `CustomerEmail`,
   `House_Name`, 
   `House_ID`,
   `House_No`, 
   `House_Qty`, 
   `House_Price`
) VALUES (
    NULL, 
   '$CustomerEmail', 
   '$House_Name', 
   '$House_ID',
   '$House_No', 
   '1', 
   (SELECT House_Price FROM house_info WHERE House_ID = '$House_ID')
)

Edit: I set House_Qty at 1, change it according to your needs :)

Maybe you can use the same subquery in your trigger directly instead (haven't tested it) :

SET NEW.House_Price = 
   (SELECT House_Price FROM house_info WHERE House_ID = NEW.House_id);

Assuming your House_ID are unique :)

Thank you so much for you'r answer I really appreciate it and your trigger is works like a charm, unbelievable why this thing doesn't cross into my mind. — Faj, Aug 14 '19 at 01:47

score 1 · Answer 2 · answered Aug 13 '19 at 23:13

1

I would expect to see a schema more or less like this:

houses(house_id*,name)

house_prices(house_id*,price_start_date*,price)

bookings(booking_id*,customer_id,total)

booking_detail(booking_id*,house_id*,start_date,end_date)

* = (component of) PRIMARY KEY

After some reflection, it should be apparent that your present concerns evaporate with this design.

answered Aug 13 '19 at 23:13

Strawberry

33,750
13
40
57

Thank you so much for you'r answer and you gave me an awesome inspiration to design the database for the future. – Faj Aug 14 '19 at 01:44

Barbaros Özhan · Answer 3 · 2019-08-13T19:36:43.873

You can construct such an UPDATE statement with INNER JOINs one of which's in the subquery as a self-JOIN for booked table, put after your existing INSERT statement :

update house_info h join (
  select b1.House_Price, b2.House_ID
  from booked b1
  join ( select House_ID,max(ID_Booking) as ID_Booking 
                from booked
               group by House_ID
                ) b2
     on b1.House_ID = b2.House_ID and b1.ID_Booking = b2.ID_Booking   
) bb on bb.House_ID = h.House_ID
set h.House_Price = bb.House_Price;

but I should admit that your tables' design is not good, because of repeating columns they hold the same information in each.

Demo

Thank you so much for you'r answer I appreciate it. – Faj Aug 14 '19 at 01:46 — Faj, Aug 14 '19 at 01:46

score 0 · Answer 4 · answered Aug 13 '19 at 13:23

Insert Into booked_table (ID_Booking, House_Name, House_Id, House_No, House_Qty, House_Price)
Select 1, House_Name, House_ID, 'RHXX', House_Qty, (SELECT House_Price FROM house_info WHERE House_ID = MM1.House_ID) From booked_table MM1
Where
NOT EXISTS(
  SELECT * 
  FROM booked_table MM2
  WHERE MM2.ID_Booking > MM1.ID_Booking
);

Fiddle: https://www.db-fiddle.com/f/7Bt3ZTQqbjs1jKzJe34qSF/0

I dont included the increment of the ID_Booking and House_No. If you want to increase the House_Price, just do that with another query.

Thank you so much for you'r answer I appreciate it. – Faj Aug 14 '19 at 01:46 — Faj, Aug 14 '19 at 01:46

How To: Insert into column where the value is on another table

Summarize the Problem:

Background you've already tried:

Some Codes:

Expected Results:

4 Answers4