PHP PDO MySQL Transaction Code Structure

Question

I am trying to set up my first transaction in MySQL using PHP / PDO ...

I have a quick question: what is the best way to determine if a previous request was successful or not? Here is what I have right now, but I would rather find a way to test the request using the if statement.

This is a pretty code layout to try to get a working model. I know that $ results does not test effectively if something is good or bad. I have more there, as a place for a real deal, when the time has come.

if ($_POST['groupID'] && is_numeric($_POST['groupID'])) { $sql = "SET AUTOCOMMIT=0"; $dbs = $dbo->prepare($sql); $dbs->execute(); $sql = "START TRANSACTION"; $dbs = $dbo->prepare($sql); $dbs->execute(); $sql = "DELETE FROM users_priveleges WHERE GroupID=:groupID"; $dbs = $dbo->prepare($sql); $dbs->bindParam(":groupID", $_POST['groupID'], PDO::PARAM_INT); $dbs->execute(); try { $sql = "DELETE FROM groups WHERE GroupID=:groupID LIMIT 1"; $dbs = $dbo->prepare($sql); $dbs->bindParam(":groupID", $_POST['groupID'], PDO::PARAM_INT); $dbs->execute(); $results["error"] = null; $results["success"] = true; try { $sql = "DELETE FROM users WHERE Group=:groupID"; $dbs = $dbo->prepare($sql); $dbs->bindParam(":groupID", $_POST['groupID'], PDO::PARAM_INT); $dbs->execute(); $results["error"] = null; $results["success"] = true; $sql = "COMMIT"; $dbs = $dbo->prepare($sql); $dbs->execute(); } catch (PDOException $e) { $sql = "ROLLBACK"; $dbs = $dbo->prepare($sql); $dbs->execute(); $results["error"] = "Could not delete associated users! $e"; $results["success"] = false; } } catch (PDOException $e) { $sql = "ROLLBACK"; $dbs = $dbo->prepare($sql); $dbs->execute(); $results["error"] = "COULD NOT REMOVE GROUP! $e"; $results["success"] = false; } } 

Answer 1

Some general notes: Do not use bindParam() unless you are using a procedure that changes the value of the parameter Therefore, use bindValue() . bindParam () takes the value of the argument as a reference variable. This means that you cannot do $stmt->bindParam(':num', 1, PDO::PARAM_INT); - it causes an error. In addition, PDO has its own functions for managing transactions; you do not need to complete requests manually.

I rewrote your code a bit to shed light on how PDO can be used:

 if($_POST['groupID'] && is_numeric($_POST['groupID'])) { // List the SQL strings that you want to use $sql['privileges'] = "DELETE FROM users_priveleges WHERE GroupID=:groupID"; $sql['groups'] = "DELETE FROM groups WHERE GroupID=:groupID"; // You don't need LIMIT 1, GroupID should be unique (primary) so it controlled by the DB $sql['users'] = "DELETE FROM users WHERE Group=:groupID"; // Start the transaction. PDO turns autocommit mode off depending on the driver, you don't need to implicitly say you want it off $pdo->beginTransaction(); try { // Prepare the statements foreach($sql as $stmt_name => &$sql_command) { $stmt[$stmt_name] = $pdo->prepare($sql_command); } // Delete the privileges $stmt['privileges']->bindValue(':groupID', $_POST['groupID'], PDO::PARAM_INT); $stmt['privileges']->execute(); // Delete the group $stmt['groups']->bindValue(":groupID", $_POST['groupID'], PDO::PARAM_INT); $stmt['groups']->execute(); // Delete the user $stmt['users']->bindParam(":groupID", $_POST['groupID'], PDO::PARAM_INT); $stmt['users']->execute(); $pdo->commit(); } catch(PDOException $e) { $pdo->rollBack(); // Report errors } } 

Answer 2

I will not prepare and execute transactional statements. I would use PDO :: beginTransaction () , PDO :: commit () and PDO :: rollback () .

PDO :: prepare () and PDO :: execute () return FALSE if there is an error, otherwise they will throw a PDOException if you setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION) .

In the exception handler, you should check PDO :: errorInfo () and report the nature of the error. It is best practice to log the initial error information, but give the user a more friendly message.

Do not repeat the literal error message in the user interface - this may give the user incorrect information about your SQL query and schema.

Answer 3

The PDO execute() statement returns TRUE on success and FALSE on failure, so you can check the return value of the previous execute() in the if statement.

 $pdo_result = $dbs->execute(); if ($pdo_result) { // handle success } else { // handle failure // you can get error info with $dbs->errorInfo(); } 

However, as @Bill Kerwin correctly points out (in my answer that I am fully pushing because it is exactly right), it would be preferable to use PDO::beginTransaction() , PDO::commit() and PDO::rollback()