使用PDO进行数据库操作

数据库操作 飞快学 300浏览

PDO 是 PHP Data Objects 的简称,提供一个通用接口访问多种数据库,即抽象的数据模型支持连接多种数据库。有了 PDO 是代码变得更简洁、更安全。

PDO 具备三大特性:1)支持多种数据库;2)参数绑定防止注入攻击;3)事务处理。

使用 db_config.php 文件来保存数据库配置信息。

<?php
 
$db_host = 'localhost';
$db_name = 'sakila';
$db_user = 'root';
$db_pass = 'xxxxx';
 
?>

【最简单的CRUD操作】

R: Retrieve 读取

<?php
require_once 'db_config.php';
 
try {
    $dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
    $db = new PDO($dsn, $db_user, $db_pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    $sql = "select * from film";
    $result = $db->query($sql);
    printf("Find %d rows<br/>\n",  $result->rowCount());
    foreach($result->FetchAll() as $row) 
        printf("%s<br/>\n", $row['title']);
}  catch (PDOException $err) {
    echo $err->getMessage();
}
?>

C:Create/Insert 插入

<?php
require_once 'db_config.php';
 
try {
    $dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
    $db = new PDO($dsn, $db_user, $db_pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // C: Create    
    $sql = "INSERT INTO `sakila`.`film` (`film_id`, `title`, `description`, 
      `release_year`, `language_id`, `original_language_id`, `rental_duration`, 
      `rental_rate`, `length`, `replacement_cost`, `rating`, 
      `special_features`, `last_update`) 
     VALUES (NULL, 'Big Times', 'Good', '2015', '2', '2', '3', 
     '4.99', '60', '19.99', 'G', 'Commentaries', CURRENT_TIMESTAMP);";
    $count = $db->exec($sql);
    $id = $db->lastInsertId();
    printf("Insert: affected rows: %d <br/>\n", $count);   
    printf("The new record's id is %d <br/>\n", $id);        
}  catch (PDOException $err) {
    echo $err->getMessage();
}   
?>

D: Delete 删除

<?php
require_once 'db_config.php';
 
try {
    $dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
    $db = new PDO($dsn, $db_user, $db_pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "DELETE FROM `film` WHERE `release_year` = 2015";
    $count = $db->exec($sql);
    printf("Delete: affected rows: %d <br/>\n", $count);       
}  catch (PDOException $err) {
    echo $err->getMessage();
}   
?>

U:Update 更新

<?php
require_once 'db_config.php';
 
try {
    $dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
    $db = new PDO($dsn, $db_user, $db_pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    $sql = "UPDATE `film` SET `release_year` = '2013' WHERE `film_id` = 1000;";
    $count = $db->exec($sql);
    printf("Update: affected rows: %d <br/>\n", $count);          
}  catch (PDOException $err) {
    echo $err->getMessage();
}   
?>

【 PDO 参数绑定 bindParam 和 bindValue 】

<?php
require_once 'db_config.php';
 
try {
    $dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
    $db = new PDO($dsn, $db_user, $db_pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    $statement = $db->prepare('SELECT * FROM film WHERE `length`=? AND `rating`=?');
    $length = 46;
    $rating = 'PG-13';
    $statement->bindParam(1, $length, PDO::PARAM_INT);
    $statement->bindParam(2, $rating, PDO::PARAM_STR, 20);
    $statement->execute();
    foreach($statement->FetchAll(PDO::FETCH_OBJ) as $row) 
        printf("%s , %s<br/>\n", $row->title, $row->rating);      
}  catch (PDOException $err) {
    echo $err->getMessage();
}   
?>

还可以使用下面的更为简洁的方式:

    $statement = $db->prepare('SELECT * FROM film WHERE `length`=? AND `rating`=?');
    $length = 46;
    $rating = 'PG-13';
    $statement->execute(array($length, $rating));

这个例子中使用的是绑定到变量(bindParam),如果是需要绑定到值(Value),就使用方法 bindValue。

之所以要使用参数绑定,是为了防止SQL注入攻击。

这段代码中另外一个值得注意的地方是获取采用 PDO::FETCH_OBJ 方式,对应的变化是 $row[‘title’] 改成了 $row->title。

【权威参考】

官方参考:http://php.net/manual/en/book.pdo.php
PDO Tutorial for MySQL Developers