patternphpMinor
Doing a roll back with multiple MySQLI prepared statement
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.
``
/ 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
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.