《PHP實例：PHP數據庫表操作的封裝類及用法實例詳解》要點：
本文介紹了PHP實例：PHP數據庫表操作的封裝類及用法實例詳解，希望對您有用。如果有疑問，可以聯系我們。
PHP編程本文實例講述了PHP數據庫表操作的封裝類及用法.分享給大家供大家參考,具體如下：
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;
PHP編程設置字符編碼：
PHP編程
header('Content-Type: text/html; charset=utf-8');
PHP編程引入Table類：
PHP編程
require 'Table.php';
PHP編程設置數據庫參數：
PHP編程
Table::$__host = '127.0.0.1:3306';
Table::$__user = 'root';
Table::$__pass = '123456';
Table::$__name = 'test';
Table::$__charset = 'utf8';
PHP編程創建實體對象：
PHP編程Table類有三個參數: $table, $pk, $pdo=null
PHP編程$table: 表名稱.
$pk: 主鍵名稱. 不支持聯合主鍵
$pdo: 獨立的PDO對象. 一般不需要傳
Notice: Table類是表操作的封裝,不是Model層的基類,所以不支持表前綴,表前綴應該在Model層實現
PHP編程
$userTable = new Table('test_blog');
$blogTable = new Table('test_blog');
PHP編程插入數據：
PHP編程
$user = array(
'username' => "admin1",
'password' => "admin1",
'nickname' => "管理員1",
'r' => mt_rand(0, 5),
);
echo $userTable->insert($user)->rowCount(), "\n";
echo $userTable->lastInsertId(), "\n";
PHP編程批量插入數據：
PHP編程
$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);
PHP編程查詢所有數據：
PHP編程select方法返回一個PDOStatement對象, fetchAll返回多行, fetch返回單行
PHP編程
var_dump($userTable->select()->fetchAll());
PHP編程field自定義：
PHP編程
var_dump($userTable->select('id,nickname')->fetchAll());
PHP編程where查詢：
PHP編程
var_dump($userTable->where('id > ?', 50)->select()->fetchAll());
PHP編程where and條件：
PHP編程
var_dump($userTable->where('id > ?', 6)->where('id in (?)', array(5,7,9))
->select()->fetchAll());
PHP編程where or條件：
PHP編程
var_dump($userTable->where('id = ? OR id = ?', 6, 8)->select()->fetchAll());
PHP編程group分組 having過濾：
PHP編程
var_dump($userTable->group('r')->having('c between ? and ?', 10, 20)
->select('*, r, count(*) as c')->fetchAll());
PHP編程order排序：
PHP編程
var_dump($userTable->order('r desc, id')->select()->fetchAll());
PHP編程limit 行數：
PHP編程跳過30行 返回10行
PHP編程
var_dump($userTable->limitOffset(10, 30)->select()->fetchAll());
PHP編程查詢單行：
PHP編程
var_dump($userTable->where('id = ?', 6)->select()->fetch());
PHP編程根據主鍵查詢數據：
PHP編程
var_dump($userTable->find(4));
PHP編程update更新數據：
PHP編程
$user = array( 'username' => 'admin4-1', 'nickname' => '管理員4-1', );
echo $userTable->where('id = ?', 4)->update($user)->rowCount(), "\n";
PHP編程replace替換數據：
PHP編程使用了MySQL的REPLACE語句
PHP編程
$user = array(
'id' => 4,
'username' => 'admin4',
'password' => 'admin4',
'nickname' => '管理員4',
'r' => mt_rand(0, 5),
);
echo $userTable->replace($user)->rowCount(), "\n";
PHP編程刪除數據：
PHP編程
echo $userTable->where('id = ?', 4)->delete()->rowCount(), "\n";
PHP編程分頁查詢
PHP編程第2頁, 每頁10行數據：
PHP編程
var_dump($userTable->page(2, 10)->select()->fetchAll());
PHP編程分頁查詢的總行數：
PHP編程
$userTable->where('r=?', 3)->order('id desc')->page(2, 10)
->select()->fetchAll();
echo $userTable->count(), "\n";
PHP編程復雜查詢：
PHP編程
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());
PHP編程自增：
PHP編程
$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));
PHP編程自增,并獲得自增后的值：
PHP編程
$id = 2;
// 加一
echo $userTable->where('id = ?', $id)->incr('r'), "\n";
// 減一
echo $userTable->where('id = ?', $id)->incr('r', -1), "\n";
PHP編程save 保存修改：
PHP編程判斷數據中是否存在主鍵字段,如果存在主鍵字段就update數據,反之insert數據
PHP編程
// 修改
$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));
PHP編程生成外表測試數據：
PHP編程
$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);
}
}
PHP編程Table類不支持JOIN查詢
PHP編程需要的朋友可以手寫sql語句,使用query方法來執行.或者自己修改Table類來支持JOIN
PHP編程獲取外表數據：
PHP編程
$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));
PHP編程PDOStatement::fetchAll 示例：
PHP編程
// 獲取映射數據
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";
}));
PHP編程Table類源代碼：
PHP編程
<?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);
}
}
PHP編程github地址：
PHP編程https://github.com/dotcoo/php/blob/master/Table/Table.php
PHP編程更多關于PHP相關內容感興趣的讀者可查看本站專題：《PHP+MongoDB數據庫操作技巧大全》、《PHP基于pdo操作數據庫技巧總結》、《php面向對象程序設計入門教程》、《php字符串(string)用法總結》、《php+mysql數據庫操作入門教程》及《php常見數據庫操作技巧匯總》
PHP編程希望本文所述對大家PHP程序設計有所幫助.
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5703.html
