本文实例讲述了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());
新闻热点
疑难解答