PDO数据库操作
连接数据库
require 'connect.php';
connect.php
<?php
namespace pdo_edu;
$config = require 'database.php';
use PDO;
extract($config);
$dsn = sprintf('%s:host=%s;dbname=%s',$type,$host,$dbname);
try {
$pdo = new PDO($dsn,$username ,$password,[PDO::ATTR_ERRMODE=> PDO::ERRMODE_WARNING]);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
} catch(\Exception $e) {
die('Connection error: '. $e->getMessage());
}
database.php
<?php
namespace pdo_edu;
return [
'type' => $type ?? 'mysql',
'host' => $host ?? 'localhost',
'dbname' => $dbname ?? 'study',
'port' => $port ?? '3306',
'charset' => $charset ?? 'utf8',
'username' => $username ?? 'root',
'password' => $password ?? '123456'
];
PDO查询数据:
方法一:
$sql = "SELECT * FROM `order` WHERE `id`=1";
$stmt = $pdo->query($sql);
foreach($stmt as $row)
{
var_dump($row);
}
// 结果:array(4) { ["id"]=> string(1) "1" ["name"]=> string(6) "张大" ["pro"]=> string(12) "旧房装修" ["price"]=> string(5) "10.00" }
方法二:防sql注入
// PDO::prepare — 准备要执行的SQL语句并返回一个 PDOStatement 对象
// PDOStatement::execute — 执行一条预处理语句
$sql = "SELECT `id`,`name`,`pro` FROM `order` WHERE `id`>=?;";
$stmt = $pdo->prepare($sql);
$stmt->execute([1]);
// var_dump($stmt->fetch());
while ($order = $stmt->fetch())
{
// vprintf() 函数输出格式化的字符串
vprintf('<li>%s:%s | %s</li>',$order);
}
// 结果:
// 1:张大 | 旧房装修
// 2:小二 | 局部改造
// 3:张三 | 防水补漏
// 4:李四 | 墙面翻新
// 5:小伍 | 水电维修
方法三:防sql注入
$sql = "SELECT `id`,`name`,`pro` FROM `order` WHERE `id`>=?;";
$stmt = $pdo->prepare($sql);
$stmt->execute([3]);
$orders = $stmt->fetchAll();
foreach($orders as $order)
{
vprintf('<li>%s:%s | %s</li>',$order);
}
// 结果:
// 3:张三 | 防水补漏
// 4:李四 | 墙面翻新
// 5:小伍 | 水电维修
2、PDO更新数据
方法一:
$sql = "UPDATE `order` SET `price` = 60 WHERE `id` = 3 ";
$res = $pdo->exec($sql);
var_dump($res);//int(1)
if($res == 0 )
{
var_dump($pdo->errorInfo());
}
方法二:
$sql = 'UPDATE `order` SET `name` = ?,`pro`=?,`price`=? WHERE `id`=?;';
$stmt = $pdo->prepare($sql);
// 将值直接绑定到占位符上
$stmt->execute(['大马猴','门窗维修',30,5]);
if($stmt->rowCount() > 0) echo '更新成功' . $stmt->rowCount() . '条记录';//更新成功1条记录
3、PDO插入操作
$sql = 'INSERT `order` SET `name`=?,`pro`=?,`price`=?;';
$stmt = $pdo->prepare($sql);
// PDOStatement::bindParam — 绑定一个参数到指定的变量名
$stmt->bindParam(1,$name);
$stmt->bindParam(2,$pro);
$stmt->bindParam(3,$price);
$name= '东方不败';
$pro = '马桶疏通';
$price = 50.25;
$stmt ->execute();
$stmt->rowCount();
if($stmt->rowCount() > 0) echo '新增成功' . $stmt->rowCount() . '条记录,主键id:' . $pdo->lastInsertId();
// 执行结果:新增成功1条记录,主键id:6
4、PDO删除操作
$sql = 'DELETE FROM `order` WHERE `id`= ?;';
$stmt = $pdo->prepare($sql);
$stmt->execute([3]);
if($stmt->rowCount() > 0) echo '删除成功' . $stmt->rowCount() . '条记录';
// 执行结果:删除成功1条记录
关闭数据连接
$pdo = null;