如何使用PHP的预处理语句与MySQL事务执行多个查询？

Question

I need to execute 2 or more not identical queries (INSERT's in this example.) using PHP's prepared statements and MySQL's transactions. So if I have a 2 INSERT statements, I want both of them executed, or none of them.

I want to traslate this example MySQL transaction to a PHP code:

START TRANSACTION;
     INSERT INTO `file` (`name`, `mime_type`, `size`, `comment`) VALUES ('file.png', 'image/png', '1024', 'My comment');
     INSERT INTO `complementary_file` (`file_id`, `user_id`) VALUES (LAST_INSERT_ID(), '1');
COMMIT;  

PHP code I'm working on:

mysqli_autocommit($connection, FALSE);
if ($stmtFile = mysqli_prepare($connection, "INSERT INTO `file` (`name`, `mime_type`, `size`, `comment`) VALUES (?, ?, ?, ?)")) {
    mysqli_stmt_bind_param($stmtFile, 'ssis', $name, $mime_type, $size, $comment);
    if (mysqli_stmt_execute($stmtFile)) {
        if ($stmtComplementaryFile = mysqli_prepare($connection, "INSERT INTO `complementary_file` (`file_id`, `user_id`) VALUES (?, ?)")) {
            mysqli_stmt_bind_param($stmtComplementaryFile, 'ii', mysqli_insert_id($connection), $user_id);
            mysqli_stmt_execute($stmtComplementaryFile);
        }
    } else {
        mysqli_rollback($connection);
    }
}
mysqli_commit($connection);  

PHP code above works but what if I have more critical statements to execute? Is there are good way to execute statements with PHP's prepared statemens and transactions at the same time?

Please note that for $stmtComplementaryFile I must have a mysqli_insert_id() value. Also please note that I am not using PDO with this code — I appreciate if suggestions will be MySQLi. Thanks.

Answer 1

The following SQL:

START TRANSACTION;
     INSERT INTO `file` (`name`, `mime_type`, `size`, `comment`) VALUES ('file.png', 'image/png', '1024', 'My comment');
     INSERT INTO `complementary_file` (`file_id`, `user_id`) VALUES (LAST_INSERT_ID(), '1');
COMMIT;  

Can be converted to PHP (with prepared statements) as follows:

mysqli_begin_transaction($connection, MYSQLI_TRANS_START_READ_WRITE);
if ($stmtFile = mysqli_prepare($connection, "INSERT INTO `file` (`name`, `mime_type`, `size`, `comment`) VALUES (?, ?, ?, ?)")) {
    mysqli_stmt_bind_param($stmtFile, 'ssis', $name, $mime_type, $size, $comment);
    if (mysqli_stmt_execute($stmtFile)) {
        if ($stmtComplementaryFile = mysqli_prepare($connection, "INSERT INTO `complementary_file` (`file_id`, `user_id`) VALUES (?, ?)")) {
            mysqli_stmt_bind_param($stmtComplementaryFile, 'ii', mysqli_insert_id($connection), $user_id);
            mysqli_stmt_execute($stmtComplementaryFile);
        }
    } else {
        mysqli_rollback($connection);
    }
}
mysqli_commit($connection);  

In general these two statements are equivalent, however it's a good idea to know what transactions are and what transactions are not.

Transactions are a mechanism to ensure that bulk operations are executed atomically and their results are reflected in the database only if ALL of them succeed. It ensures atomicity of operations.

Transactions are not a mechanism to implement a mutual exclusion lock on the tables. If a table needs to be locked then the LOCK TABLES [READ|WRITE] needs to be used. Equivalently in PHP this is achieved by doing a:

mysqli_query($connection, "LOCK TABLES tableName as TableAlias [read|write]");

followed by

mysqli_query($connection, "UNLOCK TABLES");