[PHP知识库]PHP开发-PDO操作数据库

一. PDO 操作

1. 采用预处理操作（PDOStatment类，prepare()方法）
2. 采用命令占位符
   

PHP使用PDO操作数据库步骤

1. 连接数据库，创建PDO对象
   $pdo = new($dsn,$user,$pass);
2. 执行预处理方法，创建预处理对象
   $stmt = $pdo->prepare($sql);
3. 执行查询
   $stmt->execute();
4. 解析结果集
   $stmt->fetchAll();
5. 遍历结果集
   foreach()

1.1 POD连接关闭数据库

PDO类

PDO($dsn,$user,$pass)

<?php
/**
 * Dev: wp
 * Date:2022-03-09
 * Source:PhpStorm
 * File:PDO.php
 */

$type = 'mysql';
$host = '127.0.0.1';
$dbname = 'test';
$username = 'root';
$password = '';
$charset = 'utf8';
$port = 3306;

$dsn = "$type:host=$host;dbname=$dbname;charset=$charset;port=$port";
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
if ($pdo){
    echo 'Connected successfully';
    $pdo = null;
}

1.2 操作数据库

主要使用 PDOStatement 类 预处理对象

<?php
/**
 * Dev: wp
 * Date:2022-03-09
 * Source:PhpStorm
 * File:PDO.php
 */

$type = 'mysql';
$host = '127.0.0.1';
$dbname = 'test';
$username = 'root';
$password = '';
$charset = 'utf8';
$port = 3306;

// 连接数据库
$dsn = "$type:host=$host;dbname=$dbname;charset=$charset;port=$port";
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

// 创建预处理对象,:id 为命名参数
$sql = "SELECT id,question FROM question WHERE id > :id";
// prepare 对象是pdo类的方法，用于解析sql语句
$stmt = $pdo->prepare($sql);
// 执行sql语句
// 也可以通过 setFetchMode 设置关联模式
// $stmt->setFetchMode(PDO::FETCH_ASSOC);
if ($stmt->execute(['id' => 310])) {
    // 解析结果集 PDO::FETCH_ASSOC 只获取关联数组
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    print_r('查询失败'.$stmt->errorInfo());
}
// 遍历结果集
foreach ($result as $row) {
    echo $row['id'] . ' ' . $row['question'] . '<br>';
}
echo '查询到'. count($result) . '条记录';

fetch

在 fetch 中，每次执行后 指针会下移，如果不想使用

// 预处理对象
$sql = "SELECT id,question FROM question WHERE id > :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 315]);
// 解析结果集
$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);

1.3 参数绑定与结果集解析

参数绑定

• 参数绑定
  bindParam(':占位符',变量,类型常量).类型常量默认字符串;
bindValue(':点位符',值或变量,类型常量),如果直接传值可忽略类型常量'
execute([':占位符'=>值/变量]):将参数以数组方式与sql语句的占位符绑定;
• 列绑定
  bindColumn('列名或索引',变量,变量类型,最大长度),如果是字符串类型，应该指出最大长度进行分配;

bindParam 只支持变量，而bindValue支持字面量

// 预处理对象
$sql = "SELECT id,question FROM question WHERE id > :id";
$stmt = $pdo->prepare($sql);
// 参数绑定 bindParam
$id_num = 315;
$stmt->bindParam(':id', $id_num , PDO::PARAM_INT);
// 执行sql语句
$stmt->execute();
// 结果集绑定变量
$stmt->bindColumn(1,$id,PDO::PARAM_INT);
$stmt->bindColumn(2,$question,PDO::PARAM_STR,1000);
// 结果集绑定
while ($stmt->fetch(PDO::FETCH_BOUND)){
//    echo $id." ".$question."<br/>";
//    转换位数组
    $rows[] = compact('id','question');
}
// 遍历数组
foreach ($rows as $row){
    echo $row['id']." ".$row['question']."<br/>";
}

1.4 PDO 增

• rowCound()方法：返回受影响的记录数量
• errorinfo()方法：返回出错格式（数组格式）

// PDO对象
$dsn = "$type:host=$host;dbname=$dbname;charset=$charset;port=$port";
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
// 预处理对象
$sql = "insert into test values(id,:name)";
$stmt = $pdo->prepare($sql);
// 参数绑定
$name = 'wpsec';
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
if ($stmt->execute()){
    if ($stmt->rowCount()){
        echo '插入成功';
    }
}else{
    print_r($stmt->errorInfo() . '插入失败');
    die;
}

1.5 PDO 删

// PDO对象
$dsn = "$type:host=$host;dbname=$dbname;charset=$charset;port=$port";
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
// 预处理对象
$sql = "delete from test.test where id = :id";
$stmt = $pdo->prepare($sql);
// 参数绑定
$id = 6;
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
if ($stmt->execute()){
    if ($stmt->rowCount()){
        echo '删除成功';
    }
}else{
    print_r($stmt->errorInfo() . '删除失败');
    die;
}

1.5 PDO 改

// PDO对象
$dsn = "$type:host=$host;dbname=$dbname;charset=$charset;port=$port";
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
// 预处理对象
$sql = "update test.test set name = :name where id = :id";
$stmt = $pdo->prepare($sql);
// 参数绑定
$name = 'wp';
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':id',2, PDO::PARAM_INT);
if ($stmt->execute()){
    if ($stmt->rowCount()){
        echo '更新成功';
    }
}else{
    print_r($stmt->errorInfo() . '更新失败');
    die;
}