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

require 'Table.php';

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

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

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

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

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

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

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

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

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

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

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

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

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

$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";

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";

// 修改$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);  }}

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

// 获取映射数据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/** * @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);  }}