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','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() 返回上一次操作的 错误信息

返回的错误信息的数组，数组中包含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); // 执行失败返回 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();
}