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

MySQLi/接口与多态

1、MySQLi常见的CURD操作

1.1 新增

<?php

namespace chapter2;
use mysqli;
//MySQLi 面向对象方式操作数据库

//新增操作
//1、连接数据库
//mysqli(主机名，用户名，密码，默认数据库);
$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 = ['张三丰', '[e-mail]', 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;
    }


//4、关闭操作

//关闭数据库连接
$stmt -> close();

代码执行效果：

---

1.2 更新

这里只上传关键代码。

//2、执行SQL语句操作
//2.1 准备sql语句
$sql = 'UPDATE `users` SET `name`= ?, `email` = ? WHERE `id` = ?';

//初始化一个预处理对象
$stmt = $mysqli -> stmt_init();

//创建一个SQL预处理对象
$stmt -> prepare($sql);

//绑定变量参数
$user = ['张无忌', '[e-mail]', 9];
list($name, $email, $id) = $user;
$stmt -> bind_param('ssi', $name, $email, $id);

//执行
$stmt -> execute();

//3、处理执行结果
if ($stmt->affected_rows === 1) :
    echo '更新成功';
else :
    echo '更新失败' . $stmt->error;
endif;

代码执行效果：

---

1.3 删除

//2、
$sql = 'DELETE FROM `users` WHERE `id` = ?';


$stmt = $mysqli -> stmt_init();
$stmt -> prepare($sql);


$id = 9;
$stmt -> bind_param('i', $id);



$stmt -> execute();



//3、
if($stmt -> affected_rows === 1){
    echo '删除成功。';
}else{
    echo '删除失败。';
}

代码执行效果：

---

1.4 查询

//2、
$sql = 'SELECT `id`, `name`, `email` FROM `users` WHERE `id` > ?;';


$stmt = $mysqli ->stmt_init();
$stmt ->prepare($sql);

$id = 2;
$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();

代码执行效果：

---

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

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

<?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 iDbParam,iDblink
{
    //新增
    public function insert (array $data);

    //查询
    public function select (string $where = '');

    //更新
    public function update (array $data, string $where);

    //删除
    public function delete (string $where);

}

2.2 PDO来实现数据操作（实现接口方法）

<?php
namespace 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 = "SELECE * FROM `user` {$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 ? '删除成功' : '删除失败';
    }

}

2.3 MySQLi来实现数据操作（实现接口方法）

<?php
namespace 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 = "SELECE * FROM `user` {$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 felete (string $where)
    {
        $sql = "DELETE FROM `users` WHERE {$where}";
        $stmt = $this -> mysqli -> prepare($sql);
        $stmt -> execute();
        return $stmt -> affected_row === 1 ? '删除成功' : '删除失败';
    }

}

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

<?php

namespace chapter2;

//通用类：数据库操作，面向接口实现多态，动态支持PDO/MySQLi

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);
    }
}

2.5 演示PDO操作

<?php

namespace chapter2;

//使用PDO来操作数据库，接口实现

//加载PDO操作类：Db_PDO
require 'demo8.php';

//加载数据库通用类
require 'demo10.php';

//创建DSN
$dsn = iDbParam::TYPE .':host=' .iDbParam::HOST .';dbname=' .iDbParam::DBNAME .';charset=' .iDbParam::CHARSET;

//die($dsn);
$link = new Db_PDO($dsn, iDbParam::USER_NAME, iDbParam::PASSWORD);

//测试新增
//echo DB::insert($link, ['guojue', '[e-mail]', sha1('123456'), time()]);

//查询
//foreach (DB::select($link, 'id > 2') as $user){
//    echo "{$user['id']} --- {$user['name']} --- {$user['email']} <br>";
//}

//更新
//echo DB::update($link, ['name'=>'赵大叔', 'email'=>'[e-mail]'], 'id= 11');

//删除
echo DB::delete($link, 'id = 10');

代码执行效果：

2.6 演示MySQLi操作

<?php


namespace chapter2;

//使用MySQLi来操作数据库，接口实现

//加载MySQLi操作类：Db_MySQLi
require 'demo9.php';

//加载数据库通用类
require 'demo10.php';


//die($dsn);
$link = new Db_MySQLi(iDbParam::HOST, iDbParam::USER_NAME, iDbParam::PASSWORD, iDbParam::DBNAME);

//测试新增
//echo DB::insert($link, ['linmeimei', '[e-mail]', sha1('123456'), time()]);

//查询
//foreach (DB::select($link, 'id > 2') as $user){
//    echo "{$user['id']} --- {$user['name']} --- {$user['email']} <br>";
//}

//更新
//echo DB::update($link, ['name'=>'赵大叔', 'email'=>'[e-mail]'], 'id= 13');

//删除
echo DB::delete($link, 'id = 12');

代码执行效果：

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