Is there any way to do multiple mysql queries and if any fail to undo all?

Question

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

Answer 1

There is, for that you use the Transaction, on it you 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 off "auto-commit" using:

mysqli_autocommit($link, false);

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

To save 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 have been made and what has been modified and the second query will actually change the user name. If one of them is not made will fall into the rollback.

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