php pdo簡書,PDO學習

PDO(PHP Data Object)，資料庫通路抽象層，統一各種資料庫的通路接口。

image.png

一、PDO連接配接資料庫

1、通過參數形式連接配接資料庫

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的形式連接配接資料庫

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、通過配置檔案連接配接資料庫

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() 可以實作一次插入多條語句

try{

$dsn = 'mysql:host=localhost;dbname=article';

$username = 'root';

$password = '';

$pdo = new PDO($dsn,$username,$password);

// exec();執行一條sql語句并傳回其受影響的記錄的條數,如果沒有受影響的記錄，傳回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','[email protected]')";

$res = $pdo->exec($sql);

var_dump($res); // int 1

}catch (PDOException $e){

echo $e->getMessage();

}

2、lastInsertiId() 獲得最後插入的Id号

3、errorCode() 傳回上一次操作的SQLSTATE

4、errorInfo() 傳回上一次操作的 錯誤資訊

傳回的錯誤資訊的數組，數組中包含3個單元

0=>SQLSTATE,1=>CODE,2=>INFO

5、query() 傳回一個PDOStatement對象

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); // 執行失敗傳回 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); // 執行失敗傳回 false 成功 PDOStatement對象

$res = $stmt->execute();// 成功傳回 true 失敗傳回 false

if($res){

$row = $stmt->fetch(); // 索引加關聯

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操作傳回的結果集中記錄的條數，對于Insert、Update、Delete傳回受影響的記錄的條數

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()綁定參數形式

用命名方式做占位符

$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() 傳回結果集中的列數

$sql = "select username,password from user ;";

$stmt=$pdo->prepare($sql);

$stmt->execute();

echo '結果集中的列數：'.$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();

}