Community Learn Tools Library Leisure
search
English
Home > List of blog posts > 2月11日 学号：478291 MySQLi常用CURD操作、通过接口实现多态
Blogger Information
Blog 24
fans 4
comment 0
visits 20136
Special Recommendation
More>
Related recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
2月11日 学号：478291 MySQLi常用CURD操作、通过接口实现多态
Lin__
Original
625 people have browsed it

MySQLi常用CURD操作

  • 连接数据库：mysqli(主机名,用户名,密码,数据库名)
  • 数据库操作：

(1)stmt_init()，返回预处理对象

(2)prepare(SQL语句)，创建语句对象

(3)bind_param(参数类型，参数列表)，绑定参数

(4)execite()，执行

(5)affected_row()，返回受影响的记录数

(6)insert_id，新增的记录的ID

(7)error，错误信息，字符串

(8)fetch()，获取一条记录，stmt方法

(9)get_result()，返回结果集对象实例，结果集方式方法

(10)fetch_array()，一条记录结果，为索引+关联的一维数组，结果集方式方法

(11)fetch_row()，一条记录结果，为索引一维数组，结果集方式方法

(12)fetch_assoc()，一条记录结果，为关联一维数组，结果集方式方法

(13)fetch_all，一次性获取所有记录，索引二维数组，若要改成关联数组，需添加参数MYSQL_ASSOC，结果集方式方法

(14)data_seek(),数据库指针复位

(15)free_result()，释放结果集

  • 关闭数据库连接：close()

使用mysqli操作数据库

数据表

新增操作

  1. <?php
  2. //连接数据库
  3. $conn=new mysqli('localhost','root','root','test_db');
  4. //准备sql语句
  5. $sql="insert `user` set `name`=? , `age` = ?";
  6. //预处理方式
  7. $stmt=$conn->stmt_init();
  8. //创建语句对象
  9. $stmt->prepare($sql);
  10. //绑定参数
  11. $name='Jane';
  12. $age=35;
  13. $stmt->bind_param('si',$name,$age);
  14. //执行
  15. $stmt->execute();
  16. //处理执行结果
  17. if($stmt->affected_rows){
  18.     echo "新增成功，新增的ID是{$stmt->insert_id}";
  19. }else{
  20.     echo "新增失败，错误原因{$stmt->error}";
  21. }
  22. //关闭数据库连接
  23. $stmt->close();



修改操作

  1. <?php
  2. //连接数据库
  3. $conn=new mysqli('localhost','root','root','test_db');
  4. //准备sql语句
  5. $sql="update `user` set `name`=? where id=?";
  6. //预处理方式
  7. $stmt=$conn->stmt_init();
  8. //创建语句对象
  9. $stmt->prepare($sql);
  10. //绑定参数
  11. $name='Maria';
  12. $id=5;
  13. $stmt->bind_param('si',$name,$id);
  14. //执行
  15. $stmt->execute();
  16. //处理执行结果
  17. if($stmt->affected_rows){
  18.     echo "修改成功";
  19. }else{
  20.     echo "修改失败，错误原因{$stmt->error}";
  21. }
  22. //关闭数据库连接
  23. $stmt->close();



删除操作

  1. <?php
  2. //连接数据库
  3. $conn=new mysqli('localhost','root','root','test_db');
  4. //准备sql语句
  5. $sql="delete from `user` where id=?";
  6. //预处理方式
  7. $stmt=$conn->stmt_init();
  8. //创建语句对象
  9. $stmt->prepare($sql);
  10. //绑定参数
  11. $id=5;
  12. $stmt->bind_param('i',$id);
  13. //执行
  14. $stmt->execute();
  15. //处理执行结果
  16. if($stmt->affected_rows){
  17.     echo "删除成功";
  18. }else{
  19.     echo "删除失败，错误原因{$stmt->error}";
  20. }
  21. //关闭数据库连接
  22. $stmt->close();



查询操作

  1. <?php
  2. //连接数据库
  3. $conn=new mysqli('localhost','root','root','test_db');
  4. //准备sql语句
  5. $sql="select *  from `user`";
  6. //预处理方式
  7. $stmt=$conn->stmt_init();
  8. //创建语句对象
  9. $stmt->prepare($sql);
  10. //绑定参数
  11. $stmt->bind_result($id,$name,$age);
  12. //执行
  13. $stmt->execute();
  14. //处理执行结果
  15. while($result=$stmt->fetch()){
  16.     echo "{$name}的年龄是{$age}<br/>";
  17. }
  18. //释放结果集
  19. $stmt->free_result();
  20. //关闭数据库连接
  21. $stmt->close();

通过接口实现多态

数据操作接口

  1. <?php
  2. namespace db;
  4. //定义常用参数接口
  5. interface IParam{
  6.     const HOST='localhost';//主机名称
  7.     const USERNAME='root';//用户名
  8.     const PASSWORD='root';//密码
  9.     const PORT='3306';//端口号
  10.     const TYPE='mysql';//数据库类型
  11.     const CHARSET='utf8';//编码集
  12.     const DBNAME='test_db';//数据库名称
  13. }
  15. //定义构造方法接口，用于连接数据库
  16. interface IStruction{
  17.     public function __construct(...$paramArr);
  18. }
  20. //定义常用数据库操作方法接口
  21. interface IMethod extends IParam,IStruction{
  22.     public function insert(string $tableName,array $data);//添加
  23.     public function select(string $tableName,string $field,string $where);//查询
  24.     public function update(string $tableName,array $data,string $where);//修改
  25.     public function delete(string $tableName,string $where);//删除
  26. }

pdo数据库操作类

  1. <?php
  2. namespace db;
  3. require_once 'interface.php';
  5. class Pdo_db implements IMethod{
  6.     private $pdo;
  8.     public function __construct(...$paramArr){
  9.         list($dsn, $username, $password) = $paramArr;
  10.         $this->pdo=new \PDO($dsn, $username, $password);
  11.     }
  13.     public function insert(string $tableName,array $data){
  14.         $param='';
  15.         foreach($data as $k=>$v){
  16.             if(gettype($v)=='integer'){
  17.                 $param.=$k."= {$v},";
  18.             }else{
  19.                 $param.=$k."= '{$v}',";
  20.             }
  21.         }
  22.         $param=rtrim($param,',');
  23.         $sql="insert `{$tableName}` set {$param} ;";
  24.         $stmt=$this->pdo->prepare($sql);
  25.         $stmt->execute();
  26.         if($stmt->rowCount()){
  27.             echo "新增成功，新增的记录ID为{$pdo->lastInsertId()}";
  28.         }else{
  29.             echo "新增失败，错误为：{$pdo->errorInfo()}";
  30.         }
  31.     }
  33.     public function select(string $tableName,string $field,string $where){
  34.         $sql="select {$field} from `{$tableName}` where {$where}";
  35.         $stmt=$this->pdo->prepare($sql);
  36.         $stmt->execute();
  37.         while($result=$stmt->fetch(\PDO::FETCH_ASSOC)){
  38.             var_dump($result);
  39.             echo '<br/>';
  40.         }
  41.     }
  43.     public function update(string $tableName,array $data,string $where){
  44.         $param='';
  45.         foreach($data as $k=>$v){
  46.             if(gettype($v)=='integer'){
  47.                 $param.=$k."= {$v},";
  48.             }else{
  49.                 $param.=$k."= '{$v}',";
  50.             }
  51.         }
  52.         $param=rtrim($param,',');
  53.         $sql="update `{$tableName}` set {$param} where {$where}";
  54.         $stmt=$this->pdo->prepare($sql);
  55.         $stmt->execute();
  56.         if($stmt->rowCount()){
  57.             echo "修改成功";
  58.         }else{
  59.             echo "修改失败，错误为：{$pdo->errorInfo()}";
  60.         }
  61.     }
  63.     public function delete(string $tableName,string $where){
  64.         $sql="delete from `{$tableName}` where {$where}";
  65.         $stmt=$this->pdo->prepare($sql);
  66.         $stmt->execute();
  67.         if($stmt->rowCount()){
  68.             echo "删除成功";
  69.         }else{
  70.             echo "删除失败，错误为：{$pdo->errorInfo()}";
  71.         }
  72.     }
  73. }

mysqli数据库操作类

  1. <?php
  2. namespace db;
  3. require_once 'interface.php';
  5. class Mysqli_db implements IMethod{
  6.     private $conn;
  8.     public function __construct(...$paramArr){
  9.         $this->conn=new mysqli($paramArr['host'],$paramArr['username'],$paramArr['password'],$paramArr['dbname']);
  10.     }
  12.     public function insert(string $tableName,array $data){
  13.         $param='';
  14.         foreach($data as $k=>$v){
  15.             if(gettype($v)=='integer'){
  16.                 $param.=$k."= {$v},";
  17.             }else{
  18.                 $param.=$k."= '{$v}',";
  19.             }
  20.         }
  21.         $param=rtrim($param,',');
  22.         $sql="insert `{$tableName}` set {$param} ;";
  23.         $stmt=$this->conn->stmt_init();
  24.         $stmt->prepare($sql);
  25.         $stmt->execute();
  26.         if($stmt->affected_rows){
  27.             echo "新增成功，新增的ID是{$stmt->insert_id}";
  28.         }else{
  29.             echo "新增失败，错误原因{$stmt->error}";
  30.         }
  31.         $stmt->close();
  32.     }
  34.     public function select(string $tableName,string $field,string $where){
  35.         $sql="select {$field} from `{$tableName}` where {$where}";
  36.         $stmt=$this->conn->stmt_init();
  37.         $stmt->prepare($sql);
  38.         $stmt->execute();
  39.         while($result=$stmt->fetch_assoc()){
  40.             echo '<pre>'.print_r($result,1).'</pre>';
  41.         }
  42.         $stmt->free_result();
  43.         $stmt->close();
  44.     }
  46.     public function update(string $tableName,array $data,string $where){
  47.         $param='';
  48.         foreach($data as $k=>$v){
  49.             if(gettype($v)=='integer'){
  50.                 $param.=$k."= {$v},";
  51.             }else{
  52.                 $param.=$k."= '{$v}',";
  53.             }
  54.         }
  55.         $param=rtrim($param,',');
  56.         $sql="update `{$tableName}` set {$param} where {$where}";
  57.         $stmt=$this->conn->stmt_init();
  58.         $stmt->prepare($sql);
  59.         $stmt->execute();
  60.         if($stmt->affected_rows){
  61.             echo "修改成功";
  62.         }else{
  63.             echo "修改失败，错误为：{$pdo->errorInfo()}";
  64.         }
  65.         $stmt->close();
  66.     }
  68.     public function delete(string $tableName,string $where){
  69.         $sql="delete from `{$tableName}` where {$where}";
  70.         $stmt=$this->conn->stmt_init();
  71.         $stmt->prepare($sql);
  72.         $stmt->execute();
  73.         if($stmt->affected_rows){
  74.             echo "删除成功";
  75.         }else{
  76.             echo "删除失败，错误为：{$pdo->errorInfo()}";
  77.         }
  78.         $stmt->close();
  79.     }
  80. }

测试

  1. <?php
  2. namespace db;
  3. require_once 'pdo_db.php';
  4. require_once 'mysqli_db.php';
  6. $dsn=IParam::TYPE . ':host='.IParam::HOST . ';dbname=' . IParam::DBNAME . ';charset='.IParam::CHARSET;
  7. $link=new Pdo_db($dsn, IParam::USERNAME, IParam::PASSWORD);
  8. $link->select('user','*','id=1');

运行结果

Correcting teacher：天蓬老师天蓬老师

Correction status：qualified

Teacher's comments：看得出写得非常认真, 相信这个博客, 能帮到更多的人
Statement of this Website
The copyright of this blog article belongs to the blogger. Please specify the address when reprinting! If there is any infringement or violation of the law, please contact admin@php.cn Report processing!
All comments Speak rationally on civilized internet, please comply with News Comment Service Agreement
0 comments
Author's latest blog post