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

Asked

Viewed 2,239 times

0

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);
}
?>

1 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.
  • Rogerio, the concept of the site in relation to quality is Wikipedia; that is, formal and well formatted Portuguese. I can edit your answer, but I prefer to leave the tip in this case. We have a community FAQ for this: How we should format questions and answers? (I think there is more focused on formatting, but good PT is essential;)

  • Yes, you can. I tried to help, because the question was a little vague. I will take greater care in writing the answers. Sorry.

  • Well, I got your answer.

  • Rogerio, will MYSQL really be discontinued? Anyway I switched to Mysqli on the question, now regarding the theory of transactions I understood, but I found no examples, so I tried to put something in the answer, is that it? need some lib in my directory? thanks!

  • Yes, Mysql functions in PHP will be discontinued. About transactions (transactions). See an example at the link below: http://www.w3schools.com/php/func_mysqli_commit.asp

  • OK @Rogeriocoelho changed the question and I’m using mysqli and transactions, but I’m having problems in the for, can you analyze the question edit by gentiliza? Most Grateful!

  • Put it like this: for($in installments=1; $in installments<=$installments; $in installments++ )

Show 2 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.