Security Handling and Transaction Processing Methods of PDO

Transaction (Transaction) is an important function in operating databases, which allows you to reserve one or a series of SQL statements and execute them together. During the execution process, if any of the statements fail to execute, all the changed operations can be rolled back. If the execution is successful, all the operations in this series will be permanently effective. Transactions effectively solve the problem of different synchronization during database operations. At the same time, when executing large amounts of data through transactions, the execution efficiency can be greatly improved.

Transaction processing has four characteristics: atomicity, consistency, isolation, and durability. Not all databases support transaction processing, and PDO provides transaction support for databases that can execute transaction processing.

I. PDO Exception Handling
PDO::ATTR_ERRMODE

1) PDO::ATTR_ERRMODE//Do not report errors (ignore) (0)

2) PDO::ERRMODE_WARNING
//Error reporting in the form of warnings (1)

3) PDO::ERRMODE_EXCEPTION　 //Error reporting in the form of exceptions (2)

<?php 
//The default is PDO::ATTR_ERRMODE, which does not report errors (ignore) (0), and errorCode() and errorInfo() need to be used. 
try{ 
  $pdo=new PDO("mysql:host=localhost;dbname=myapp","root",""); 
//  $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); 
  $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); 
}catch (PDOException $e){ 
  die("fail to connect db".$e->getMessage()); 
} 
$sql="INSERT INTO user VALUES(null,'dabao','26')"; 
try{ 
  $res=$pdo->exec($sql); 
}catch (PDOException $e){ 
  echo $e->getMessage(); 
} 
//$res=$pdo->exec($sql); 
//if($res){ 
//  echo 'OK'; 
//} 
//  echo $pdo->errorCode(); 
//  echo '<br/>'; 
//  print_r($pdo->errorInfo()); 
//} 

II. PDO Preprocessing Methods

1) prepare()　　　 //Used to execute SQL query statements and return PDOStatement objects

2) bindValue()　 //) bind values to corresponding parameters, return a boolean value

3) bindParam()　 //) bind parameters to corresponding query placeholders, return a boolean value

4) bindColumn() //) match column names with a specified variable name

5) execute()　　　 // ) execute a prepared statement, return a boolean value

6) rowCount() // Return the total number of rows affected by the operation statements for add, delete, modify, and query

<?php 
/** 
 * Question mark-style prepared statements, with three binding methods 
 */ 
//1.Connect to the database 
try{ 
  $pdo=new PDO("mysql:host=localhost;dbname=myapp","root",""); 
}catch (PDOException $e){ 
  die("fail to connect db".$e->getMessage()); 
} 
//2. Prepared SQL statement 
$sql="INSERT INTO users(id,name,age) VALUES(?,?,?," 
$stmt=$pdo->prepare($sql); 
//3. Binding parameters to the question mark placeholders 
$id=null; 
$name="test103"; 
$age=103; 
//The first binding method 
//$stmt->bindValue(1,$id); 
//$stmt->bindValue(2,$name); 
//$stmt->bindValue(3,$age); 
//The second binding method 
//$stmt->bindParam(1,$id); 
//$stmt->bindParam(2,$name); 
//$stmt->bindParam(3,$age); 
//4. Execute 
//$stmt->execute(); 
//The third binding method: execute directly with an array 
$stmt->execute(array($id,$name,$age)); 
echo $stmt->rowCount(); 

<?php 
/** 
 * Alias-style prepared statements, with three binding methods 
 */ 
//1.Connect to the database 
try{ 
  $pdo=new PDO("mysql:host=localhost;dbname=myapp","root",""); 
}catch (PDOException $e){ 
  die("fail to connect db".$e->getMessage()); 
} 
//2. Prepared SQL statement 
$sql="INSERT INTO users(id,name,age) VALUES(:id,:name,:age)"; 
$stmt=$pdo->prepare($sql); 
//3. Binding parameters 
$id=null; 
$name="test203"; 
$age=23; 
//The first binding method 
//$stmt->bindValue("id",$id); 
//$stmt->bindValue("name",$name); 
//$stmt->bindValue("age",$age); 
//The second binding method 
//$stmt->bindParam("id",$id); 
//$stmt->bindParam("name",$name); 
//$stmt->bindParam("age",$age); 
//4. Execute 
//$stmt->execute(); 
//The third binding method: execute directly with an array 
$stmt->execute(array("id"=>$id,"name"=>$name,"age"=>$age)); 
echo $stmt->rowCount(); 

<?php 
/** 
 * Query data using prepared statements 
 */ 
//1.Connect to the database 
try{ 
  $pdo=new PDO("mysql:host=localhost;dbname=myapp","root",""); 
}catch (PDOException $e){ 
  die("fail to connect mysql".$e->getMessage()); 
} 
//2. Prepared query 
$sql="SELECT id,name,age FROM users"; 
$stmt=$pdo->prepare($sql); 
//3. Execute 
$stmt->execute(); 
foreach($stmt as $val){ 
  echo $val['id']."------".$val['name']."------".$val['age']."<br/">"; 
} 

3. Introduction to transaction processing methods
1beginTransaction()　　　　 //Open a thing (make a rollback point)

2) commit()　
　　 //Commit transaction

3) rollBack()　　　　　　　　　　　 //Transaction rollback operation

<?php 
//1.Connect to the database 
try{ 
  $pdo=new PDO("mysql:host=localhost;dbname=myapp","root",""); 
  $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); 
}catch (PDOException $e){ 
  die("fail to connect db".$e->getMessage()); 
} 
//2.Perform data operations 
try{ 
  //Start transaction 
  $pdo->beginTransaction(); 
  $sql="insert into users(id,name,age) VALUES(?,?,?," 
  $stmt=$pdo->prepare($sql); 
  //Passing parameters 
  $stmt->execute(array(null,"test1",21")); 
  $stmt->execute(array(null,"test2",22")); 
  $stmt->execute(array(null,"test3",23")); 
  //Commit transaction 
  $pdo->commit(); 
}catch (PDOException $e){ 
  die("fail to execute".$e->getMessage()); 
  //Transaction rollback 
  $pdo->roolback(); 
} 

That's all for this article. I hope it will be helpful to everyone's learning and that everyone will support the Shouting Tutorial more.

Declaration: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume relevant legal liability. If you find any content suspected of copyright infringement, please send an email to notice#w3Please send an email to codebox.com (replace # with @ when sending email) to report violations, and provide relevant evidence. Once verified, this site will immediately delete the infringing content.