|
1
|
|
|
<?php
|
|
2
|
|
|
namespace DBAL;
|
|
3
|
|
|
|
|
4
|
|
|
use PDO;
|
|
5
|
|
|
|
|
6
|
|
|
/**
|
|
7
|
|
|
* PDO Database connection class
|
|
8
|
|
|
*
|
|
9
|
|
|
* @author Adam Binnersley <[email protected]>
|
|
10
|
|
|
* @version PDO Database Class 1.0.1
|
|
11
|
|
|
*/
|
|
12
|
|
|
final class Database implements DBInterface{
|
|
13
|
|
|
public $db;
|
|
14
|
|
|
public $sql;
|
|
15
|
|
|
private $key;
|
|
16
|
|
|
|
|
17
|
|
|
public $logLocation = 'logs'.DIRECTORY_SEPARATOR;
|
|
18
|
|
|
public $logErrors = true;
|
|
19
|
|
|
public $logQueries = false;
|
|
20
|
|
|
public $displayErrors = false;
|
|
21
|
|
|
|
|
22
|
|
|
protected $database;
|
|
23
|
|
|
protected $cacheEnabled = false;
|
|
24
|
|
|
protected $cacheObj;
|
|
25
|
|
|
protected $cacheValue;
|
|
26
|
|
|
protected $modified = false;
|
|
27
|
|
|
|
|
28
|
|
|
private $query;
|
|
29
|
|
|
private $values = array();
|
|
30
|
|
|
|
|
31
|
|
|
/**
|
|
32
|
|
|
* Connect to database using PDO connection
|
|
33
|
|
|
* @param string $hostname This should be the host of the database e.g. 'localhost'
|
|
34
|
|
|
* @param string $username This should be the username for the chosen database
|
|
35
|
|
|
* @param string $password This should be the password for the chosen database
|
|
36
|
|
|
* @param string $database This should be the database that you wish to connect to
|
|
37
|
|
|
* @param string|null $backuphost If you have a replication server set up put the hostname or IP address incase the primary server goes down
|
|
38
|
|
|
*/
|
|
39
|
|
|
public function __construct($hostname, $username, $password, $database, $backuphost = NULL){
|
|
40
|
|
|
try{
|
|
41
|
|
|
$this->connectToServer($username, $password, $database, $hostname);
|
|
42
|
|
|
}
|
|
43
|
|
|
catch(\Exception $e){
|
|
44
|
|
|
if($backuphost !== NULL){
|
|
45
|
|
|
$this->connectToServer($username, $password, $database, $backuphost);
|
|
46
|
|
|
}
|
|
47
|
|
|
$this->error($e);
|
|
48
|
|
|
}
|
|
49
|
|
|
}
|
|
50
|
|
|
|
|
51
|
|
|
/**
|
|
52
|
|
|
* Closes the PDO database connection when Database object unset
|
|
53
|
|
|
*/
|
|
54
|
|
|
public function __destruct(){
|
|
55
|
|
|
$this->closeDatabase();
|
|
56
|
|
|
}
|
|
57
|
|
|
|
|
58
|
|
|
/**
|
|
59
|
|
|
* Connect to the database using PDO connection
|
|
60
|
|
|
* @param string $username This should be the username for the chosen database
|
|
61
|
|
|
* @param string $password This should be the password for the chosen database
|
|
62
|
|
|
* @param string $database This should be the database that you wish to connect to
|
|
63
|
|
|
* @param string $hostname The hostname for the database
|
|
64
|
|
|
*/
|
|
65
|
|
|
protected function connectToServer($username, $password, $database, $hostname){
|
|
66
|
|
|
if(!$this->db){
|
|
67
|
|
|
$this->database = $database;
|
|
68
|
|
|
$this->db = new PDO('mysql:host='.$hostname.';dbname='.$database, $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, PDO::ATTR_PERSISTENT => true, PDO::ATTR_EMULATE_PREPARES => true));
|
|
69
|
|
|
}
|
|
70
|
|
|
}
|
|
71
|
|
|
|
|
72
|
|
|
/**
|
|
73
|
|
|
* Enables the caching and set the caching object to the one provided
|
|
74
|
|
|
* @param object $caching This should be class of the type of caching you are using
|
|
75
|
|
|
*/
|
|
76
|
|
|
public function setCaching($caching){
|
|
77
|
|
|
if(is_object($caching)){
|
|
78
|
|
|
$this->cacheObj = $caching;
|
|
79
|
|
|
$this->cacheEnabled = true;
|
|
80
|
|
|
}
|
|
81
|
|
|
return $this;
|
|
82
|
|
|
}
|
|
83
|
|
|
|
|
84
|
|
|
/**
|
|
85
|
|
|
* This outputs the SQL where query based on a given array
|
|
86
|
|
|
* @param array $where This should be an array that you wish to create the where query for in the for array('field1' => 'test') or array('field1' => array('>=', 0))
|
|
87
|
|
|
* @return string|false If the where query is an array will return the where string and set the values else returns false if no array sent
|
|
88
|
|
|
*/
|
|
89
|
|
|
private function where($where){
|
|
90
|
|
|
if(is_array($where)){
|
|
91
|
|
|
$wherefields = array();
|
|
92
|
|
|
foreach($where as $what => $value){
|
|
93
|
|
|
if(is_array($value)){
|
|
94
|
|
|
if($value[1] == 'NULL' || $value[1] == 'NOT NULL'){
|
|
95
|
|
|
$wherefields[] = sprintf("`%s` %s %s", $what, addslashes($value[0]), $value[1]);
|
|
96
|
|
|
}
|
|
97
|
|
|
else{
|
|
98
|
|
|
$wherefields[] = sprintf("`%s` %s ?", $what, addslashes($value[0]));
|
|
99
|
|
|
$this->values[] = $value[1];
|
|
100
|
|
|
}
|
|
101
|
|
|
}
|
|
102
|
|
|
else{
|
|
103
|
|
|
$wherefields[] = sprintf("`%s` = ?", $what);
|
|
104
|
|
|
$this->values[] = $value;
|
|
105
|
|
|
}
|
|
106
|
|
|
}
|
|
107
|
|
|
if(!empty($wherefields)){
|
|
108
|
|
|
return " WHERE ".implode(' AND ', $wherefields);
|
|
109
|
|
|
}
|
|
110
|
|
|
}
|
|
111
|
|
|
return false;
|
|
112
|
|
|
}
|
|
113
|
|
|
|
|
114
|
|
|
/**
|
|
115
|
|
|
* Sets the order sting for the SQL query based on an array or string
|
|
116
|
|
|
* @param array|string $order This should be either set to array('fieldname' => 'ASC/DESC') or RAND()
|
|
117
|
|
|
* @return string|false If the SQL query has an valid order by will return a string else returns false
|
|
118
|
|
|
*/
|
|
119
|
|
|
private function orderBy($order){
|
|
120
|
|
|
if(is_array($order)){
|
|
121
|
|
|
foreach($order as $fieldorder => $fieldvalue){
|
|
122
|
|
|
return sprintf(" ORDER BY `%s` %s", $fieldorder, strtoupper($fieldvalue));
|
|
123
|
|
|
}
|
|
124
|
|
|
}
|
|
125
|
|
|
elseif($order == 'RAND()'){
|
|
126
|
|
|
return " ORDER BY RAND()";
|
|
127
|
|
|
}
|
|
128
|
|
|
return false;
|
|
129
|
|
|
}
|
|
130
|
|
|
|
|
131
|
|
|
/**
|
|
132
|
|
|
* Returns the limit SQL for the current query as a string
|
|
133
|
|
|
* @param integer|array $limit This should either be set as an integer or should be set as an array with a start and end value
|
|
134
|
|
|
* @return string|false Will return the LIMIT string for the current query if it is valid else returns false
|
|
135
|
|
|
*/
|
|
136
|
|
|
private function limit($limit = 0){
|
|
137
|
|
|
if(is_array($limit)){
|
|
138
|
|
|
foreach($limit as $start => $end){
|
|
139
|
|
|
return " LIMIT ".(int)$start.", ".(int)$end;
|
|
140
|
|
|
}
|
|
141
|
|
|
}
|
|
142
|
|
|
elseif((int)$limit > 0){
|
|
143
|
|
|
return " LIMIT ".(int)$limit;
|
|
144
|
|
|
}
|
|
145
|
|
|
return false;
|
|
146
|
|
|
}
|
|
147
|
|
|
|
|
148
|
|
|
/**
|
|
149
|
|
|
* This query function is used for more advanced SQL queries for which non of the other methods fit
|
|
150
|
|
|
* @param string $sql This should be the SQL query which you wish to run
|
|
151
|
|
|
* @return array Returns array of results for the query that has just been run
|
|
152
|
|
|
*/
|
|
153
|
|
|
public function query($sql, $variables = array(), $cache = true){
|
|
154
|
|
|
try{
|
|
155
|
|
|
$this->sql = $sql;
|
|
156
|
|
|
$this->query = $this->db->prepare($this->sql);
|
|
157
|
|
|
$this->query->execute($variables);
|
|
158
|
|
|
if(strpos($this->sql, 'SELECT') !== false){
|
|
159
|
|
|
return $this->query->fetchAll(PDO::FETCH_ASSOC);
|
|
160
|
|
|
}
|
|
161
|
|
|
}
|
|
162
|
|
|
catch(\Exception $e){
|
|
163
|
|
|
$this->error($e);
|
|
164
|
|
|
}
|
|
165
|
|
|
}
|
|
166
|
|
|
|
|
167
|
|
|
/**
|
|
168
|
|
|
* Returns a single record for a select query for the chosen table
|
|
169
|
|
|
* @param string $table This should be the table you wish to select the values from
|
|
170
|
|
|
* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
|
171
|
|
|
* @param string|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
|
|
172
|
|
|
* @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC')
|
|
173
|
|
|
* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
|
|
174
|
|
|
* @return array Returns a single table record as the standard array when running SQL queries
|
|
175
|
|
|
*/
|
|
176
|
|
|
public function select($table, $where = array(), $fields = '*', $order = array(), $cache = true){
|
|
177
|
|
|
return $this->selectAll($table, $where, $fields, $order, 1, $cache);
|
|
178
|
|
|
}
|
|
179
|
|
|
|
|
180
|
|
|
/**
|
|
181
|
|
|
* Returns a multidimensional array of the results from the selected table given the given parameters
|
|
182
|
|
|
* @param string $table This should be the table you wish to select the values from
|
|
183
|
|
|
* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
|
184
|
|
|
* @param string|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
|
|
185
|
|
|
* @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC')
|
|
186
|
|
|
* @param integer|array $limit The number of results you want to return 0 is default and returns all results, else should be formated either as a standard integer or as an array as the start and end values e.g. array(0 => 150)
|
|
187
|
|
|
* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
|
|
188
|
|
|
* @return array Returns a multidimensional array with the chosen fields from the table
|
|
189
|
|
|
*/
|
|
190
|
|
|
public function selectAll($table, $where = array(), $fields = '*', $order = array(), $limit = 0, $cache = true){
|
|
191
|
|
|
$this->buildSelectQuery($table, $where, $fields, $order, $limit);
|
|
192
|
|
|
$result = $this->executeQuery($cache);
|
|
193
|
|
|
if(!$result){
|
|
194
|
|
|
if($limit === 1){$result = $this->query->fetch(PDO::FETCH_ASSOC);} // Reduce the memory usage if only one record and increase performance
|
|
195
|
|
|
else{$result = $this->query->fetchAll(PDO::FETCH_ASSOC);}
|
|
196
|
|
|
if($cache && $this->cacheEnabled){$this->setCache($this->key, $result);}
|
|
197
|
|
|
}
|
|
198
|
|
|
return $result;
|
|
199
|
|
|
}
|
|
200
|
|
|
|
|
201
|
|
|
/**
|
|
202
|
|
|
* Returns a single column value for a given query
|
|
203
|
|
|
* @param string $table This should be the table you wish to select the values from
|
|
204
|
|
|
* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
|
205
|
|
|
* @param array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
|
|
206
|
|
|
* @param int $colNum This should be the column number you wish to get (starts at 0)
|
|
207
|
|
|
* @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC') so it can be done in both directions
|
|
208
|
|
|
* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
|
|
209
|
|
|
* @return mixed If a result is found will return the value of the colum given else will return false
|
|
210
|
|
|
*/
|
|
211
|
|
|
public function fetchColumn($table, $where = array(), $fields = '*', $colNum = 0, $order = array(), $cache = true){
|
|
212
|
|
|
$this->buildSelectQuery($table, $where, $fields, $order, 1);
|
|
213
|
|
|
$result = $this->executeQuery($cache);
|
|
214
|
|
|
if(!$result){
|
|
215
|
|
|
$result = $this->query->fetchColumn(intval($colNum));
|
|
216
|
|
|
if($cache && $this->cacheEnabled){$this->setCache($this->key, $result);}
|
|
217
|
|
|
}
|
|
218
|
|
|
return $result;
|
|
219
|
|
|
}
|
|
220
|
|
|
|
|
221
|
|
|
/**
|
|
222
|
|
|
* Inserts into database using the prepared PDO statements
|
|
223
|
|
|
* @param string $table This should be the table you wish to insert the values into
|
|
224
|
|
|
* @param array $records This should be the field names and values in the format of array('fieldname' => 'value', 'fieldname2' => 'value2', etc.)
|
|
225
|
|
|
* @return boolean If data is inserted returns true else returns false
|
|
226
|
|
|
*/
|
|
227
|
|
|
public function insert($table, $records){
|
|
228
|
|
|
unset($this->values);
|
|
229
|
|
|
$fields = array();
|
|
230
|
|
|
$prepare = array();
|
|
231
|
|
|
|
|
232
|
|
View Code Duplication |
foreach($records as $field => $value){
|
|
|
|
|
|
|
233
|
|
|
$fields[] = sprintf("`%s`", $field);
|
|
234
|
|
|
$prepare[] = '?';
|
|
235
|
|
|
$this->values[] = $value;
|
|
236
|
|
|
}
|
|
237
|
|
|
|
|
238
|
|
|
$this->sql = sprintf("INSERT INTO `%s` (%s) VALUES (%s);", $table, implode(', ', $fields), implode(', ', $prepare));
|
|
239
|
|
|
$this->executeQuery(false);
|
|
240
|
|
|
return $this->numRows() ? true : false;
|
|
241
|
|
|
}
|
|
242
|
|
|
|
|
243
|
|
|
/**
|
|
244
|
|
|
* Updates values in a database using the provide variables
|
|
245
|
|
|
* @param string $table This should be the table you wish to update the values for
|
|
246
|
|
|
* @param array $records This should be the field names and new values in the format of array('fieldname' => 'newvalue', 'fieldname2' => 'newvalue2', etc.)
|
|
247
|
|
|
* @param array $where Should be the field names and values you wish to update in the form of an array e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
|
248
|
|
|
* @param int $limit The number of results you want to return 0 is default and will update all results that match the query, else should be formated as a standard integer
|
|
249
|
|
|
* @return boolean Returns true if update is successful else returns false
|
|
250
|
|
|
*/
|
|
251
|
|
|
public function update($table, $records, $where = array(), $limit = 0){
|
|
252
|
|
|
unset($this->values);
|
|
253
|
|
|
$fields = array();
|
|
254
|
|
|
|
|
255
|
|
View Code Duplication |
foreach($records as $field => $value){
|
|
|
|
|
|
|
256
|
|
|
$fields[] = sprintf("`%s` = ?", $field);
|
|
257
|
|
|
$this->values[] = $value;
|
|
258
|
|
|
}
|
|
259
|
|
|
$this->sql = sprintf("UPDATE `%s` SET %s %s%s;", $table, implode(', ', $fields), $this->where($where), $this->limit($limit));
|
|
260
|
|
|
$this->executeQuery(false);
|
|
261
|
|
|
return $this->numRows() ? true : false;
|
|
262
|
|
|
}
|
|
263
|
|
|
|
|
264
|
|
|
/**
|
|
265
|
|
|
* Deletes records from the given table based on the variables given
|
|
266
|
|
|
* @param string $table This should be the table you wish to delete the records from
|
|
267
|
|
|
* @param array $where This should be an array of for the where statement
|
|
268
|
|
|
* @param int $limit The number of results you want to return 0 is default and will delete all results that match the query, else should be formated as a standard integer
|
|
269
|
|
|
*/
|
|
270
|
|
|
public function delete($table, $where, $limit = 0){
|
|
271
|
|
|
unset($this->values);
|
|
272
|
|
|
$this->sql = sprintf("DELETE FROM `%s` %s%s;", $table, $this->where($where), $this->limit($limit));
|
|
273
|
|
|
$this->executeQuery(false);
|
|
274
|
|
|
return $this->numRows() ? true : false;
|
|
275
|
|
|
}
|
|
276
|
|
|
|
|
277
|
|
|
/**
|
|
278
|
|
|
* Count the number of return results
|
|
279
|
|
|
* @param string $table The table you wish to count the result of
|
|
280
|
|
|
* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
|
281
|
|
|
* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
|
|
282
|
|
|
* @return int Returns the number of results
|
|
283
|
|
|
*/
|
|
284
|
|
|
public function count($table, $where = array(), $cache = true){
|
|
285
|
|
|
unset($this->values);
|
|
286
|
|
|
$this->sql = sprintf("SELECT count(*) FROM `%s`%s;", $table, $this->where($where));
|
|
287
|
|
|
$this->key = md5($this->database.$this->sql.serialize($this->values));
|
|
288
|
|
|
|
|
289
|
|
|
$result = $this->executeQuery($cache);
|
|
290
|
|
|
if(!$result){
|
|
291
|
|
|
$result = $this->query->fetchColumn();
|
|
292
|
|
|
if($cache && $this->cacheEnabled){$this->setCache($this->key, $result);}
|
|
293
|
|
|
}
|
|
294
|
|
|
return $result;
|
|
295
|
|
|
}
|
|
296
|
|
|
|
|
297
|
|
|
/**
|
|
298
|
|
|
* Truncates a given table from the selected database so there are no values in the table
|
|
299
|
|
|
* @param string $table This should be the table you wish to truncate
|
|
300
|
|
|
* @return boolean If the table is emptied returns true else returns false
|
|
301
|
|
|
*/
|
|
302
|
|
|
public function truncate($table){
|
|
303
|
|
|
try{
|
|
304
|
|
|
$this->sql = sprintf("TRUNCATE TABLE `%s`", $table);
|
|
305
|
|
|
$this->query = $this->db->exec($this->sql);
|
|
306
|
|
|
}
|
|
307
|
|
|
catch(\Exception $e){
|
|
308
|
|
|
$this->error($e);
|
|
309
|
|
|
}
|
|
310
|
|
|
return $this->query ? true : false;
|
|
311
|
|
|
}
|
|
312
|
|
|
|
|
313
|
|
|
/**
|
|
314
|
|
|
* Returns the number of rows for the last query sent
|
|
315
|
|
|
* @return int Returns the number of rows for the last query
|
|
316
|
|
|
*/
|
|
317
|
|
|
public function numRows(){
|
|
318
|
|
|
if(isset($this->query)){
|
|
319
|
|
|
return $this->query->rowCount();
|
|
320
|
|
|
}
|
|
321
|
|
|
return 0;
|
|
322
|
|
|
}
|
|
323
|
|
|
|
|
324
|
|
|
/**
|
|
325
|
|
|
* Returns the number of rows for the last query sent (Looks a the numRows() function just added incase of habbit)
|
|
326
|
|
|
* @return int Returns the number of rows for the last query
|
|
327
|
|
|
*/
|
|
328
|
|
|
public function rowCount(){
|
|
329
|
|
|
return $this->numRows();
|
|
330
|
|
|
}
|
|
331
|
|
|
|
|
332
|
|
|
/**
|
|
333
|
|
|
* Returns the ID of the last record last inserted
|
|
334
|
|
|
* @param string $name This should be the name of the sequence object you wish to retrieve
|
|
335
|
|
|
* @return int|string Returns the last inserted ID of the last insert item if $name is null else returns string with sequenced object
|
|
336
|
|
|
*/
|
|
337
|
|
|
public function lastInsertId($name = null) {
|
|
338
|
|
|
return $this->db->lastInsertId($name);
|
|
339
|
|
|
}
|
|
340
|
|
|
|
|
341
|
|
|
/**
|
|
342
|
|
|
* Returns the index of the given table or tables within the database
|
|
343
|
|
|
* @param string|array $table Table can wither be a standard string with a single table name or an array with multiple table names
|
|
344
|
|
|
* @return array Returns the table index for the selected table as an array
|
|
345
|
|
|
*/
|
|
346
|
|
|
public function fulltextIndex($table){
|
|
347
|
|
|
$fieldlist = array();
|
|
348
|
|
|
if(is_array($table)){
|
|
349
|
|
|
foreach($table as $name){
|
|
350
|
|
|
$fieldlist[$name] = $this->fulltextIndex($name);
|
|
351
|
|
|
}
|
|
352
|
|
|
}else{
|
|
353
|
|
|
try{
|
|
354
|
|
|
$this->sql = sprintf("SHOW INDEX FROM %s;", $table);
|
|
355
|
|
|
$this->query = $this->db->exec($this->sql);
|
|
356
|
|
|
}
|
|
357
|
|
|
catch(\Exception $e){
|
|
358
|
|
|
$this->error($e);
|
|
359
|
|
|
}
|
|
360
|
|
|
|
|
361
|
|
|
while($index = $this->query->fetch(PDO::FETCH_ASSOC)){
|
|
362
|
|
|
if($index['Index_type'] == 'FULLTEXT' && $index['Key_name'] == 'fulltext'){
|
|
363
|
|
|
$fieldlist[] = $index['Column_name'];
|
|
364
|
|
|
}
|
|
365
|
|
|
}
|
|
366
|
|
|
}
|
|
367
|
|
|
return $fieldlist;
|
|
368
|
|
|
}
|
|
369
|
|
|
|
|
370
|
|
|
/**
|
|
371
|
|
|
* Returns the server version information
|
|
372
|
|
|
*/
|
|
373
|
|
|
public function serverVersion(){
|
|
374
|
|
|
return $this->db->getAttribute(PDO::ATTR_SERVER_VERSION);
|
|
375
|
|
|
}
|
|
376
|
|
|
|
|
377
|
|
|
/**
|
|
378
|
|
|
* Displays the error massage which occurs
|
|
379
|
|
|
* @param \Exception $error This should be an instance of Exception
|
|
380
|
|
|
*/
|
|
381
|
|
|
private function error($error){
|
|
382
|
|
|
if($this->logErrors){
|
|
383
|
|
|
$file = $this->logLocation.'db-errors.txt';
|
|
384
|
|
|
$current = file_get_contents($file);
|
|
385
|
|
|
$current .= date('d/m/Y H:i:s')." ERROR: ".$error->getMessage()." on ".$this->sql."\n";
|
|
386
|
|
|
file_put_contents($file, $current);
|
|
387
|
|
|
}
|
|
388
|
|
|
if($this->displayErrors){
|
|
389
|
|
|
die('ERROR: '.$error->getMessage().' on '.$this->sql);
|
|
390
|
|
|
}
|
|
391
|
|
|
}
|
|
392
|
|
|
|
|
393
|
|
|
/**
|
|
394
|
|
|
* Writes all queries to a log file
|
|
395
|
|
|
*/
|
|
396
|
|
|
public function writeQueryToLog(){
|
|
397
|
|
|
$file = $this->logLocation.'queries.txt';
|
|
398
|
|
|
$current = file_get_contents($file);
|
|
399
|
|
|
$current .= "SQL: ".$this->sql.":".serialize($this->values)."\n";
|
|
400
|
|
|
file_put_contents($file, $current);
|
|
401
|
|
|
}
|
|
402
|
|
|
|
|
403
|
|
|
/**
|
|
404
|
|
|
* Closes the PDO database connection by setting the connection to NULL
|
|
405
|
|
|
*/
|
|
406
|
|
|
public function closeDatabase(){
|
|
407
|
|
|
$this->db = null;
|
|
408
|
|
|
}
|
|
409
|
|
|
|
|
410
|
|
|
/**
|
|
411
|
|
|
* Build the SQL query but doesn't execute it
|
|
412
|
|
|
* @param string $table This should be the table you wish to select the values from
|
|
413
|
|
|
* @param array $where Should be the field names and values you wish to use as the where query e.g. array('fieldname' => 'value', 'fieldname2' => 'value2', etc).
|
|
414
|
|
|
* @param string|array $fields This should be the records you wis to select from the table. It should be either set as '*' which is the default or set as an array in the following format array('field', 'field2', 'field3', etc).
|
|
415
|
|
|
* @param array $order This is the order you wish the results to be ordered in should be formatted as follows array('fieldname' => 'ASC') or array("'fieldname', 'fieldname2'" => 'DESC') so it can be done in both directions
|
|
416
|
|
|
* @param integer|array $limit The number of results you want to return 0 is default and returns all results, else should be formated either as a standard integer or as an array as the start and end values e.g. array(0 => 150)
|
|
417
|
|
|
*/
|
|
418
|
|
|
protected function buildSelectQuery($table, $where = array(), $fields = '*', $order = array(), $limit = 0){
|
|
419
|
|
|
if(is_array($fields)){
|
|
420
|
|
|
$selectfields = array();
|
|
421
|
|
|
foreach($fields as $field => $value){
|
|
422
|
|
|
$selectfields[] = sprintf("`%s`", $value);
|
|
423
|
|
|
}
|
|
424
|
|
|
$fieldList = implode(', ', $selectfields);
|
|
425
|
|
|
}
|
|
426
|
|
|
else{$fieldList = '*';}
|
|
427
|
|
|
|
|
428
|
|
|
unset($this->values);
|
|
429
|
|
|
$this->sql = sprintf("SELECT %s FROM `%s`%s%s%s;", $fieldList, $table, $this->where($where), $this->orderBy($order), $this->limit($limit));
|
|
430
|
|
|
$this->key = md5($this->database.$this->sql.serialize($this->values));
|
|
431
|
|
|
}
|
|
432
|
|
|
|
|
433
|
|
|
/**
|
|
434
|
|
|
* Execute the current query if no cache value is available
|
|
435
|
|
|
* @param boolean $cache If the cache should be checked for the checked for the values of the query set to true else set to false
|
|
436
|
|
|
* @return mixed If a cached value exists will be returned else if cache is not checked and query is executed will not return anything
|
|
437
|
|
|
*/
|
|
438
|
|
|
protected function executeQuery($cache = true){
|
|
439
|
|
|
if($this->logQueries){$this->writeQueryToLog();}
|
|
440
|
|
|
if($cache && $this->cacheEnabled && $this->getCache($this->key)){
|
|
441
|
|
|
return $this->cacheValue;
|
|
442
|
|
|
}
|
|
443
|
|
|
else{
|
|
444
|
|
|
try{
|
|
445
|
|
|
$this->query = $this->db->prepare($this->sql);
|
|
446
|
|
|
$this->query->execute($this->values);
|
|
447
|
|
|
}
|
|
448
|
|
|
catch(\Exception $e){
|
|
449
|
|
|
$this->error($e);
|
|
450
|
|
|
}
|
|
451
|
|
|
}
|
|
452
|
|
|
}
|
|
453
|
|
|
|
|
454
|
|
|
/**
|
|
455
|
|
|
* Set the cache with a key and value
|
|
456
|
|
|
* @param string $key The unique key to store the value against
|
|
457
|
|
|
* @param mixed $value The value of the MYSQL query
|
|
458
|
|
|
*/
|
|
459
|
|
|
public function setCache($key, $value){
|
|
460
|
|
|
if($this->cacheEnabled){
|
|
461
|
|
|
$this->cacheObj->save($key, $value);
|
|
462
|
|
|
}
|
|
463
|
|
|
}
|
|
464
|
|
|
|
|
465
|
|
|
/**
|
|
466
|
|
|
* Get the results for a given key
|
|
467
|
|
|
* @param string $key The unique key to check for stored variables
|
|
468
|
|
|
* @return mixed Returned the cached results from
|
|
469
|
|
|
*/
|
|
470
|
|
|
public function getCache($key){
|
|
471
|
|
|
if($this->modified === true || !$this->cacheEnabled){return false;}
|
|
472
|
|
|
else{
|
|
473
|
|
|
$this->cacheValue = $this->cacheObj->fetch($key);
|
|
474
|
|
|
return $this->cacheValue;
|
|
475
|
|
|
}
|
|
476
|
|
|
}
|
|
477
|
|
|
|
|
478
|
|
|
/**
|
|
479
|
|
|
* Clears the cache
|
|
480
|
|
|
*/
|
|
481
|
|
|
public function flushDB(){
|
|
482
|
|
|
$this->cacheObj->deleteAll();
|
|
483
|
|
|
}
|
|
484
|
|
|
}
|
|
485
|
|
|
|
AdamB7586 /
pdo-dbal
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.