1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* This class handles building sql query statement and check |
4
|
|
|
* that the table exist in the database. |
5
|
|
|
* |
6
|
|
|
* @package Ibonly\PotatoORM\DatabaseQuery |
7
|
|
|
* @author Ibraheem ADENIYI <[email protected]> |
8
|
|
|
* @license MIT <https://opensource.org/licenses/MIT> |
9
|
|
|
*/ |
10
|
|
|
|
11
|
|
|
namespace Ibonly\PotatoORM; |
12
|
|
|
|
13
|
|
|
use PDOException; |
14
|
|
|
use Ibonly\PotatoORM\DataNotFoundException; |
15
|
|
|
use Ibonly\PotatoORM\DatabaseQueryInterface; |
16
|
|
|
use Ibonly\PotatoORM\ColumnNotExistExeption; |
17
|
|
|
use Ibonly\PotatoORM\InvalidConnectionException; |
18
|
|
|
use Ibonly\PotatoORM\TableDoesNotExistException; |
19
|
|
|
|
20
|
|
|
class DatabaseQuery implements DatabaseQueryInterface |
21
|
|
|
{ |
22
|
|
|
//Inject the inflector trait, file upload trait |
23
|
|
|
use Inflector, Upload; |
24
|
|
|
|
25
|
|
|
protected $properties = []; |
26
|
|
|
protected $output; |
27
|
|
|
|
28
|
|
|
public function __set($property, $value) |
29
|
|
|
{ |
30
|
|
|
$this->properties[$property] = $value; |
31
|
|
|
} |
32
|
|
|
|
33
|
|
|
public function __get($property) |
34
|
|
|
{ |
35
|
|
|
return $this->properties[$property]; |
36
|
|
|
} |
37
|
|
|
|
38
|
|
|
public function getProperties() |
39
|
|
|
{ |
40
|
|
|
return $this->properties; |
41
|
|
|
} |
42
|
|
|
|
43
|
|
|
/** |
44
|
|
|
* connect Setup database connection |
45
|
|
|
*/ |
46
|
|
|
protected static function connect() |
47
|
|
|
{ |
48
|
|
|
return new DBConfig(); |
49
|
|
|
} |
50
|
|
|
|
51
|
|
|
/** |
52
|
|
|
* stripclassName() |
53
|
|
|
* |
54
|
|
|
* @return string |
55
|
|
|
*/ |
56
|
|
|
public static function stripclassName() |
57
|
|
|
{ |
58
|
|
|
$className = strtolower(get_called_class()); |
59
|
|
|
$nameOfClass = explode("\\", $className); |
60
|
|
|
|
61
|
|
|
return end($nameOfClass); |
62
|
|
|
} |
63
|
|
|
|
64
|
|
|
/** |
65
|
|
|
* Get the table name if defined in the model |
66
|
|
|
* |
67
|
|
|
* @return string |
68
|
|
|
*/ |
69
|
|
|
public function tableName() |
70
|
|
|
{ |
71
|
|
|
return (isset($this->table)) ? $this->table : null; |
|
|
|
|
72
|
|
|
} |
73
|
|
|
/** |
74
|
|
|
* getClassName() |
75
|
|
|
* |
76
|
|
|
* @return string |
77
|
|
|
*/ |
78
|
|
|
public function getClassName() |
79
|
|
|
{ |
80
|
|
|
return ($this->tableName() === null) ? self::pluralize(self::stripclassName()) : $this->tableName(); |
81
|
|
|
} |
82
|
|
|
|
83
|
|
|
/** |
84
|
|
|
* getTableName() |
85
|
|
|
* |
86
|
|
|
* @return string |
87
|
|
|
*/ |
88
|
|
|
protected function getTableName($connection) |
89
|
|
|
{ |
90
|
|
|
return DatabaseQuery::checkTableName($this->getClassName(), $connection); |
91
|
|
|
} |
92
|
|
|
|
93
|
|
|
|
94
|
|
|
/** |
95
|
|
|
* sanitize(argument) Removes unwanted characters |
96
|
|
|
* |
97
|
|
|
* @param $value |
98
|
|
|
* |
99
|
|
|
* @return string |
100
|
|
|
*/ |
101
|
|
|
protected static function sanitize($value) |
102
|
|
|
{ |
103
|
|
|
$value = trim($value); |
104
|
|
|
$value = htmlentities($value); |
105
|
|
|
return $value; |
106
|
|
|
} |
107
|
|
|
|
108
|
|
|
/** |
109
|
|
|
* checkConnection |
110
|
|
|
* |
111
|
|
|
* @param $con |
112
|
|
|
* |
113
|
|
|
* @return string |
114
|
|
|
*/ |
115
|
|
|
protected static function checkConnection($con) |
116
|
|
|
{ |
117
|
|
|
return ($con === null) ? self::connect() : $con; |
118
|
|
|
} |
119
|
|
|
|
120
|
|
|
/** |
121
|
|
|
* checkTableExist Check if table already in the database |
122
|
|
|
* |
123
|
|
|
* @param $tablename |
124
|
|
|
* @param $con |
125
|
|
|
* |
126
|
|
|
* @return bool |
127
|
|
|
*/ |
128
|
|
View Code Duplication |
public function checkTableExist($table, $con=NULL) |
|
|
|
|
129
|
|
|
{ |
130
|
|
|
$connection = $this->checkConnection($con); |
131
|
|
|
$query = $connection->query("SELECT 1 FROM {$table} LIMIT 1"); |
|
|
|
|
132
|
|
|
if($query !== false) |
133
|
|
|
{ |
134
|
|
|
return true; |
135
|
|
|
} |
136
|
|
|
} |
137
|
|
|
|
138
|
|
|
/** |
139
|
|
|
* checkTableName Return the table name |
140
|
|
|
* |
141
|
|
|
* @param $tablename |
142
|
|
|
* @param $con |
143
|
|
|
* |
144
|
|
|
* @return string |
145
|
|
|
*/ |
146
|
|
View Code Duplication |
protected static function checkTableName($tableName, $con=NULL) |
|
|
|
|
147
|
|
|
{ |
148
|
|
|
$connection = self::checkConnection($con); |
149
|
|
|
|
150
|
|
|
$query = $connection->query("SELECT 1 FROM {$tableName} LIMIT 1"); |
|
|
|
|
151
|
|
|
if($query !== false) |
152
|
|
|
{ |
153
|
|
|
return $tableName; |
154
|
|
|
} |
155
|
|
|
throw new TableDoesNotExistException(); |
156
|
|
|
} |
157
|
|
|
|
158
|
|
|
/** |
159
|
|
|
* checkColumn Check if column exist in table |
160
|
|
|
* |
161
|
|
|
* @param $tableName |
162
|
|
|
* @param $columnName |
163
|
|
|
* @param $con |
164
|
|
|
* |
165
|
|
|
* @return string |
166
|
|
|
*/ |
167
|
|
|
protected static function checkColumn($tableName, $columnName, $con=NULL) |
168
|
|
|
{ |
169
|
|
|
$connection = self::checkConnection($con); |
170
|
|
|
|
171
|
|
|
$result = $connection->prepare("SELECT {$columnName} FROM {$tableName}"); |
|
|
|
|
172
|
|
|
$result->execute(); |
173
|
|
|
if (! $result->columnCount()) |
174
|
|
|
{ |
175
|
|
|
throw new ColumnNotExistExeption(); |
176
|
|
|
} |
177
|
|
|
return $columnName; |
178
|
|
|
} |
179
|
|
|
|
180
|
|
|
/** |
181
|
|
|
* buildColumn Build the column name |
182
|
|
|
* |
183
|
|
|
* @param $data |
184
|
|
|
* |
185
|
|
|
* @return string |
186
|
|
|
*/ |
187
|
|
View Code Duplication |
protected static function buildColumn($data) |
|
|
|
|
188
|
|
|
{ |
189
|
|
|
$counter = 0; |
190
|
|
|
$insertQuery = ""; |
191
|
|
|
$arraySize = count($data); |
192
|
|
|
|
193
|
|
|
foreach ($data as $key => $value) |
194
|
|
|
{ |
195
|
|
|
$counter++; |
196
|
|
|
$insertQuery .= self::sanitize($key); |
197
|
|
|
if($arraySize > $counter) |
198
|
|
|
$insertQuery .= ", "; |
199
|
|
|
} |
200
|
|
|
return $insertQuery; |
201
|
|
|
} |
202
|
|
|
|
203
|
|
|
/** |
204
|
|
|
* buildValues Build the column values |
205
|
|
|
* |
206
|
|
|
* @param $data |
207
|
|
|
* |
208
|
|
|
* @return string |
209
|
|
|
*/ |
210
|
|
View Code Duplication |
protected static function buildValues($data) |
|
|
|
|
211
|
|
|
{ |
212
|
|
|
$counter = 0; |
213
|
|
|
$insertQuery = ""; |
214
|
|
|
$arraySize = count($data); |
215
|
|
|
|
216
|
|
|
foreach ($data as $key => $value) |
217
|
|
|
{ |
218
|
|
|
$counter++; |
219
|
|
|
$insertQuery .= "'".self::sanitize($value) ."'"; |
220
|
|
|
if($arraySize > $counter) |
221
|
|
|
$insertQuery .= ", "; |
222
|
|
|
} |
223
|
|
|
return $insertQuery; |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
/** |
227
|
|
|
* buildClause Build the clause value |
228
|
|
|
* |
229
|
|
|
* @param $data |
230
|
|
|
* |
231
|
|
|
* @return string |
232
|
|
|
*/ |
233
|
|
View Code Duplication |
protected static function buildClause($tableName, $data) |
|
|
|
|
234
|
|
|
{ |
235
|
|
|
$counter = 0; |
236
|
|
|
$updateQuery = ""; |
237
|
|
|
$arraySize = count($data); |
238
|
|
|
|
239
|
|
|
foreach ($data as $key => $value) |
240
|
|
|
{ |
241
|
|
|
$counter++; |
242
|
|
|
$columnName = self::checkColumn($tableName, self::sanitize($key)); |
243
|
|
|
$updateQuery .= $columnName ." = '".self::sanitize($value)."'"; |
244
|
|
|
if ($arraySize > $counter) |
245
|
|
|
{ |
246
|
|
|
$updateQuery .= ", "; |
247
|
|
|
} |
248
|
|
|
} |
249
|
|
|
return $updateQuery; |
250
|
|
|
} |
251
|
|
|
|
252
|
|
|
/** |
253
|
|
|
* Get the fields to be fillables defined in the model |
254
|
|
|
* |
255
|
|
|
* @return string |
256
|
|
|
*/ |
257
|
|
|
protected function fields() |
258
|
|
|
{ |
259
|
|
|
if (isset($this->fillables)) { |
260
|
|
|
$this->output = (sizeof($this->fillables) > 0) ? implode($this->fillables, ',') : '*'; |
|
|
|
|
261
|
|
|
} else { |
262
|
|
|
$this->output = '*'; |
263
|
|
|
} |
264
|
|
|
|
265
|
|
|
return $this->output; |
266
|
|
|
} |
267
|
|
|
|
268
|
|
|
/** |
269
|
|
|
* selectAllQuery |
270
|
|
|
* |
271
|
|
|
* @return string |
272
|
|
|
*/ |
273
|
|
|
public function selectAllQuery($tableName) |
274
|
|
|
{ |
275
|
|
|
return "SELECT ".$this->fields()." FROM {$tableName}"; |
276
|
|
|
} |
277
|
|
|
|
278
|
|
|
/** |
279
|
|
|
* whereAndClause |
280
|
|
|
* |
281
|
|
|
* @return string |
282
|
|
|
*/ |
283
|
|
|
public static function whereAndClause($tableName, $data, $condition) |
284
|
|
|
{ |
285
|
|
|
$where = ""; |
286
|
|
|
$counter = 0; |
287
|
|
|
$arraySize = count($data); |
288
|
|
|
|
289
|
|
|
if ($data !== null) { |
290
|
|
|
foreach ($data as $key => $value) |
291
|
|
|
{ |
292
|
|
|
$counter++; |
293
|
|
|
$columnName = self::checkColumn($tableName, self::sanitize($key)); |
294
|
|
|
$where .= $tableName.'.'.$columnName ." = '".self::sanitize($value)."'"; |
295
|
|
|
if ($arraySize > $counter) |
296
|
|
|
{ |
297
|
|
|
$where .= " " . $condition . " "; |
298
|
|
|
} |
299
|
|
|
} |
300
|
|
|
} else { |
301
|
|
|
$where = ""; |
302
|
|
|
} |
303
|
|
|
|
304
|
|
|
return $where; |
305
|
|
|
} |
306
|
|
|
|
307
|
|
View Code Duplication |
public static function whereLikeClause($tableName, $data, $condition) |
|
|
|
|
308
|
|
|
{ |
309
|
|
|
$where = ""; |
310
|
|
|
$counter = 0; |
311
|
|
|
$arraySize = count($data); |
312
|
|
|
|
313
|
|
|
foreach ( $data as $key => $value ) |
314
|
|
|
{ |
315
|
|
|
$counter++; |
316
|
|
|
$columnName = self::checkColumn($tableName, self::sanitize($key)); |
317
|
|
|
$where .= $columnName ." LIKE '%".self::sanitize($value)."%'"; |
318
|
|
|
if ( $arraySize > $counter ) |
319
|
|
|
{ |
320
|
|
|
$where .= " " . $condition . " "; |
321
|
|
|
} |
322
|
|
|
} |
323
|
|
|
|
324
|
|
|
return $where; |
325
|
|
|
} |
326
|
|
|
|
327
|
|
|
/** |
328
|
|
|
* selectQuery |
329
|
|
|
* |
330
|
|
|
* @return string |
331
|
|
|
*/ |
332
|
|
|
public static function selectQuery($tableName, $fields, $data, $condition, $connection) |
333
|
|
|
{ |
334
|
|
|
$query = ""; |
|
|
|
|
335
|
|
|
$columnName = ""; |
336
|
|
|
|
337
|
|
|
try |
338
|
|
|
{ |
339
|
|
|
$arraySize = count($data); |
340
|
|
|
if( $arraySize > 1 && $condition == NULL) |
341
|
|
|
{ |
342
|
|
|
$query = "Please Supply the condition"; |
343
|
|
|
} |
344
|
|
|
else |
345
|
|
|
{ |
346
|
|
|
if ($condition === 'AND' || $condition === 'OR' || $condition === NULL) { |
347
|
|
|
$columnName = self::whereAndClause($tableName, $data, $condition); |
348
|
|
|
} elseif ($condition === 'LIKE') { |
349
|
|
|
$columnName = self::whereLikeClause($tableName, $data, $condition); |
350
|
|
|
} |
351
|
|
|
$query = "SELECT $fields FROM $tableName WHERE $columnName"; |
352
|
|
|
} |
353
|
|
|
} catch (PDOException $e) { |
354
|
|
|
$query = $e->getMessage(); |
355
|
|
|
} |
356
|
|
|
|
357
|
|
|
return $query; |
358
|
|
|
} |
359
|
|
|
|
360
|
|
|
/** |
361
|
|
|
* insertQuery |
362
|
|
|
* |
363
|
|
|
* @return string |
364
|
|
|
*/ |
365
|
|
|
public function insertQuery($tableName) |
366
|
|
|
{ |
367
|
|
|
$columnNames = self::buildColumn($this->properties); |
368
|
|
|
$values = self::buildValues($this->properties); |
369
|
|
|
|
370
|
|
|
return "INSERT INTO $tableName ({$columnNames}) VALUES ({$values})"; |
371
|
|
|
} |
372
|
|
|
|
373
|
|
|
/** |
374
|
|
|
* updateQuery |
375
|
|
|
* |
376
|
|
|
* @return string |
377
|
|
|
*/ |
378
|
|
|
public function updateQuery($tableName, $id) |
379
|
|
|
{ |
380
|
|
|
$values = self::buildClause($tableName, $this->properties); |
381
|
|
|
$updateQuery = "UPDATE $tableName SET {$values} WHERE id = $id"; |
382
|
|
|
|
383
|
|
|
return $updateQuery; |
384
|
|
|
} |
385
|
|
|
|
386
|
|
|
/** |
387
|
|
|
* query($query, $dbCOnnection) |
388
|
|
|
* Raw sql query |
389
|
|
|
* |
390
|
|
|
* @return object |
391
|
|
|
*/ |
392
|
|
View Code Duplication |
public function query($query, $con = NULL) |
|
|
|
|
393
|
|
|
{ |
394
|
|
|
$connection = self::checkConnection($con); |
395
|
|
|
|
396
|
|
|
$query = $connection->prepare($query); |
|
|
|
|
397
|
|
|
$query->execute(); |
398
|
|
|
if ($query->rowCount()) |
399
|
|
|
{ |
400
|
|
|
return new GetData($query->fetchAll($connection::FETCH_ASSOC)); |
401
|
|
|
} |
402
|
|
|
throw new DataNotFoundException(); |
403
|
|
|
} |
404
|
|
|
} |
Since your code implements the magic getter
_get
, this function will be called for any read access on an undefined variable. You can add the@property
annotation to your class or interface to document the existence of this variable.If the property has read access only, you can use the @property-read annotation instead.
Of course, you may also just have mistyped another name, in which case you should fix the error.
See also the PhpDoc documentation for @property.