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

　　总结

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