How can I go about using both transactions and prepared statements with PHP and MySQLi

Questions

I started off working on my current project looking at it from the data integrity standpoint, so I incorporated transactions into the project. Now that I am getting ready to go live, I am looking at it from a security standpoint, and want to add prepared statements. However I am not sure on how to go about using both transactions and prepared statements. In pseudo-code, this is what I have now:

$mysqli = new mysqli(hostname,username,password,database);
$query = " ... ";
$result = $mysqli->query($query);
if($result){
    $mysqli->commit();
}
else{
    $mysqli->rollback();
}

Looking at information on PHP prepared statements, I get this as the general format:

$mysqli = new mysqli(hostname,username,password,database);
if ($mysqli->connect_errno) echo("failed");
$query = "insert into X (var01,var02,var03) values (?,?,?)";
if (!($stmt = $mysqli->prepare($query))) echo("failed");
if (!$stmt->bind_param( sss ,$val01,$val02,$val03)) echo("failed");
if (!$stmt->execute()) echo("failed");
$stmt->close();

So, my confusion is where I would integrate both the prepared statements and transactions. Instead of placing the commit() after the if ($result), would I place it after the $stmt->execute() like so:

$mysqli = new mysqli(hostname,username,password,database);
if ($mysqli->connect_errno) echo("failed");
$query = "insert into X (var01,var02,var03) values (?,?,?)";
if (!($stmt = $mysqli->prepare($query))) echo("failed");
if (!$stmt->bind_param( sss ,$val01,$val02,$val03)) echo("failed");
if ($stmt->execute()){
    $mysqli->commit();
}
else{
    $mysqli->rollback();
}
$stmt->close();

Thanks for your help.

Answers

You re using commit and rollback but you never begin a transaction.

$mysqli->begin_transaction();

Note that you only need transactions if you are doing modification queries that rely on each other. A transaction rollback will undo any modifications performed in that transaction.

You can nest transactions but that s a bit more complicated.

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/23346367/how-can-i-go-about-using-both-transactions-and-prepared-statements-with-php-and

Related