How to insert data in two tables with a single line of command in mysql?

Question

First, I saw some answers close to this question, but it does not work. That is why I created a new question and here is the spec:

I created two tables with:

CREATE TABLE car_info (
    id int PRIMARY KEY AUTO_INCREMENT NOT NULL, 
    mileage int, 
    make varchar(100), 
    model varchar(100), 
    fuel varchar(100), 
    gear varchar(100), 
    offertype varchar(100)
);

CREATE TABLE sales_info (
    id int PRIMARY KEY AUTO_INCREMENT NOT NULL, 
    FOREIGN KEY(id) REFERENCES car_info(id), 
    price int, 
    hp int, 
    year int
);

And I joined these two with:

select * 
from (SELECT * 
        FROM car_info
     ) as a 
    left outer join (SELECT * 
                    FROM sales_info
                    ) as b on a.id = b.id 
union 
select * 
from (SELECT * 
        FROM car_info
      ) as a 
    right outer join (SELECT * 
                    FROM sales_info
                    ) as b on a.id = b.id;

As you can see, id is the common variable here. And here, is the car_info table:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| mileage   | int          | YES  |     | NULL    |                |
| make      | varchar(100) | YES  |     | NULL    |                |
| model     | varchar(100) | YES  |     | NULL    |                |
| fuel      | varchar(100) | YES  |     | NULL    |                |
| gear      | varchar(100) | YES  |     | NULL    |                |
| offertype | varchar(100) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

And here, is the sales_info table:

+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| price | int  | YES  |     | NULL    |                |
| hp    | int  | YES  |     | NULL    |                |
| year  | int  | YES  |     | NULL    |                |
+-------+------+------+-----+---------+----------------+

And here, is the code I normally use to import SQL data inside a single table:

app.post('/save', (req, res) => {
    let data = { mileage: req.body.mileage, make: req.body.make, model: req.body.model, fuel: req.body.fuel, gear: req.body.gear, offertype: req.body.offertype };
    let sql = "INSERT INTO car_info SET ?";
    let query = connection.query(sql, data, (err, result) => {
        if (err) throw err;
        res.redirect('/');
    });
});

When I use this code, and call it from a form, it works perfectly. But, what I want to do is, I want to call this code from a form and import both tables to related info like:

app.post('/save', (req, res) => {
    let data = { mileage: req.body.mileage, make: req.body.make, model: req.body.model, fuel: req.body.fuel, gear: req.body.gear, offertype: req.body.offertype, price: req.body.price, hp: req.body.hp, year: req.body.year };
    let sql = "**car_info and sales_info tables code here**";
    let query = connection.query(sql, data, (err, result) => {
        if (err) throw err;
        res.redirect('/');
    });
});

Lastly, I tried something like that:

let sql = `INSERT INTO car_info 
                    (mileage, make, model, fuel, gear, offertype) 
            VALUES('${req.body.mileage}', '${req.body.make}', 
                    '${req.body.model}', '${req.body.fuel}', 
                    '${req.body.gear}', '${req.body.offertype}');
           INSERT INTO sales_info
                    (price, hp, year) 
            VALUES('${req.body.price}', '${req.body.hp}', 
                    '${req.body.year}');`

But the problem here, it only insert data inside car_info and not inside sales_info although I put some value in the related fields.

I hope the question is clear, and how can I achieve this?

Answer 1

With following the @jkalandarov 's answer, I solved the problem. And the main problem here is the relation of sales_info where id is foreign key. But at the end, it is solved :)

Here is my final code:

app.post('/save', (req, res) => {
    let car_info_data = { mileage: req.body.mileage, make: req.body.make, model: req.body.model, fuel: req.body.fuel, gear: req.body.gear, offertype: req.body.offertype };
    let sales_info_data = { price: req.body.price, hp: req.body.hp, year: req.body.year };

    const car_info_query = 'INSERT into car_info SET ? ';
    const sales_info_query = 'INSERT into sales_info SET ?';

    let write_car_info = connection.query(car_info_query, car_info_data, (err, result) => {
        if (err) throw err;
    });
    let write_sales_info = connection.query(sales_info_query, sales_info_data, (err, result) => {
        if (err) throw err;
    })
    //Insert into as many tables as you want
    res.redirect('/');
});

Answer 2

As your two tables have different columns, I suggest you to create two queries in two different variables, then insert data collected from req.body in the tables accordingly.

app.post('/save', (req, res) => {
    const { milage, make, model, fuel, gear, offertype, price, hp, year } = req.body //Destructured object
    const car_info_query = `INSERT into car_info VALUES (?,?,?,?,?,?)`
    const sales_info_query = `INSERT into sales_info VALUES (?, ?, ?)`
    
    let write_car_info = connection.query(car_info_query, [milage, make, model, fuel, gear, offertype] , (err, result) => {
        if (err) throw err;
    });
    let write_sales_info = connection.query(sales_info_query, [price, hp, year], (err, result) => {
        if (err) throw err
    })
    //Insert into as many tables as you want
    res.redirect('/');
});