Mysqli multi_query INSERT + insert_id multiple tables autoincrement + Foreign key

Question

Mysqli multi_query INSERT + insert_id multiple tables autoincrement + Foreign key

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

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

My need would be through php to do for example in a single query the user's INSERT and its details. Currently I was able to do this as follows:

$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 do not know if this form is correct, but that's what I got for the moment.

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

Does anyone have any light?

Thanks!

php mysqli

asked by anonymous 07.10.2015 / 21:55

2 answers

Image Upload Check / Check Function Problem with Google Charts + AngularJS + ng-view

score 0 · Answer 1

For consistent results use transactions, this ensures that only the data will be written if both of the inserts work.

If the second insert fails automatically, the first one will be canceled (% with%). 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';
}

score 0 · Answer 2

An example that you can translate to your project:

Diagram of the two tables (product and items)

SourceCode

Code1:(wouldindicatethiscode)

<?php$con=newmysqli('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();        
    }