本文实例讲述了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数据库表操作的封装类及用法实例详解】相关文章：