这里有新鲜出炉的 PHP 设计模式,程序狗速度看过来!
PHP(外文名: Hypertext Preprocessor,中文名:"超文本预处理器")是一种通用开源脚本语言。语法吸收了 C 语言、Java 和 Perl 的特点,入门门槛较低,易于学习,使用广泛,主要适用于 web 开发领域。PHP 的文件后缀名为 php。
PDO(php Data Object) 是 PHP 的数据对象,它为 PHP 访问数据库提供了一种兼容一致的轻量级接口,也就是提供了一个数据库访问抽象层,这篇文章主要给大家介绍了关于 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 类, 用单例模式或者用依赖容器来管理较好。
总结
来源: http://www.phperz.com/article/17/0825/344413.html