is there any way to do multiple mysql queries and if one fails to undo all?

1

I wonder if mysql has some similar feature. I need to create an application and it is necessary to ensure that all sub queries succeed.

    
asked by anonymous 27.04.2017 / 00:16

1 answer

2

There is, for this you use Transaction , in it you have all queries that are executed within the transaction can be undone ( rollback ) or saved ( commit ).

MySQLi already has support, just use:

mysqli_begin_transaction($link);

Just like turning auto-commit off, using:

mysqli_autocommit($link, false);

In this way all queries executed from $ link will be part of the Transaction, until a commit or rollback is done.

To save the transaction information use:

mysqli_commit($link);

To discard the changes, use:

mysqli_rollback($link);

A simple example:

mysqli_begin_transaction($con);
mysqli_autocommit($con, false);

$logModificacao = mysqli_query($con, '
   INSERT INTO 'log_alteracao_conta'('idUsuario', 'Quando', 'QualMudanca') 
     VALUES ("1","2017-04-26 20:03:00", "1")
');
$logModificacao = mysqli_affected_rows($con) === 1;

$alteraConta = mysqli_query($con, '
   UPDATE usuario 
    SET nome = "zileknI"
     WHERE id = "1" AND nome = "Inkeliz"
');
$alteraConta = mysqli_affected_rows($con) === 1;

if($alteraNome && $logModificacao){
    mysqli_commit($con);
}else{
    mysqli_rollback($con);
}

In this case a table saves the dates that all the changes in the account were made and what was changed and the second query will actually change the user name. If one of them is not done, it will fall into rollback .

It is also possible to use without mysqli_affected_rows , but in some cases very obscure it may not be pointed out problem by MySQL and not inserted data.

    
27.04.2017 / 01:11