Php PDO在事务中多个准备语句

问题描述：

I have this code:

    $this->db->beginTransaction();
    $query = 'INSERT INTO `table1` VALUES (NULL,:a,:b,NULL,NOW())';
    $sth = $this->db->prepare($query);
    foreach ($values as $k => $v) {
      $sth->bindParam(':' . $k, $v, PDO::PARAM_INT);
    }

    $this->executeQueryRollbackOnException($sth, 'Message_1');
    $resetId = $this->db->lastInsertId();

    $query = 'UPDATE `table2` SET c=:c,d=:d WHERE reset_id IS NULL';
    $sth = $this->db->prepare($query);
    foreach ($values2 as $k => $v) {
      $sth->bindParam(':' . $k, $v, PDO::PARAM_INT);
    }


    $this->executeQueryRollbackOnException($sth, 'Message_2');

    Zend_Debug::dump($sth->rowCount(), 'Affected'); // This is 0

    // Commit
    $this->db->commit();

...

  private function executeQueryRollbackOnException($sth, $message) {
    try {
      $sth->execute();
    } catch (Exception $e) {
      $this->logSQLError($e);
      $this->db->rollBack();
      throw new Exception($message);
    }
  }

First query is executed but the second is not. No mysql error produced. Any ideas ?

我有这段代码： p>

  $ this-＆gt;  db-＆gt; beginTransaction（）; 
 $ query ='INSERT INTO`table1` VALUES（NULL，：a，：b，NULL，NOW（））'; 
 $ sth = $ this-＆gt; db-＆gt  ; prepare（$ query）; 
 foreach（$ values as $ k =＆gt; $ v）{
 $ sth-＆gt; bindParam（'：'。$ k，$ v，PDO :: PARAM_INT）; 
  } 
 
 $ this-＆gt; executeQueryRollbackOnException（$ sth，'Message_1'）; 
 $ resetId = $ this-＆gt; db-＆gt; lastInsertId（）; 
 
 $ query ='UPDATE`table2`  SET c =：c，d =：d WHERE reset_id IS NULL'; 
 $ sth = $ this-＆gt; db-＆gt; prepare（$ query）; 
 foreach（$ values2 as $ k =＆gt; $ v  ）{
 $ sth-＆gt; bindParam（'：'。$ k，$ v，PDO :: PARAM_INT）; 
} 
 
 
 $ this-＆gt; executeQueryRollbackOnException（$ sth，'Message_2'）  ; 
 
 Zend_Debug :: dump（$ sth-＆gt; rowCount（），'受影响'）;  //这是0 
 
 //提交
 $ this-＆gt; db-＆gt; commit（）;  
  code>  pre> 
 
  ...  p> 
 
 
 私有函数executeQueryRollbackOnException（$ sth，$ message）{
 try {\  n $ sth-＆gt; execute（）; 
} catch（Exception $ e）{
 $ this-＆gt; logSQLError（$ e）; 
 $ this-＆gt; db-＆gt; rollBack（）; 
 抛出新的异常（$ message）; 
} 
} 
  code>  pre> 
 
 执行第一个查询但第二个查询不执行。 没有产生mysql错误。 有什么想法吗？ p> 
  div>

答

I found the solution.

foreach ($values as $k => $v) {
   $sth->bindParam(':' . $k, $v, PDO::PARAM_INT);
}

Should be

foreach ($values as $k => $v) {
   $sth->bindParam(':' . $k, $values[$k], PDO::PARAM_INT);
}

Sorry I didn't posted the right code at first time, I excluded foreaches to simplify the code

答

The fact that $sth->rowCount() is 0 does not mean the query was not run. If you have no error reported in your PHP error log, then the query ran just fine. If the row count is 0, that means that no rows were updated. This is very likely due to your condition not matching any rows.

Run the following; if you get 0, then you know the problem is your UPDATE condition:

SELECT COUNT(*) FROM `table2` WHERE reset_id IS NULL

Php PDO在事务中多个准备语句

相关推荐