![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
天蓬老师Correction status：qualified
Teacher's comments：非常棒
Collection
Collection
<?php// 导入配置参数，数组$config = require __DIR__ . '/../config.php';// 关联数组 转为 独立变量extract($config);$mysqli = new mysqli($host, $username, $password, $dbname);// var_dump($mysqli);// 检测错误if ($mysqli->connect_errno) die('Connect Error: ' . $mysqli->connect_error);// 字符编码$mysqli->set_charset($charset);
<?php// 1.连接require 'connect.php';// 2.操作// sql语句，?：匿名占位符$sql = 'INSERT `users` SET `name`=?, `email`=? , `password`=?;';// 第一步: 将sql语句转为sql语句对象: stmt对象,预处理对象, 预编译语句对象$stmt = $mysqli->prepare($sql);// 占位符? 绑定变量// 给占位符绑定一个变量名(变量标识符)$stmt->bind_param('sss', $name, $email, $password);// 一次性插入多条数据$users = [['name' => '小燕子', 'email' => 'xyz@php.cn', 'password' => sha1('123456')],['name' => '紫薇', 'email' => 'zw@php.cn', 'password' => sha1('123456')],['name' => '五阿哥', 'email' => 'wag@php.cn', 'password' => sha1('123456')],['name' => '尔康', 'email' => 'ek@php.cn', 'password' => sha1('123456')],['name' => '金锁', 'email' => 'js@php.cn', 'password' => sha1('123456')],];// user拆分变量，用execute执行foreach ($users as $user) {extract($user);if ($stmt->execute())printf('成功的新增了 %s 条记录, 新增主键ID = %d<br>', $stmt->affected_rows, $stmt->insert_id);elseexit(sprintf('新增失败 , $d: %s', $stmt->errno, $stmt->error));}// 3.关闭$mysqli->close();?>
<?phprequire 'connect.php';$sql = 'DELETE FROM `users` WHERE `id` = ?;';$stmt = $mysqli->prepare($sql);$stmt->bind_param('i', $id);$id = 5;$stmt->execute();printf('删除了 %s 条记录', $stmt->affected_rows);$mysqli->close();
<?php// mysqli的查询操作1：fetch_assoc()+while()// 1.连接require 'connect.php';// 2.操作$sql = 'SELECT * FROM `users` WHERE `id` > ?';// 对象$stmt = $mysqli->prepare($sql);// 绑定$stmt->bind_param('i', $id);$id = 10;// 执行$stmt->execute() or die($stmt->error);// 获取结果$result = $stmt->get_result();// 解析结果集if ($result->num_rows === 0) exit('结果为空');// mysqli的查询操作1：fetch_assoc()+while()// 因为可能有多条数据，重复查询操作，用循环来做,fetch_assoc()遍历while ($user = $result->fetch_assoc()) {vprintf('%d: %s | %s <br>', $user);}// mysqli的查询操作2：fetch_all()+foreach()// fetch_all()遍历二维数组，返回记录集合$user = $result->fetch_all(MYSQLI_ASSOC);foreach ($user as $user) {vprintf('%d: %s ###| %s <br>', $user);}// mysqli的查询操作3：bind_result()+fetch()+while()$stmt->execute() or die($stmt->error);// 方法：bind_result()，字段与变量绑定$stmt->bind_result($id, $name, $email);while ($stmt->fetch()) {printf('%d: %s ***| %s <br>', $id, $name, $email);}// 判断有多少条内容,用以下两行代码$stmt->store_result();if ($stmt->num_rows === 0) exit('没有内容');// 3.释放结果$result->free();// 4.关闭$mysqli->close();
<?php// mysqli的更新操作// 1.连接require 'connect.php';// 2.操作$sql = 'UPDATE `users` SET `name`=?, `email`= ?, `password`=? WHERE `id` = ?;';// 生成sql语句对象$stmt = $mysqli->prepare($sql);// 占位符与变量名绑定sssi$stmt->bind_param('sssi', $name, $email, $password, $id);// 变量赋值，把id为3和5的变量修改$user = ['name' => '小李子', 'email' => 'xlz@php.cn', 'password' => sha1('122'), 'id' => 5];$user = ['name' => '小飞', 'email' => 'xf@php.cn', 'password' => sha1('123'), 'id' => 3];// 展开独立变量，给sql语句中与占位符对应的变量赋值extract($user);// 执行更新$stmt->execute();printf('更新了 %s 条记录', $stmt->affected_rows);// 3.关闭$mysqli->close();
<?php$config = require __DIR__ . '/../config.php';extract($config);try {$dsn = sprintf('%s:host=%s;dbname=%s;charset=%s;port=%s', $type, $host, $dbname, $charset, $port);$pdo = new PDO($dsn, $username, $password);// 设置结果集的默认获取模式: 只关心关联数组部分$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);} catch (PDOException $e) {exit('Connection Error: ' . $e->getMessage());}
<?php// pdo新增// 1.连接require 'connect.php';// 2.操作$sql = 'INSERT `users` SET `name`=?, `email`=? , `password`=?;';// 生成对象$stmt = $pdo->prepare($sql);// 简单方法：给execute()传参来简化执行$stmt->execute(['小芳', 'xf@php.cn', sha1('xf567')]);if ($stmt->rowCount() > 0) echo '新增成功 ' . $stmt->rowCount() . ' 条记录,主键id: ' . $pdo->lastInsertId();// 3.关闭// $pdo = null;unset($pdo);
<?php// 1.连接require 'connect.php';// 2.操作$sql = 'DELETE FROM `users` WHERE `id` = ?;';// 生成sql语句对象$stmt = $pdo->prepare($sql);// 将值直接绑定到点位符？上$stmt->execute([27]);if ($stmt->rowCount() > 0) echo '删除成功 ' . $stmt->rowCount() . ' 条记录';// 3.关闭$pdo->null;
<?php// 1. 连接require 'connect.php';// 2. 操作$sql = 'SELECT `id`,`name`,`email` FROM `users` WHERE `id` >= ?;';$stmt = $pdo->prepare($sql);$stmt->execute([10]);// pdo查询操作1: fetch() + while()while ($user = $stmt->fetch()) {vprintf('<li>%s: %s | %s</li>', $user);}// pdo查询操作2: fetchAll() + foreach()$users = $stmt->fetchAll();foreach ($users as $user) {vprintf('<li>%s: %s ***| %s</li>', $user);}// pdo查询操作3: bindColumn() + fetch() + while（)$stmt->bindColumn('id', $id);$stmt->bindColumn('name', $name);$stmt->bindColumn('email', $email);// 遍历while ($stmt->fetch(PDO::FETCH_BOUND)) {printf('<li>%s: %s ##| %s</li>', $id, $name, $email);}// 获取数量个数$sql = 'SELECT COUNT(`id`) AS `count` FROM `users` WHERE `id` >= ?;';$stmt = $pdo->prepare($sql);// 将值直接绑定到匿名占位符?上面// id大于5的$stmt->execute([5]);$stmt->bindColumn('count', $count);$stmt->fetch(PDO::FETCH_BOUND);echo '满足条件的记录数量' . $count;// 3. 关闭$pdo = null;
<?phprequire 'connect.php';$sql = 'UPDATE `users` SET `name`=?, `email`= ?, `password`=? WHERE `id` = ?;';$stmt = $pdo->prepare($sql);$stmt->execute(['小芳2', 'xf@php.cn', sha1('xf567'), 27]);if ($stmt->rowCount() > 0) echo '更新成功 ' . $stmt->rowCount() . ' 条记录';$pdo->null;