forked from wickyaswal/php-my-sql-pdo-database-class
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Db.class.php
306 lines (275 loc) · 8.86 KB
/
Db.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
<?php
/**
* DB - A simple database class
*
* @author Author: Vivek Wicky Aswal. (https://twitter.com/#!/VivekWickyAswal)
* @git https://github.com/wickyaswal/PHP-MySQL-PDO-Database-Class
* @version 0.2ab
*
*/
require("Log.class.php");
class DB
{
# @object, The PDO object
private $pdo;
# @object, PDO statement object
private $sQuery;
# @array, The database settings
private $settings;
# @bool , Connected to the database
private $bConnected = false;
# @object, Object for logging exceptions
private $log;
# @array, The parameters of the SQL query
private $parameters;
/**
* Default Constructor
*
* 1. Instantiate Log class.
* 2. Connect to database.
* 3. Creates the parameter array.
*/
public function __construct()
{
$this->log = new Log();
$this->Connect();
$this->parameters = array();
}
/**
* This method makes connection to the database.
*
* 1. Reads the database settings from a ini file.
* 2. Puts the ini content into the settings array.
* 3. Tries to connect to the database.
* 4. If connection failed, exception is displayed and a log file gets created.
*/
private function Connect()
{
$this->settings = parse_ini_file("settings.ini.php");
$dsn = 'mysql:dbname=' . $this->settings["dbname"] . ';host=' . $this->settings["host"] . '';
try {
# Read settings from INI file, set UTF8
$this->pdo = new PDO($dsn, $this->settings["user"], $this->settings["password"], array(
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
));
# We can now log any exceptions on Fatal error.
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
# Disable emulation of prepared statements, use REAL prepared statements instead.
$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
# Connection succeeded, set the boolean to true.
$this->bConnected = true;
}
catch (PDOException $e) {
# Write into log
echo $this->ExceptionLog($e->getMessage());
die();
}
}
/*
* You can use this little method if you want to close the PDO connection
*
*/
public function CloseConnection()
{
# Set the PDO object to null to close the connection
# http://www.php.net/manual/en/pdo.connections.php
$this->pdo = null;
}
/**
* Every method which needs to execute a SQL query uses this method.
*
* 1. If not connected, connect to the database.
* 2. Prepare Query.
* 3. Parameterize Query.
* 4. Execute Query.
* 5. On exception : Write Exception into the log + SQL query.
* 6. Reset the Parameters.
*/
private function Init($query, $parameters = "")
{
# Connect to database
if (!$this->bConnected) {
$this->Connect();
}
try {
# Prepare query
$this->sQuery = $this->pdo->prepare($query);
# Add parameters to the parameter array
$this->bindMore($parameters);
# Bind parameters
if (!empty($this->parameters)) {
foreach ($this->parameters as $param => $value) {
if(is_int($value[1])) {
$type = PDO::PARAM_INT;
} else if(is_bool($value[1])) {
$type = PDO::PARAM_BOOL;
} else if(is_null($value[1])) {
$type = PDO::PARAM_NULL;
} else {
$type = PDO::PARAM_STR;
}
// Add type when binding the values to the column
$this->sQuery->bindValue($value[0], $value[1], $type);
}
}
# Execute SQL
$this->sQuery->execute();
}
catch (PDOException $e) {
# Write into log and display Exception
echo $this->ExceptionLog($e->getMessage(), $query);
die();
}
# Reset the parameters
$this->parameters = array();
}
/**
* @void
*
* Add the parameter to the parameter array
* @param string $para
* @param string $value
*/
public function bind($para, $value)
{
$this->parameters[sizeof($this->parameters)] = [":" . $para , $value];
}
/**
* @void
*
* Add more parameters to the parameter array
* @param array $parray
*/
public function bindMore($parray)
{
if (empty($this->parameters) && is_array($parray)) {
$columns = array_keys($parray);
foreach ($columns as $i => &$column) {
$this->bind($column, $parray[$column]);
}
}
}
/**
* If the SQL query contains a SELECT or SHOW statement it returns an array containing all of the result set row
* If the SQL statement is a DELETE, INSERT, or UPDATE statement it returns the number of affected rows
*
* @param string $query
* @param array $params
* @param int $fetchmode
* @return mixed
*/
public function query($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
{
$query = trim(str_replace("\r", " ", $query));
$this->Init($query, $params);
$rawStatement = explode(" ", preg_replace("/\s+|\t+|\n+/", " ", $query));
# Which SQL statement is used
$statement = strtolower($rawStatement[0]);
if ($statement === 'select' || $statement === 'show') {
return $this->sQuery->fetchAll($fetchmode);
} elseif ($statement === 'insert' || $statement === 'update' || $statement === 'delete') {
return $this->sQuery->rowCount();
} else {
return NULL;
}
}
/**
* Returns the last inserted id.
* @return string
*/
public function lastInsertId()
{
return $this->pdo->lastInsertId();
}
/**
* Starts the transaction
* @return boolean, true on success or false on failure
*/
public function beginTransaction()
{
return $this->pdo->beginTransaction();
}
/**
* Execute Transaction
* @return boolean, true on success or false on failure
*/
public function executeTransaction()
{
return $this->pdo->commit();
}
/**
* Rollback of Transaction
* @return boolean, true on success or false on failure
*/
public function rollBack()
{
return $this->pdo->rollBack();
}
/**
* Returns an array which represents a column from the result set
*
* @param string $query
* @param array $params
* @return array
*/
public function column($query, $params = null)
{
$this->Init($query, $params);
$Columns = $this->sQuery->fetchAll(PDO::FETCH_NUM);
$column = null;
foreach ($Columns as $cells) {
$column[] = $cells[0];
}
return $column;
}
/**
* Returns an array which represents a row from the result set
*
* @param string $query
* @param array $params
* @param int $fetchmode
* @return array
*/
public function row($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
{
$this->Init($query, $params);
$result = $this->sQuery->fetch($fetchmode);
$this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued,
return $result;
}
/**
* Returns the value of one single field/column
*
* @param string $query
* @param array $params
* @return string
*/
public function single($query, $params = null)
{
$this->Init($query, $params);
$result = $this->sQuery->fetchColumn();
$this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued
return $result;
}
/**
* Writes the log and returns the exception
*
* @param string $message
* @param string $sql
* @return string
*/
private function ExceptionLog($message, $sql = "")
{
$exception = 'Unhandled Exception. <br />';
$exception .= $message;
$exception .= "<br /> You can find the error back in the log.";
if (!empty($sql)) {
# Add the Raw SQL to the Log
$message .= "\r\nRaw SQL : " . $sql;
}
# Write into log
$this->log->write($message);
return $exception;
}
}
?>