Insert data into 3 tables at once

Question

I want to insert values of data array in 3 tables. In one insert everything works fine, but in multiple inserts no. Also, is there any way to cancel the procedure if 1/3 table insert fails?

My code:

    if($_POST["action"] == 'Add')
{
    $data = array(
        ':afm'      =>  $_POST["afm"],
        ':fname'            =>  $visitor->clean_input($_POST["fname"]),
        ':lname'        =>  $visitor->clean_input($_POST["lname"]),
        ':city'     =>  $visitor->clean_input($_POST["city"]),
        ':street'       =>  $visitor->clean_input($_POST["street"]),
        ':company'      =>  $_POST["company"],
        ':email'        =>  $_POST["email"],
        ':phone'        =>  $_POST["phone"],
        ':make'     =>  $_POST["make"],
        ':model'        =>  $_POST["model"],
        ':gen'      =>  $_POST["gen"],
        ':engine'       =>  $_POST["engine"],
        ':plate'        =>  $_POST["plate"],
        ':vin'      =>  $_POST["vin"]
    );

    $visitor->query = "
    INSERT INTO Client 
    (afm, fname, lname, city, street, company, email) 
    VALUES (:afm, :fname, :lname, :city, :street, :company, :email)
        ";


    $visitor->query = "
    INSERT INTO Phone 
    (phone_type, phone, clientID) 
    VALUES ("main phone", :phone, :afm)
        ";


    $visitor->query = "
    INSERT INTO Vehicle 
    (plate, make, model, gen, engine, vin, clientID) 
    VALUES (:plate, :make, :model, :gen, :engine, :vin, :afm)
        ";

    $visitor->execute($data);

    echo '<div class="alert alert-success">success!</div>';
}

Yes, there is a way to do it. Use transactions. More post [here](https://stackoverflow.com/questions/3860280/insert-into-multiple-tables-in-one-query) — Petr Fořt Fru-Fru, Dec 21 '21 at 20:09
Does this answer your question? [Insert into multiple tables in one query](https://stackoverflow.com/questions/3860280/insert-into-multiple-tables-in-one-query) — Joundill, Dec 21 '21 at 21:36

score 0 · Answer 1 · answered Dec 24 '21 at 11:48

First insert

As you said, this work and I will assume here that you are correct.

Second insert

This looks extremely suspect to me, your " main phone" looks to be syntactically incorrect. I doubt that this is your actual code. You will need to have a syntactically correct command there.

Third insert

This looks to be syntactically correct.

Overriding queries

Your second and the third query is assigned to $visitor->query. You will need to do

$visitor->query = ...

at the first assignment and

$visitor->query .= ...

in the second and the third. The reason is that first you are happy to initialize the query member, but on the second and the third command you would be unhappy if you would lose the earlier. Instead, you need to append the second and the third query.

Transactions

This is how you can use a transaction:

$db->beginTransaction();
$stmt1->execute($query1);
$stmt2->execute($query2);
$stmt3->execute($query3);
$db->commit();

Also, it is recommended to break up your commands into 3 separate exec calls instead of merging them into a single one. If you want to execute all inside a single execute, then you need to call ->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);