欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

PHP用PDO如何封装简单易用的DB类详解

程序员文章站 2024-03-11 18:16:37
前言 pdo扩展为php访问数据库定义了一个轻量级的、一致性的接口,它提供了一个数据访问抽象层,这样,无论使用什么数据库,都可以通过一致的函数执行查询和获取数据。pdo随...

前言

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

运行结果

PHP用PDO如何封装简单易用的DB类详解

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

总结

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