在执行事务之前准备多个语句?

Is it ok to prepare multiple statments before executing them?

是否可以在执行之前准备多个语句?

$db = PDO('..connection info...');
$cats_stmt = $db->prepare('SELECT * FROM cats');
$dogs_stmt = $db->prepare('SELECT * FROM dogs');

$cats_stmt->execute();
$cats = $cats_stmt->fetchAll(PDO::FETCH_CLASS);//list of cats

$dogs_stmt->execute();
$dogs = $dogs_stmt->fetchAll(PDO::FETCH_CLASS);//list of dogs

This would come in handy for loops where 2 statements with different variables need to be executed after each other. like this:

这对于循环非常有用，其中两个具有不同变量的语句需要逐个执行。是这样的:

$stmt_addcat = $db->prepare('INSERT INTO cats (name,age) VALUES(?,?)');
$stmt_adddog = $db->prepare('INSERT INTO dogs (name,age) VALUES(?,?)');

foreach($cat_n_dog as $bunch){
  $db->beginTransaction();
  $dog_name = $bunch['dog']['name'];
  $dog_age  = $bunch['dog']['age'];
  $stmt_adddog->bindParam(1,$dog_name,PDO::PARAM_STR);
  $stmt_adddog->bindParam(2,$dog_age,PDO::PARAM_STR);
  $result = $stmt_adddog->execute();
  if($result===false){
    $db->rollBack();
    continue;
  }
  $cat_name = $bunch['cat']['name'];
  $cat_age  = $bunch['cat']['age'];
  $stmt_addcat->bindParam(1,$cat_name,PDO::PARAM_STR);
  $stmt_addcat->bindParam(2,$cat_age,PDO::PARAM_STR);
  $result = $stmt_addcat->execute();
  if($result===false){
    $db->rollBack();
    continue;
  }
  $db->commit();
}

I am asking because I had situations where PDO would act buggy and throw errors on the sqlite driver, so I'm wondering if the above example is even supposed to work.

我之所以问这个问题，是因为我曾经遇到过PDO在sqlite驱动程序中表现错误并抛出错误的情况，所以我想知道上面的例子是否有效。

p.s. examples are made-up on the spot.

附注:例子是现场编造的。

2 个解决方案

#1

I'll post as answer since comments don't allow enough space.

因为注释没有足够的空间，所以我将作为答案发布。

Yes, you can prepare several prepared statements and then execute them in a loop, there's nothing wrong with that.

是的，您可以准备好几个准备好的语句，然后在循环中执行它们，这没有什么错。

The transaction part is wrong. If you want to execute all or no queries, you need to start your transaction outside of the loop (same with commit). That's where PHP's try/catch comes in handy.

交易部分是错误的。如果希望执行全部查询或不执行查询，则需要在循环之外启动事务(与提交相同)。这就是PHP的try/catch派上用场的地方。

$db = PDO('..connection info...');

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exceptions

try
{
    $stmt_addcat = $db->prepare('INSERT INTO cats (name,age) VALUES(?,?)');
    $stmt_adddog = $db->prepare('INSERT INTO dogs (name,age) VALUES(?,?)');

    $db->beginTransaction();

    foreach($cat_n_dog as $bunch) { } // Do your foreach binding and executing here 

    $db->commit();
}
catch(PDOException $e)
{
    $db->rollBack();

    echo "Error occurred. Error message: ". $e->getMessage() .". File: ". $e->getFile() .". Line: ". $e->getLine();
}

#2

-1

just wanted to comment here that when I placed the code provided by Michael J.V. into my project I had a smile on my face that I could not remove.

我想说的是，当我把Michael J.V.提供的代码放到我的项目中时，我脸上露出了无法去除的微笑。

This code with PDO is so beautiful. I just prepared and executed over 500 queries with rollback... HOW ELEGANT!

这个带有PDO的代码非常漂亮。我刚刚准备并执行了500多个查询，并进行了回滚……多么优雅啊!

For a bit more explanation inside the foreach loop... your code should look simmilar to

为了在foreach循环中获得更多的解释……您的代码应该看起来很简单

$stmt = $db->prepare($query);
$stmt->execute(array('name', 'age'));

#1