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 "";$insertId = $db->insert("user", ["name" => "salamander", "created_at" => time()]);echo "insert user {$insertId}";$users = $db->fetchAll("SELECT * FROM user");foreach ($users as $item) { echo "user {$item["id"]} is {$item["name"]} ";} 

运行结果

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}".$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类,用单例模式或者用依赖容器来管理较好。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对网页设计的支持