Summary of PHP data object PDO operation skills

The examples in this article describe the PHP data object PDO operation skills. Share it with everyone for your reference, the details are as follows:

PHP Data Object (PDO) extension defines a lightweight consistent interface for PHP to access the database.

<?php
 try {
  $dsn = "mysql:host=localhost; port=3306; dbname=wsq_hotel; charset=utf-8";
  $user = &#39;root&#39;;
  $psw =&#39;root&#39;;
  $pdo = new PDO($dsn,$user,$psw);
  $sql = &#39;select goods_prices from wsq_goods_info where goods_id=2&#39;;
  // $sql = "show database";
  $res = $pdo->query($sql) or var_dump($pdo->errorInfo());
  // var_dump($res);
  $mon = $res->fetch(PDO::FETCH_ASSOC);
  echo $mon[&#39;goods_price&#39;];
 } catch (PDOException $e) {
  echo $e->getMessage();
 }
?>

PDO operation transaction

//开启事务
beginTransacition()
//回滚
rollback()
//提交
commit()
//判断是否处于事务之中
inTransaction()

Return the ID of the last inserted row

PDO::lastInsertID()

exec() execution

Compared with query(), exec() Returns the number of affected rows

$sql = "insert into table values('$val')";
if(false===$pdo->exec($sql)){
 echo '执行失败';
}

PDO implements pre-compilation

Refers to a syntax for executing sql that pre-compiles the sql structure

If multiple structures are executed For the same SQL, the intermediate result (syntax tree) of compilation should also be consistent, so the same structure can be compiled uniformly and executed with different data each time.

Compile unified structure

$pdoStatement = $pdo->prepare(sql结构)

Bind data to intermediate compilation results

$pdoStatement ->bindValue()

Execution

$pdoStatement ->execute()
//$sql = "insert into table values(null,?)";
$sql = "insert into table values(null,:name)";
$stmt = $pdo->prepare($sql);
//多组数据也是一编译一执行
//$stmt->bindValue(1,'bee');
$stmt->bindValue(':name','bee');
$res = $stmt->execute();
var_dump($res);

Pre-compilation can better prevent sql injection, yes Because the user's data is not required to participate in pre-compilation, the structure is fixed during compilation, so the data does not affect the SQL structure.

$pdo->query() and $pdo->execute() If you need to prevent sql injection, you can use $pdo->quote() (its function is to escape first and then add quotes)

Commonly used methods of PDOstatement:

errorInfo()
errorCode()
fetchColumn()
fetch()
fetchAll()
rowCount()
closeCursor()

pdo application

<?php
header(&#39;content-type:text/html;charset=utf-8&#39;);
 class PDODB{
  static private $_init;
  private $_host;
  private $_port;
  private $_dbname;
  private $_username;
  private $_password;
  private $_charset;
  private $_dns;
  private $_pdo;
  private function __construct($config){
   $this->_initParamas($config);
   $this->_initDNS();
   $this->_initDriverOptions();
   $this->_initPDO();
  }
  private function __clone(){}
  static public function getInstance($config){
   if(!static::$_init instanceof static){
    static::$_init = new static($config);
   }
   return static::$_init;
  }
  private function _initParamas($config){
   $this->_host = isset($config[&#39;host&#39;])?$config[&#39;host&#39;]:&#39;localhost&#39;;
   $this->_port = isset($config[&#39;port&#39;])?$config[&#39;port&#39;]:&#39;3306&#39;;
   $this->_dbname = isset($config[&#39;dbname&#39;])?$config[&#39;dbname&#39;]:&#39;&#39;;
   $this->_username = isset($config[&#39;username&#39;])?$config[&#39;username&#39;]:&#39;root&#39;;
   $this->_passward = isset($config[&#39;passward&#39;])?$config[&#39;passward&#39;]:&#39;&#39;;
   $this->_charset = isset($config[&#39;charset&#39;])?$config[&#39;charset&#39;]:&#39;utf8&#39;;
  }
  private function _initDNS(){
   $this->_dns = "mysql:host=$this->_host;port=$this->_port;dbname=$this->_dbname";
  }
  private function _initDriverOptions(){
   $this->_driverOptions = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => "set names $this->_charset"
   );
  }
  private function _initPDO(){
   $this->_pdo = new PDO($this->_dns,$this->_username,$this->_passward,$this->_driverOptions) or die("fail");
  }
  public function query($sql){
   if(!$result = $this->_pdo->query($sql)){
    $erro = $this->_pdo->errorInfo();
    echo &#39;失败的语句&#39;.$sql.&#39;<br>&#39;;
    echo &#39;错误代码&#39;.$erro[1].&#39;<br>&#39;;
    echo &#39;错误信息&#39;.$erro[2].&#39;<br>&#39;;
    die;
   }
   return $result;
  }
  public function fetchAll($sql){
   $res = $this->query($sql);
   $list = $res->fetchAll(PDO::FETCH_ASSOC);
   $res->closeCursor();
   return $list;
  }
  public function fetchRow($sql){
   $res = $this->query($sql);
   $row = $res->fetch(PDO::FETCH_ASSOC);
   $res->closeCursor();
   return $row;
  }
  public function fetchOne($sql){
   $res = $this->query($sql);
   $one = $res->fetchColumn();
   $res->closeCursor();
   return $one;
  }
  public function escape_string($data){
   return $this->_pdo->quote($data);
  }
 }
 $config = array(
  "host"=>"localhost",
  "username"=>"root",
  "passward"=>"root",
  "dbname"=>"students"
 );
 $pdo = PDODB::getInstance($config);
 $sql = "select sdept from student where sage=21";
 var_dump($pdo->fetchRow($sql));
?>

The running effect diagram is as follows:

I hope this article will be useful to everyone in PHP programming. helped.

For more articles related to summary of PHP data object PDO operation skills, please pay attention to the PHP Chinese website!