PHP用PDO如何封裝簡單易用的DB類詳解

更新時間：2017年07月30日 09:26:43 作者：火蜥蜴

PDO(php Data Object)是PHP的數(shù)據(jù)對象，它為PHP訪問數(shù)據(jù)庫提供了一種兼容一致的輕量級接口，也就是提供了一個數(shù)據(jù)庫訪問抽象層，這篇文章主要給大家介紹了關于PHP用PDO如何封裝簡單易用的DB類的相關資料，需要的朋友可以參考借鑒，下面來一起看看吧。

前言

PDO擴展為PHP訪問數(shù)據(jù)庫定義了一個輕量級的、一致性的接口，它提供了一個數(shù)據(jù)訪問抽象層，這樣，無論使用什么數(shù)據(jù)庫，都可以通過一致的函數(shù)執(zhí)行查詢和獲取數(shù)據(jù)。PDO隨PHP5.1發(fā)行，在PHP5.0的PECL擴展中也可以使用。

我個人理解：PDO是一個抽象類，為我們提供訪問數(shù)據(jù)的接口方法，下面這篇將給大家介紹關于PHP如何利用PDO封裝簡單易用的DB類，下面話不多說，來一起看看詳細的介紹：

使用

創(chuàng)建測試庫和表

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

運行結(jié)果

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類,用單例模式或者用依賴容器來管理較好。

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持

您可能感興趣的文章: