Prepare multiple statments before executing them in a transaction?

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_addc开发者_如何学编程at->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.

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

---

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.

$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();
}

---

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.

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

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

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