0211作业+mysqli操作数据库与接口实战+10期线上班 - mysqli操作数据库和接口实战

Community

Learn

Tools Library

AI Tools

Leisure

English

Home > List of blog posts > 0211作业+mysqli操作数据库与接口实战+10期线上班

Blogger Information

Blog 25

fans 1

comment 1

visits 17506

Special Recommendation

More>

Related recommendations

MYSQLI操作数据库

以下是增加操作

<?php
namespace mysql;
use mysqli;
//添加
//用mysqli来进行数据库操作
//链接数据库
$mysqli = new mysqli('localhost','root','root','phpedu');
//数据库操作
//初始化返回预处理对象
 $stmt = $mysqli->stmt_init();
// 准备SQL执行语句
$sql = "INSERT staffs SET name=?,age=?,sex=?,position=?,mobile=?,hiredate=?" ;
//创建sql语句对象
$stmt->prepare($sql);
//var_dump($stmt);
//绑定参数
$user = ['小江',23,1,'学生',1111111,2222222];
list($name,$age,$sex,$position,$mobile,$hiredate) = $user;
$stmt->bind_param('siisii',$name,$age,$sex,$position,$mobile,$hiredate);
//执行
$stmt->execute();
//处理执行结果
if($stmt->affected_rows === 1){
    echo '添加成功,'. '受影响的条数为：'. $stmt->affected_rows;
}else {
    echo '添加失败'.$stmt->error;
}
//关闭数据库
$mysqli->close();

以下是更改操作

<?php
namespace mysql;
use mysqli;
//更新/改
//用mysqli来进行数据库操作
//链接数据库
$mysqli = new mysqli('localhost','root','root','phpedu');
//数据库操作
//初始化返回预处理对象
$stmt = $mysqli->stmt_init();
// 准备SQL执行语句
//$sql = 'UPDATE `staffs` SET `name`=?,`age`=? WHERE  `id`=?';
$sql = 'UPDATE `staffs` SET  `name`=?,`age`=? WHERE `id`>=?';
//创建sql语句对象
$stmt->prepare($sql);
//var_dump($stmt);
//绑定参数
$user = ['小王',20,14];
list($name,$age,$id) = $user;
$stmt->bind_param('sii',$name,$age,$id);
//执行
$stmt->execute();
//处理执行结果
if($stmt->affected_rows > 0){
    echo '更新成功,';
}else {
    echo '更新失败'.$stmt->error;
}
//关闭数据库
$mysqli->close();

以下是删除操作

<?php
namespace mysql;
use mysqli;
//删除
//用mysqli来进行数据库操作
//链接数据库
$mysqli = new mysqli('localhost','root','root','phpedu');
//数据库操作
//初始化返回预处理对象
$stmt = $mysqli->stmt_init();
// 准备SQL执行语句
//$sql = 'UPDATE `staffs` SET `name`=?,`age`=? WHERE  `id`=?';
$sql = 'DELETE FROM  `staffs` WHERE `id`>=?';
//创建sql语句对象
$stmt->prepare($sql);
//var_dump($stmt);
//绑定参数
$user = [14];
list($id) = $user;
$stmt->bind_param('i',$id);
//执行
$stmt->execute();
//处理执行结果
if($stmt->affected_rows > 0){
    echo '删除成功,';
}else {
    echo '删除失败'.$stmt->error;
}
//关闭数据库
$mysqli->close();

以下是查询操作

<?php
namespace mysql;
use mysqli;
//查询操作
//STMT查询操作
//连接数据库
$mysqli = new mysqli('localhost','root','root','phpedu');
//var_dump($mysqli);
//创建SQL语句
$sql = 'SELECT `id`,`name`,`sex` FROM `staffs` ';
//创建预处理对象
$stmt = $mysqli->stmt_init();
$stmt->prepare($sql);
//执行
$stmt->execute();
//处理执行结果
//绑定结果集
$stmt->bind_result($id,$name,$sex);
//遍历结果集
//2，循环结果遍历
while ($stmt->fetch()){
    if ($sex ==1 ) {
        echo "$id 号选手是:$name,性别男 '<br>'";
    }else{
        echo "$id 号选手是:$name, 性别女'<br>' ";
    }
}

接口与多态实战

创建基础接口

<?php
namespace chapterA;
//创建基础接口_接口常量
interface iPARAM
{
    const SDN = 'mysql';
    const HOST = 'localhost';
    const USERNAME = 'root';
    const PASSWORD = 'root';
    const CHARSET = 'utf8';
    const DBNAME = 'phpedu';
}
//创建接口方法——构造函数，连接数据库
interface iMYSQL
{
    public function __construct(...$link);
}
//进行接口继承，以后就用以下主接口
interface iCURD extends iPARAM, iMYSQL
{
//    增
    public function insert(array $data);
//    删
    public function delete(string $where);
//    改
    public function update(array $data,string $where);
//    查
    public function select(string $where);
}

实现基础接口PDO

<?php
namespace chapterA;
//用PDO操作数据库
use PDO;
//实现主接口
require 'demo8.php';
class PDO_mysql implements iCURD
{
    //连接对象
    private $pdo = '';
    //连接数据库
    public function __construct(...$link)
    {
        list($dsn,$username,$password) = $link;
       $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 delete(string $where)
    {
        $sql = "DELETE FROM `users` WHERE {$where }" ;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount() === 1 ? '删除成功' : '删除失败' ;
    }
//    改
    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 select(string $where)
    {
        $where = empty($where) ? $where : 'WHERE' . $where;
        $sql = "SELECT * FROM `users` {$where}";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

实现基础接口Mysqli

<?php
namespace chapterA;
//用mysqli操作数据库
use mysqli;
//实现主接口
require 'demo8.php';
class Mysqli_mysql implements iCURD
{
    //连接对象
    private $mysqli = '';
    //连接数据库
    public function __construct(...$link)
    {
        list($host,$username,$password,$dbname) = $link;
        $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);
        list($name,$email,$password,$register_time) = $data;
        $stmt->bind_param('sssi',$name,$email,$password,$register_time);
        $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 ? '删除成功' : '删除失败' ;
    }
//    改
    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 select( string $where)
    {
        $where = empty($where) ? $where : 'WHERE' . $where;
        $sql = "SELECT * FROM `users` {$where}";
        $stmt = $this->mysqli->prepare($sql);
        $stmt->execute();
        return $stmt->get_result();
    }
}

创建通用类

<?php
namespace chapterA;
use chapterA\iCURD;
//通用类，数据库操作，面向接口实现多态
class DB
{
    //    增
    public function insert(iCURD $db, array $data){
        return $db->insert($data);
    }
//    删
    public function delete(iCURD $db,string $where){
        return $db->delete($where);
    }
//    改
    public function update(iCURD $db,array $data,string $where){
        return $db->update($data,$where);
    }
//    查
    public function select(iCURD $db,string $where){
        return $db->select($where);
    }
}

进行数据库操作

<?php
//使用PDO操作数据库：接口实现
namespace chapterA;
use PDO;
//加载PDO接口
require 'demo9.php';
//加载通用类
require 'demo11.php';
//创建DSN
$dsn = iPARAM::SDN.':host='.iPARAM::HOST.';dbname='.iPARAM::DBNAME.';';
//连接数据库
$link =  new PDO_mysql($dsn,PDO_mysql::USERNAME,PDO_mysql::PASSWORD);
//var_dump($link);
//die();
//测试增加操作
//$pdop = new DB();
//echo $pdop->insert($link,['小江','jiang@php.cn',sha1('123456'),time()]);
//测试删除操作
//$pdop = new DB();
//echo $pdop->delete($link,'`id`=9');
//测试更新数据
//$pdop = new DB();
//echo $pdop->update($link,['name'=>'小李','email'=>'jiang@php.cn'],'id=7');
//测试查询操作
$pdop = new DB();
foreach ($pdop->select($link,'`id`>2') as $user){
    echo "{$user['id']} -- {$user['name']} -- {$user['email']} <br>";
}

手写mysqli_result相关类用途

上传不起，一直显示未发现，，，

Correcting teacher：

天蓬老师

Correction status：qualified

Teacher's comments：mysqli做为知识扩展, 还有要掌握的, 不要小看, 还有许多项目在使用

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

HTML文档的基本机构及局部标签的学习-PHP培训10期线上班

2019-12-20 18:07:53