这是一个使用交易的好例子吗？

Question

I have the following update of the users table and a selection which gives me the updated result right after the update, and I am wondering if is it appropriate to use a transaction here? Also if yes then am I using it right codewisely together with prepared statements?

try {

        // connect to the database
        require 'connect.php';
        // create an update query
        $conn->beginTransaction();
        $queryUpdate = $conn->prepare("UPDATE users SET userName=:userName, firstName=:firstName,
        lastName=:lastName, password=:password, image=:image  WHERE userId=:userId"); 
        $queryUpdate->bindParam( ':userId' , $sUserId );
        $queryUpdate->bindParam( ':userName' , $sNewUserName );
        $queryUpdate->bindParam( ':firstName' , $sNewFirstName );
        $queryUpdate->bindParam( ':lastName' , $sNewLastName );
        $queryUpdate->bindParam( ':password' , $sNewPassword );
        $queryUpdate->bindParam( ':image' , $sNewImagePath );
        $bResult = $queryUpdate->execute();
        // create another query to get some of the updated values
        $querySelect = $conn->prepare("SELECT users.userName, users.firstName, users.lastName, users.image
        FROM users WHERE userId=:userId");
        $querySelect->bindParam( ':userId' , $sUserId );
        // run query 
        $querySelect->execute();  
        $ajResult = $querySelect->fetch(PDO::FETCH_ASSOC);
        // take each property one by one
        $sUserName = $ajResult['userName'];
        $sFirstName = $ajResult['firstName'];
        $sLastName = $ajResult['lastName'];
        $sImagePath = $ajResult['image'];
        // i.e. no query has failed, and we can commit the transaction
        $conn->commit();
        $sjResponse = $bResult ? '{"status":"ok", "userName":"'.$sUserName.'", "firstName":"'.$sFirstName.'",
         "lastName":"'.$sLastName.'", "image":"'.$sImagePath.'"}' : '{"status":"error"}'; 
        echo $sjResponse;
} catch (Exception $e) {
        // An exception has been thrown
        // We must rollback the transaction
        echo "ERROR";
        $conn->rollback();

}

Answer 1

If you just need to pass back the result of the update, if the update execute has succeeded, then pass back the values you've just used in the update. As an extra check - you can use rowCount() to check it's actually updated something.

require 'connect.php';
// create an update query
$queryUpdate = $conn->prepare("UPDATE users SET userName=:userName, firstName=:firstName,
lastName=:lastName, password=:password, image=:image  WHERE userId=:userId"); 
$queryUpdate->bindParam( ':userId' , $sUserId );
$queryUpdate->bindParam( ':userName' , $sNewUserName );
$queryUpdate->bindParam( ':firstName' , $sNewFirstName );
$queryUpdate->bindParam( ':lastName' , $sNewLastName );
$queryUpdate->bindParam( ':password' , $sNewPassword );
$queryUpdate->bindParam( ':image' , $sNewImagePath );
$bResult = $queryUpdate->execute();
$sjResponse = ( $bResult && $queryUpdate->rowCount() == 1) ? 
   '{"status":"ok",
    "userName":"'.$sUserName.'", 
    "firstName":"'.$sNewFirstName.'",
    "lastName":"'.$sNewLastName.'", 
    "image":"'.$sNewImagePath.'"}' 
  : '{"status":"error"}'; 
echo $sjResponse;

As for transactions - they are more relevant when you are doing multiple updates/insert/deletes to a database. So for example if you wanted to transfer some points from one user to another - you want to make sure that the value taken off user A gets to user B. If you subtracted the value from A and then something failed with updating user B then the points may just disappear. Using transactions, this could roll back both changes and everything is consistent.