php - Mysql Php 使用事务和提交一次选择和处理一条记录

Question

从一个表中选择一系列 id 然后将它们放入的最佳方法是什么通过事务循环处理一次更新一条记录？

$result = mysql_query('SELECT p_code FROM replenishment ');
$ids = array();

while ($p_code = mysql_fetch_row($result)) {
    $ids[] = $p_code[0];
    foreach($ids as $p_code) {
        mysql_query('SELECT @A:=replenishment.p_code,@B:=replenishment.model
                        from replenishment
                        left join replenishment1 on replenishment1.p_code = replenishment.p_code
                        where replenishment.branch=10
                        and replenishment.p_code=$p_code
                        and replenishment.stock < min

                        and replenishment1.stock > 0

                        group by replenishment.p_code');
        mysql_query('UPDATE replenishment1
                        SET stock = (stock - @B), B5=(b5+@B)  WHERE @A = replenishment1.p_code
                        and replenishment1.stock >= @B');
        $row = mysql_fetch_assoc();
    }
}

Answer 1

开始使用 PDO 或 MySQLi。

通过它您可以管理您的交易并确保数据安全地进入您的数据库。

<?php
try {
  $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2', 
      array(PDO::ATTR_PERSISTENT => true));
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}

try {  
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $dbh->beginTransaction();

/* You can put your foreach loop over here or you can also do the entire process for single update. It's your choice */

  $dbh->commit();

} catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}
?>

它保证在完成之前没有其他人能够看到这些更改。如果出现问题，catch block 将回滚自事务开始以来所做的所有更改，然后打印出一条错误消息。