Mysqli multi_query INSERT + insert_id multiples tables autoincrement + Foreign key

Question

I have 2 tables for example a table "users" containing a primary key ID user and other table "details" with a foreign key do usuario.ID

My need would be for php to do for example in a single query o INSERT user and their details. Currently I managed to do this in the following way:

$sql = "INSERT INTO usuario (usuario.nome,usuario.cpf) VALUES ('$nome','$cpf')";
$trans1 = $mysqli->query($sql);

$sql = "INSERT INTO detalhes (usuario.user_id,detalhes.civil,detalhes.idade) VALUES ($mysqli->insert_id,'$civil','$idade')";
$trans2 = $mysqli->query($sql);

if ($trans1 AND $trans2 == 1){
     echo "<script type='text/javascript'>alert('Usuário Cadastrado');document.location.href=\"index.php?pagina=home\"</script>";
  } else {
     echo "<script type='text/javascript'>alert('Não foi possível inserir o usuario.');document.location.href=\"index.php?pagina=home\"</script>";
  }

$conn->close();

I don’t know if this shape is right, but it’s the one I got for now.

I spent hours trying to get the same result through multi_query() but I’m not getting it at all.

Someone has some light?

Thanks!

Answer 1

To get consistent results use transactions, this ensures that only the data will be recorded if both Serts work.

If the second Insert fails automatically the first will be canceled(rollback). For more details see

$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT;

$mysqli->autocommit(false);
try{
    $sql = "INSERT INTO usuario (usuario.nome,usuario.cpf) VALUES (?,?)";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('ss', $nome, $cpf);
    $insert1 = $stmt->execute();
    $id = $mysqli->insert_id;

    $sql = "INSERT INTO detalhes (usuario.user_id,detalhes.civil,detalhes.idade) VALUES (?,?,?')";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('isi', $id, $civil, $idade);
    $insert2 = $stmt->execute();

    $mysqli->commit();
}catch(mysqli_sql_exception $e){
    echo 'SQLState: '. $e->getCode() .' <br>Descrição: '. $e->getMessage();
}

if(isset($insert1, $insert2)){
    echo 'sucesso';
}

Answer 2

An example you can translate for your project:

Diagram of the two tables (product and items)

Source Code

Code 1: (I would indicate that code)

<?php

    $con = new mysqli('localhost', 'root', 'senha', 'dbase');

    $con->begin_transaction();

    try 
    {

        $sts = $con->prepare("INSERT INTO product(description) VALUES(?)");

        $description = "Café";
        $sts->bind_param('s', $description);    
        $sts->execute();
        $sts->close();

        $productid = $con->insert_id;
        $count = 100;

        $sts = $con->prepare("INSERT INTO items(productid, count) VALUES(?,?)");
        $sts->bind_param('ss', $productid, $count);
        $sts->execute();
        $sts->close();

        $con->commit();

    } 
    catch (Exception $ex) 
    {
        $con->rollback();
    }

Code 2:

<?php

    $con = new mysqli('localhost', 'root', 'senha', 'dbase');

    $con->begin_transaction();

    try 
    {
        $description = "Feijão";
        $query1 = "INSERT INTO product(description) VALUES('".$description."');";

        $count = 200;
        $query2 = "INSERT INTO items(productid, count) VALUES(LAST_INSERT_ID(),".$count.");";

        $con->multi_query($query1.$query2);

        do {
            $con->store_result();            
        }
        while($con->next_result()); 

        $con->commit();

    } 
    catch (Exception $ex) 
    {
        $con->rollback();        
    }