PHP用PDO如何封装简单易用的DB类详解
作者:火蜥蜴 发布时间:2023-11-23 16:05:39
标签:php,pdo,封装db类
前言
PDO扩展为PHP访问数据库定义了一个轻量级的、一致性的接口,它提供了一个数据访问抽象层,这样,无论使用什么数据库,都可以通过一致的函数执行查询和获取数据。PDO随PHP5.1发行,在PHP5.0的PECL扩展中也可以使用。
我个人理解:PDO是一个抽象类,为我们提供访问数据的接口方法,下面这篇将给大家介绍关于PHP如何利用PDO封装简单易用的DB类,下面话不多说,来一起看看详细的介绍:
使用
创建测试库和表
create database db_test;
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(11) NOT NULL,
`created_at` int(10) unsigned NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', 'wang', '1501109027');
INSERT INTO `user` VALUES ('2', 'meng', '1501109026');
INSERT INTO `user` VALUES ('3', 'liu', '1501009027');
INSERT INTO `user` VALUES ('4', 'yuan', '1500109027');
代码测试
require __DIR__ . '/DB.php';
$db = new DB();
$db->__setup([
'dsn'=>'mysql:dbname=db_test;host=localhost',
'username'=>'root',
'password'=>'******',
'charset'=>'utf8'
]);
$user = $db->fetch('SELECT * FROM user where id = :id', ['id' => 1]);
echo $user['name'];
echo "\n";
$insertId = $db->insert('user', ['name' => 'salamander', 'created_at' => time()]);
echo "insert user {$insertId}\n";
$users = $db->fetchAll('SELECT * FROM user');
foreach ($users as $item) {
echo "user {$item['id']} is {$item['name']} \n";
}
运行结果
DB工具类
<?php
/**
* User: Salamander
* Date: 2016/9/2
* Time: 9:16
*/
class DB
{
private $dsn;
private $sth;
private $dbh;
private $user;
private $charset;
private $password;
public $lastSQL = '';
public function __setup($config = array())
{
$this->dsn = $config['dsn'];
$this->user = $config['username'];
$this->password = $config['password'];
$this->charset = $config['charset'];
$this->connect();
}
private function connect()
{
if(!$this->dbh){
$options = array(
\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . $this->charset,
);
$this->dbh = new \PDO($this->dsn, $this->user,
$this->password, $options);
}
}
public function beginTransaction()
{
return $this->dbh->beginTransaction();
}
public function inTransaction()
{
return $this->dbh->inTransaction();
}
public function rollBack()
{
return $this->dbh->rollBack();
}
public function commit()
{
return $this->dbh->commit();
}
function watchException($execute_state)
{
if(!$execute_state){
throw new MySQLException("SQL: {$this->lastSQL}\n".$this->sth->errorInfo()[2], intval($this->sth->errorCode()));
}
}
public function fetchAll($sql, $parameters=[])
{
$result = [];
$this->lastSQL = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchException($this->sth->execute($parameters));
while($result[] = $this->sth->fetch(\PDO::FETCH_ASSOC)){ }
array_pop($result);
return $result;
}
public function fetchColumnAll($sql, $parameters=[], $position=0)
{
$result = [];
$this->lastSQL = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchException($this->sth->execute($parameters));
while($result[] = $this->sth->fetch(\PDO::FETCH_COLUMN, $position)){ }
array_pop($result);
return $result;
}
public function exists($sql, $parameters=[])
{
$this->lastSQL = $sql;
$data = $this->fetch($sql, $parameters);
return !empty($data);
}
public function query($sql, $parameters=[])
{
$this->lastSQL = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchException($this->sth->execute($parameters));
return $this->sth->rowCount();
}
public function fetch($sql, $parameters=[], $type=\PDO::FETCH_ASSOC)
{
$this->lastSQL = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchException($this->sth->execute($parameters));
return $this->sth->fetch($type);
}
public function fetchColumn($sql, $parameters=[], $position=0)
{
$this->lastSQL = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchException($this->sth->execute($parameters));
return $this->sth->fetch(\PDO::FETCH_COLUMN, $position);
}
public function update($table, $parameters=[], $condition=[])
{
$table = $this->format_table_name($table);
$sql = "UPDATE $table SET ";
$fields = [];
$pdo_parameters = [];
foreach ( $parameters as $field=>$value){
$fields[] = '`'.$field.'`=:field_'.$field;
$pdo_parameters['field_'.$field] = $value;
}
$sql .= implode(',', $fields);
$fields = [];
$where = '';
if(is_string($condition)) {
$where = $condition;
} else if(is_array($condition)) {
foreach($condition as $field=>$value){
$parameters[$field] = $value;
$fields[] = '`'.$field.'`=:condition_'.$field;
$pdo_parameters['condition_'.$field] = $value;
}
$where = implode(' AND ', $fields);
}
if(!empty($where)) {
$sql .= ' WHERE '.$where;
}
return $this->query($sql, $pdo_parameters);
}
public function insert($table, $parameters=[])
{
$table = $this->format_table_name($table);
$sql = "INSERT INTO $table";
$fields = [];
$placeholder = [];
foreach ( $parameters as $field=>$value){
$placeholder[] = ':'.$field;
$fields[] = '`'.$field.'`';
}
$sql .= '('.implode(",", $fields).') VALUES ('.implode(",", $placeholder).')';
$this->lastSQL = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchException($this->sth->execute($parameters));
$id = $this->dbh->lastInsertId();
if(empty($id)) {
return $this->sth->rowCount();
} else {
return $id;
}
}
public function errorInfo()
{
return $this->sth->errorInfo();
}
protected function format_table_name($table)
{
$parts = explode(".", $table, 2);
if(count($parts) > 1) {
$table = $parts[0].".`{$parts[1]}`";
} else {
$table = "`$table`";
}
return $table;
}
function errorCode()
{
return $this->sth->errorCode();
}
}
class MySQLException extends \Exception { }
框架中使用建议
在框架中使用DB类,用单例模式或者用依赖容器来管理较好。
来源:https://segmentfault.com/a/1190000010391179
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- SQL> show all --查看所有68个系统变量值 SQL> show user --显示当前连接用户 SQL> s
- 如何用我的国际域名做虚拟域名?config.asp<%Domain_URL = "intels.net&
- Sql Server 中一个非常强大的日期格式化函数: 获得当前系统时间,GETDATE(): 2008年01月08日 星期二 14:59
- 1.11 – 添加缎带修饰网页局部模块中右上角的蓝色缎带修饰是这个网站界面设计中的一个亮点,只要合理的运用CSS、PNG透明图片和绝对定位属
- 应该是很方便的了,支持几乎所有主流浏览器(ie5,6,7,8;ff;傲游;Opera)已更新至可提交录入内容<script type=
- 本文实例讲述了Python删除windows垃圾文件的方法。分享给大家供大家参考。具体如下:#coding:utf-8import os#f
- 在你自己安装了一个新的MySQL服务器后,你需要为MySQL的root用户指定一个目录(缺省无口令),否则如果你忘记这点,你将你的MySQL
- //方法1:$ip = $_SERVER["REMOTE_ADDR"];echo $ip;//方法2:$user_IP
- 如果你是个赛车手,并且按一下按钮就能够立即更换引擎而不需要把车开到车库里去换,那会是什么感觉呢?MySQL数据库为开发人员所做的就好像是按按
- 无论何时,IE总是让页面制作者感到那么的黯然销魂,尤其是IE6,IE7次之,虽然IE8已经做了很大的改进,但由于XP用户的数量实在太大,而且
- 当成功安装了PHP,MYSQL后,我们一般要安装phpMyAdmin来管理你的mysql。本文介绍了phpMyAdmin 2.10.2的配置
- 首先需要安装Win32-ODBC模块,具体的步骤如下:1:从TOOLS栏目中下载Win32-ODBC.zip,下载完后用winzip解开到一
- < ?php if (!function_exists("T7FC56270E7A70FA81A5935B72EACBE29
- 这个主要应用于,获取用户输入的时候,防止用户不小心,多输入了一个空格,导致验证无法通过,多用于用户名跟密码的,好多情况下,大家复制的winr
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN&
- 本文实例讲述了python新式类和经典类的区别。分享给大家供大家参考,具体如下:新式类就是 class person(objec
- 什么是Three.js? 如果你正在读这篇文章,你可能对Three.js有一定的了解,那我们来简单地介绍下Three.js是什么.Three
- 由于ajax在跨域的访问上有问题,目前最好的方法是做代理.写了个代理程序和心得为了做ajax的代理,研究了下服务器端的xmlhttp并和客户
- 如果您还不太了解XML技术,您可以先看看此文:XML的语法、结构以及相关的一些技术 及 XML DOM介绍和例子XML中 CDATA的作用:
- 前段时间做一个小项目碰到了一个导航制作的方式然后突然想到曾经很久以前看到的梯形状的不规则导航,就尝试做了一下。结果碰到了几个问题,后来在同事