一. PDO 操作
- 采用预处理操作(PDOStatment类,prepare()方法)
-
采用命令占位符
PHP使用PDO操作数据库步骤
-
连接数据库,创建PDO对象
$pdo = new($dsn,$user,$pass);
-
执行预处理方法,创建预处理对象
$stmt = $pdo->prepare($sql);
-
执行查询
$stmt->execute();
-
解析结果集
$stmt->fetchAll();
-
遍历结果集
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;
}
版权声明:本文为qq_38626043原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。