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 = ['张三丰', 'zhangsanfeng@163.cn', 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 = ['张无忌', 'wuji@163.com', 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', 'guojue@163.cm', 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'=>'dashuzhao@php.cn'], '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', 'linmeimei@php.cn', 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'=>'dashuzhao@php.cn'], 'id= 13');
//删除
echo DB::delete($link, 'id = 12');
代码执行效果: