HiveBrain v1.2.0
Get Started
← Back to all entries
patternphpMinor

Doing a roll back with multiple MySQLI prepared statement

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
statementwithdoingrollbackpreparedmultiplemysqli

Problem

This is my code for doing a roll back with multiple MySQLI prepared statement.

I want to know if anything is wrong with the code.

``
$newRegistrationDataArray = array(
"date" => $date,
"name" => $name,
"address" => $address,
"country" => $country,
"occupation" => $occupation,
"emailAddress" => $emailAddress,
"memberID" => $memberID,
"usename" => $usename,
"password" => $password,
"salt" => $salt,
"secretQuestion" => $secretQuestion,
"secretAnswer" => $secretAnswer,
"transactionCode" => $transactionCode,
"perfectMoneyAccount" => $perfectMoneyAccount,
"egoPayAccount" => $egoPayAccount,
"payeerAccount" => $payeerAccount,
"okPayAccount" => $okPayAccount,
"bitcoinAccount" => $bitcoinAccount,
"upline" => $upline,
"userRegistrationIp" => $userRegistrationIp,
"status" => $status
);

public function CreateNewAccount(&$newRegistrationDataArray){ ///note the &

/ set autocommit to off /
$this->dbConnection->autocommit(FALSE);

$stmt1=$this->dbConnection->prepare("INSERT INTO
members (date,name,address,country,occupation,emailAddress,memberID,usename,password,salt,secretQuestion,secretAnswer,transactionCode,perfectMoneyAccount,egoPayAccount,payeerAccount,okPayAccount,bitcoinAccount,upline,userRegistrationIp,status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$stmt2=$this->dbConnection->prepare("INSERT INTO
membertotalfinancialstatus` (memberId) VALUES (?)");

/ variable needed /
$memberId = $this->generateMemberId(); // a private function that generate member id
$date = $newRegistrationDataArray['date'];
$name = $newRegistrationDataArray['name'];
$address = $newRegistrationDataArray['address'];
$country = $newRegistrationDataArray['country'];
$occupation = $newRegistrationDataArray['occupation'];
$emailAddress = $newRegistrationDataArray['emailAddress'];
$usename = $newRegistrationDataArray['usename'];
$password = $newRe

Solution

function CreateNewAccount(&$newRegistrationDataArray){ ///note the &

    /* variable needed */
    $memberId = $this->generateMemberId(); // a private function that generate member id

    // that is lots of repetitive code, lazy way is use extract,
    // although can be dangerous if you can't trust data in $newRegistrationDataArray
    extract($newRegistrationDataArray);

//    $date = $newRegistrationDataArray['date'];
//    $name = $newRegistrationDataArray['name'];
//    $address = $newRegistrationDataArray['address'];
//    $country = $newRegistrationDataArray['country'];
//    $occupation = $newRegistrationDataArray['occupation'];
//    $emailAddress = $newRegistrationDataArray['emailAddress'];
//    $usename = $newRegistrationDataArray['usename'];
//    $password = $newRegistrationDataArray['password'];
//    $salt = $newRegistrationDataArray['salt'];
//    $secretQuestion = $newRegistrationDataArray['secretQuestion'];
//    $secretAnswer = $newRegistrationDataArray['secretAnswer'];
//    $transactionCode = $newRegistrationDataArray['transactionCode'];
//    $perfectMoneyAccount = $newRegistrationDataArray['perfectMoneyAccount'];
//    $egoPayAccount = $newRegistrationDataArray['egoPayAccount'];
//    $payeerAccount = $newRegistrationDataArray['payeerAccount'];
//    $okPayAccount = $newRegistrationDataArray['okPayAccount'];
//    $bitcoinAccount = $newRegistrationDataArray['bitcoinAccount'];
//    $upline = $newRegistrationDataArray['upline'];
//    $userRegistrationIp = $newRegistrationDataArray['userRegistrationIp'];
//    $status = $newRegistrationDataArray['status'];
    /* variable needed */

    try
    {
        /* set autocommit to off */
        $this->dbConnection->autocommit(FALSE);

        $stmt1=$this->dbConnection->prepare("INSERT INTO `members` (date,name,address,country,occupation,emailAddress,memberID,usename,password,salt,secretQuestion,secretAnswer,transactionCode,perfectMoneyAccount,egoPayAccount,payeerAccount,okPayAccount,bitcoinAccount,upline,userRegistrationIp,status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $stmt1->bind_param('sssssssssssssssssssss',$date,$name,$address,$country,$occupation,$emailAddress,$memberID,$usename,$password,$salt,$secretQuestion,$secretAnswer,$transactionCode,$perfectMoneyAccount,$egoPayAccount,$payeerAccount,$okPayAccount,$bitcoinAccount,$upline,$userRegistrationIp,$status);

        // failure is unexpected, so lets use exceptions to handle it
        if ($stmt1->execute() == false) {
            throw new Exception('Statement 1 Failed');
        }

        $stmt2=$this->dbConnection->prepare("INSERT INTO `membertotalfinancialstatus` (memberId) VALUES (?)");
        $stmt2->bind_param('s',$memberId);

        if ($stmt2->execute() == false) {
            throw new Exception('Statement 2 Failed');
        }

        // tidy up
        $stmt1->close();
        $stmt2->close();

        // is it normal to close connection at this level? or do you leave it open for other requests
        $this->closeDBConn;
    }
    catch(Exception $e)
    {
        // any sort of exception and we should rollback?
        // either that or subclass exception and handle each exception type differently
        $this->dbConnection->rollback();

        // whats the point of this, just re-throw the same exception?
        // $error=$e->getMessage();
        // throw new Exception($error);
        throw $e;
    } finally {
       // if you have php 5.5 you can do your tidy up here, although if 
       // an exception has occured, you probably don't care about freeing stmt's as
       // they all end up in the garbage anyway

        // tidy up
        //$stmt1->close();
        //$stmt2->close();
    }

}

Code Snippets

function CreateNewAccount(&$newRegistrationDataArray){ ///note the &


    /* variable needed */
    $memberId = $this->generateMemberId(); // a private function that generate member id

    // that is lots of repetitive code, lazy way is use extract,
    // although can be dangerous if you can't trust data in $newRegistrationDataArray
    extract($newRegistrationDataArray);

//    $date = $newRegistrationDataArray['date'];
//    $name = $newRegistrationDataArray['name'];
//    $address = $newRegistrationDataArray['address'];
//    $country = $newRegistrationDataArray['country'];
//    $occupation = $newRegistrationDataArray['occupation'];
//    $emailAddress = $newRegistrationDataArray['emailAddress'];
//    $usename = $newRegistrationDataArray['usename'];
//    $password = $newRegistrationDataArray['password'];
//    $salt = $newRegistrationDataArray['salt'];
//    $secretQuestion = $newRegistrationDataArray['secretQuestion'];
//    $secretAnswer = $newRegistrationDataArray['secretAnswer'];
//    $transactionCode = $newRegistrationDataArray['transactionCode'];
//    $perfectMoneyAccount = $newRegistrationDataArray['perfectMoneyAccount'];
//    $egoPayAccount = $newRegistrationDataArray['egoPayAccount'];
//    $payeerAccount = $newRegistrationDataArray['payeerAccount'];
//    $okPayAccount = $newRegistrationDataArray['okPayAccount'];
//    $bitcoinAccount = $newRegistrationDataArray['bitcoinAccount'];
//    $upline = $newRegistrationDataArray['upline'];
//    $userRegistrationIp = $newRegistrationDataArray['userRegistrationIp'];
//    $status = $newRegistrationDataArray['status'];
    /* variable needed */


    try
    {
        /* set autocommit to off */
        $this->dbConnection->autocommit(FALSE);

        $stmt1=$this->dbConnection->prepare("INSERT INTO `members` (date,name,address,country,occupation,emailAddress,memberID,usename,password,salt,secretQuestion,secretAnswer,transactionCode,perfectMoneyAccount,egoPayAccount,payeerAccount,okPayAccount,bitcoinAccount,upline,userRegistrationIp,status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $stmt1->bind_param('sssssssssssssssssssss',$date,$name,$address,$country,$occupation,$emailAddress,$memberID,$usename,$password,$salt,$secretQuestion,$secretAnswer,$transactionCode,$perfectMoneyAccount,$egoPayAccount,$payeerAccount,$okPayAccount,$bitcoinAccount,$upline,$userRegistrationIp,$status);

        // failure is unexpected, so lets use exceptions to handle it
        if ($stmt1->execute() == false) {
            throw new Exception('Statement 1 Failed');
        }

        $stmt2=$this->dbConnection->prepare("INSERT INTO `membertotalfinancialstatus` (memberId) VALUES (?)");
        $stmt2->bind_param('s',$memberId);

        if ($stmt2->execute() == false) {
            throw new Exception('Statement 2 Failed');
        }

        // tidy up
        $stmt1->close();
        $stmt2->close();

        // is it normal to close connection at this level? or do you leave it open for 

Context

StackExchange Code Review Q#62452, answer score: 2

Revisions (0)

No revisions yet.