服务器之家

服务器之家 > 正文

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

时间：2021-06-09 16:40     来源/作者：火蜥蜴

前言

pdo扩展为php访问数据库定义了一个轻量级的、一致性的接口，它提供了一个数据访问抽象层，这样，无论使用什么数据库，都可以通过一致的函数执行查询和获取数据。pdo随php5.1发行，在php5.0的pecl扩展中也可以使用。

我个人理解：pdo是一个抽象类，为我们提供访问数据的接口方法，下面这篇将给大家介绍关于php如何利用pdo封装简单易用的db类，下面话不多说，来一起看看详细的介绍：

使用

创建测试库和表

?
1
2
3
4
5
6
7
8
9
10
11
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');

代码测试

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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工具类

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
<?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类,用单例模式或者用依赖容器来管理较好。

总结

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

原文链接：https://segmentfault.com/a/1190000010391179

标签：
PHP PDO 封装 DB类 

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
2021德云社封箱演出完整版 2021年德云社封箱演出在线看
2021德云社封箱演出完整版 2021年德云社封箱演出在线看 2021-03-15
返回顶部