本文執行個體講述了PHP資料對象PDO操作技巧。分享給大家供大家參考,具體如下:
PHP 資料對象 (PDO) 擴充為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操作事務
//開啟事務
beginTransacition()
//復原
rollback()
//送出
commit()
//判斷是否處于事務之中
inTransaction()
傳回最後插入行的ID
PDO::lastInsertID()
exec()執行
與query()相比,exec()傳回的是受影響行數
$sql = "insert into table values('$val')";
if(false===$pdo->exec($sql)){
echo '執行失敗';
}
PDO實作預編譯
指的是預先編譯sql的結構的一種執行sql的文法
如果執行多條結構相同的sql,編譯的中間結果(文法樹)應該也是一緻的,是以可以将相同的結構,統一編譯,每次使用不同的資料執行即可。
編譯統一的結構
$pdoStatement = $pdo->prepare(sql結構)
綁定資料到中間編譯結果
$pdoStatement ->bindValue()
執行
$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);
預編譯能更好地防止sql注入,是因為預編譯時候不需要使用者的資料參與,是以編譯時結構固定,是以資料不影響到sql結構。
$pdo->query()與$pdo->execute()如果需要防止sql注入,可以使用$pdo->quote()(其作用是先轉義後加引号)
PDOstatement常用方法:
errorInfo()
errorCode()
fetchColumn()
fetch()
fetchAll()
rowCount()
closeCursor()
pdo應用
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->_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 '失敗的語句'.$sql.'
';
echo '錯誤代碼'.$erro[1].'
';
echo '錯誤資訊'.$erro[2].'
';
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));
?>
運作效果圖如下:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5CZyETO5cjZ2ADZlNTMwMjYxkzM0kjN0QjZxQTMhJDZ28CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
希望本文所述對大家PHP程式設計有所幫助。