博客列表 >PHP MySQLi/接口与多态------PHP培训十期线上班 学号:510251 02月11日作业

PHP MySQLi/接口与多态------PHP培训十期线上班 学号:510251 02月11日作业

赵大叔
赵大叔原创
2020年03月03日 06:57:581243浏览

MySQLi/接口与多态

1、MySQLi常见的CURD操作

1.1 新增

  1. <?php
  2. namespace chapter2;
  3. use mysqli;
  4. //MySQLi 面向对象方式操作数据库
  5. //新增操作
  6. //1、连接数据库
  7. //mysqli(主机名,用户名,密码,默认数据库);
  8. $mysqli = new mysqli('localhost', 'root', 'root', 'phpedu');
  9. //2、执行SQL语句查询
  10. //准备SQL语句
  11. $sql = 'INSERT `users` SET `name` = ?, `email` = ?, `password` = ?, `register_time` = ?';
  12. //作用预处理方式,防止SQL攻击
  13. //初始化一个预处理对象
  14. $stmt = $mysqli -> stmt_init();
  15. //创建sql语句预处理对象
  16. $stmt ->prepare($sql);
  17. //绑定变量参数
  18. $user = ['张三丰', 'zhangsanfeng@163.cn', sha1('123456'), time()];
  19. list($name, $email, $password, $register_time) = $user;
  20. $stmt -> bind_param('sssi', $name, $email, $password, $register_time);
  21. //执行
  22. $stmt -> execute();
  23. //3、处理执行有结果
  24. if ($stmt->affected_rows === 1) {
  25. echo '添加成功,新记录主键id:' .$stmt -> insert_id ;
  26. }else {
  27. echo '更新失败' .$stmt -> error;
  28. }
  29. //4、关闭操作
  30. //关闭数据库连接
  31. $stmt -> close();

代码执行效果:


1.2 更新

这里只上传关键代码。

  1. //2、执行SQL语句操作
  2. //2.1 准备sql语句
  3. $sql = 'UPDATE `users` SET `name`= ?, `email` = ? WHERE `id` = ?';
  4. //初始化一个预处理对象
  5. $stmt = $mysqli -> stmt_init();
  6. //创建一个SQL预处理对象
  7. $stmt -> prepare($sql);
  8. //绑定变量参数
  9. $user = ['张无忌', 'wuji@163.com', 9];
  10. list($name, $email, $id) = $user;
  11. $stmt -> bind_param('ssi', $name, $email, $id);
  12. //执行
  13. $stmt -> execute();
  14. //3、处理执行结果
  15. if ($stmt->affected_rows === 1) :
  16. echo '更新成功';
  17. else :
  18. echo '更新失败' . $stmt->error;
  19. endif;

代码执行效果:


1.3 删除

  1. //2、
  2. $sql = 'DELETE FROM `users` WHERE `id` = ?';
  3. $stmt = $mysqli -> stmt_init();
  4. $stmt -> prepare($sql);
  5. $id = 9;
  6. $stmt -> bind_param('i', $id);
  7. $stmt -> execute();
  8. //3、
  9. if($stmt -> affected_rows === 1){
  10. echo '删除成功。';
  11. }else{
  12. echo '删除失败。';
  13. }

代码执行效果:


1.4 查询

  1. //2、
  2. $sql = 'SELECT `id`, `name`, `email` FROM `users` WHERE `id` > ?;';
  3. $stmt = $mysqli ->stmt_init();
  4. $stmt ->prepare($sql);
  5. $id = 2;
  6. $stmt ->bind_param('i', $id);
  7. $stmt ->execute();
  8. //3、
  9. $stmt->bind_result($id, $name, $email);
  10. while ($stmt ->fetch()) {
  11. echo "$id : $name --->$email <br>";
  12. }
  13. //4、
  14. //释放结果集
  15. $stmt -> free_result();
  16. $stmt -> close();

代码执行效果:


2、面向接口实现多态操作数据库

2.1 配置数据库连接参数和声明主要接口

  1. <?php
  2. // 接口实战:使用PDO/MySQLi实现数据库的多想操作
  3. namespace chapter2;
  4. //配置数据库的连接参数
  5. interface iDbParam
  6. {
  7. const HOST = 'localhost';
  8. const TYPE = 'mysql';
  9. const DBNAME = 'phpedu';
  10. const USER_NAME = 'root';
  11. const PASSWORD = 'root';
  12. const CHARSET = 'utf8';
  13. const PORT = '3306';
  14. }
  15. //接口构造方法
  16. //接口构造方法
  17. interface iDblink
  18. {
  19. //接口允许的构造方法
  20. public function __construct(...$linkParams);
  21. }
  22. //接口方法:后面的代码就是用下面的这个主接口
  23. //接口之间允许继承,而且允许多重继承
  24. interface iCURD extends iDbParam,iDblink
  25. {
  26. //新增
  27. public function insert (array $data);
  28. //查询
  29. public function select (string $where = '');
  30. //更新
  31. public function update (array $data, string $where);
  32. //删除
  33. public function delete (string $where);
  34. }

2.2 PDO来实现数据操作(实现接口方法)

  1. <?php
  2. namespace chapter2;
  3. //用PDO来实现数据操作
  4. use PDO;
  5. //加载iCURD 的接口声明
  6. require 'demo7.php';
  7. class Db_PDO implements iCURD
  8. {
  9. //连接对象
  10. private $pdo = null;
  11. //实现接口构造方法:连接数据库
  12. public function __construct(...$linkParams)
  13. {
  14. list($dsn, $username, $password) = $linkParams;
  15. $this -> pdo = new PDO ($dsn, $username, $password);
  16. }
  17. //新增
  18. public function insert (array $data)
  19. {
  20. $sql = "INSERT `users` SET `name` = ?, `email` = ?, `password` = ?, `register_time` = ?";
  21. $stmt = $this -> pdo ->prepare($sql);
  22. $stmt -> execute($data);
  23. return $stmt -> rowCount() === 1 ? '新增成功。' : '新增失败。';
  24. }
  25. //查询
  26. public function select (string $where = '')
  27. {
  28. $where = empty($where) ? $where : 'WHERE' .$where;
  29. $sql = "SELECE * FROM `user` {$where}";
  30. $stmt = $this -> pdo -> prepare($sql);
  31. $stmt -> execute();
  32. return $stmt -> fetchALL(PDO::FETCH_ASSOC);
  33. }
  34. //更新
  35. public function update (array $data, string $where)
  36. {
  37. //设置更新参数
  38. $params = '';
  39. foreach ($data as $key => $value) {
  40. $params .= "`{$key}` = `{$value}`, ";
  41. }
  42. //将最后的“,”去掉
  43. $params = rtrim($params, ', ');
  44. //执行更新
  45. $sql = "UPDATE `users` SET {$params} WHERE {$where}";
  46. $stmt = $this -> pdo -> prepare($sql);
  47. $stmt -> execute;
  48. return $stmt -> rowCount() === 1 ? '更新成功' : '更新失败';
  49. }
  50. //删除
  51. public function delete (string $where)
  52. {
  53. $sql = "DELETE FROM `users` WHERE {$where}";
  54. $stmt = $this -> pdo -> prepare($sql);
  55. $stmt -> execute();
  56. return $stmt -> rowCount() === 1 ? '删除成功' : '删除失败';
  57. }
  58. }

2.3 MySQLi来实现数据操作(实现接口方法)

  1. <?php
  2. namespace chapter2;
  3. //用MySQLi来实现数据操作
  4. use MySQLi;
  5. //加载iCURD 的接口声明
  6. require 'demo7.php';
  7. class Db_MySQLi implements iCURD
  8. {
  9. //连接对象
  10. private $mysqli = null;
  11. //实现接口构造方法:连接数据库
  12. public function __construct(...$linkParams)
  13. {
  14. list($host, $username, $password, $dbname) = $linkParams;
  15. $this -> mysqli = new mysqli ($host, $username, $password, $dbname);
  16. // 设置默认字符集
  17. $this -> mysqli -> set_charset('utf8');
  18. }
  19. //新增
  20. public function insert (array $data)
  21. {
  22. $sql = "INSERT `users` SET `name` = ?, `email` = ?, `password` = ?, `register_time` = ?";
  23. $stmt = $this -> mysqli ->prepare($sql);
  24. $stmt -> bind_param('sssi', $name, $email, $password, $register_time);
  25. list($name, $email, $password, $register_time) = $data;
  26. $stmt -> execute();
  27. return $stmt -> affected_rows === 1 ? '新增成功。' : '新增失败。';
  28. }
  29. //查询
  30. public function select (string $where = '')
  31. {
  32. $where = empty($where) ? $where : 'WHERE' .$where;
  33. $sql = "SELECE * FROM `user` {$where}";
  34. $stmt = $this -> mysqli -> prepare($sql);
  35. $stmt -> execute();
  36. return $stmt -> get_result();
  37. }
  38. //更新
  39. public function update (array $data, string $where)
  40. {
  41. //设置更新参数
  42. $params = '';
  43. foreach ($data as $key => $value) {
  44. $params .= "`{$key}` = `{$value}`, ";
  45. }
  46. //将最后的“,”去掉
  47. $params = rtrim($params, ', ');
  48. //执行更新
  49. $sql = "UPDATE `users` SET {$params} WHERE {$where}";
  50. $stmt = $this -> mysqli -> prepare($sql);
  51. $stmt -> execute();
  52. return $stmt -> affected_rows === 1 ? '更新成功' : '更新失败';
  53. }
  54. //删除
  55. public function felete (string $where)
  56. {
  57. $sql = "DELETE FROM `users` WHERE {$where}";
  58. $stmt = $this -> mysqli -> prepare($sql);
  59. $stmt -> execute();
  60. return $stmt -> affected_row === 1 ? '删除成功' : '删除失败';
  61. }
  62. }

2.4 通用类:面向接口实现多态,动态支持PDO/MySQLi

  1. <?php
  2. namespace chapter2;
  3. //通用类:数据库操作,面向接口实现多态,动态支持PDO/MySQLi
  4. use chapter2\iCURD;
  5. class DB
  6. {
  7. //新增
  8. public static function insert(iCURD $db, array $data)
  9. {
  10. return $db->insert($data);
  11. }
  12. //查询
  13. public static function select(iCURD $db, string $where = '')
  14. {
  15. return $db->select($where);
  16. }
  17. //更新
  18. public static function update(iCURD $db, array $data, string $where = '')
  19. {
  20. return $db->update($data, $where);
  21. }
  22. //删除
  23. public static function delete(iCURD $db, string $where = '')
  24. {
  25. return $db->delete($where);
  26. }
  27. }

2.5 演示PDO操作

  1. <?php
  2. namespace chapter2;
  3. //使用PDO来操作数据库,接口实现
  4. //加载PDO操作类:Db_PDO
  5. require 'demo8.php';
  6. //加载数据库通用类
  7. require 'demo10.php';
  8. //创建DSN
  9. $dsn = iDbParam::TYPE .':host=' .iDbParam::HOST .';dbname=' .iDbParam::DBNAME .';charset=' .iDbParam::CHARSET;
  10. //die($dsn);
  11. $link = new Db_PDO($dsn, iDbParam::USER_NAME, iDbParam::PASSWORD);
  12. //测试新增
  13. //echo DB::insert($link, ['guojue', 'guojue@163.cm', sha1('123456'), time()]);
  14. //查询
  15. //foreach (DB::select($link, 'id > 2') as $user){
  16. // echo "{$user['id']} --- {$user['name']} --- {$user['email']} <br>";
  17. //}
  18. //更新
  19. //echo DB::update($link, ['name'=>'赵大叔', 'email'=>'dashuzhao@php.cn'], 'id= 11');
  20. //删除
  21. echo DB::delete($link, 'id = 10');

代码执行效果:






2.6 演示MySQLi操作

  1. <?php
  2. namespace chapter2;
  3. //使用MySQLi来操作数据库,接口实现
  4. //加载MySQLi操作类:Db_MySQLi
  5. require 'demo9.php';
  6. //加载数据库通用类
  7. require 'demo10.php';
  8. //die($dsn);
  9. $link = new Db_MySQLi(iDbParam::HOST, iDbParam::USER_NAME, iDbParam::PASSWORD, iDbParam::DBNAME);
  10. //测试新增
  11. //echo DB::insert($link, ['linmeimei', 'linmeimei@php.cn', sha1('123456'), time()]);
  12. //查询
  13. //foreach (DB::select($link, 'id > 2') as $user){
  14. // echo "{$user['id']} --- {$user['name']} --- {$user['email']} <br>";
  15. //}
  16. //更新
  17. //echo DB::update($link, ['name'=>'赵大叔', 'email'=>'dashuzhao@php.cn'], 'id= 13');
  18. //删除
  19. echo DB::delete($link, 'id = 12');

代码执行效果:






3、MySQLi常用类/属性/方法

声明:本文内容转载自脚本之家,由网友自发贡献,版权归原作者所有,如您发现涉嫌抄袭侵权,请联系admin@php.cn 核实处理。
全部评论
文明上网理性发言,请遵守新闻评论服务协议