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

创建实体对象：

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

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

插入数据：

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

批量插入数据：

batchInsert($fields,$rows);

查询所有数据：

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

select()->fetchAll());

field自定义：

select('id,nickname')->fetchAll());

where查询：

where('id > ?',50)->select()->fetchAll());

where and条件：

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

where or条件：

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

group分组 having过滤：

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

order排序：

order('r desc,id')->select()->fetchAll());

limit 行数：

跳过30行 返回10行

limitOffset(10,30)->select()->fetchAll());

查询单行：

where('id = ?',6)->select()->fetch());

根据主键查询数据：

find(4));

update更新数据：

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

replace替换数据：

使用了MySQL的REPLACE语句

4,'username' => 'admin4','password' => 'admin4','nickname' => '管理员4',); echo $userTable->replace($user)->rowCount(),"n";

删除数据：

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

分页查询

第2页,每页10行数据：

page(2,10)->select()->fetchAll());

分页查询的总行数：

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

复杂查询：

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());

自增：

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

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

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

save 保存修改：

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

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

生成外表测试数据：

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

获取外表数据：

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,'id,username,nickanem,id') ->fetchAll(PDO::FETCH_UNIQUE)); // 获取外表数据 返回键值对数组 key为id value为username var_dump($userTable->foreignKey($blogs,username') ->fetchAll(PDO::FETCH_KEY_PAIR));

PDOStatement::fetchAll 示例：

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,$r){ return "$id - $username - $password - $r"; }));

Table类源代码：

*/ /** * 模型 */ 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,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); } /** * 删除数据 * @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 ',$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程序设计有所帮助。

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!