How to write MYSQL data to two tables at the same time

Question

How to write MYSQL data to two tables at the same time, but the second name table "t_cadparcelas" should insert more than one record according to the number of installments so I implemented for but am having compilation errors, which is the best way to write these records?

It is also necessary to capture the last record of the inserted table a moment before "t_cadcontratos" so that I can insert in the second table "t_cadparcelas".

Connection to a database:

    <?php include"../Connections/config.php";
    $conexao = mysqli_connect("$hostname_config","$username_config","$password_config", "$database_config");
    if (mysqli_connect_errno())
      {
      echo "Erro ao selecionar o banco de dados, por favor informe o administrador do sistema ([email protected]) ou envie um email para [email protected] !" . mysqli_connect_error();
      }

// Set autocommit to off
mysqli_autocommit($conexao,FALSE);
?>

Insert’s:

<div id="painelcadastro2" align="center">
<?php   if (isset($_GET['cadastra']) && $_GET['cadastra'] == 'add') {
  $datacompra = implode("-", array_reverse(explode("/",$_GET['datacompra'])));
  $codigoProduto = filter_input(INPUT_GET, 'identProduto1');
  $nomeProduto = filter_input(INPUT_GET, 'nomeProduto1');
  $qtProduto = filter_input(INPUT_GET, 'qtProduto1');
  $valorProduto = filter_input(INPUT_GET, 'valorProduto1');
  $valorparc = filter_input(INPUT_GET, 'valorparcela');
  $parcelas = filter_input(INPUT_GET, 'select_parcelas');
  $entrada = filter_input(INPUT_GET, 'entrada');
  $total = filter_input(INPUT_GET, 'total');
  $nrFicha = filter_input(INPUT_GET,'cadastro');
  $status = "ativo";
  $dataVencimento = filter_input(INPUT_GET, 'datasvenc');
  $dataVencimento = unserialize(base64_decode($dataVencimento));//Decode para array
  $pagamento = "CREDIARIO";
  $cadastracontratos = mysqli_query($conexao, "INSERT INTO t_cadcontratos (Ficha, DataContrato, QuantParcelas, ValorContrato, Entrada, Saldo, DescricaoProduto, QuantProdutos, Vendedores, FormaPagamento) 
                          VALUES ('$nrFicha', '$datacompra', '$parcelas', '$valorProduto', '$entrada', '$total', '$nomeProduto', '$qtProduto', UPPER('$_SESSION[MM_Username]'), '$pagamento')");
  for($numparcelas=1; $numparcelas>$parcelas; $numparcelas++ ){
  $cadastraparcelas = mysqli_query($conexao, "INSERT INTO t_cadparcelas (NumContrato, NumParcela, ValorParcela, DataVencimento, Status) 
                          VALUES ('$NumContrato', '$numparcelas', '$valorparc', '$dataVencimento[numparcelas]', '$status')");
  }
  if($cadastracontratos == '1' && $cadastraparcelas == '1') {
    // Commit transaction
mysqli_commit($conexao);
        echo "Venda Crediário realizada com sucesso !";
  }else{
        echo "Erro ao realizar a venda Crediário, tente novamente !";
  }
// Close connection
mysqli_close($conexao);
}
?>

Answer 1

Yes, you need in loop of for, use the ID generated by the last query.

$ultimoID = mysql_insert_id();

I suggest to test that you check for each insertion in the base, if it was successful, adding up the number of successful installments and testing at the end.

Now you have some important remarks:

• Mysql functions in PHP will be discontinued, you should use PDO or Mysqli instead of Mysql.

• Mysqli changes almost nothing that is used in Mysql. Change very little, take a look.

Another issue is:

• It cannot be trusted that all query`s executed by insertion will be executed. In this case, you must make a transaction (transaction), that is, starts the transaction, executes the queries, if any fails, you give a rollback in everything. So you won’t get garbage in the base and you’ll be sure everything is ok. Read about transactions.