PDO(PHP Data Object)粮坞，數(shù)據(jù)庫訪問抽象層蝗岖，統(tǒng)一各種數(shù)據(jù)庫的訪問接口晦雨。
image.png
一架曹、PDO連接數(shù)據(jù)庫
1、通過參數(shù)形式連接數(shù)據(jù)庫
try{
$dsn = 'mysql:host=localhost;dbname=article';
$username = 'root';
$password = '';
$pdo = new PDO($dsn,$username,$password);
var_dump($pdo);
}catch (PDOException $e){
echo $e->getMessage();
}
2闹瞧、通過uri的形式連接數(shù)據(jù)庫
try{
$dsn = 'uri:file://D:\wamp64\www\dsn.txt';
$username = 'root';
$password = '';
$pdo = new PDO($dsn,$username,$password);
var_dump($pdo);
}catch (PDOException $e){
echo $e->getMessage();
}
dsn.txt
mysql:dbname=article;host=localhost
3绑雄、通過配置文件連接數(shù)據(jù)庫
try{
$dsn = 'imooc';
$username = 'root';
$password = '';
$pdo = new PDO($dsn,$username,$password);
var_dump($pdo);
}catch (PDOException $e){
echo $e->getMessage();
}
php.ini
pdo.dsn.imooc="mysql:host=localhost;dbname=article"
二、pdo方法
1奥邮、exec() 可以實現(xiàn)一次插入多條語句
try{
$dsn = 'mysql:host=localhost;dbname=article';
$username = 'root';
$password = '';
$pdo = new PDO($dsn,$username,$password);
// exec();執(zhí)行一條sql語句并返回其受影響的記錄的條數(shù),如果沒有受影響的記錄万牺，返回0
// exec對于select沒有作用
$sql = <<< EOF
create table if not exists pdo_user(
id int unsigned auto_increment key,
username varchar(20) not null unique,
password char(32) not null,
email varchar(30) not null
);
EOF;
$res = $pdo->exec($sql);
var_dump($res); // int 0
$sql = "insert into pdo_user(username,password,email) values ('king','king','imooc@qq.com')";
$res = $pdo->exec($sql);
var_dump($res); // int 1
}catch (PDOException $e){
echo $e->getMessage();
}
2、lastInsertiId() 獲得最后插入的Id號
3洽腺、errorCode() 返回上一次操作的SQLSTATE
4脚粟、errorInfo() 返回上一次操作的 錯誤信息
返回的錯誤信息的數(shù)組，數(shù)組中包含3個單元
0=>SQLSTATE,1=>CODE,2=>INFO
5蘸朋、query() 返回一個PDOStatement對象
<?php
/**
* Created by PhpStorm.
* User: zhengjiayuan
* Date: 2018/7/14
* Time: 16:44
*/
header("content-type:text/html;charset=utf-8");
try{
$pdo = new PDO('mysql:host=localhost;dbname=article','root','');
$sql = "select * from pdo_user where id = 1";
$stmt = $pdo->query($sql); // 執(zhí)行失敗返回 false
foreach ($stmt as $row){
print_r($row);
}
}catch (PDOException $e){
}
5核无、prepare() + execute()
header("content-type:text/html;charset=utf-8");
try{
$pdo = new PDO('mysql:host=localhost;dbname=article','root','');
$sql = "select * from pdo_user where id = 1";
$stmt = $pdo->prepare($sql); // 執(zhí)行失敗返回 false 成功 PDOStatement對象
$res = $stmt->execute();// 成功返回 true 失敗返回 false
if($res){
$row = $stmt->fetch(); // 索引加關聯(lián)
print_r($row);
}
}catch (PDOException $e){
$e->getMessage();
}
6、getAttribute() ==== setAttribute()
$pdo->getAttribute(PDO:ATTR_AUTOCOMMIT); // 自動提交
常用屬性
AUTOCOMMIT 自動提交
ERRMODE 錯誤處理模式
CASE 字段名稱是否大小寫
PERSISTENT 是否持久連接
TIMEOUT 超時設置
ORACLE_NULLS 返回空字符串返回sql的null
SERVER_INFO 錯誤信息
SERVER_VERSION 服務端版本
CLIENT_VAERSION 客戶端版本
CONNECTION_STATUS 連接信息
7藕坯、quote() 返回待引號的字符串团南，過濾字符串中的特殊字符,防止sql注入
// ' or 1=1 #
$username = $pdo->quote($username);
// ' \' or 1=1 #'
$sql="select * from user where username = ${username} and password = '${password}'";
8噪沙、PDOStatement對象的方法:rouCount() : 對于select操作返回的結果集中記錄的條數(shù)，對于Insert吐根、Update正歼、Delete返回受影響的記錄的條數(shù)
9、預處理
用命名方式做占位符
$sql = "select * from user where username=:username and password=:password";
$stmt=$pdo->prepare($sql);
$stmt->execute(array(":username"=>$username,":password"=>$password));
用?做占位符
$sql = "select * from user where username = ? and password= ? ";
$stmt=$pdo->prepare($sql);
$stmt->execute(array($username,$password));
10拷橘、bindParam()綁定參數(shù)形式
用命名方式做占位符
$sql = "select * from user where username=:username and password =:password";
$stmt=$pdo->prepare($sql);
$stmt->bindParam(":username",$username,PDO::PARAM_STR):
$stmt->bindParam(":password",$password,PDO::PARAM_STR):
$stmt->execute();
用?做占位符
$sql = "select * from user where username = ? and password= ?;";
$stmt=$pdo->prepare($sql);
$stmt->bindParam(1,$username):
$stmt->bindParam(2,$password):
$stmt->execute();
11局义、bindValue()
用?做占位符
$sql = "select * from user where username = ? and password= ?;";
$stmt=$pdo->prepare($sql);
$stmt->bindValue(1,$username):
$stmt->bindValue(2,$password):
$stmt->execute();
用命名方式做占位符
$sql = "select * from user where username=:username and password =:password";
$stmt=$pdo->prepare($sql);
$stmt->bindValue(":username",$username):
$stmt->bindValue(":password",$password):
$stmt->execute();
12、bindColumn()
$sql = "select username,password from user ;";
$stmt=$pdo->prepare($sql);
$stmt->execute();
$stmt->bindColumn(1,$username):
$stmt->bindColumn(2,$password):
while($stmt->fetch(PDO::FETCH_BOUND)){
echo '用戶名:'.$username;
echo '密碼:'.$password;
}
13膜楷、columnCount() 返回結果集中的列數(shù)
$sql = "select username,password from user ;";
$stmt=$pdo->prepare($sql);
$stmt->execute();
echo '結果集中的列數(shù)：'.$pdo->columnCount();
14旭咽、錯誤處理模式
PDO::ERRMODE_SLIENT :默認模式，靜默模式
PDO::ERRMODE_WARNING : 警告模式
PDO::ERRMODE_EXCEPTION:異常模式
15赌厅、事務
image.png
try{
$options=array(PDO::ATTR_AUTOCOMMIT,0)穷绵； // 關閉自動提交
$pdo = new PDO('mysql:host=localhost;dbname=article','root','',$options);
// 開啟事務
$pdo->beginTransaction();
$sql = "update userAccount set money=money-2000 where username = 'imooc' ;";
$res1 = $pdo->exec($sql);
if($res1 == 0 ){
throw new PDOException('imooc 轉賬失敗');
}
$res2 = $pdo->exec("update userAccount set money = money + 2000 where username = 'king'");
if($res2 == 0) {
throw new PDOExcetpion('king 接收失敗');
}
$pdo->commit();
}catch(PDOException $e){
// 失敗回滾
$pdo->rollback();
$e->getMessage();
}
