Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Prepare multiple statments before executing them in a transaction?

Tags:

php

pdo

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.

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

like image 560
Timo Huovinen Avatar asked Jul 06 '11 14:07

Timo Huovinen


People also ask

What is a prepared statement?

What are Prepared Statements? A prepared statement is a parameterized and reusable SQL query which forces the developer to write the SQL command and the user-provided data separately. The SQL command is executed safely, preventing SQL Injection vulnerabilities.

What is prepared statement in php?

A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. Prepared statements basically work like this: Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?").

What is Prepare in MySQL?

The PREPARE statement prepares a SQL statement and assigns it a name, stmt_name , by which to refer to the statement later. The prepared statement is executed with EXECUTE and released with DEALLOCATE PREPARE . For examples, see Section 13.5, “Prepared Statements”. Statement names are not case-sensitive.


1 Answers

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();
}
like image 105
Michael J.V. Avatar answered Oct 20 '22 08:10

Michael J.V.