|
1
|
|
|
<?php
|
|
2
|
|
|
namespace DBAL;
|
|
3
|
|
|
|
|
4
|
|
|
use PDO;
|
|
5
|
|
|
use DBAL\Modifiers\SafeString;
|
|
6
|
|
|
use DBAL\Modifiers\Operators;
|
|
7
|
|
|
|
|
8
|
|
|
/**
|
|
9
|
|
|
* PDO Database connection class
|
|
10
|
|
|
*
|
|
11
|
|
|
* @author Adam Binnersley <[email protected]>
|
|
12
|
|
|
* @version PDO Database Class
|
|
13
|
|
|
*/
|
|
14
|
|
|
final class Database implements DBInterface{
|
|
15
|
|
|
protected $db;
|
|
16
|
|
|
public $sql;
|
|
17
|
|
|
private $key;
|
|
18
|
|
|
|
|
19
|
|
|
protected $logLocation;
|
|
20
|
|
|
public $logErrors = true;
|
|
21
|
|
|
public $logQueries = false;
|
|
22
|
|
|
public $displayErrors = false;
|
|
23
|
|
|
|
|
24
|
|
|
protected $database;
|
|
25
|
|
|
protected $cacheEnabled = false;
|
|
26
|
|
|
protected $cacheObj;
|
|
27
|
|
|
protected $cacheValue;
|
|
28
|
|
|
protected $modified = false;
|
|
29
|
|
|
|
|
30
|
|
|
private $query;
|
|
31
|
|
|
private $values = [];
|
|
32
|
|
|
private $prepare = [];
|
|
33
|
|
|
|
|
34
|
|
|
private static $connectors = array(
|
|
35
|
|
|
'cubrid' => 'cubrid:host=%s;port=%d;dbname=%s',
|
|
36
|
|
|
'dblib' => 'dblib:host=%s:%d;dbname=%s',
|
|
37
|
|
|
'mssql' => 'sqlsrv:Server=%s,%d;Database=%s',
|
|
38
|
|
|
'mysql' => 'mysql:host=%s;port=%d;dbname=%s',
|
|
39
|
|
|
'pgsql' => 'pgsql:host=%s;port=%d;dbname=%s',
|
|
40
|
|
|
'sqlite' => 'sqlite::memory:'
|
|
41
|
|
|
);
|
|
42
|
|
|
|
|
43
|
|
|
/**
|
|
44
|
|
|
* Connect to database using PDO connection
|
|
45
|
|
|
* @param string $hostname This should be the host of the database e.g. 'localhost'
|
|
46
|
|
|
* @param string $username This should be the username for the chosen database
|
|
47
|
|
|
* @param string $password This should be the password for the chosen database
|
|
48
|
|
|
* @param string $database This should be the database that you wish to connect to
|
|
49
|
|
|
* @param string|false $backuphost If you have a replication server set up put the hostname or IP address incase the primary server goes down
|
|
50
|
|
|
* @param object|false $cache If you want to cache the queries with Memcache(d)/Redis/APC/Xcache This should be the object else set to false
|
|
51
|
|
|
* @param boolean $persistent If you want a persistent database connection set to true
|
|
52
|
|
|
* @param string $type The type of connection that you wish to make can be 'mysql', 'cubrid', 'dblib', 'mssql', 'odbc', 'pgsql, or 'sqlite'
|
|
53
|
|
|
* @param int $port This should be the port number of the MySQL database connection
|
|
54
|
|
|
*/
|
|
55
|
1 |
|
public function __construct($hostname, $username, $password, $database, $backuphost = false, $cache = false, $persistent = false, $type = 'mysql', $port = 3306) {
|
|
56
|
1 |
|
$this->setLogLocation();
|
|
57
|
|
|
try{
|
|
58
|
1 |
|
$this->connectToServer($username, $password, $database, $hostname, $persistent, $type, $port);
|
|
59
|
|
|
}
|
|
60
|
1 |
|
catch(\Exception $e) {
|
|
61
|
1 |
|
if($backuphost !== false) {
|
|
62
|
|
|
$this->connectToServer($username, $password, $database, $backuphost, $persistent, $type, $port);
|
|
63
|
|
|
}
|
|
64
|
1 |
|
$this->error($e);
|
|
65
|
|
|
}
|
|
66
|
1 |
|
if(is_object($cache)) {
|
|
67
|
|
|
$this->setCaching($cache);
|
|
68
|
|
|
}
|
|
69
|
1 |
|
}
|
|
70
|
|
|
|
|
71
|
|
|
/**
|
|
72
|
|
|
* Closes the PDO database connection when Database object unset
|
|
73
|
|
|
*/
|
|
74
|
|
|
public function __destruct() {
|
|
75
|
|
|
$this->closeDatabase();
|
|
76
|
|
|
}
|
|
77
|
|
|
|
|
78
|
|
|
/**
|
|
79
|
|
|
* Connect to the database using PDO connection
|
|
80
|
|
|
* @param string $username This should be the username for the chosen database
|
|
81
|
|
|
* @param string $password This should be the password for the chosen database
|
|
82
|
|
|
* @param string $database This should be the database that you wish to connect to
|
|
83
|
|
|
* @param string $hostname The hostname for the database
|
|
84
|
|
|
* @param boolean $persistent If you want a persistent database connection set to true
|
|
85
|
|
|
* @param string $type The type of connection that you wish to make can be 'mysql', 'cubrid', 'dblib', 'mssql', 'pgsql, or 'sqlite'
|
|
86
|
|
|
* @param int $port The port number to connect to the MySQL server
|
|
87
|
|
|
*/
|
|
88
|
2 |
|
protected function connectToServer($username, $password, $database, $hostname, $persistent = false, $type = 'mysql', $port = 3306) {
|
|
89
|
2 |
|
if(!$this->db) {
|
|
90
|
2 |
|
$this->database = $database;
|
|
91
|
2 |
|
$this->db = new PDO(sprintf(self::$connectors[$type], $hostname, $port, $database), $username, $password,
|
|
92
|
2 |
|
array_merge(
|
|
93
|
2 |
|
($persistent !== false ? array(PDO::ATTR_PERSISTENT => true) : array()),
|
|
94
|
2 |
|
($type === 'mysql' ? array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, PDO::ATTR_EMULATE_PREPARES => true) : array())
|
|
95
|
|
|
)
|
|
96
|
|
|
);
|
|
97
|
2 |
|
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
98
|
|
|
}
|
|
99
|
2 |
|
}
|
|
100
|
|
|
|
|
101
|
|
|
/**
|
|
102
|
|
|
* Enables the caching and set the caching object to the one provided
|
|
103
|
|
|
* @param object $caching This should be class of the type of caching you are using
|
|
104
|
|
|
*/
|
|
105
|
|
|
public function setCaching($caching) {
|
|
106
|
|
|
if(is_object($caching)) {
|
|
107
|
|
|
$this->cacheObj = $caching;
|
|
108
|
|
|
$this->cacheEnabled = true;
|
|
109
|
|
|
}
|
|
110
|
|
|
return $this;
|
|
111
|
|
|
}
|
|
112
|
|
|
|
|
113
|
|
|
/**
|
|
114
|
|
|
* This query function is used for more advanced SQL queries for which non of the other methods fit
|
|
115
|
|
|
* @param string $sql This should be the SQL query which you wish to run
|
|
116
|
|
|
* @return array Returns array of results for the query that has just been run
|
|
117
|
|
|
*/
|
|
118
|
1 |
|
public function query($sql, $variables = array(), $cache = true) {
|
|
119
|
|
|
try{
|
|
120
|
1 |
|
$this->sql = $sql;
|
|
121
|
1 |
|
$this->query = $this->db->prepare($this->sql);
|
|
122
|
1 |
|
$this->query->execute($variables);
|
|
123
|
1 |
|
if(strpos($this->sql, 'SELECT') !== false) {
|
|
124
|
1 |
|
return $this->query->fetchAll(PDO::FETCH_ASSOC);
|
|
125
|
|
|
}
|
|
126
|
|
|
}
|
|
127
|
|
|
catch(\Exception $e) {
|
|
128
|
|
|
$this->error($e);
|
|
129
|
|
|
}
|
|
130
|
1 |
|
}
|
|
131
|
|
|
|
|
132
|
|
|
/**
|
|
133
|
|
|
* Returns a single record for a select query for the chosen table
|
|
134
|
|
|
* @param string $table This should be the table you wish to select the values from
|
|
135
|
|
|
* @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).
|
|
136
|
|
|
* @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).
|
|
137
|
|
|
* @param array|string $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')
|
|
138
|
|
|
* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
|
|
139
|
|
|
* @return array Returns a single table record as the standard array when running SQL queries
|
|
140
|
|
|
*/
|
|
141
|
1 |
|
public function select($table, $where = array(), $fields = '*', $order = array(), $cache = true) {
|
|
142
|
1 |
|
return $this->selectAll($table, $where, $fields, $order, 1, $cache);
|
|
|
|
|
|
|
143
|
|
|
}
|
|
144
|
|
|
|
|
145
|
|
|
/**
|
|
146
|
|
|
* Returns a multidimensional array of the results from the selected table given the given parameters
|
|
147
|
|
|
* @param string $table This should be the table you wish to select the values from
|
|
148
|
|
|
* @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).
|
|
149
|
|
|
* @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).
|
|
150
|
|
|
* @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')
|
|
151
|
|
|
* @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)
|
|
152
|
|
|
* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
|
|
153
|
|
|
* @return array Returns a multidimensional array with the chosen fields from the table
|
|
154
|
|
|
*/
|
|
155
|
3 |
|
public function selectAll($table, $where = array(), $fields = '*', $order = array(), $limit = 0, $cache = true) {
|
|
156
|
3 |
|
$this->buildSelectQuery(SafeString::makeSafe($table), $where, $fields, $order, $limit);
|
|
157
|
3 |
|
$result = $this->executeQuery($cache);
|
|
158
|
3 |
|
if(!$result) {
|
|
159
|
3 |
|
if($limit === 1) {$result = $this->query->fetch(PDO::FETCH_ASSOC);} // Reduce the memory usage if only one record and increase performance
|
|
160
|
2 |
|
else{$result = $this->query->fetchAll(PDO::FETCH_ASSOC);}
|
|
161
|
3 |
|
if($cache && $this->cacheEnabled) {$this->setCache($this->key, $result);}
|
|
162
|
|
|
}
|
|
163
|
3 |
|
return $result ? $result : false;
|
|
164
|
|
|
}
|
|
165
|
|
|
|
|
166
|
|
|
/**
|
|
167
|
|
|
* Returns a single column value for a given query
|
|
168
|
|
|
* @param string $table This should be the table you wish to select the values from
|
|
169
|
|
|
* @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).
|
|
170
|
|
|
* @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).
|
|
171
|
|
|
* @param int $colNum This should be the column number you wish to get (starts at 0)
|
|
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') so it can be done in both directions
|
|
173
|
|
|
* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
|
|
174
|
|
|
* @return mixed If a result is found will return the value of the colum given else will return false
|
|
175
|
|
|
*/
|
|
176
|
|
|
public function fetchColumn($table, $where = array(), $fields = '*', $colNum = 0, $order = array(), $cache = true) {
|
|
177
|
|
|
$this->buildSelectQuery(SafeString::makeSafe($table), $where, $fields, $order, 1);
|
|
178
|
|
|
$result = $this->executeQuery($cache);
|
|
179
|
|
|
if(!$result) {
|
|
180
|
|
|
$result = $this->query->fetchColumn(intval($colNum));
|
|
181
|
|
|
if($cache && $this->cacheEnabled) {$this->setCache($this->key, $result);}
|
|
182
|
|
|
}
|
|
183
|
|
|
return $result;
|
|
184
|
|
|
}
|
|
185
|
|
|
|
|
186
|
|
|
/**
|
|
187
|
|
|
* Inserts into database using the prepared PDO statements
|
|
188
|
|
|
* @param string $table This should be the table you wish to insert the values into
|
|
189
|
|
|
* @param array $records This should be the field names and values in the format of array('fieldname' => 'value', 'fieldname2' => 'value2', etc.)
|
|
190
|
|
|
* @return boolean If data is inserted returns true else returns false
|
|
191
|
|
|
*/
|
|
192
|
2 |
|
public function insert($table, $records) {
|
|
193
|
2 |
|
unset($this->prepare);
|
|
194
|
|
|
|
|
195
|
2 |
|
$this->sql = sprintf("INSERT INTO `%s` (%s) VALUES (%s);", SafeString::makeSafe($table), $this->fields($records, true), implode(', ', $this->prepare));
|
|
196
|
2 |
|
$this->executeQuery(false);
|
|
197
|
2 |
|
return $this->numRows() ? true : false;
|
|
198
|
|
|
}
|
|
199
|
|
|
|
|
200
|
|
|
/**
|
|
201
|
|
|
* Updates values in a database using the provide variables
|
|
202
|
|
|
* @param string $table This should be the table you wish to update the values for
|
|
203
|
|
|
* @param array $records This should be the field names and new values in the format of array('fieldname' => 'newvalue', 'fieldname2' => 'newvalue2', etc.)
|
|
204
|
|
|
* @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).
|
|
205
|
|
|
* @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
|
|
206
|
|
|
* @return boolean Returns true if update is successful else returns false
|
|
207
|
|
|
*/
|
|
208
|
2 |
|
public function update($table, $records, $where = array(), $limit = 0) {
|
|
209
|
2 |
|
$this->sql = sprintf("UPDATE `%s` SET %s %s%s;", SafeString::makeSafe($table), $this->fields($records), $this->where($where), $this->limit($limit));
|
|
210
|
2 |
|
$this->executeQuery(false);
|
|
211
|
2 |
|
return $this->numRows() ? true : false;
|
|
212
|
|
|
}
|
|
213
|
|
|
|
|
214
|
|
|
/**
|
|
215
|
|
|
* Deletes records from the given table based on the variables given
|
|
216
|
|
|
* @param string $table This should be the table you wish to delete the records from
|
|
217
|
|
|
* @param array $where This should be an array of for the where statement
|
|
218
|
|
|
* @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
|
|
219
|
|
|
*/
|
|
220
|
2 |
|
public function delete($table, $where, $limit = 0) {
|
|
221
|
2 |
|
$this->sql = sprintf("DELETE FROM `%s` %s%s;", SafeString::makeSafe($table), $this->where($where), $this->limit($limit));
|
|
222
|
2 |
|
$this->executeQuery(false);
|
|
223
|
2 |
|
return $this->numRows() ? true : false;
|
|
224
|
|
|
}
|
|
225
|
|
|
|
|
226
|
|
|
/**
|
|
227
|
|
|
* Count the number of return results
|
|
228
|
|
|
* @param string $table The table you wish to count the result of
|
|
229
|
|
|
* @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).
|
|
230
|
|
|
* @param boolean $cache If the query should be cached or loaded from cache set to true else set to false
|
|
231
|
|
|
* @return int Returns the number of results
|
|
232
|
|
|
*/
|
|
233
|
1 |
|
public function count($table, $where = array(), $cache = true) {
|
|
234
|
1 |
|
$this->sql = sprintf("SELECT count(*) FROM `%s`%s;", SafeString::makeSafe($table), $this->where($where));
|
|
235
|
1 |
|
$this->key = md5($this->database.$this->sql.serialize($this->values));
|
|
236
|
|
|
|
|
237
|
1 |
|
$result = $this->executeQuery($cache);
|
|
238
|
1 |
|
if(!$result) {
|
|
239
|
1 |
|
$result = $this->query->fetchColumn();
|
|
240
|
1 |
|
if($cache && $this->cacheEnabled) {$this->setCache($this->key, $result);}
|
|
241
|
|
|
}
|
|
242
|
1 |
|
return $result;
|
|
243
|
|
|
}
|
|
244
|
|
|
|
|
245
|
|
|
/**
|
|
246
|
|
|
* Truncates a given table from the selected database so there are no values in the table
|
|
247
|
|
|
* @param string $table This should be the table you wish to truncate
|
|
248
|
|
|
* @return boolean If the table is emptied returns true else returns false
|
|
249
|
|
|
*/
|
|
250
|
|
|
public function truncate($table) {
|
|
251
|
|
|
try{
|
|
252
|
|
|
$this->sql = sprintf("TRUNCATE TABLE `%s`", SafeString::makeSafe($table));
|
|
253
|
|
|
$this->query = $this->db->exec($this->sql);
|
|
254
|
|
|
}
|
|
255
|
|
|
catch(\Exception $e) {
|
|
256
|
|
|
$this->error($e);
|
|
257
|
|
|
}
|
|
258
|
|
|
return $this->query ? true : false;
|
|
259
|
|
|
}
|
|
260
|
|
|
|
|
261
|
|
|
/**
|
|
262
|
|
|
* Returns the number of rows for the last query sent
|
|
263
|
|
|
* @return int Returns the number of rows for the last query
|
|
264
|
|
|
*/
|
|
265
|
7 |
|
public function numRows() {
|
|
266
|
7 |
|
if(isset($this->query)) {
|
|
267
|
7 |
|
return $this->query->rowCount();
|
|
268
|
|
|
}
|
|
269
|
|
|
return 0;
|
|
270
|
|
|
}
|
|
271
|
|
|
|
|
272
|
|
|
/**
|
|
273
|
|
|
* Returns the number of rows for the last query sent (Looks a the numRows() function just added incase of habbit)
|
|
274
|
|
|
* @return int Returns the number of rows for the last query
|
|
275
|
|
|
*/
|
|
276
|
1 |
|
public function rowCount() {
|
|
277
|
1 |
|
return $this->numRows();
|
|
278
|
|
|
}
|
|
279
|
|
|
|
|
280
|
|
|
/**
|
|
281
|
|
|
* Returns the ID of the last record last inserted
|
|
282
|
|
|
* @param string $name This should be the name of the sequence object you wish to retrieve
|
|
283
|
|
|
* @return int|string Returns the last inserted ID of the last insert item if $name is null else returns string with sequenced object
|
|
284
|
|
|
*/
|
|
285
|
1 |
|
public function lastInsertId($name = null) {
|
|
286
|
1 |
|
return $this->db->lastInsertId($name);
|
|
287
|
|
|
}
|
|
288
|
|
|
|
|
289
|
|
|
/**
|
|
290
|
|
|
* Returns the index of the given table or tables within the database
|
|
291
|
|
|
* @param string|array $table Table can wither be a standard string with a single table name or an array with multiple table names
|
|
292
|
|
|
* @return array Returns the table index for the selected table as an array
|
|
293
|
|
|
*/
|
|
294
|
|
|
public function fulltextIndex($table) {
|
|
295
|
|
|
$fieldlist = array();
|
|
296
|
|
|
if(is_array($table)) {
|
|
297
|
|
|
foreach($table as $name) {
|
|
298
|
|
|
$fieldlist[$name] = $this->fulltextIndex($name);
|
|
299
|
|
|
}
|
|
300
|
|
|
}else{
|
|
301
|
|
|
try{
|
|
302
|
|
|
$this->query = $this->db->prepare("SHOW INDEX FROM ?;");
|
|
303
|
|
|
$this->query->execute($table);
|
|
304
|
|
|
}
|
|
305
|
|
|
catch(\Exception $e) {
|
|
306
|
|
|
$this->error($e);
|
|
307
|
|
|
}
|
|
308
|
|
|
|
|
309
|
|
|
while($index = $this->query->fetchAll(PDO::FETCH_ASSOC)) {
|
|
310
|
|
|
if($index['Index_type'] == 'FULLTEXT' && $index['Key_name'] == 'fulltext') {
|
|
311
|
|
|
$fieldlist[] = $index['Column_name'];
|
|
312
|
|
|
}
|
|
313
|
|
|
}
|
|
314
|
|
|
}
|
|
315
|
|
|
return $fieldlist;
|
|
316
|
|
|
}
|
|
317
|
|
|
|
|
318
|
|
|
/**
|
|
319
|
|
|
* Checks to see if a connection has been made to the server
|
|
320
|
|
|
* @return boolean
|
|
321
|
|
|
*/
|
|
322
|
2 |
|
public function isConnected() {
|
|
323
|
2 |
|
return is_object($this->db) ? true : false;
|
|
324
|
|
|
}
|
|
325
|
|
|
|
|
326
|
|
|
/**
|
|
327
|
|
|
* Returns the server version information
|
|
328
|
|
|
*/
|
|
329
|
|
|
public function serverVersion() {
|
|
330
|
|
|
return $this->db->getAttribute(PDO::ATTR_SERVER_VERSION);
|
|
331
|
|
|
}
|
|
332
|
|
|
|
|
333
|
|
|
/**
|
|
334
|
|
|
* Sets the location of the log files
|
|
335
|
|
|
* @param string $location This should be where you wish the logs to be stored
|
|
336
|
|
|
* @return $this
|
|
337
|
|
|
*/
|
|
338
|
|
|
public function setLogLocation($location = false) {
|
|
339
|
|
|
if($location === false) {
|
|
340
|
|
|
$location = dirname(__FILE__).DIRECTORY_SEPARATOR.'logs'.DIRECTORY_SEPARATOR;
|
|
341
|
|
|
}
|
|
342
|
|
|
$this->logLocation = $location;
|
|
343
|
|
|
if (!file_exists($location)) {
|
|
344
|
|
|
mkdir($location, 0777, true);
|
|
345
|
|
|
}
|
|
346
|
|
|
return $this;
|
|
347
|
|
|
}
|
|
348
|
|
|
|
|
349
|
|
|
/**
|
|
350
|
|
|
* Displays the error massage which occurs
|
|
351
|
|
|
* @param \Exception $error This should be an instance of Exception
|
|
352
|
|
|
*/
|
|
353
|
1 |
|
private function error($error) {
|
|
354
|
1 |
|
if($this->logErrors) {
|
|
355
|
1 |
|
$file = $this->logLocation.'db-errors.txt';
|
|
356
|
1 |
|
$current = file_get_contents($file);
|
|
357
|
1 |
|
$current .= date('d/m/Y H:i:s')." ERROR: ".$error->getMessage()." on ".$this->sql."\n";
|
|
358
|
1 |
|
file_put_contents($file, $current);
|
|
359
|
|
|
}
|
|
360
|
1 |
|
if($this->displayErrors) {
|
|
361
|
|
|
die('ERROR: '.$error->getMessage().' on '.$this->sql);
|
|
362
|
|
|
}
|
|
363
|
1 |
|
}
|
|
364
|
|
|
|
|
365
|
|
|
/**
|
|
366
|
|
|
* Writes all queries to a log file
|
|
367
|
|
|
*/
|
|
368
|
|
|
public function writeQueryToLog() {
|
|
369
|
|
|
$file = $this->logLocation.'queries.txt';
|
|
370
|
|
|
$current = file_get_contents($file);
|
|
371
|
|
|
$current .= "SQL: ".$this->sql.":".serialize($this->values)."\n";
|
|
372
|
|
|
file_put_contents($file, $current);
|
|
373
|
|
|
}
|
|
374
|
|
|
|
|
375
|
|
|
/**
|
|
376
|
|
|
* Closes the PDO database connection by setting the connection to NULL
|
|
377
|
|
|
*/
|
|
378
|
|
|
public function closeDatabase() {
|
|
379
|
|
|
$this->db = null;
|
|
380
|
|
|
}
|
|
381
|
|
|
|
|
382
|
|
|
/**
|
|
383
|
|
|
* Build the SQL query but doesn't execute it
|
|
384
|
|
|
* @param string $table This should be the table you wish to select the values from
|
|
385
|
|
|
* @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).
|
|
386
|
|
|
* @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).
|
|
387
|
|
|
* @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
|
|
388
|
|
|
* @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)
|
|
389
|
|
|
*/
|
|
390
|
3 |
|
protected function buildSelectQuery($table, $where = array(), $fields = '*', $order = array(), $limit = 0) {
|
|
391
|
3 |
|
if(is_array($fields)) {
|
|
392
|
|
|
$selectfields = array();
|
|
393
|
|
|
foreach($fields as $field => $value) {
|
|
394
|
|
|
$selectfields[] = sprintf("`%s`", SafeString::makeSafe($value));
|
|
395
|
|
|
}
|
|
396
|
|
|
$fieldList = implode(', ', $selectfields);
|
|
397
|
|
|
}
|
|
398
|
3 |
|
else{$fieldList = '*';}
|
|
399
|
|
|
|
|
400
|
3 |
|
$this->sql = sprintf("SELECT %s FROM `%s`%s%s%s;", $fieldList, SafeString::makeSafe($table), $this->where($where), $this->orderBy($order), $this->limit($limit));
|
|
401
|
3 |
|
$this->key = md5($this->database.$this->sql.serialize($this->values));
|
|
402
|
3 |
|
}
|
|
403
|
|
|
|
|
404
|
|
|
/**
|
|
405
|
|
|
* Execute the current query if no cache value is available
|
|
406
|
|
|
* @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
|
|
407
|
|
|
* @return mixed If a cached value exists will be returned else if cache is not checked and query is executed will not return anything
|
|
408
|
|
|
*/
|
|
409
|
10 |
|
protected function executeQuery($cache = true) {
|
|
410
|
10 |
|
if($this->logQueries) {$this->writeQueryToLog();}
|
|
411
|
10 |
|
if($cache && $this->cacheEnabled && $this->getCache($this->key)) {
|
|
412
|
|
|
return $this->cacheValue;
|
|
413
|
|
|
}
|
|
414
|
|
|
try{
|
|
415
|
10 |
|
$this->query = $this->db->prepare($this->sql);
|
|
416
|
10 |
|
$this->bindValues($this->values);
|
|
417
|
10 |
|
$this->query->execute();
|
|
418
|
9 |
|
unset($this->values);
|
|
419
|
9 |
|
$this->values = [];
|
|
420
|
|
|
}
|
|
421
|
2 |
|
catch(\Exception $e) {
|
|
422
|
2 |
|
$this->error($e);
|
|
423
|
|
|
}
|
|
424
|
10 |
|
}
|
|
425
|
|
|
|
|
426
|
|
|
/**
|
|
427
|
|
|
* This outputs the SQL where query based on a given array
|
|
428
|
|
|
* @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))
|
|
429
|
|
|
* @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
|
|
430
|
|
|
*/
|
|
431
|
|
|
private function where($where) {
|
|
432
|
|
|
if(is_array($where) && !empty($where)) {
|
|
433
|
|
|
$wherefields = array();
|
|
434
|
|
|
foreach($where as $field => $value) {
|
|
435
|
|
|
$wherefields[] = $this->formatValues($field, $value);
|
|
436
|
|
|
}
|
|
437
|
|
|
if(!empty($wherefields)) {
|
|
438
|
|
|
return " WHERE ".implode(' AND ', $wherefields);
|
|
439
|
|
|
}
|
|
440
|
|
|
}
|
|
441
|
|
|
return false;
|
|
442
|
|
|
}
|
|
443
|
|
|
|
|
444
|
|
|
/**
|
|
445
|
|
|
* Sets the order sting for the SQL query based on an array or string
|
|
446
|
|
|
* @param array|string $order This should be either set to array('fieldname' => 'ASC/DESC') or RAND()
|
|
447
|
|
|
* @return string|false If the SQL query has an valid order by will return a string else returns false
|
|
448
|
|
|
*/
|
|
449
|
1 |
|
private function orderBy($order) {
|
|
450
|
1 |
|
if(is_array($order) && !empty(array_filter($order))) {
|
|
451
|
1 |
|
$string = array();
|
|
452
|
1 |
|
foreach($order as $fieldorder => $fieldvalue) {
|
|
453
|
1 |
|
if(!empty($fieldorder) && !empty($fieldvalue)) {
|
|
454
|
1 |
|
$string[] = sprintf("`%s` %s", SafeString::makeSafe($fieldorder), strtoupper(SafeString::makeSafe($fieldvalue)));
|
|
455
|
|
|
}
|
|
456
|
|
|
elseif($fieldvalue === 'RAND()') {
|
|
457
|
1 |
|
$string[] = $fieldvalue;
|
|
458
|
|
|
}
|
|
459
|
|
|
}
|
|
460
|
1 |
|
return sprintf(" ORDER BY %s", implode(", ", $string));
|
|
461
|
|
|
}
|
|
462
|
|
|
elseif($order == 'RAND()') {
|
|
463
|
|
|
return " ORDER BY RAND()";
|
|
464
|
|
|
}
|
|
465
|
|
|
return false;
|
|
466
|
|
|
}
|
|
467
|
|
|
|
|
468
|
|
|
/**
|
|
469
|
|
|
* Build the field list for the query
|
|
470
|
|
|
* @param array $records This should be an array listing all of the fields
|
|
471
|
|
|
* @param boolean $insert If this is an insert statement should be set to true to create the correct amount of queries for the prepared statement
|
|
472
|
|
|
* @return string The fields list will be returned as a string to insert into the SQL query
|
|
473
|
|
|
*/
|
|
474
|
|
|
private function fields($records, $insert = false) {
|
|
475
|
|
|
$fields = array();
|
|
476
|
|
|
|
|
477
|
|
|
foreach($records as $field => $value) {
|
|
478
|
|
|
if($insert === true) {
|
|
479
|
|
|
$fields[] = sprintf("`%s`", SafeString::makeSafe($field));
|
|
480
|
|
|
$this->prepare[] = '?';
|
|
481
|
|
|
}
|
|
482
|
|
|
else{
|
|
483
|
|
|
$fields[] = sprintf("`%s` = ?", SafeString::makeSafe($field));
|
|
484
|
|
|
}
|
|
485
|
|
|
$this->values[] = $value;
|
|
486
|
|
|
}
|
|
487
|
|
|
return implode(', ', $fields);
|
|
488
|
|
|
}
|
|
489
|
|
|
|
|
490
|
|
|
/**
|
|
491
|
|
|
* Returns the limit SQL for the current query as a string
|
|
492
|
|
|
* @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
|
|
493
|
|
|
* @return string|false Will return the LIMIT string for the current query if it is valid else returns false
|
|
494
|
|
|
*/
|
|
495
|
1 |
|
private function limit($limit = 0) {
|
|
496
|
1 |
|
if(is_array($limit) && !empty(array_filter($limit))) {
|
|
497
|
|
|
foreach($limit as $start => $end) {
|
|
498
|
|
|
return " LIMIT ".intval($start).", ".intval($end);
|
|
499
|
|
|
}
|
|
500
|
|
|
}
|
|
501
|
1 |
|
elseif((int)$limit > 0) {
|
|
502
|
1 |
|
return " LIMIT ".intval($limit);
|
|
503
|
|
|
}
|
|
504
|
1 |
|
return false;
|
|
505
|
|
|
}
|
|
506
|
|
|
|
|
507
|
|
|
|
|
508
|
|
|
/**
|
|
509
|
|
|
* Set the cache with a key and value
|
|
510
|
|
|
* @param string $key The unique key to store the value against
|
|
511
|
|
|
* @param mixed $value The value of the MYSQL query
|
|
512
|
|
|
*/
|
|
513
|
|
|
public function setCache($key, $value) {
|
|
514
|
|
|
if($this->cacheEnabled) {
|
|
515
|
|
|
$this->cacheObj->save($key, $value);
|
|
516
|
|
|
}
|
|
517
|
|
|
}
|
|
518
|
|
|
|
|
519
|
|
|
/**
|
|
520
|
|
|
* Get the results for a given key
|
|
521
|
|
|
* @param string $key The unique key to check for stored variables
|
|
522
|
|
|
* @return mixed Returned the cached results from
|
|
523
|
|
|
*/
|
|
524
|
|
|
public function getCache($key) {
|
|
525
|
|
|
if($this->modified === true || !$this->cacheEnabled) {return false;}
|
|
526
|
|
|
else{
|
|
527
|
|
|
$this->cacheValue = $this->cacheObj->fetch($key);
|
|
528
|
|
|
return $this->cacheValue;
|
|
529
|
|
|
}
|
|
530
|
|
|
}
|
|
531
|
|
|
|
|
532
|
|
|
/**
|
|
533
|
|
|
* Clears the cache
|
|
534
|
|
|
*/
|
|
535
|
|
|
public function flushDB() {
|
|
536
|
|
|
$this->cacheObj->deleteAll();
|
|
537
|
|
|
}
|
|
538
|
|
|
|
|
539
|
|
|
/**
|
|
540
|
|
|
* Format the where queries and set the prepared values
|
|
541
|
|
|
* @param string $field This should be the field name in the database
|
|
542
|
|
|
* @param mixed $value This should be the value which should either be a string or an array if it contains an operator
|
|
543
|
|
|
* @return string This should be the string to add to the SQL query
|
|
544
|
|
|
*/
|
|
545
|
2 |
|
protected function formatValues($field, $value) {
|
|
546
|
2 |
|
if(!is_array($value) && Operators::isOperatorValid($value) && !Operators::isOperatorPrepared($value)) {
|
|
547
|
|
|
return sprintf("`%s` %s", SafeString::makeSafe($field), Operators::getOperatorFormat($value));
|
|
548
|
|
|
}
|
|
549
|
2 |
|
elseif(is_array($value)) {
|
|
550
|
2 |
|
if(!is_array(array_values($value)[0])) {
|
|
551
|
2 |
|
$this->values[] = (isset($value[1]) ? $value[1] : array_values($value)[0]);
|
|
552
|
2 |
|
$operator = (isset($value[0]) ? $value[0] : key($value));
|
|
553
|
|
|
}
|
|
554
|
|
|
else{
|
|
555
|
|
|
foreach(array_values($value)[0] as $op => $array_value) {
|
|
556
|
|
|
$this->values[] = $array_value;
|
|
557
|
|
|
}
|
|
558
|
|
|
$operator = key($value);
|
|
559
|
|
|
}
|
|
560
|
2 |
|
return sprintf("`%s` %s", SafeString::makeSafe($field), Operators::getOperatorFormat($operator));
|
|
561
|
|
|
|
|
562
|
|
|
}
|
|
563
|
|
|
$this->values[] = $value;
|
|
564
|
|
|
return sprintf("`%s` = ?", SafeString::makeSafe($field));
|
|
565
|
|
|
}
|
|
566
|
|
|
|
|
567
|
|
|
/**
|
|
568
|
|
|
* Band values to use in the query
|
|
569
|
|
|
* @param array $values This should be the values being used in the query
|
|
570
|
|
|
*/
|
|
571
|
9 |
|
protected function bindValues($values) {
|
|
572
|
9 |
|
if(is_array($values)) {
|
|
573
|
9 |
|
foreach($values as $i => $value) {
|
|
574
|
8 |
|
if(is_numeric($value) && intval($value) == $value) {$type = PDO::PARAM_INT; $value = intval($value);}
|
|
575
|
3 |
|
elseif(is_null($value) || $value === 'NULL') {$type = PDO::PARAM_NULL; $value = NULL;}
|
|
576
|
3 |
|
elseif(is_bool($value)) {$type = PDO::PARAM_BOOL;}
|
|
577
|
3 |
|
else{$type = PDO::PARAM_STR;}
|
|
578
|
8 |
|
$this->query->bindValue(intval($i + 1), $value, $type);
|
|
579
|
|
|
}
|
|
580
|
|
|
}
|
|
581
|
9 |
|
}
|
|
582
|
|
|
}
|
|
583
|
|
|
|
AdamB7586 /
pdo-dbal
This check looks at variables that have been passed in as parameters and are passed out again to other methods.
If the outgoing method call has stricter type requirements than the method itself, an issue is raised.
An additional type check may prevent trouble.