PHP数据库CURD接口与实现(接口实战)

接口实战的小案例:

<?php

namespace _0802;

// 接口实战的小案例
// 定义一个接口, 实现数据库的常用操作: CURD
interface iCurd
{
    // 增加数据
    public function create($data);

    // 读取数据
    public function read();

    // 更新数据
    public function update($data, $where);

    // 删除数据
    public function delete($where);
}

// 创建Db类,实现iCurd接口
class Db implements iCurd
{
    // 数据库的连接对象
    protected $pdo = null;

    // 数据库
    protected $table;

    // 构造方法: 连接数据库,并设置默认的数据表
    public function __construct($dsn, $username, $password, $table)
    {
        $this->pdo = new \PDO($dsn, $username, $password);
        $this->table = $table;
    }

    // 增加数据
    public function create($data)
    {
        // 字段列表
        $fields = ' (name, age, sex, position, mobile, hiredate) ';
        // 值列表
        $values = ' (:name, :age, :sex, :position, :mobile, :hiredate) ';
        // 创建SQL语句
        $sql = 'INSERT INTO '.$this->table . $fields . ' VALUES ' . $values;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);

//        die($stmt->debugDumpParams());

        // 返回新增数据, 新增记录的id也返回
        return [
            'count' => $stmt->rowCount(),
            'id' => $this->pdo->lastInsertId()
        ];
    }

    // 读取数据
    public function read($fileds = '*' , $where='', $limit = '0, 5')
    {
        // 设置查询条件
        $where = empty($where) ? '' : ' WHERE ' . $where;
        $limit = ' LIMIT ' . $limit;
        $sql = 'SELECT '. $fileds . ' FROM ' . $this->table. $where . $limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        // 用二维数组返回所有数据
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);

    }

    // 更新数据
    public function update($data, $where)
    {
        // 难点在于SET参数
        $keyArr = array_keys($data);
        $set = '';

        // 遍历这个字段列表, 拼装 set
        foreach ($keyArr as $value) {
            $set .= $value . '= :' . $value. ',';
        }
        //age= :age,position= :position,把最后一个逗号删除
        $set =rtrim($set, ', ');

        $sql = 'UPDATE '. $this->table.' SET '.$set.' WHERE '.$where;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);

        // 返回更新的数量
        return $stmt->rowCount();

    }

    // 删除数据
    public function delete($where)
    {
        $sql = 'DELETE FROM '. $this->table. ' WHERE '.$where;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();

        // 返回删除的数量
        return $stmt->rowCount();
    }
}

// 实例化Db
$dsn = 'mysql:host=127.0.0.1;dbname=php';
$username = 'root';
$password = 'a757871402';
$table = 'staff';
$db = new Db($dsn, $username, $password, $table);

// 新增操作
$data = [
    'name'=> '德斌',
    'age'=> 23,
    'sex' => 1,
    'position' => '驸马',
    'mobile'=> '1388888888',
    'hiredate' => time()
];

//$res = $db->create($data);
//echo '成功的新增了 '. $res['count']. '条记录, 新增的记录的ID是: '. $res['id'];

echo '<hr>';

// 查询操作
foreach ($db->read() as $item) {
    print_r($item); echo '<br>';
}

echo '<hr>';

// 查询操作
foreach ($db->read('staff_id, name,position, age', 'age > 50') as $item) {
    print_r($item); echo '<br>';
}

// 更新操作
$data = [
    'age' => 40,
    'position' => '总裁'
];

//$where = 'staff_id = 22';

//echo '成功的更新了: ' . $db->update($data, $where) . ' 条记录';

echo '<hr>';

// 删除操作
$where = 'staff_id = 22';

echo '成功的删除了: ' . $db->delete($where) . ' 条记录';

数据库

-- Adminer 4.7.2 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
  `staff_id` int(3) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(30) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '25' COMMENT '年龄',
  `sex` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:男0:女',
  `position` varchar(20) NOT NULL COMMENT '职位',
  `mobile` char(11) NOT NULL COMMENT '手机号',
  `hiredate` int(10) unsigned NOT NULL COMMENT '入职时间',
  PRIMARY KEY (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `staff` (`staff_id`, `name`, `age`, `sex`, `position`, `mobile`, `hiredate`) VALUES
(1,	'侯亮平',	43,	1,	'反贪局长',	'13576543210',	851356800),
(2,	'祁同伟',	46,	1,	'公安厅长',	'13023458765',	1420041600),
(3,	'高育良',	57,	1,	'政法书记',	'15678789933',	981734400),
(4,	'李达康',	51,	1,	'市委书记',	'13088856222',	1336838400),
(5,	'沙瑞金',	56,	1,	'省委书记',	'18956490987',	1179158400),
(6,	'赵东来',	41,	1,	'公安局长',	'15999999999',	1375891200),
(7,	'高小琴',	36,	0,	'山水庄园',	'18967678877',	1452528000),
(8,	'陈清泉',	48,	1,	'外语学霸',	'13023487666',	1320940800),
(9,	'钟小艾',	38,	0,	'反贪总局',	'13576588333',	1318348800),
(10,	'陆亦可',	35,	0,	'调查处长',	'13099912345',	1218297600),
(11,	'小龙女',	29,	0,	'古墓掌门',	'13989897754',	1378656000),
(12,	'韦小宝',	24,	1,	'太监总管',	'15709887875',	1560927140),
(13,	'郭靖',	44,	1,	'中原大侠',	'13576543422',	1560927140),
(14,	'灭绝师太',	68,	0,	'峨眉掌门',	'18909887655',	1481472000),
(15,	'李莫愁',	37,	0,	'赤炼仙子',	'13878665439',	1481472000),
(16,	'洪七公',	75,	1,	'丐帮帮主',	'13507665428',	1234567890),
(17,	'杨康',	32,	1,	'金国王爷',	'15805512348',	1560993700),
(18,	'梅超风',	49,	0,	'女魔头',	'17765443256',	1560993815),
(19,	'金轮法王',	52,	1,	'蒙古国师',	'13709887878',	1560994207),
(20,	'老顽童',	68,	1,	'全真教师叔',	'13688776548',	1560995043),
(21,	'欧阳克',	37,	1,	'大淫贼',	'19988776676',	1560993700);