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