![[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
MySQLi常见CURD操作直观好理解，直接写代码；接口实现数据库操作理解的时候容易理解错，多看了几遍视频看课件注释帮助理解最后写代码。
接口实战编程完后，测试时遇到蛮多报错，都是自己小失误造成的，比如查询的’WHERE’没有敲空格，导致SQL语句不生效，’ WHERE ‘，这样才对：
// 查询public function select(string $where = ''){$where = empty($where) ? $where : 'WHERE' . $where;$sql = "SELECT * FROM `users` {$where}";$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->fetchAll(PDO::FETCH_ASSOC);}
慢慢改，最后解决了。为了辅助自己理解记忆整理了下他们的关系：
<?phpnamespace chapter02;use mysqli;//一、新增操作//1.连接数据库$mysqli = new mysqli('localhost', 'root', 'root', 'phpedu');//2.执行SQL查询//准备sql语句$sql = 'INSERT `users` SET `name`=?, `email`=?, `password`=?, `register_time`=?;';//使用预处理方式防止sql注入攻击$stmt = $mysqli->stmt_init();//创建sql语句对象$stmt->prepare($sql);//绑定变量参数$user = ['admin', 'admin@php.cn', sha1('123456'), time()];list($name, $email, $password, $register_time) = $user;$stmt->bind_param('sssi', $name, $email, $password, $register_time);//执行$stmt->execute();//3.处理执行的结果if ($stmt->affected_rows === 1) :echo '添加成功，新记录的主键id：' . $stmt->insert_id;else :echo '添加失败' . $stmt->error;endif;//4.结束操作//关闭数据库连接$stmt->close();
<?phpnamespace chapter02;use mysqli;//二、更新操作//1.连接数据库$mysqli = new mysqli('localhost', 'root', 'root', 'phpedu');//2.执行sql查询//准备sql语句$sql = 'UPDATE `users` SET `name`=?, `email`=? WHERE `id`=?;';//使用预处理方式防止sql注入攻击$stmt = $mysqli->stmt_init();//创建sql语句对象$stmt->prepare($sql);//绑定参数变量$user = ['jack', 'jack@php.cn', '8'];list($name, $email, $id) = $user;$stmt->bind_param('ssi', $name, $email, $id);//执行$stmt->execute();//3.处理执行的结果if ($stmt->affected_rows === 1 ) :echo '更新成功';else :echo '更新失败';endif;//4.结束操作//关闭数据库$stmt->close();
<?phpnamespace chapter02;use mysqli;//三、删除操作//1.连接数据库$mysqli = new mysqli('localhost', 'root', 'root', 'phpedu');//2.执行sql查询//准备sql语句$sql = 'DELETE FROM `users` WHERE `id`=?;';//使用预处理方式防止sql注入攻击$stmt = $mysqli->stmt_init();//创建sql语句对象$stmt->prepare($sql);//绑定参数变量$id = '8';$stmt->bind_param('i', $id);//执行$stmt->execute();//3.处理执行结果if ($stmt->affected_rows === 1) :echo '删除成功';else :echo '删除失败';endif;//4.结束操作//关闭数据库连接$stmt->close();
<?phpnamespace chapter02;use mysqli;//四、查询操作—使用STMT对象//1.连接数据库$mysqli = new mysqli('localhost', 'root', 'root', 'phpedu');//2.执行SQL查询//准备sql语句$sql = 'SELECT `id`,`name`,`email` FROM `users` WHERE `id`>?;';//使用预处理方式防止sql注入攻击$stmt = $mysqli->stmt_init();//创建sql语句对象$stmt->prepare($sql);//绑定变量参数$id = 3;$stmt->bind_param('i', $id);//执行$stmt->execute();//3.处理执行的结果//将结果集中的字段/列，绑定到指定的变量上$stmt->bind_result($id, $name, $email);//遍历结果集while ($stmt->fetch()) {echo "$id : $name ---> $email <br>";}//4.结束操作//释放结果集$stmt->free_result();//关闭数据库连接$stmt->close();
<?phpnamespace chapter02;use mysqli;//四、查询操作—使用结果集对象//1.连接数据库$mysqli = new mysqli('localhost', 'root', 'root', 'phpedu');//2.执行SQL查询//准备sql语句$sql = 'SELECT `id`,`name`,`email` FROM `users` WHERE `id`>?;';//使用预处理方式防止sql注入攻击$stmt = $mysqli->stmt_init();//创建sql语句对象$stmt->prepare($sql);//绑定变量参数$id = 3;$stmt->bind_param('i', $id);//执行$stmt->execute();//获取到结果集对象$result = $stmt->get_result();//3.处理执行的结果//获取记录，返回关联数组while ($user = $result->fetch_assoc()) {echo "{$user['id']} : {$user['name']} ==> {$user['email']} <br>";}echo '<hr>';//数据表指针复位$result->data_seek(0);//一次性的获取所有数据$users = $result->fetch_all(MYSQLI_ASSOC);foreach ($users as $user) {echo "{$user['id']} : {$user['name']} ==> {$user['email']} <br>";}//4.结束操作//释放结果集$stmt->free_result();//关闭数据库连接$stmt->close();
<?php# 接口实战: 使用PDO/MySQLi实现数据库的多态操作namespace chapter2;// 配置数据库的连接参数: 接口常量interface iDbParam{const HOST = 'localhost';const TYPE = 'mysql';const DBNAME = 'phpedu';const USER_NAME = 'root';const PASSWORD = 'root';const CHARSET = 'utf8';const PORT = '3306';}// 接口构造方法interface iDbLink{// 接口中允许构造方法public function __construct(...$linkParams);}// 接口方法: 后面的代码就是用下面的这个主接口// 接口之间允许继承, 而且允许多重继承interface iCURD extends iDbLink, iDbParam{// 新增public function insert(array $data);// 查询public function select(string $where = '');// 更新public function update(array $data, string $where);// 删除public function delete(string $where);}
<?phpnamespace chapter2;// 用PDO来实现数据库操作use PDO;// 加载iCURD的接口声明require 'demo7.php';class Db_PDO implements iCURD{// 连接对象private $pdo = null;// 实现接口中的构造方法:连接数据库public function __construct(...$linkParams){list($dsn, $username, $password) = $linkParams;$this->pdo = new PDO($dsn, $username, $password);}// 新增public function insert(array $data){$sql = "INSERT `users` SET `name`=?, `email`=?,`password`=?,`register_time`=?";$stmt = $this->pdo->prepare($sql);$stmt->execute($data);return $stmt->rowCount() === 1 ? '新增成功' : '新增失败';}// 查询public function select(string $where = ''){$where = empty($where) ? $where : ' WHERE ' . $where;$sql = "SELECT * FROM `users` {$where}";$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->fetchAll(PDO::FETCH_ASSOC);}// 更新public function update(array $data, string $where){// 设置更新参数$params = '';foreach ($data as $key => $value) {$params .= "`{$key}` = '{$value}', ";}// 将最后的", "去掉$params = rtrim($params, ', ');// 执行更新$sql = "UPDATE `users` SET {$params} WHERE {$where}";$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->rowCount() === 1 ? '更新成功' : '更新失败';}// 删除public function delete(string $where){$sql = "DELETE FROM `users` WHERE {$where}";$stmt = $this->pdo->prepare($sql);$stmt->execute();return $stmt->rowCount() === 1 ? '删除成功' : '删除失败';}}
<?phpnamespace chapter2;// 用MySQLi来实现数据库操作use mysqli;// 加载iCURD的接口声明require 'demo7.php';class Db_MySQLi implements iCURD{// 连接对象private $mysqli = null;// 实现接口中的构造方法:连接数据库public function __construct(...$linkParams){list($host, $username, $password, $dbname) = $linkParams;$this->mysqli = new mysqli($host, $username, $password, $dbname);// 设置默认字符集$this->mysqli -> set_charset('utf8');}// 新增public function insert(array $data){$sql = "INSERT `users` SET `name`=?, `email`=?,`password`=?,`register_time`=?";$stmt = $this->mysqli->prepare($sql);$stmt->bind_param('sssi', $name, $email, $password, $register_time);list($name, $email, $password, $register_time) = $data;$stmt->execute();return $stmt->affected_rows === 1 ? '新增成功' : '新增失败';}// 查询public function select(string $where = ''){$where = empty($where) ? $where : ' WHERE ' . $where;$sql = "SELECT * FROM `users` {$where}";$stmt = $this->mysqli->prepare($sql);$stmt->execute();return $stmt->get_result();}// 更新public function update(array $data, string $where){// 设置更新参数$params = '';foreach ($data as $key => $value) {$params .= "`{$key}` = '{$value}', ";}// 将最后的", "去掉$params = rtrim($params, ', ');// 执行更新$sql = "UPDATE `users` SET {$params} WHERE {$where}";$stmt = $this->mysqli->prepare($sql);$stmt->execute();return $stmt->affected_rows === 1 ? '更新成功' : '更新失败';}// 删除public function delete(string $where){$sql = "DELETE FROM `users` WHERE {$where}";$stmt = $this->mysqli->prepare($sql);$stmt->execute();return $stmt->affected_rows === 1 ? '删除成功' : '删除失败';}}
<?phpnamespace chapter2;// 通用类: 数据库操作,面向接口实现多态, 动态的支持PDO, MySQLiuse chapter2\iCURD;class DB{// 新增public static function insert(iCURD $db, array $data){return $db->insert($data);}// 查询public static function select(iCURD $db, string $where = ''){return $db->select($where);}// 更新public static function update(iCURD $db, array $data, string $where= ''){return $db->update($data, $where);}// 删除public static function delete(iCURD $db, string $where= ''){return $db->delete($where);}}
<?phpnamespace chapter02;//使用PDO来操作数据库：接口实现// 加载PDO操作类: Db_PDOrequire 'demo8.php';//加载数据库的通用类require 'demo10.php';//先创建DSN$dsn = iDbParam::TYPE . ':host=' . iDbParam::HOST . ';dbname=' . iDbParam::DBNAME . ';charset=' . iDbParam::CAHRSET;$link = new Db_PDO($dsn, iDbParam::USER_NAME, iDbParam::PASSWORD);//var_dump($link);//var_dump($dsn);//测试新增操作echo DB::insert($link, ['欧阳峰', 'rose@php.cn', sha1('123456'), time()]);echo '<hr>';////测试查询foreach (DB::select($link,'id > 2') as $user) {echo "{$user['id']} -- {$user['name']} -- {$user['email']} <br>";}echo '<hr>';//测试更新echo DB::update($link, ['id'=>'9', 'email'=>'ouyang@php.cn'], 'id=26');echo '<hr>';//测试删除echo DB::delete($link, 'id=8');
<?phpnamespace chapter02;//使用MySQLi来操作数据库：接口实现// 加载MySQLi操作类: Db_MySQLirequire 'demo9.php';//加载数据库的通用类require 'demo10.php';//连接实例$link = new Db_MySQLi(iDbParam::HOST, iDbParam::USER_NAME, iDbParam::PASSWORD, iDbParam::DBNAME);//var_dump($link);//var_dump($dsn);//测试新增操作echo DB::insert($link, ['丘处机', 'rose@php.cn', sha1('123456'), time()]);echo '<hr>';//测试查询foreach (DB::select($link,'id > 3') as $user) {echo "{$user['id']} -- {$user['name']} -- {$user['email']} <br>";}echo '<hr>';////测试更新echo DB::update($link, ['name'=>'郭芙蓉', 'email'=>'guofurong@php.cn'], 'id=3');//echo DB::update($link, ['id'=>'8'], 'id=9');echo '<hr>';//测试删除echo DB::delete($link, 'id=2');
天蓬老师