How to insert data on 2 table with join statement database in MySQL

Question

I want to combine 2 tables when inputting data, there is my table

products table (product_id is Auto Increment)
product_id   product_name
-------------------------
1              Mouse
2              Keyboard

price table (price_id is Auto Increment)
price_id      product_id    price
---------------------------------
1              0             2000
2              0             1000

I want to join the table product_id from products table to product_id in price table when I inputted the data, the problem is when I input and add to the database in the price table does not display the data that I have entered

This is my code

<?php 
    if(isset($_POST['submit'])){
    // ambil data dari formulir
    $product_name = $_POST['product_name'];
    $price = $_POST['price'];

// buat query
    $sql1 = "INSERT INTO products VALUES ('', '$product_name')";

    $sql2 = "SELECT price.price,
                   products.product_id
             FROM price LEFT JOIN products AS procs ON product_id = procs.product_id
             INSERT INTO price VALUES ('',procs.product_id,'$price')";
    $objectModel = new Model();
    $query = $objectModel->getConnection();
    $data1 = mysqli_query($query,$sql1);
    $data2 = mysqli_query($query,$sql2);
}

**Warning!** You are _wide open_ for [SQL injection](https://owasp.org/www-community/attacks/SQL_Injection) attacks! You should use parameterized [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of using completely unescaped user data directly in your queries like that. _Never ever ever never_ trust user input. — M. Eriksson, Oct 05 '20 at 08:37

score 0 · Answer 1 · answered Oct 05 '20 at 08:36

The second query is not valid MySQL syntax. INSERT goes before SELECT. I would also recommend LAST_INSERT_ID() to retrieve the id of the last inserted product rather than a join:

insert into price(product_id, price) values (last_insert_id(), ?)

Note that this is a prepared statement, where input values are passed through a placeholder (?) rather than concatenated in the query string. More about the why and how can be read in this famous SO question.

How to insert data on 2 table with join statement database in MySQL

1 Answers1