PHP data Object PDO Operation Skill Summary _php Skill

This article describes the PHP data object PDO manipulation techniques. Share to everyone for your reference, specific as follows:

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

<?php
 try {
  $dsn = "mysql:host=localhost; port=3306; Dbname=wsq_hotel; Charset=utf-8 ";
  $user = ' root ';
  $PSW = ' root ';
  $pdo = new PDO ($DSN, $user, $PSW);
  $sql = ' Select Goods_prices from Wsq_goods_info where goods_id=2 ';
  $sql = "Show database";
  $res = $pdo->query ($sql) or Var_dump ($pdo->errorinfo ());
  Var_dump ($res);
  $mon = $res->fetch (PDO::FETCH_ASSOC);
  echo $mon [' Goods_price '];
 } catch (Pdoexception $e) {
  echo $e->getmessage ();
 }
? >

PDO Operations Transactions

Open transaction
begintransacition ()
//Rollback
rollback ()/
/Commit
commit ()
//Judge whether in the transaction
Intransaction ()

Returns the ID of the last inserted row

Pdo::lastinsertid ()

EXEC () execution

Compared to query (), EXEC () returns the number of affected rows

$sql = "INSERT into table values (' $val ')";
if (false=== $pdo->exec ($sql)) {
 echo ' failed to execute ';
}

PDO implementation Pre-compilation

A syntax for executing SQL that refers to a precompiled SQL structure

If you execute multiple structures with the same SQL, the middle result of the compilation (the syntax tree) should also be consistent, so you can compile the same structure uniformly, each time using a different data execution.

Compiling a unified structure

$pdoStatement = $pdo->prepare (SQL structure)

Binding data to intermediate compilation results

$pdoStatement->bindvalue ()

Perform

$pdoStatement->execute ()
//$sql = "INSERT into table values (NULL,?)";
$sql = "INSERT into table values (Null,:name)";
$stmt = $pdo->prepare ($sql);
Multiple sets of data are also compiled one execution
//$stmt->bindvalue (1, ' Bee ');
$stmt->bindvalue (': Name ', ' Bee ');
$res = $stmt->execute ();
Var_dump ($res);

Precompilation is a better way to prevent SQL injection because the compile-time structure is fixed, so the data does not affect the SQL structure, because the user's data participation is not required at precompiled time.

$pdo->query () and $pdo->execute () if you need to prevent SQL injection, you can use $pdo->quote () (the effect is to escape the following quotes)

Pdostatement Common methods:

ErrorInfo ()
ErrorCode ()
Fetchcolumn ()
Fetch ()
Fetchall ()
RowCount ()
Closecursor ()

PDO application

<?php header (' Content-type:text/html;charset=utf-8 ');
  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 [' Host '])? $config [' Host ']: ' localhost ';
   $this->_port = isset ($config [' Port '])? $config [' Port ']: ' 3306 ';
   $this->_dbname = isset ($config [' dbname '])? $config [' dbname ']: ';
   $this->_username = isset ($config [' username '])? $config [' username ']: ' Root '; $this->_passward = isset ($config [' Passward '])?$config [' Passward ']: ';
  $this->_charset = isset ($config [' CharSet '])? $config [' CharSet ']: ' UTF8 '; Private Function _initdns () {$this->_dns = "mysql:host= $this->_host;port= $this->_port;dbname= $this->_d
  Bname "; Private Function _initdriveroptions () {$this->_driveroptions = array (pdo::mysql_attr_init_command => SE
  T 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->errorinf
    O (); Echo ' failed statement '. $sql. '
    <br> '; Echo ' Error code '. $erro [1]. '
    <br> '; Echo ' error message '. $erro [2]. '
    <br> ';
   Die
  return $result;
   The 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;
   The Public Function Fetchone ($sql) {$res = $this->query ($sql);
   $one = $res->fetchcolumn ();
   $res->closecursor ();
  return $one;
  The Public Function escape_string ($data) {return $this->_pdo->quote ($data); }} $config = Array ("host" => "localhost", "username" => "root", "Passward" => "root", "dbname" => "student"
 S ");
 $pdo = Pdodb::getinstance ($config);
 $sql = "Select Sdept from student where sage=21";

 Var_dump ($pdo->fetchrow ($sql));?>

The Operation effect chart is as follows:

More about PHP Interested readers can view the site topics: "PHP based on PDO Operation Database Skills Summary", "Php+oracle Database Programming Skills Summary", "PHP+MONGODB Database Operation Skills Encyclopedia", "PHP object-oriented Programming Program", Summary of PHP string usage, Getting Started tutorial on Php+mysql database operations, and summary of common PHP database operations techniques

I hope this article will help you with the PHP program design.