PHP数据库表操作的封装类及用法实例详解

前端技术 2023/09/01 PHP

本文实例讲述了PHP数据库表操作的封装类及用法。分享给大家供大家参考，具体如下：

数据库表结构：

CREATE TABLE `test_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(45) NOT NULL,
`password` varchar(45) NOT NULL,
`nickname` varchar(45) NOT NULL,
`r` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `test_blog` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `title` varchar(45) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

设置字符编码：

header(\'Content-Type: text/html; charset=utf-8\');

引入Table类：

require \'Table.php\';

设置数据库参数：

Table::$__host = \'127.0.0.1:3306\';
Table::$__user = \'root\';
Table::$__pass = \'123456\';
Table::$__name = \'test\';
Table::$__charset = \'utf8\';

创建实体对象：

Table类有三个参数: $table, $pk, $pdo=null

$table: 表名称.
$pk: 主键名称. 不支持联合主键
$pdo: 独立的PDO对象. 一般不需要传
Notice: Table类是表操作的封装,不是Model层的基类,所以不支持表前缀,表前缀应该在Model层实现

$userTable = new Table(\'test_blog\');
$blogTable = new Table(\'test_blog\');

插入数据：

$user = array(
    \'username\' => \"admin1\",
    \'password\' => \"admin1\",
    \'nickname\' => \"管理员1\",
    \'r\' => mt_rand(0, 5),
);
echo $userTable->insert($user)->rowCount(), \"\\n\";
echo $userTable->lastInsertId(), \"\\n\";

批量插入数据：

$fields = array(\'username\',\'password\',\'nickname\',\'r\');
for ($i=2; $i<=100; $i++) {
  $rows[] = array(\"admin$i\", \"admin$i\", \"管理员$i\", mt_rand(0, 5));
}
$userTable->batchInsert($fields, $rows);

查询所有数据：

select方法返回一个PDOStatement对象, fetchAll返回多行, fetch返回单行

var_dump($userTable->select()->fetchAll());

field自定义：

var_dump($userTable->select(\'id,nickname\')->fetchAll());

where查询：

var_dump($userTable->where(\'id > ?\', 50)->select()->fetchAll());

where and条件：

var_dump($userTable->where(\'id > ?\', 6)->where(\'id in (?)\', array(5,7,9))
  ->select()->fetchAll());

where or条件：

var_dump($userTable->where(\'id = ? OR id = ?\', 6, 8)->select()->fetchAll());

group分组 having过滤：

var_dump($userTable->group(\'r\')->having(\'c between ? and ?\', 10, 20)
  ->select(\'*, r, count(*) as c\')->fetchAll());

order排序：

var_dump($userTable->order(\'r desc, id\')->select()->fetchAll());

limit 行数：

跳过30行 返回10行

var_dump($userTable->limitOffset(10, 30)->select()->fetchAll());

查询单行：

var_dump($userTable->where(\'id = ?\', 6)->select()->fetch());

根据主键查询数据：

var_dump($userTable->find(4));

update更新数据：

$user = array( \'username\' => \'admin4-1\', \'nickname\' => \'管理员4-1\', );
echo $userTable->where(\'id = ?\', 4)->update($user)->rowCount(), \"\\n\";

replace替换数据：

使用了MySQL的REPLACE语句

$user = array(
    \'id\' => 4,
    \'username\' => \'admin4\',
    \'password\' => \'admin4\',
    \'nickname\' => \'管理员4\',
    \'r\' => mt_rand(0, 5),
);
echo $userTable->replace($user)->rowCount(), \"\\n\";

删除数据：

echo $userTable->where(\'id = ?\', 4)->delete()->rowCount(), \"\\n\";

分页查询

第2页, 每页10行数据：

var_dump($userTable->page(2, 10)->select()->fetchAll());

分页查询的总行数：

$userTable->where(\'r=?\', 3)->order(\'id desc\')->page(2, 10)
  ->select()->fetchAll();
echo $userTable->count(), \"\\n\";

复杂查询：

var_dump($userTable->where(\'id > ?\', 1)->where(\'id < ?\', 100)
  ->group(\'r\')->having(\'c between ? and ?\', 1, 100)->having(\'c > ?\', 1)
  ->order(\'c desc\')->page(2, 3)->select(\'*, count(*) as c\')->fetchAll());

自增：

$id = 2;
// 加一
var_dump($userTable->where(\'id = ?\', $id)->plus(\'r\')->find($id));
// 减一
var_dump($userTable->where(\'id = ?\', $id)->plus(\'r\', -1)->find($id));
// 多列
var_dump($userTable->where(\'id = ?\', $id)->plus(\'r\', 1, \'r\', -1)->find($id));

自增,并获得自增后的值：

$id = 2;
// 加一
echo $userTable->where(\'id = ?\', $id)->incr(\'r\'), \"\\n\";
// 减一
echo $userTable->where(\'id = ?\', $id)->incr(\'r\', -1), \"\\n\";

save 保存修改：

判断数据中是否存在主键字段,如果存在主键字段就update数据,反之insert数据

// 修改
$user = array(
  \'id\' => 3,
  \'nickname\' => \'管理员3-3\',
);
echo $userTable->save($user)->rowCount(), \"\\n\";
var_dump($userTable->find(3));
// 添加
$user = array(
    \'username\' => \'admin11\',
    \'password\' => \'admin11\',
    \'nickname\' => \'管理员11\',
    \'r\' => mt_rand(0, 5),
);
echo $userTable->save($user)->rowCount(), \"\\n\";
$id = $userTable->lastInsertId();
var_dump($userTable->find($id));

生成外表测试数据：

$users = $userTable->select(\'id\')->fetchAll();
$id = 0;
foreach ($users as $user) {
  for ($i=0; $i<10; $i++) {
    $id++;
    $blog = array(
        \'user_id\' => $user[\'id\'],
        \'title\' => \"blog$id\",
    );
    $blogTable->insert($blog);
  }
}

Table类不支持JOIN查询

需要的朋友可以手写sql语句,使用query方法来执行.或者自己修改Table类来支持JOIN

获取外表数据：

$blogs = $blogTable->where(\'id in (?)\', array(1,12,23,34,56,67,78,89,90,101))
  ->select()->fetchAll();
// 获取外表数据 key为外表id value为外表行数据
var_dump($userTable->foreignKey($blogs, \'user_id\')
  ->fetchAll(PDO::FETCH_UNIQUE));
var_dump($userTable->foreignKey($blogs, \'user_id\', \'*,id\')
  ->fetchAll(PDO::FETCH_UNIQUE));
var_dump($userTable->foreignKey($blogs, \'user_id\', \'id,username,nickanem,id\')
  ->fetchAll(PDO::FETCH_UNIQUE));
// 获取外表数据 返回键值对数组 key为id value为username
var_dump($userTable->foreignKey($blogs, \'user_id\', \'id,username\')
  ->fetchAll(PDO::FETCH_KEY_PAIR));

PDOStatement::fetchAll 示例：

// 获取映射数据
var_dump($userTable->select(\'*, id\')->fetchAll(PDO::FETCH_UNIQUE));
// 获取数组
var_dump($userTable->select(\'nickname\')->fetchAll(PDO::FETCH_COLUMN));
// 获取键值对
var_dump($userTable->select(\'id, nickname\')->fetchAll(PDO::FETCH_KEY_PAIR));
// 获取数据分组
var_dump($userTable->select(\'r, id, nickname\')->fetchAll(PDO::FETCH_GROUP));
// 获取数据分组
var_dump($userTable->select(\'r, id\')->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_COLUMN));
// 获取数据分组
var_dump($userTable->select(\'r, nickname\')->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_KEY_PAIR));
// 获取对象 指定获取方式，将结果集中的每一行作为一个属性名对应列名的对象返回。
var_dump($userTable->select()->fetchAll(PDO::FETCH_OBJ));
// 获取对象 指定获取方式，返回一个所请求类的新实例，映射列到类中对应的属性名。
// Note: 如果所请求的类中不存在该属性，则调用 __set() 魔术方法
var_dump($userTable->select()->fetchAll(PDO::FETCH_CLASS));
// 获取对象 指定获取方式，更新一个请求类的现有实例，映射列到类中对应的属性名。
var_dump($userTable->select()->fetchAll(PDO::FETCH_INTO));
// 获取自定义行
var_dump($userTable->select()->fetchAll(PDO::FETCH_FUNC, function($id, $username, $password, $r){
  return array(\'id\'=>$id, \'name\'=>\"$username - $password - $r\");
}));
// 获取单一值
var_dump($userTable->select()->fetchAll(PDO::FETCH_FUNC, function($id, $username, $password, $r){
  return \"$id - $username - $password - $r\";
}));

Table类源代码：

<?php
/**
 * @author dotcoo zhao <dotcoo at 163 dot com>
 */
/**
 * 模型
*/
class Table {
  /**
   * @var PDO
   */
  public static $__pdo = null;      // 默认PDO对象
  public static $__host = \'127.0.0.1\';  // 默认主机
  public static $__user = \'root\';     // 默认账户
  public static $__pass = \'123456\';    // 默认密码
  public static $__name = \'test\';     // 默认数据库名称
  public static $__charset = \'utf8\';   // 默认字符集
  /**
   * @var PDO
   */
  public $_pdo = null;          // PDO对象
  public $_table = null;         // 表名
  public $_pk = \'id\';           // paramry
  public $_where = array();        // where
  public $_where_params = array();    // where params
  public $_count_where = array();     // count where
  public $_count_where_params = array(); // count where params
  public $_group = \'\';          // group
  public $_having = array();       // having
  public $_having_params = array();    // having params
  public $_order = null;         // order
  public $_limit = null;         // limit
  public $_offset = null;         // offset
  public $_for_update = \'\';        // read lock
  public $_lock_in_share_model = \'\';   // write lock
  /**
   * Table Construct
   * @param string $table_name
   * @param string $pk
   * @param string $prefix
   * @param PDO $pdo
   */
  function __construct($table=null, $pk=null, PDO $pdo=null) {
    $this->_table = isset($table) ? $table : $this->_table;
    $this->_pk = isset($pk) ? $pk : $this->_pk;
    $this->_pdo = $pdo;
  }
  /**
   * @return PDO
   */
  public function getPDO() {
    if (isset($this->_pdo)) {
      return $this->_pdo;
    }
    if (isset(self::$__pdo)) {
      return self::$__pdo;
    }
    $dsn = sprintf(\"mysql:host=%s;dbname=%s;charset=%s;\", self::$__host, self::$__name, self::$__charset);
    $options = array(
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    );
    return self::$__pdo = new PDO($dsn, self::$__user, self::$__pass, $options);
  }
  /**
   * 执行语句
   * @param string $sql
   * @return PDOStatement
   */
  public function query($sql) {
    $params = func_get_args();
    array_shift($params);
    return $this->queryParams($sql, $params);
  }
  /**
   * 执行语句
   * @param string $sql
   * @return PDOStatement
   */
  public function queryParams($sql, array $params) {
    $sqls = explode(\'?\', $sql);
    $sql_new = array_shift($sqls);
    $params_new = array();
    foreach ($sqls as $i => $sql_item) {
      if (is_array($params[$i])) {
        $sql_new .= str_repeat(\'?,\', count($params[$i])-1).\'?\'.$sql_item;
        $params_new = array_merge($params_new, $params[$i]);
      } else {
        $sql_new .= \'?\'.$sql_item;
        $params_new[] = $params[$i];
      }
    }
    $stmt = $this->getPDO()->prepare($sql_new);
    foreach ($params_new as $i => $param) {
      switch (gettype($param)) {
        case \'integer\':
          $stmt->bindValue($i+1, $param, PDO::PARAM_INT);
          break;
        case \'NULL\':
          $stmt->bindValue($i+1, $param, PDO::PARAM_NULL);
          break;
        default :
          $stmt->bindValue($i+1, $param);
      }
    }
//   echo $sql_new, \"\\n\"; var_dump($params_new); // exit();
    $stmt->executeResult = $stmt->execute();
    $this->reset();
    return $stmt;
  }
  /**
   * 查询数据
   * @param string $field
   * @return PDOStatement
   */
  public function select($columns=\'*\') {
    $params = array_merge($this->_where_params, $this->_having_params);
    $sql = \"SELECT $columns FROM `{$this->_table}`\";
    $sql .= empty($this->_where) ? \'\' : \' WHERE \'. implode(\' AND \', $this->_where);
    $sql .= empty($this->_group) ? \'\' : \' GROUP BY \'. $this->_group;
    $sql .= empty($this->_having) ? \'\' : \' HAVING \'. implode(\' AND \', $this->_having);
    $sql .= empty($this->_order) ? \'\' : \' ORDER BY \'. $this->_order;
    if (isset($this->_limit)) {
      $sql .= \' LIMIT ?\';
      $params[] = $this->_limit;
      if (isset($this->_offset)) {
        $sql .= \' OFFSET ?\';
        $params[] = $this->_offset;
      }
    }
    $sql .= $this->_for_update;
    $sql .= $this->_lock_in_share_model;
    $this->_count_where = $this->_where;
    $this->_count_where_params = $this->_where_params;
    return $this->queryParams($sql, $params);
  }
  /**
   * 添加数据
   * @param array $data
   * @return PDOStatement
   */
  public function insert(array $data) {
    $sql = \"INSERT `{$this->_table}` SET\";
    $params = array();
    foreach ($data as $col=>$val) {
      $sql .= \" `$col` = ?,\";
      $params[] = $val;
    }
    $sql{strlen($sql)-1} = \' \';
    return $this->queryParams($sql, $params);
  }
  /**
   * 批量插入数据
   * @param array $names
   * @param array $rows
   * @param number $batch
   * @return Table
   */
  public function batchInsert(array $fields, array $rows, $batch=1000) {
    $i = 0;
    $sql = \"INSERT `{$this->_table}` (`\".implode(\'`, `\', $fields).\"`) VALUES \";
    foreach ($rows as $row) {
      $i++;
      $sql .= \"(\'\".implode(\"\',\'\", array_map(\'addslashes\', $row)).\"\'),\";
      if ($i >= $batch) {
        $sql{strlen($sql)-1} = \' \';
        $this->query($sql);
        $i = 0;
        $sql = \"INSERT `{$this->_table}` (`\".implode(\'`, `\', $fields).\"`) VALUES \";
      }
    }
    if ($i > 0) {
      $sql{strlen($sql)-1} = \' \';
      $this->query($sql);
    }
    return $this;
  }
  /**
   * 更新数据
   * @param array $data
   * @return PDOStatement
   */
  public function update(array $data) {
    $sql = \"UPDATE `{$this->_table}` SET\";
    $params = array();
    foreach ($data as $col=>$val) {
      $sql .= \" `$col` = ?,\";
      $params[] = $val;
    }
    $sql{strlen($sql)-1} = \' \';
    $sql .= empty($this->_where) ? \'\' : \'WHERE \'. implode(\' AND \', $this->_where);
    $params = array_merge($params, $this->_where_params);
    return $this->queryParams($sql, $params);
  }
  /**
   * 替换数据
   * @param array $data
   * @return PDOStatement
   */
  public function replace(array $data) {
    $sql = \"REPLACE `{$this->_table}` SET\";
    $params = array();
    foreach ($data as $col=>$val) {
      $sql .= \" `$col` = ?,\";
      $params[] = $val;
    }
    $sql{strlen($sql)-1} = \' \';
    $sql .= empty($this->_where) ? \'\' : \'WHERE \'. implode(\' AND \', $this->_where);
    $params = array_merge($params, $this->_where_params);
    return $this->queryParams($sql, $params);
  }
  /**
   * 删除数据
   * @return PDOStatement
   */
  public function delete() {
    $sql = \"DELETE FROM `{$this->_table}`\";
    $sql .= empty($this->_where) ? \'\' : \' WHERE \'. implode(\' AND \', $this->_where);
    return $this->queryParams($sql, $this->_where_params);
  }
  /**
   * 重置所有
   * @return Table
   */
  public function reset() {
    $this->_where = array();
    $this->_where_params = array();
    $this->_group = null;
    $this->_having = array();
    $this->_having_params = array();
    $this->_order = null;
    $this->_limit = null;
    $this->_offset = null;
    $this->_for_update = \'\';
    $this->_lock_in_share_model = \'\';
    return $this;
  }
  /**
   * where查询条件
   * @param string $format
   * @return Table
   */
  public function where($format) {
    $args = func_get_args();
    array_shift($args);
    $this->_where[] = $format;
    $this->_where_params = array_merge($this->_where_params, $args);
    return $this;
  }
  /**
   * group分组
   * @param string $columns
   * @return Table
   */
  public function group($columns) {
    $this->_group = $columns;
    return $this;
  }
  /**
   * having过滤条件
   * @param string $format
   * @return Table
   */
  public function having($format) {
    $args = func_get_args();
    array_shift($args);
    $this->_having[] = $format;
    $this->_having_params = array_merge($this->_having_params, $args);
    return $this;
  }
  /**
   * order排序
   * @param string $columns
   * @return Table
   */
  public function order($order) {
    $this->_order = $order;
    return $this;
  }
  /**
   * limit数据偏移
   * @param number $offset
   * @param number $limit
   * @return Table
   */
  public function limitOffset($limit, $offset=null) {
    $this->_limit = $limit;
    $this->_offset = $offset;
    return $this;
  }
  /**
   * 独占锁，不可读不可写
   * @return Table
   */
  public function forUpdate() {
    $this->forUpdate = \' FOR UPDATE\';
    return $this;
  }
  /**
   * 共享锁，可读不可写
   * @return Table
   */
  public function lockInShareMode() {
    $this->_lock_in_share_model = \' LOCK IN SHARE MODE\';
    return $this;
  }
  /**
   * 事务开始
   * @return bool
   */
  public function begin() {
    return $this->getPDO()->beginTransaction();
  }
  /**
   * 事务提交
   * @return bool
   */
  public function commit() {
    return $this->getPDO()->commit();
  }
  /**
   * 事务回滚
   * @return bool
   */
  public function rollBack() {
    return $this->getPDO()->rollBack();
  }
  /**
   * page分页
   * @param number $page
   * @param number $pagesize
   * @return Table
   */
  public function page($page, $pagesize = 15) {
    $this->_limit = $pagesize;
    $this->_offset = ($page - 1) * $pagesize;
    return $this;
  }
  /**
   * 获取自增ID
   * @return int
   */
  public function lastInsertId() {
    return $this->getPDO()->lastInsertId();
  }
  /**
   * 获取符合条件的行数
   * @return int
   */
  public function count() {
    $sql = \"SELECT count(*) FROM `{$this->_table}`\";
    $sql .= empty($this->_count_where) ? \'\' : \' WHERE \'. implode(\' AND \', $this->_count_where);
    return $this->queryParams($sql, $this->_count_where_params)->fetchColumn();
  }
  /**
   * 将选中行的指定字段加一
   * @param string $col
   * @param number $val
   * @return Table
   */
  public function plus($col, $val = 1) {
    $sets = array(\"`$col` = `$col` + $val\");
    $args = array_slice(func_get_args(), 2);
    while (count($args) > 1) {
      $col = array_shift($args);
      $val = array_shift($args);
      $sets[] = \"`$col` = `$col` + $val\";
    }
    $sql = \"UPDATE `{$this->_table}` SET \".implode(\', \', $sets);
    $sql .= empty($this->_where) ? \'\' : \' WHERE \'. implode(\' AND \', $this->_where);
    $params = array_merge(array($val), $this->_where_params);
    $this->queryParams($sql, $params);
    return $this;
  }
  /**
   * 将选中行的指定字段加一
   * @param string $col
   * @param number $val
   * @return int
   */
  public function incr($col, $val = 1) {
    $sql = \"UPDATE `{$this->_table}` SET `$col` = last_insert_id(`$col` + ?)\";
    $sql .= empty($this->_where) ? \'\' : \' WHERE \'. implode(\' AND \', $this->_where);
    $params = array_merge(array($val), $this->_where_params);
    $this->queryParams($sql, $params);
    return $this->getPDO()->lastInsertId();
  }
  /**
   * 根据主键查找行
   * @param number $id
   * @return array
   */
  public function find($id) {
    return $this->where(\"`{$this->_pk}` = ?\", $id)->select()->fetch();
  }
  /**
   * 保存数据,自动判断是新增还是更新
   * @param array $data
   * @return PDOStatement
   */
  public function save(array $data) {
    if (array_key_exists($this->_pk, $data)) {
      $pk_val = $data[$this->_pk];
      unset($data[$this->_pk]);
      return $this->where(\"`{$this->_pk}` = ?\", $pk_val)->update($data);
    } else {
      return $this->insert($data);
    }
  }
  /**
   * 获取外键数据
   * @param array $rows
   * @param string $fkey
   * @param string $field
   * @param string $key
   * @return PDOStatement
   */
  public function foreignKey(array $rows, $fkey, $field=\'*\') {
    $ids = array(); foreach($rows as $row) { $ids[] = $row[$fkey]; }
//   $ids = array_column($rows, $fkey);
    if (empty($ids)) {
      return new PDOStatement();
    }
    return $this->where(\"`{$this->_pk}` in (?)\", $ids)->select($field);
  }
}

github地址：

https://github.com/dotcoo/php/blob/master/Table/Table.php

更多关于PHP相关内容感兴趣的读者可查看本站专题：《PHP+MongoDB数据库操作技巧大全》、《PHP基于pdo操作数据库技巧总结》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》

希望本文所述对大家PHP程序设计有所帮助。

本文地址：https://www.stayed.cn/item/2107

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案