![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
PHPzCorrection status：qualified
Teacher's comments：
Collection
Collection
<?phpnamespace pdo_edu;// use PDO as PDO; //如果别名相同可以不写as后面的use PDO;$dbConfig = require 'database.php';extract($dbConfig);// 1. dsn$tpl = '%s:host=%s;dbname=%s;port=%s;charset=%s';$args = [$type, $host, $dbname, $port, $charset];$dsn = sprintf($tpl, ...$args);// echo $dsn;// 2. 创建数据对象$db = new PDO($dsn, $username, $password);// 设置结果集的默认获取模式：只要关联部分$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
//关闭连接可以不写,因为长时间不操作数据库会自动断开连接$db = null;unset($db);
为什么要PDO预处理
预处理过程
SQL语句推荐规范
单条查询 fetch()
SELECT 字段列表 FROM 表名 WHERE 查询条件创建SQL语句模板对象
$sql = 'SELECT `id`,`name` FROM `staff` WHERE `id` > :id';$stmt = $db->prepare($sql);
执行SQL语句
$stmt->execute(['id' => 10]);//单条查询,拿到查询结果$staff = $stmt->fetch(PDO::FETCH_ASSOC);printf('<pre>%s</pre>', print_r($staff, true));$staff = $stmt->fetch(PDO::FETCH_ASSOC);printf('<pre>%s</pre>', print_r($staff, true));//一条一条获取太麻烦了,用while来全部获取while ($staff = $stmt->fetch()) {printf('<pre>%s</pre>', print_r($staff, true));}
多条查询 fetchAll()
执行SQL语句
$stmt->execute(['id' => 10]);// fetchAll: 返回全部满足条件的记录集合，二维数组$staffs = $stmt->fetchAll();// print_r($staffs);foreach ($staffs as $staff) {printf('<pre>%s</pre>', print_r($staff, true));}
insert(插入/增加)
INSERT 表名 SET 字段1=值1, 字段2=值2, ....创建SQL语句模板对象
PDO::prepare() — 准备要执行的语句，并返回语句对象
$sql = 'INSERT `staff` SET `name` = ?, `sex` = ?, `email` = ?';$stmt = $db->prepare($sql);
执行SQL语句(可以执行多条)
PDOStatement::execute() — 执行一条预处理语句
$stmt->execute(['小龙女', 1, 'xiaolongnv@php.cn']);$stmt->execute(['洪七公', 0, 'hongqigong@php.cn']);$stmt->execute(['黄蓉', 0, 'huangrong@php.cn']);
完整代码
// 1. 连接数据库require __DIR__ . '/config/connect.php';// 1. 创建SQL语句模板对象$sql = 'INSERT `staff` SET `name` = ?, `sex` = ?, `email` = ?';$stmt = $db->prepare($sql);// 2. 执行SQL语句$stmt->execute(['小龙女', 0, 'xiaolongnv@php.cn']);$stmt->execute(['洪七公', 1, 'hongqigong@php.cn']);$stmt->execute(['黄蓉', 0, 'huangrong@php.cn']);// 成功// $stmt->rowCount(): 返回受影响的记录数量if ($stmt->rowCount() > 0) {echo '新增成功, 新增记录的最新一条主键ID = ' . $db->lastInsertId();} else {echo '新增失败';print_r($stmt->errorInfo());}
update(更新),一定要有条件
UPDATE 表名 SET 字段1=值1 ... WHERE 更新条件创建SQL语句模板对象
$sql = 'UPDATE `staff` SET `name` = ? WHERE `id` = ?';$stmt = $db->prepare($sql);
$stmt->execute(['老顽童', 6]);完整代码
// 1. 连接数据库require __DIR__ . '/config/connect.php';// 2. CURD: UPDATE 更新// UPDATE 表名 SET 字段1=值1 ... WHERE 更新条件$sql = 'UPDATE `staff` SET `name` = ? WHERE `id` = ?';$stmt = $db->prepare($sql);$stmt->execute(['老顽童', 6]);if ($stmt->rowCount() > 0) {echo '更新成功';} else {echo '更新失败';print_r($stmt->errorInfo());}
delete(删除)
DELETE FROM 表名 SET 字段1=值1 ... WHERE 更新条件创建SQL语句模板对象
//:id 命名占位符,比较灵活摆脱了顺序,格式 :名称//下面执行的时候,也要用同样的命名占位符$sql = 'DELETE FROM `staff` WHERE `id` = :id';$stmt = $db->prepare($sql);
$stmt->execute([':id' => 5]);完整代码
// 1. 连接数据库require __DIR__ . '/config/connect.php';// 2. CURD: DELETE 删除// DELETE FROM 表名 SET 字段1=值1 ... WHERE 更新条件// $sql = 'DELETE FROM `staff` WHERE `id` = ? ';// '?' : 匿名占位符// 'string': 命名占位符$sql = 'DELETE FROM `staff` WHERE `id` = :id';$stmt = $db->prepare($sql);// 如果条件来自外部， 例如 url 中 get 参数// echo $_GET['id'];//:id 的:可选 也可以不写//$stmt->execute([':id' => $_GET['id']]);$stmt->execute(['id' => $_GET['id']]);if ($stmt->rowCount() > 0) {echo 'id = ' . $_GET['id'] . ' 删除成功';} else {echo '删除失败';print_r($stmt->errorInfo());}