PHP表操作的封装类

2014-05-30 19:04  4218人阅读  评论 (0)
Tags: phptablemodelorm

数据库表结构

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>
 * @link http://www.dotcoo.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判断是否手机浏览器 >>
<< tcpdump使用简介
豫ICP备09035262号-1