doucu5836 2016-04-13 14:04
浏览 88
已采纳

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

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.

  • 写回答

1条回答 默认 最新

  • dongweihuai5601 2016-04-13 15:06
    关注

    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"); 
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 如何用Thoony写ESP32温湿度检测无源蜂鸣器报警代码?
  • ¥20 部件内部的CT图像数据集
  • ¥15 Visual studio调用动态库
  • ¥15 双目摄像头标定后的校准文件
  • ¥15 powerbi矩阵增加度量值后出现对应关系错乱
  • ¥30 频率分析法分析绘制奈奎斯特图、波特图
  • ¥15 弹出来一万个系统找不到指定的文件框框,怎么解决
  • ¥15 ADS生成的微带线为什么是蓝色空心的
  • ¥15 求一下解题思路,完全不懂
  • ¥15 tensorflow