php之pdo知识梳理

       一，防sql注入

      二，pdo  错误   errorCode  与 errorInfo

      三，pdo 视图

一，防sql注入

1，bindParam(1, $username, PDO::PARAM_STR);

try {
    $pdo = new PDO("mysql:host=localhost;dbname=dm", "root", "");
    $sql = "INSERT users(username,passwd,email) VALUES(?,?,?)";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(1, $username, PDO::PARAM_STR);
    $stmt->bindParam(2, $passwd, PDO::PARAM_STR);
    $stmt->bindParam(3, $email, PDO::PARAM_STR);
    $username = "testname";
    $passwd = "testpassword";
    $email = "testname@qq.com";
    $stmt->execute();
    // 打印一条 SQL 预处理命令(PHP 5 >= 5.1.0, PECL pdo >= 0.9.0)
    $stmt->debugDumpParams();
} catch (PDOException $e) {
    echo $e->getMessage();
}

2， bindParam(":username", $username, PDO::PARAM_STR);

try {
    $pdo = new PDO("mysql:host=localhost;dbname=dm", "root", "");
    $sql = "SELECT * FROM users WHERE username=:username AND passwd=:passwd";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":username", $username, PDO::PARAM_STR);
    $stmt->bindParam(":passwd", $passwd, PDO::PARAM_STR);
    $username = "testname";
    $passwd = "testpassword";
    $email = "testname@qq.com";
    $stmt->execute();
    // 打印一条 SQL 预处理命令(PHP 5 >= 5.1.0, PECL pdo >= 0.9.0)
    $stmt->debugDumpParams();
} catch (PDOException $e) {
    echo $e->getMessage();
}

 二，pdo  错误   errorCode  与 errorInfo

1,   mysql 视图

$sql =<<<EOF 
DELIMITER //
CREATE PROCEDURE test1()
BEGIN
    SELECT * FROM users;
    SELECT * FROM userAccount;
END
//
DELIMITER;
EOF;

2，$stmt->fetchAll(PDO::FETCH_ASSOC)    与  $stmt->fetch(PDO::FETCH_ASSOC);

/***
 * PDO::ERRMODE_SLIENT : 默认模式，静默模式
 * PDO::ERRMODE_WARNING : 警告模式
 */
try {
    $pdo = new PDO("mysql:host=localhost;dbname=dm", "root", "");
    $sql="call test1()";
    $stmt=$pdo->query($sql);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($rows);
    // --------------------------------------
    $rows = $stmt->fetch(PDO::FETCH_ASSOC);
    print_r($rows);
    // --------------------------------------
    $sql = "SELECT * FROM users";
    $pdo->query($sql);
    echo $pdo->errorCode();
    echo '<br/>';
    print_r($pdo->errorInfo());
}catch (PDOException $e){
    $e->getMessage();
}

三，pdo 事务

/**
 * 方法                      描述
 * beginTransaction()       启动一个事务
 * commit()                 提交一个事务
 * rollBack()               回滚一个事务
 * inTransaction()          检测是否在一个内
 */
try {
    $pdo = new PDO("mysql:host=localhost;dbname=dm", "root", "");

    $options=array(PDO::ATTR_AUTOCOMMIT,0);
    // 开启事务
    $pdo->beginTransaction();
    $sql="UPDATE userAccount SET money=money-2000 WHERE username='admin'";
    $res = $pdo->exec($sql);
    if ($res == 0){
        throw new PDOException('admin 转账失败');
    }
    $res_add = $pdo->exec("UPDATE userAccount SET money=money+2000 WHREE username='king'");
    if ($res_add ==0 ){
        throw new PDOException('king 接收失败');
    }
}catch (PDOException $e){
    $e->getMessage();
}