本文实例讲述了PHP的PDO常用类库。分享给大家供大家参考，具体如下：
1、Db.class.php 连接数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
<?php// 连接数据库class Db { static public function getDB() { try { $pdo = new PDO(DB_DSN, DB_USER, DB_PWD); $pdo->setAttribute(PDO::ATTR_PERSISTENT, true); // 设置数据库连接为持久连接 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 设置抛出错误 $pdo->setAttribute(PDO::ATTR_ORACLE_NULLS, true); // 设置当字符串为空转换为 SQL 的 NULL $pdo->query('SET NAMES utf8'); // 设置数据库编码 } catch (PDOException $e) { exit('数据库连接错误，错误信息：'. $e->getMessage()); } return $pdo; }}?> |
2、Model.class.php 数据库操作类
|
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
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
|
<?php/*** 数据库操作类库* author Lee.* Last modify $Date: 2012-1-19 13:59；04 $*/class M { private $_db; //数据库句柄 public $_sql; //SQL语句 /** * 构造方法 */ public function __construct() { $this->_db = Db::getDB(); } /** * 数据库添加操作 * @param string $tName 表名 * @param array $field 字段数组 * @param array $val 值数组 * @param bool $is_lastInsertId 是否返回添加ID * @return int 默认返回成功与否，$is_lastInsertId 为true，返回添加ID */ public function insert($tName, $fields, $vals, $is_lastInsertId=FALSE) { try { if (!is_array($fields) || !is_array($vals)) exit($this->getError(__FUNCTION__, __LINE__)); $fields = $this->formatArr($fields); $vals = $this->formatArr($vals, false); $tName = $this->formatTabName($tName); $this->_sql = "INSERT INTO {$tName} ({$fields}) VALUES ({$vals})"; if (!$is_lastInsertId) { $row = $this->_db->exec($this->_sql); return $row; } else { $this->_db->exec($this->_sql); $lastId = (int)$this->_db->lastInsertId(); return $lastId; } } catch (PDOException $e) { exit($e->getMessage()); } } /** * 数据库修改操作 * @param string $tName 表名 * @param array $field 字段数组 * @param array $val 值数组 * @param string $condition 条件 * @return int 受影响的行数 */ public function update($tName, $fieldVal, $condition) { try { if (!is_array($fieldVal) || !is_string($tName) || !is_string($condition)) exit($this->getError(__FUNCTION__, __LINE__)); $tName = $this->formatTabName($tName); $upStr = ''; foreach ($fieldVal as $k=>$v) { $upStr .= '`'.$k . '`=' . '\'' . $v . '\'' . ','; } $upStr = rtrim($upStr, ','); $this->_sql = "UPDATE {$tName} SET {$upStr} WHERE {$condition}"; $row = $this->_db->exec($this->_sql); return $row; } catch (PDOException $e) { exit($e->getMessage()); } } /** * 数据库删除操作（注：必须添加 where 条件） * @param string $tName 表名 * @param string $condition 条件 * @return int 受影响的行数 */ public function del($tName, $condition) { try { if (!is_string($tName) || !is_string($condition)) exit($this->getError(__FUNCTION__, __LINE__)); $tName= $this->formatTabName($tName); $this->_sql = "DELETE FROM {$tName} WHERE {$condition}"; $row = $this->_db->exec($this->_sql); return $row; } catch (PDOException $e) { exit($e->getMessage()); } } /** * 返回表总个数 * @param string $tName 表名 * @param string $condition 条件 * @return int */ public function total($tName, $condition='') { try { if (!is_string($tName)) exit($this->getError(__FUNCTION__, __LINE__)); $tName = $this->formatTabName($tName); $this->_sql = "SELECT COUNT(*) AS total FROM {$tName}" . ($condition=='' ? '' : ' WHERE ' . $condition); $re = $this->_db->query($this->_sql); foreach ($re as $v) { $total = $v['total']; } return (int)$total; } catch (PDOException $e) { exit($e->getMessage()); } } /** * 数据库删除多条数据 * @param string $tName 表名 * @param string $field 依赖字段 * @param array $ids 删除数组 * @return int 受影响的行数 */ public function delMulti($tName, $field, $ids) { try { if (!is_string($tName) || !is_array($ids)) exit($this->getError(__FUNCTION__, __LINE__)); $delStr = ''; $tName = $this->formatTabName($tName); $field = $this->formatTabName($field); foreach ($ids as $v) { $delStr .= $v . ','; } $delStr = rtrim($delStr, ','); $this->_sql = "DELETE FROM {$tName} WHERE {$field} IN ({$delStr})"; $row = $this->_db->exec($this->_sql); return $row; } catch (PDOException $e) { exit($e->getMessage()); } } /** * 获取表格的最后主键（注：针对 INT 类型） * @param string $tName 表名 * @return int */ public function insertId($tName) { try { if (!is_string($tName)) exit($this->getError(__FUNCTION__, __LINE__)); $this->_sql = "SHOW TABLE STATUS LIKE '{$tName}'"; $result = $this->_db->query($this->_sql); $insert_id = 0; foreach ($result as $v) { $insert_id = $v['Auto_increment']; } return (int)$insert_id; } catch (PDOException $e) { exit($e->getMessage()); } } /** * 检查数据是否已经存在（依赖条件） * @param string $tName 表名 * @param string $field 依赖的字段 * @return bool */ public function exists($tName, $condition) { try { if (!is_string($tName) || !is_string($condition)) exit($this->getError(__FUNCTION__, __LINE__)); $tName = $this->formatTabName($tName); $this->_sql = "SELECT COUNT(*) AS total FROM {$tName} WHERE {$condition}"; $result = $this->_db->query($this->_sql); foreach ($result as $v) { $b = $v['total']; } if ($b) { return true; } else { return false; } } catch (PDOException $e) { exit($e->getMessage()); } } /** * 检查数据是否已经存在（依赖 INT 主键） * @param string $tName 表名 * @param string $primary 主键 * @param int $id 主键值 * @return bool */ public function existsByPK($tName, $primary, $id) { try { if (!is_string($tName) || !is_string($primary) || !is_int($id)) exit($this->getError(__FUNCTION__, __LINE__)); $tName = $this->formatTabName($tName); $this->_sql = "SELECT COUNT(*) AS total FROM {$tName} WHERE {$primary} = ". $id; $result = $this->_db->query($this->_sql); foreach ($result as $v) { $b = $v['total']; } if ($b) { return true; } else { return false; } } catch (PDOException $e) { exit($e->getMessage()); } } /** * 预处理删除（注：针对主键为 INT 类型，推荐使用） * @param string $tName 表名 * @param string $primary 主键字段 * @param int or array or string $ids 如果是删除一条为 INT，多条为 array，删除一个范围为 string * @return int 返回受影响的行数 */ public function delByPK($tName, $primary, $ids, $mult=FALSE) { try { if (!is_string($tName) || !is_string($primary) || (!is_int($ids) && !is_array($ids) && !is_string($ids)) || !is_bool($mult)) exit($this->getError(__FUNCTION__, __LINE__)); $tName = $this->formatTabName($tName); $stmt = $this->_db->prepare("DELETE FROM {$tName} WHERE {$primary}=?"); if (!$mult) { $stmt->bindParam(1, $ids); $row = $stmt->execute(); } else { if (is_array($ids)) { $row = 0; foreach ($ids as $v) { $stmt->bindParam(1, $v); if ($stmt->execute()) { $row++; } } } elseif (is_string($ids)) { if (!strpos($ids, '-')) exit($this->getError(__FUNCTION__, __LINE__)); $split = explode('-', $ids); if (count($split)!=2 || $split[0]>$split[1]) exit($this->getError(__FUNCTION__, __LINE__)); $i = null; $count = $split[1]-$split[0]+1; for ($i=0; $i<$count; $i++) { $idArr[$i] = $split[0]++; } $idStr = ''; foreach ($idArr as $id) { $idStr .= $id . ','; } $idStr = rtrim($idStr, ','); $this->_sql ="DELETE FROM {$tName} WHERE {$primary} in ({$idStr})"; $row = $this->_db->exec($this->_sql); } } return $row; } catch (PDOException $e) { exit($e->getMessage()); } } /** * 返回单个字段数据或单条记录 * @param string $tName 表名 * @param string $condition 条件 * @param string or array $fields 返回的字段，默认是* * @return string || array */ public function getRow($tName, $condition='', $fields="*") { try { if (!is_string($tName) || !is_string($condition) || !is_string($fields) || empty($fields)) exit($this->getError(__FUNCTION__, __LINE__)); $tName = $this->formatTabName($tName); $this->_sql = "SELECT {$fields} FROM {$tName} "; $this->_sql .= ($condition=='' ? '' : "WHERE {$condition}") . " LIMIT 1"; $sth = $this->_db->prepare($this->_sql); $sth->execute(); $result = $sth->fetch(PDO::FETCH_ASSOC); if ($fields === '*') { return $result; } else { return $result[$fields]; } } catch (PDOException $e) { exit($e->getMessage()); } } /** * 返回多条数据 * @param string $tName 表名 * @param string $fields 返回字段，默认为* * @param string $condition 条件 * @param string $order 排序 * @param string $limit 显示个数 * @return PDOStatement */ public function getAll($tName, $fields='*', $condition='', $order='', $limit='') { try { if (!is_string($tName) || !is_string($fields) || !is_string($condition) || !is_string($order) || !is_string($limit)) exit($this->getError(__FUNCTION__, __LINE__)); $tName = $this->formatTabName($tName); $fields = ($fields=='*' || $fields=='') ? '*' : $fields; $condition = $condition=='' ? '' : " WHERE ". $condition ; $order = $order=='' ? '' : " ORDER BY ". $order; $limit = $limit=='' ? '' : " LIMIT ". $limit; $this->_sql = "SELECT {$fields} FROM {$tName} {$condition} {$order} {$limit}"; $sth = $this->_db->prepare($this->_sql); $sth->execute(); $result = $sth->fetchAll(PDO::FETCH_ASSOC); return $result; } catch (PDOException $e) { exit($e->getMessage()); } } /** * 格式化数组（表结构和值） * @param array $field * @param bool $isField * @return string */ private function formatArr($field, $isField=TRUE) { if (!is_array($field)) exit($this->getError(__FUNCTION__, __LINE__)); $fields = ''; if ($isField) { foreach ($field as $v) { $fields .= '`'.$v.'`,'; } } else { foreach ($field as $v) { $fields .= '\''.$v.'\''.','; } } $fields = rtrim($fields, ','); return $fields; } /** * 格式化问号 * @param int $count 数量 * @return string 返回格式化后的字符串 */ private function formatMark($count) { $str = ''; if (!is_int($count)) exit($this->getError(__FUNCTION__, __LINE__)); if ($count==1) return '?'; for ($i=0; $i<$count; $i++) { $str .= '?,'; } return rtrim($str, ','); } /** * 错误提示 * @param string $fun * @return string */ private function getError($fun, $line) { return __CLASS__ . '->' . $fun . '() line<font color="red">'. $line .'</font> ERROR!'; } /** * 处理表名 * @param string $tName * @return string */ private function formatTabName($tName) { return '`' . trim($tName, '`') . '`'; } /** * 析构方法 */ public function __destruct() { $this->_db = null; }} |
希望本文所述对大家PHP程序设计有所帮助。
