本文實例講述了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為usernamevar_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
新聞熱點
疑難解答
圖片精選
