Php pdo: prepare () for transactions

Question

Are prepare() and transactions mutually exclusive? I have many queries that I create and then execute, so it seems like using a transaction is what I want; but I read on the prepare.statment page, which using the bindParam method eliminates SQL injection. Is there a way to do both?

Here is an example of the code that I have right now (which may or may not be right):

 $dbhost=FOO; $dbuser=FOOBAR; $dbpass=RABOOF; $options=array(STUFF); $dbh = new PDO("mysql:host=$dbhost", $dbuser, $dbpass, $options); // I know this ^ works $dbh->beginTransaction(); $record_data = $dbh->prepare("UPDATE $db.$tbl SET :column=:value WHERE `key` = :key;"); function record_data($q,$a,$k){ $record_data->bindParam(':column', $q); $record_data->bindParam(':value', $a); $record_data->bindParam(':key', $k); $record_data->execute(); } // $pairs is an array with ~50 objects/rows foreach($pairs as $pair){ list($qstn , $ans) = explode('=', $pair); switch($qstn){ case 1: if(something) record_data($qstn,$ans,$key); break; case 2: if(something) record_data($qstn,$ans,$key); break; case 3: if(something) record_data($qstn,$ans,$key); break; // more default: record_data($qstn,$ans,$key); break; } } $dbh->commit(); 

When I tried the full code, I got No connection could be made because the target machine actively refused it. I usually see this message when the connection information is incorrect (or the account is not configured properly / as I expect). But I tested the PDO connection separately, and it worked fine. So I probably did something else wrong.

EDIT : Are variables allowed in prepare() ?

EDIT 2 : I added try{} around $dbh = PDO(…) and added echo "connected" at the end of try (and made the catch bit) and it made echo'd "connected", so it connects. But after "connecting" it prints this error message, so the problem occurs after a successful connection.

EDIT 3 : I added

 $dbRS = $dbh->query("SELECT * FROM `database`.`table`;"); $row = empty($dbRS) ? false : $dbRS->fetch(PDO::FETCH_ASSOC); print_r($row); 

and he printed the first row of the table, so of course this is a join.

Answer 1

You are using vars that were not defined as part of the function. Just use:

 global $record_data; 

like the first line in a function, and it will work.

Answer 2

• “Are they mutually exclusive?”: No, as you show, this is a kind of “function declaration”, and the transaction is similar to the process (OS) in which the function is executed.
• “Are variables allowed?”: I think you should start checking your PHP function record_data($q,$a,$k) : there is an error. Try adding global $record_data; at the beginning of the function.

General comments: The main advantage of PDO is error capture (by PHP error string or returning SQL error messages) for each single SQL state. See pdo.begintransaction , pdo.commit , pdo.rollback and pdo.error-handling .

Example:

 $dbh->beginTransaction(); /* Do SQL */ $sth1 = $dbh->exec("CREATE TABLE xyz (..)"); $sth2 = record_data($qstn1,$ans1,$key1); $sth2 = record_data($qstn2,$ans2,$key2); /* Commit the changes */ $dbh->commit();