Community Learn Tools Library Leisure

English

Home > List of blog posts > 0211MySQLi常见CURD操作+接口实战实例

Blogger Information

Blog 30

fans 0

comment 1

visits 21956

Special Recommendation

More>

Related recommendations

MySQLi常见CURD操作+接口实战实例

MySQLi常见CURD操作

首先是MySQL一些常用类

mysqli代表PHP于Mysql数据库类里面的一个连接;
mysqli_stmt 代表一条预编译的sql语句
mysqli_result 代表一个从数据库查询中的结果集;

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操作

namespace chapter2;
// 采用面向对象方式来写要导入mysqli
use mysqli;
// MYSQLI 操作数据库
$mysqli = new mysqli('localhost','root','root','xiaoyu');
//设置字符集
$mysqli->set_charset('utf8');
// 创建一条SQL语句 ：name命名占位 ?索引占位
$sql = 'INSERT `users` SET `name`=?,`email`=?,`password`=?,`register_time`=?;';
//预处理方式
$stmt = $mysqli->stmt_init();
//准备执行一条sql语句
$stmt->prepare($sql);
//占位参数绑定
$stmt->bind_param('sssi',$name,$email,$password,$register_time);
//将值写入
$users = ['周小雨','1016673080@qq.com','xiaoyu',time()];
list($name,$email,$password,$register_time)=$users;
//执行语句
$stmt->execute();
//3.处理结果
if($stmt->affected_rows >=1){
    echo '添加成功,新纪录的主键Id:'.$stmt->insert_id;
}else{
    echo '添加失败'.$stmt->error;
}

Delete操作

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

Update操作

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

Select 通过mysqli_result

namespace chapter2;
use mysqli;
$mysqli = new mysqli('localhost','root','root','xiaoyu');
$mysqli->set_charset('utf8');
$sql = 'SELECT `id`,`name`,`email` FROM `users` WHERE `id`>?;';
$stmt=$mysqli->stmt_init();
$stmt->prepare($sql);
$id=4;
$stmt->bind_param('i',$id);
$stmt->execute();
//获取结果集对象
$result=$stmt->get_result();
//fetch_rows() 索引
//fetch_assoc() 关联
//fetch_array 都有
while($user = $result->fetch_assoc())
{
    echo $user['id'].'=>'.$user['name'].'=>'.$user['email'].'</br>';
}
//数据表指针复位 归零 将指针指向0;
$result->data_seek(0);
echo '<hr>';
$users = $result->fetch_all(MYSQLI_ASSOC);
foreach ($users as $value) {
    echo "{$value['id']} : {$value['name']} ==> {$value['email']} <br>";
}
//4.结束操作
//释放结果集
$result->free_result();
//关闭数据库链接
$stmt->close();

Select bind_result方式

namespace chapter2;
use mysqli;
$mysqli = new mysqli('localhost','root','root','xiaoyu');
$mysqli->set_charset('utf8');
$sql = 'SELECT `id`,`name`,`email` FROM `users` WHERE `id`>?;';
$stmt=$mysqli->stmt_init();
$stmt->prepare($sql);
$id=4;
$stmt->bind_param('i',$id);
$stmt->execute();
//将结果集中的字段绑定到变量中
$stmt->bind_result($id,$name,$email);
while ($stmt->fetch()) {
    echo "$id : $name ---> $email <br>";
}
//4.结束操作
//释放结果集
$stmt->free_result();
//关闭数据库链接
$stmt->close();

接口多态数据库操作实例

首先创建一个数据库连接操作接口iDbParam用来定义接口常量,iDbLink接口中构造方法用来连接数据库,iCURD接口继承前二者加上数据库操作

#接口实战:使用PDO MySQLi实现数据库多态操作
namespace chapter2;
//配置数据库的连接参数:接口常量
interface iDbParam
{
    const HOST = 'localhost';
    const TYPE = 'mysql';
    const DBNAME = 'xiaoyu';
    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);
}

接下来就是实现iCURD接口(因为是多态实现,所以我们要写两个实例化)
mysqli

namespace chapter2;
//引入接口文件
require 'mysqliinter.php';
//用mysqli方式
use mysqli;
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}";
        echo $sql;
        $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}";
        echo $sql;
        // echo $sql;
        $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 ? '删除成功':'删除失败';
    }
}

pdo

<?php
namespace chapter2;
//引入接口文件
require 'mysqliinter.php';
//用pdo方式
use PDO;
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}";
        echo $sql;
        $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,', ');
        //执行更新
        var_dump($params);
        $sql = "UPDATE `users` SET {$params} WHERE {$where}";
        echo $sql;
        $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 ? '删除成功':'删除失败';
    }
}

然后我们创建一个公共类来进行调用

namespace chapter2;
//通用类:数据库操作,面向接口实现多态
use 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);
    }
}

//接下来就是调用
namespace chapter2;
require 'mysqli_demo.php';
require 'DB.php';
use mysqli;
//使用mysqli类来增删改查
//声明一下用接口多态的mysqli类
$link = new Db_MySQLi(iDbParam::HOST,iDbParam::USER_NAME,iDbParam::PASSWORD,iDbParam::DBNAME);
//------------------------分割线----------------------------------------
namespace chapter2;
//导入pdo类
//导入公共操作类
require 'pdo_demo.php';
require 'DB.php';
$dsn = iDbParam::TYPE.':'.'dbname='.iDbParam::DBNAME.';host='.iDbParam::HOST;
$user = iDbParam::USER_NAME;
$password = iDbParam::PASSWORD;
$link = new Db_PDO($dsn,$user,$password);

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

2019.12.19 第一次前端作业

2019-12-20 19:45:19