PHP - Make 3 INSERT on different tables with relationship between them

Question

PHP - Make 3 INSERT on different tables with relationship between them

Navigation

#1 by (2 votes)

7

I would like your endorsement. I need to do 3 INSERT at once. But I also need to get the last ID of each table to do the relationship of the tables.

Is this correct, to do this?

if($_GET['operacao'] == 'addPrimeiroCadastro'){
    $mysqli->query("INSERT INTO grupo (id_condominio, nome) VALUES ('".$loginUltimoRegistro."','".$_GET['inputPriGrupo']."')");
    $ultimoGrupo = $mysqli->insert_id;

    $mysqli->query("INSERT INTO unidades (id_condominio, id_grupo, nome) VALUES ('".$loginUltimoRegistro."','".$_GET['inputPriGrupo']."')");
    $ultimoUnidade = $mysqli->insert_id;

    $mysqli->query("INSERT INTO morador (id_condominio, id_unidade, funcao, nome, cpf, senha) VALUES ('".$loginUltimoRegistro."', '".$ultimoUnidade."', ,'sin', '".$_GET['inputPriNome']."', '".$_GET['inputPriCPF']."', '".$_GET['inputPriSenha']."')");   
}

php mysql mysqli

asked by anonymous 18.12.2015 / 02:59

1 answer

How to maintain multiple connections to postgresql in Node.js? How to fill an ontology from Java?

score 2 · Answer 1

Use two-phase transactions to ensure that all three inserts (group, units, and host) are made or none in case of failure.

if($_GET['operacao'] == 'addPrimeiroCadastro'){

    mysqli_autocommit($conexao, false);
    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

    try {
        $mysqli->begin_transaction();

        $sql = "INSERT INTO grupo (id_condominio, nome) VALUES (?,?)";
        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param('is', $loginUltimoRegistro, $_GET['inputPriGrupo']);
        $stmt->execute();
        $ultimoGrupo = $mysqli->insert_id;

        $sql = "INSERT INTO unidades (id_condominio, id_grupo, nome) VALUES (?,?,?)";
        $stmt = $mysqli->prepare($sql);
        $nome = "Doge";
        $stmt->bind_param('iis', $loginUltimoRegistro, $_GET['inputPriGrupo'], $nome);
        $stmt->execute();
        $ultimoUnidade = $mypresqli->insert_id;

        $sql = "INSERT INTO morador (id_condominio, id_unidade, funcao, nome, cpf, senha) VALUES (?,?,?,?,?,?)";
        $stmt = $mysqli->prepare($sql);
        $funcao = "sin";
        $stmt->bind_param('iissss', $loginUltimoRegistro, $ultimoUnidade, $funcao, $_GET['inputPriNome'], $_GET['inputPriCPF'], $_GET['inputPriSenha']);
        $stmt->execute();

        $mysqli->commit();
    }catch (mysqli_sql_exception $e){
        echo 'erro ao inserir, código: '. $e->getCode() .' mensagem: '. $e->getMessage();
        $mysqli->rollback();
    }
}