search

博客列表 >MySQLi常见CURD操作+接口

MySQLi常见CURD操作+接口

安
安原创
2020年02月18日 22:51:10880浏览
MySQL常用类方法

__construct($host,$username,$password,$dbname,$port,$socket) 数据库连接
select_db() 默认数据库选择
set_charset() 数据库编码
stmt_init() 初始化一条语句并且返回一个mysqli_stmt类实例 (其实用的时候不写好像也不会出错)
Mysqli_STMT类常用属性/方法
prepare() 预处理一条SQL语句
bind_param() 将变量绑定至prepare中
bind_result() 将变量参数保存到prepared语句,用于结果储存(说人话就是你查了啥,按照顺序绑定到变量方便你到时候fetch)
fetch() 将prepare语句中的结果按照bind_result中所绑定的数据输出
execute() 执行一条prepare预处理语句
get_result() 获取prepare语句中的结果，返回一个结果集对象mysqli_res
free_result() 释放prepare所占的运行内存
affected_rows 返回 增删改 所影响的函数
insert_id 返回上次insert插入的ID
$stmt-error 返回错误语句
close() 关闭查询语句

Mysqli_常用类result

fetch_array()获取mysqli_result对象中的结果集 带有索引数组和关联数组形式(就是这个和下面2个都只拿一条)
fetch_row() 同上仅为索引数组
fetch_assoc() 同上仅为关联数组
fetch_all(MYSQLI_ASSOC/ROW) 获取mysqli_result对象中所有的结果集 带有索引数组和关联数组形式(这个一下子拿全部)
data_seek() 指针复位
free_result() 释放prepare所占的运行内存

开始使用常用CURD操作

INSERT操作

  1. namespace chapter2;
  2. use mysqli;
  3. // MYSQLI 操作数据库
  4. $mysqli = new mysqli('localhost','root','root','xiaoyu');
  5. //设置字符集
  6. $mysqli->set_charset('utf8');
  7. // 创建一条SQL语句 ：name命名占位 ?索引占位
  8. $sql = 'INSERT `users` SET `name`=?,`email`=?,`password`=?,`register_time`=?;';
  9. //预处理方式
  10. $stmt = $mysqli->stmt_init();
  11. //准备执行一条sql语句
  12. $stmt->prepare($sql);
  13. //占位参数绑定
  14. $stmt->bind_param('sssi',$name,$email,$password,$register_time);
  15. //将值写入
  16. $users = ['周小雨','1016673080@qq.com','xiaoyu',time()];
  17. list($name,$email,$password,$register_time)=$users;
  18. //执行语句
  19. $stmt->execute();
  20. //3.处理结果
  21. if($stmt->affected_rows >=1){
  22.     echo '添加成功,新纪录的主键Id:'.$stmt->insert_id;
  23. }else{
  24.     echo '添加失败'.$stmt->error;
  25. }

Delete操作

  1. namespace chapter2;
  2. use mysqli;
  3. //数据库连接
  4. $mysqli=new mysqli('localhost','root','root','xiaoyu');
  5. //设置字符集
  6. $mysqli->set_charset('utf8');
  7. //写一条改数据的SQL
  8. $sql = 'DELETE FROM `users` WHERE `id`=? ;';
  9. //使用预处理
  10. $stmt = $mysqli->stmt_init();
  11. //准备一个将要执行的SQL
  12. $stmt->prepare($sql);
  13. //绑定参数
  14. $id = 7;
  15. $stmt->bind_param('i',$id);
  16. $stmt->execute();
  17. if($stmt->affected_rows >= 1){
  18.     echo '删除成功';
  19. }else{
  20.     echo '删除失败'.$stmt->error;
  21. }
  22. $stmt->close();

Update操作

  1. namespace chapter2;
  2. use mysqli;
  3. //数据库连接
  4. $mysqli=new mysqli('localhost','root','root','xiaoyu');
  5. //设置字符集
  6. $mysqli->set_charset('utf8');
  7. //写一条改数据的SQL
  8. $sql = 'UPDATE `users` SET `name`=?,`email`=? WHERE `id`=? ;';
  9. //使用预处理
  10. $stmt = $mysqli->stmt_init();
  11. //准备一个将要执行的SQL
  12. $stmt->prepare($sql);
  13. //绑定参数
  14. $datas = ['小雨1','23232@qq.com',8];
  15. list($name,$email,$id)=$datas;
  16. $stmt->bind_param('ssi',$name,$email,$id);
  17. $stmt->execute();
  18. if($stmt->affected_rows >= 1){
  19.     echo '更新成功';
  20. }else{
  21.     echo '更新失败'.$stmt->error;
  22. }
  23. $stmt->close();

Select 通过mysqli_result

  1. namespace chapter2;
  2. use mysqli;
  3. $mysqli = new mysqli('localhost','root','root','xiaoyu');
  4. $mysqli->set_charset('utf8');
  5. $sql = 'SELECT `id`,`name`,`email` FROM `users` WHERE `id`>?;';
  6. $stmt=$mysqli->stmt_init();
  7. $stmt->prepare($sql);
  8. $id=4;
  9. $stmt->bind_param('i',$id);
  10. $stmt->execute();
  11. //获取结果集对象
  12. $result=$stmt->get_result();
  13. //fetch_rows() 索引
  14. //fetch_assoc() 关联
  15. //fetch_array 都有
  16. while($user = $result->fetch_assoc())
  17. {
  18.     echo $user['id'].'=>'.$user['name'].'=>'.$user['email'].'</br>';
  19. }
  20. //数据表指针复位 归零 将指针指向0;
  21. $result->data_seek(0);
  22. echo '<hr>';
  23. $users = $result->fetch_all(MYSQLI_ASSOC);
  24. foreach ($users as $value) {
  25.     echo "{$value['id']} : {$value['name']} ==> {$value['email']} <br>";
  26. }
  27. //4.结束操作
  28. //释放结果集
  29. $result->free_result();
  30. //关闭数据库链接
  31. $stmt->close();

Select bind_result方式

  1. namespace chapter2;
  2. use mysqli;
  3. $mysqli = new mysqli('localhost','root','root','xiaoyu');
  4. $mysqli->set_charset('utf8');
  5. $sql = 'SELECT `id`,`name`,`email` FROM `users` WHERE `id`>?;';
  6. $stmt=$mysqli->stmt_init();
  7. $stmt->prepare($sql);
  8. $id=4;
  9. $stmt->bind_param('i',$id);
  10. $stmt->execute();
  11. //将结果集中的字段绑定到变量中
  12. $stmt->bind_result($id,$name,$email);
  13. while ($stmt->fetch()) {
  14.     echo "$id : $name ---> $email <br>";
  15. }
  16. //4.结束操作
  17. //释放结果集
  18. $stmt->free_result();
  19. //关闭数据库链接
  20. $stmt->close();

接口多态数据库操作实例

  1. namespace chapter2;
  2. //配置数据库的连接参数:接口常量
  3. interface iDbParam
  4. {
  5.     const HOST = 'localhost';
  6.     const TYPE = 'mysql';
  7.     const DBNAME = 'xiaoyu';
  8.     const USER_NAME = 'root';
  9.     const PASSWORD = 'root';
  10.     const CHARSET = 'utf8';
  11.     const PORT = '3306';
  12. }
  13. interface iDbLink
  14. {
  15.     //接口中允许有构造方法
  16.     public function __construct(...$linkParams);
  17.     //接口方法:后面的代码就是用下面的这个主接口
  18. }
  19. interface iCURD extends iDbLink,iDbParam
  20. {
  21.     //新增
  22.     public function insert(array $data);
  23.     //查询
  24.      public function select(string $where = '');
  25.      //更新
  26.      public function update(array $data,string $where);
  27.      //删除
  28.      public function delete(string $where);
  29. }
  30. namespace chapter2;
  31. //引入接口文件
  32. require 'mysqliinter.php';
  33. //用mysqli方式
  34. use mysqli;
  35. class Db_MySQLi implements iCURD
  36. {
  37.     //连接对象
  38.     private $mysqli = null;
  39.     //构造方法
  40.     public function __construct(...$linkparams)
  41.     {
  42.         list($host,$username,$password,$dbname)=$linkparams;
  43.         $this->mysqli = new mysqli($host,$username,$password,$dbname);
  44.         $this->mysqli->set_charset('utf8');
  45.     }
  46.     public function insert(array $data)
  47.     {
  48.         $sql = "INSERT `users` SET `name`=?, `email`=?,`password`=?,`register_time`=?";
  49.         $stmt = $this->mysqli->prepare($sql);
  50.         $stmt->bind_param('sssi',$name,$email,$password,$register_time);
  51.         list($name,$email,$password,$register_time)=$data;
  52.         $stmt->execute();
  53.         return $stmt->affected_rows === 1 ? '新增成功':'新增失败';
  54.     }
  55.     public function select(string $where = '')
  56.     {
  57.         $where = empty($where)?$where:'WHERE '.$where;
  58.         $sql = "SELECT * FROM `users` {$where}";
  59.         echo $sql;
  60.         $stmt = $this->mysqli->prepare($sql);
  61.         $stmt->execute();
  62.         return $stmt->get_result();
  63.     }
  64.     public function update(array $data,string $where)
  65.     {
  66.         $params = '';
  67.         foreach ($data as $key => $value) {
  68.             $params .= "`{$key}` = '{$value}', ";
  69.         }
  70.         $params=rtrim($params,', ');
  71.         //执行更新
  72.         $sql = "UPDATE `users` SET {$params} WHERE {$where}";
  73.         echo $sql;
  74.         // echo $sql;
  75.         $stmt = $this->mysqli->prepare($sql);
  76.         $stmt->execute();
  77.         return $stmt->affected_rows >= 1 ? '更新成功':'更新失败';
  78.     }
  79.     public function delete(string $where)
  80.     {
  81.         $sql = "DELETE FROM `users` WHERE {$where}";
  82.         $stmt = $this->mysqli->prepare($sql);
  83.         $stmt->execute();
  84.         return $stmt->affected_rows >= 1 ? '删除成功':'删除失败';
  85.     }
  86. }
  87. namespace chapter2;
  88. //引入接口文件
  89. require 'mysqliinter.php';
  90. //用pdo方式
  91. use PDO;
  92. class Db_PDO implements iCURD
  93. {
  94.     //连接对象
  95.     private $pdo = null;
  96.     //构造方法
  97.     public function __construct(...$linkparams)
  98.     {
  99.         list($dsn,$username,$password)=$linkparams;
  100.         $this->pdo = new PDO($dsn,$username,$password);
  101.     }
  102.     public function insert(array $data)
  103.     {
  104.         $sql = 'INSERT `users` SET `name`=?,`email`=?,`password`=?,`register_time`=?;';
  105.         $stmt = $this->pdo->prepare($sql);
  106.         $stmt->execute($data);
  107.         return $stmt->rowCount() === 1 ? '新增成功':'新增失败';
  108.     }
  109.     public function select(string $where = '')
  110.     {
  111.         $where = empty($where)?$where:'WHERE '.$where;
  112.         $sql = "SELECT * FROM `users` {$where}";
  113.         echo $sql;
  114.         $stmt = $this->pdo->prepare($sql);
  115.         $stmt->execute();
  116.         return $stmt->fetchAll(PDO::FETCH_ASSOC);
  117.     }
  118.     public function update(array $data,string $where)
  119.     {
  120.         $params = '';
  121.         foreach ($data as $key => $value) {
  122.             $params .= "`{$key}`='{$value}', ";
  123.         }
  124.         $params=rtrim($params,', ');
  125.         //执行更新
  126.         var_dump($params);
  127.         $sql = "UPDATE `users` SET {$params} WHERE {$where}";
  128.         echo $sql;
  129.         $stmt = $this->pdo->prepare($sql);
  130.         $stmt->execute();
  131.         return $stmt->rowCount() >= 1 ? '更新成功':'更新失败';
  132.     }
  133.     public function delete(string $where)
  134.     {
  135.         $sql = "DELETE FROM `users` WHERE {$where}";
  136.         $stmt = $this->pdo->prepare($sql);
  137.         $stmt->execute();
  138.         return $stmt->rowCount() >= 1 ? '删除成功':'删除失败';
  139.     }
  140. }
  141. namespace chapter2;
  142. //通用类:数据库操作,面向接口实现多态
  143. use chapter2\iCURD;
  144. class DB
  145. {
  146.     public static function insert(iCURD $db,array $data)
  147.     {
  148.         return $db->insert($data);
  149.     }
  150.      // 查询
  151.     public static function select(iCURD $db, string $where = '')
  152.     {
  153.         return $db->select($where);
  154.     }
  155.     // 更新
  156.     public static function update(iCURD $db, array $data, string $where='')
  157.     {
  158.         return $db->update($data, $where);
  159.     }
  160.     // 删除
  161.     public static function delete(iCURD $db, string $where='')
  162.     {
  163.         return $db->delete($where);
  164.     }
  165. }
  166. //接下来就是调用
  167. namespace chapter2;
  168. require 'mysqli_demo.php';
  169. require 'DB.php';
  170. use mysqli;
  171. //使用mysqli类来增删改查
  172. //声明一下用接口多态的mysqli类
  173. $link = new Db_MySQLi(iDbParam::HOST,iDbParam::USER_NAME,iDbParam::PASSWORD,iDbParam::DBNAME);
  174. //------------------------分割线----------------------------------------
  175. namespace chapter2;
  176. //导入pdo类
  177. //导入公共操作类
  178. require 'pdo_demo.php';
  179. require 'DB.php';
  180. $dsn = iDbParam::TYPE.':'.'dbname='.iDbParam::DBNAME.';host='.iDbParam::HOST;
  181. $user = iDbParam::USER_NAME;
  182. $password = iDbParam::PASSWORD;
  183. $link = new Db_PDO($dsn,$user,$password);
上一条：PSR-4标准自动加载,composer使用下一条：HTML链接
声明：本文内容转载自脚本之家，由网友自发贡献，版权归原作者所有，如您发现涉嫌抄袭侵权，请联系admin@php.cn 核实处理。
全部评论
文明上网理性发言，请遵守新闻评论服务协议

相关文章

查看更多