当前位置:首页 > 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类,用单例模式或者用依赖容器来管理较好。

总结

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



【说明】本文章由站长整理发布,文章内容不代表本站观点,如文中有侵权行为,请与本站客服联系(QQ:254677821)!