Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
100.00% |
144 / 144 |
|
100.00% |
45 / 45 |
CRAP | |
100.00% |
1 / 1 |
| QueryBuilder | |
100.00% |
144 / 144 |
|
100.00% |
45 / 45 |
69 | |
100.00% |
1 / 1 |
| select | |
100.00% |
13 / 13 |
|
100.00% |
1 / 1 |
5 | |||
| selectMax | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| selectMin | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| selectAvg | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| selectSum | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| returning | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
2 | |||
| distinct | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| explain | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| table | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| from | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| like | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orLike | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| notLike | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orNotLike | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| having | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orHaving | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| where | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orWhere | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| whereIn | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orWhereIn | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| whereNotIn | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| orWhereNotIn | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| set | |
100.00% |
12 / 12 |
|
100.00% |
1 / 1 |
2 | |||
| join | |
100.00% |
8 / 8 |
|
100.00% |
1 / 1 |
1 | |||
| groupBy | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
2 | |||
| orderBy | |
100.00% |
13 / 13 |
|
100.00% |
1 / 1 |
4 | |||
| limit | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
1 | |||
| groupStart | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| notGroupStart | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| orGroupStart | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| orNotGroupStart | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| groupEnd | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| get | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
3 | |||
| getWhere | |
100.00% |
2 / 2 |
|
100.00% |
1 / 1 |
1 | |||
| countAll | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| countAllResults | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
2 | |||
| insert | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| insertBatch | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
2 | |||
| update | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| updateBatch | |
100.00% |
5 / 5 |
|
100.00% |
1 / 1 |
4 | |||
| delete | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| getCompiledSelect | |
100.00% |
3 / 3 |
|
100.00% |
1 / 1 |
2 | |||
| getCompiledInsert | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| getCompiledUpdate | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| getCompiledDelete | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
| 1 | <?php declare(strict_types=1); |
| 2 | /** |
| 3 | * Query |
| 4 | * |
| 5 | * SQL Query Builder / Database Abstraction Layer |
| 6 | * |
| 7 | * PHP version 8.1 |
| 8 | * |
| 9 | * @package Query |
| 10 | * @author Timothy J. Warren <tim@timshomepage.net> |
| 11 | * @copyright 2012 - 2022 Timothy J. Warren |
| 12 | * @license http://www.opensource.org/licenses/mit-license.html MIT License |
| 13 | * @link https://git.timshomepage.net/aviat/Query |
| 14 | * @version 4.0.0 |
| 15 | */ |
| 16 | namespace Query; |
| 17 | |
| 18 | use function is_array; |
| 19 | use function is_int; |
| 20 | use function mb_trim; |
| 21 | |
| 22 | use PDOStatement; |
| 23 | |
| 24 | /** |
| 25 | * Convenience class for creating sql queries |
| 26 | */ |
| 27 | class QueryBuilder extends QueryBuilderBase implements QueryBuilderInterface { |
| 28 | // -------------------------------------------------------------------------- |
| 29 | // ! Select Queries |
| 30 | // -------------------------------------------------------------------------- |
| 31 | /** |
| 32 | * Specifies rows to select in a query |
| 33 | */ |
| 34 | public function select(string $fields): self |
| 35 | { |
| 36 | // Split fields by comma |
| 37 | $fieldsArray = explode(',', $fields); |
| 38 | $fieldsArray = array_map('mb_trim', $fieldsArray); |
| 39 | |
| 40 | // Split on 'As' |
| 41 | foreach ($fieldsArray as $key => $field) |
| 42 | { |
| 43 | if (stripos($field, 'as') !== FALSE) |
| 44 | { |
| 45 | $fieldsArray[$key] = preg_split('` as `i', (string) $field); |
| 46 | $fieldsArray[$key] = array_map('mb_trim', $fieldsArray[$key]); |
| 47 | } |
| 48 | } |
| 49 | |
| 50 | // Quote the identifiers |
| 51 | $safeArray = $this->driver->quoteIdent($fieldsArray); |
| 52 | |
| 53 | unset($fieldsArray); |
| 54 | |
| 55 | // Join the strings back together |
| 56 | foreach ($safeArray as $i => $iValue) |
| 57 | { |
| 58 | if (is_array($iValue)) |
| 59 | { |
| 60 | $safeArray[$i] = implode(' AS ', $iValue); |
| 61 | } |
| 62 | } |
| 63 | |
| 64 | $this->state->appendSelectString(implode(', ', $safeArray)); |
| 65 | |
| 66 | return $this; |
| 67 | } |
| 68 | |
| 69 | /** |
| 70 | * Selects the maximum value of a field from a query |
| 71 | * |
| 72 | * @param string|bool $as |
| 73 | */ |
| 74 | public function selectMax(string $field, $as=FALSE): self |
| 75 | { |
| 76 | // Create the select string |
| 77 | $this->state->appendSelectString(' MAX'.$this->_select($field, $as)); |
| 78 | return $this; |
| 79 | } |
| 80 | |
| 81 | /** |
| 82 | * Selects the minimum value of a field from a query |
| 83 | * |
| 84 | * @param string|bool $as |
| 85 | */ |
| 86 | public function selectMin(string $field, $as=FALSE): self |
| 87 | { |
| 88 | // Create the select string |
| 89 | $this->state->appendSelectString(' MIN'.$this->_select($field, $as)); |
| 90 | return $this; |
| 91 | } |
| 92 | |
| 93 | /** |
| 94 | * Selects the average value of a field from a query |
| 95 | * |
| 96 | * @param string|bool $as |
| 97 | */ |
| 98 | public function selectAvg(string $field, $as=FALSE): self |
| 99 | { |
| 100 | // Create the select string |
| 101 | $this->state->appendSelectString(' AVG'.$this->_select($field, $as)); |
| 102 | return $this; |
| 103 | } |
| 104 | |
| 105 | /** |
| 106 | * Selects the sum of a field from a query |
| 107 | * |
| 108 | * @param string|bool $as |
| 109 | */ |
| 110 | public function selectSum(string $field, $as=FALSE): self |
| 111 | { |
| 112 | // Create the select string |
| 113 | $this->state->appendSelectString(' SUM'.$this->_select($field, $as)); |
| 114 | return $this; |
| 115 | } |
| 116 | |
| 117 | /** |
| 118 | * Add a 'returning' clause to an insert,update, or delete query |
| 119 | * |
| 120 | * @return $this |
| 121 | */ |
| 122 | public function returning(string $fields = ''): self |
| 123 | { |
| 124 | $this->returning = TRUE; |
| 125 | |
| 126 | // Re-use the string select field for generating the returning type clause |
| 127 | if ($fields !== '') |
| 128 | { |
| 129 | return $this->select($fields); |
| 130 | } |
| 131 | |
| 132 | return $this; |
| 133 | } |
| 134 | |
| 135 | /** |
| 136 | * Adds the 'distinct' keyword to a query |
| 137 | */ |
| 138 | public function distinct(): self |
| 139 | { |
| 140 | // Prepend the keyword to the select string |
| 141 | $this->state->setSelectString(' DISTINCT' . $this->state->getSelectString()); |
| 142 | return $this; |
| 143 | } |
| 144 | |
| 145 | /** |
| 146 | * Tell the database to give you the query plan instead of result set |
| 147 | */ |
| 148 | public function explain(): self |
| 149 | { |
| 150 | $this->explain = TRUE; |
| 151 | return $this; |
| 152 | } |
| 153 | |
| 154 | /** |
| 155 | * Specify the database table to select from |
| 156 | * |
| 157 | * Alias of `from` method to better match CodeIgniter 4 |
| 158 | * |
| 159 | * @param string $tableName |
| 160 | */ |
| 161 | public function table(string $tableName): self |
| 162 | { |
| 163 | return $this->from($tableName); |
| 164 | } |
| 165 | |
| 166 | /** |
| 167 | * Specify the database table to select from |
| 168 | */ |
| 169 | public function from(string $tableName): self |
| 170 | { |
| 171 | // Split identifiers on spaces |
| 172 | $identArray = explode(' ', mb_trim($tableName)); |
| 173 | $identArray = array_map('mb_trim', $identArray); |
| 174 | |
| 175 | // Quote the identifiers |
| 176 | $identArray[0] = $this->driver->quoteTable($identArray[0]); |
| 177 | $identArray = $this->driver->quoteIdent($identArray); |
| 178 | |
| 179 | // Paste it back together |
| 180 | $this->state->setFromString(implode(' ', $identArray)); |
| 181 | |
| 182 | return $this; |
| 183 | } |
| 184 | |
| 185 | // -------------------------------------------------------------------------- |
| 186 | // ! 'Like' methods |
| 187 | // -------------------------------------------------------------------------- |
| 188 | /** |
| 189 | * Creates a Like clause in the sql statement |
| 190 | */ |
| 191 | public function like(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self |
| 192 | { |
| 193 | return $this->_like($field, $values, LikeType::parse($pos)); |
| 194 | } |
| 195 | |
| 196 | /** |
| 197 | * Generates an OR Like clause |
| 198 | */ |
| 199 | public function orLike(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self |
| 200 | { |
| 201 | return $this->_like($field, $values, LikeType::parse($pos), 'LIKE', 'OR'); |
| 202 | } |
| 203 | |
| 204 | /** |
| 205 | * Generates a NOT LIKE clause |
| 206 | */ |
| 207 | public function notLike(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self |
| 208 | { |
| 209 | return $this->_like($field, $values, LikeType::parse($pos), 'NOT LIKE'); |
| 210 | } |
| 211 | |
| 212 | /** |
| 213 | * Generates a OR NOT LIKE clause |
| 214 | */ |
| 215 | public function orNotLike(string $field, mixed $values, LikeType|string $pos=LikeType::BOTH): self |
| 216 | { |
| 217 | return $this->_like($field, $values, LikeType::parse($pos), 'NOT LIKE', 'OR'); |
| 218 | } |
| 219 | |
| 220 | // -------------------------------------------------------------------------- |
| 221 | // ! Having methods |
| 222 | // -------------------------------------------------------------------------- |
| 223 | /** |
| 224 | * Generates a 'Having' clause |
| 225 | */ |
| 226 | public function having(mixed $key, mixed $values=[]): self |
| 227 | { |
| 228 | return $this->_having($key, $values); |
| 229 | } |
| 230 | |
| 231 | /** |
| 232 | * Generates a 'Having' clause prefixed with 'OR' |
| 233 | */ |
| 234 | public function orHaving(mixed $key, mixed $values=[]): self |
| 235 | { |
| 236 | return $this->_having($key, $values, 'OR'); |
| 237 | } |
| 238 | |
| 239 | // -------------------------------------------------------------------------- |
| 240 | // ! 'Where' methods |
| 241 | // -------------------------------------------------------------------------- |
| 242 | /** |
| 243 | * Specify condition(s) in the where clause of a query |
| 244 | * Note: this function works with key / value, or a |
| 245 | * passed array with key / value pairs |
| 246 | */ |
| 247 | public function where(mixed $key, mixed $values=[]): self |
| 248 | { |
| 249 | return $this->_whereString($key, $values); |
| 250 | } |
| 251 | |
| 252 | /** |
| 253 | * Where clause prefixed with "OR" |
| 254 | */ |
| 255 | public function orWhere(mixed $key, mixed $values=[]): self |
| 256 | { |
| 257 | return $this->_whereString($key, $values, 'OR'); |
| 258 | } |
| 259 | |
| 260 | /** |
| 261 | * Where clause with 'IN' statement |
| 262 | */ |
| 263 | public function whereIn(string $field, mixed $val=[]): self |
| 264 | { |
| 265 | return $this->_whereIn($field, $val); |
| 266 | } |
| 267 | |
| 268 | /** |
| 269 | * Where in statement prefixed with "or" |
| 270 | */ |
| 271 | public function orWhereIn(string $field, mixed $val=[]): self |
| 272 | { |
| 273 | return $this->_whereIn($field, $val, 'IN', 'OR'); |
| 274 | } |
| 275 | |
| 276 | /** |
| 277 | * WHERE NOT IN (FOO) clause |
| 278 | */ |
| 279 | public function whereNotIn(string $field, mixed $val=[]): self |
| 280 | { |
| 281 | return $this->_whereIn($field, $val, 'NOT IN'); |
| 282 | } |
| 283 | |
| 284 | /** |
| 285 | * OR WHERE NOT IN (FOO) clause |
| 286 | */ |
| 287 | public function orWhereNotIn(string $field, mixed $val=[]): self |
| 288 | { |
| 289 | return $this->_whereIn($field, $val, 'NOT IN', 'OR'); |
| 290 | } |
| 291 | |
| 292 | // -------------------------------------------------------------------------- |
| 293 | // ! Other Query Modifier methods |
| 294 | // -------------------------------------------------------------------------- |
| 295 | /** |
| 296 | * Sets values for inserts / updates / deletes |
| 297 | */ |
| 298 | public function set(mixed $key, mixed $val = NULL): self |
| 299 | { |
| 300 | $pairs = is_scalar($key) ? [$key => $val] : $key; |
| 301 | |
| 302 | $keys = array_keys($pairs); |
| 303 | $values = array_values($pairs); |
| 304 | |
| 305 | $this->state->appendSetArrayKeys($keys); |
| 306 | $this->state->appendValues($values); |
| 307 | |
| 308 | // Use the keys of the array to make the insert/update string |
| 309 | // Escape the field names |
| 310 | $this->state->setSetArrayKeys( |
| 311 | array_map([$this->driver, '_quote'], $this->state->getSetArrayKeys()) |
| 312 | ); |
| 313 | |
| 314 | // Generate the "set" string |
| 315 | $setString = implode('=?,', $this->state->getSetArrayKeys()); |
| 316 | $setString .= '=?'; |
| 317 | |
| 318 | $this->state->setSetString($setString); |
| 319 | |
| 320 | return $this; |
| 321 | } |
| 322 | |
| 323 | /** |
| 324 | * Creates a join phrase in a compiled query |
| 325 | */ |
| 326 | public function join(string $table, string $condition, JoinType|string $type=JoinType::INNER): self |
| 327 | { |
| 328 | // Prefix and quote table name |
| 329 | $tableArr = explode(' ', mb_trim($table)); |
| 330 | $tableArr[0] = $this->driver->quoteTable($tableArr[0]); |
| 331 | $tableArr = $this->driver->quoteIdent($tableArr); |
| 332 | $table = implode(' ', $tableArr); |
| 333 | |
| 334 | // Parse out the join condition |
| 335 | $parsedCondition = $this->parser->compileJoin($condition); |
| 336 | $condition = $table . ' ON ' . $parsedCondition; |
| 337 | |
| 338 | $this->state->appendMap("\n" . strtoupper(JoinType::parse($type)->value) . ' JOIN ', $condition, MapType::JOIN); |
| 339 | |
| 340 | return $this; |
| 341 | } |
| 342 | |
| 343 | /** |
| 344 | * Group the results by the selected field(s) |
| 345 | */ |
| 346 | public function groupBy(mixed $field): self |
| 347 | { |
| 348 | if ( ! is_scalar($field)) |
| 349 | { |
| 350 | $newGroupArray = array_merge( |
| 351 | $this->state->getGroupArray(), |
| 352 | array_map([$this->driver, 'quoteIdent'], $field) |
| 353 | ); |
| 354 | $this->state->setGroupArray($newGroupArray); |
| 355 | } |
| 356 | else |
| 357 | { |
| 358 | $this->state->appendGroupArray($this->driver->quoteIdent($field)); |
| 359 | } |
| 360 | |
| 361 | $this->state->setGroupString(' GROUP BY ' . implode(',', $this->state->getGroupArray())); |
| 362 | |
| 363 | return $this; |
| 364 | } |
| 365 | |
| 366 | /** |
| 367 | * Order the results by the selected field(s) |
| 368 | */ |
| 369 | public function orderBy(string $field, string $type=''): self |
| 370 | { |
| 371 | // When ordering by random, do an ascending order if the driver |
| 372 | // doesn't support random ordering |
| 373 | if (stripos($type, 'rand') !== FALSE) |
| 374 | { |
| 375 | $rand = $this->driver->getSql()->random(); |
| 376 | $type = $rand ?? 'ASC'; |
| 377 | } |
| 378 | |
| 379 | // Set fields for later manipulation |
| 380 | $field = $this->driver->quoteIdent($field); |
| 381 | $this->state->setOrderArray($field, $type); |
| 382 | |
| 383 | $orderClauses = []; |
| 384 | |
| 385 | // Flatten key/val pairs into an array of space-separated pairs |
| 386 | foreach($this->state->getOrderArray() as $k => $v) |
| 387 | { |
| 388 | $orderClauses[] = $k . ' ' . strtoupper($v); |
| 389 | } |
| 390 | |
| 391 | // Set the final string |
| 392 | $orderString = isset($rand) |
| 393 | ? "\nORDER BY".$rand |
| 394 | : "\nORDER BY ".implode(', ', $orderClauses); |
| 395 | |
| 396 | $this->state->setOrderString($orderString); |
| 397 | |
| 398 | return $this; |
| 399 | } |
| 400 | |
| 401 | /** |
| 402 | * Set a limit on the current sql statement |
| 403 | */ |
| 404 | public function limit(int $limit, ?int $offset=NULL): self |
| 405 | { |
| 406 | $this->state->setLimit($limit); |
| 407 | $this->state->setOffset($offset); |
| 408 | |
| 409 | return $this; |
| 410 | } |
| 411 | |
| 412 | // -------------------------------------------------------------------------- |
| 413 | // ! Query Grouping Methods |
| 414 | // -------------------------------------------------------------------------- |
| 415 | /** |
| 416 | * Adds a paren to the current query for query grouping |
| 417 | */ |
| 418 | public function groupStart(): self |
| 419 | { |
| 420 | $conj = empty($this->state->getQueryMap()) ? ' WHERE ' : ' '; |
| 421 | |
| 422 | $this->state->appendMap($conj, '(', MapType::GROUP_START); |
| 423 | |
| 424 | return $this; |
| 425 | } |
| 426 | |
| 427 | /** |
| 428 | * Adds a paren to the current query for query grouping, |
| 429 | * prefixed with 'NOT' |
| 430 | */ |
| 431 | public function notGroupStart(): self |
| 432 | { |
| 433 | $conj = empty($this->state->getQueryMap()) ? ' WHERE ' : ' AND '; |
| 434 | |
| 435 | $this->state->appendMap($conj, ' NOT (', MapType::GROUP_START); |
| 436 | |
| 437 | return $this; |
| 438 | } |
| 439 | |
| 440 | /** |
| 441 | * Adds a paren to the current query for query grouping, |
| 442 | * prefixed with 'OR' |
| 443 | */ |
| 444 | public function orGroupStart(): self |
| 445 | { |
| 446 | $this->state->appendMap('', ' OR (', MapType::GROUP_START); |
| 447 | |
| 448 | return $this; |
| 449 | } |
| 450 | |
| 451 | /** |
| 452 | * Adds a paren to the current query for query grouping, |
| 453 | * prefixed with 'OR NOT' |
| 454 | */ |
| 455 | public function orNotGroupStart(): self |
| 456 | { |
| 457 | $this->state->appendMap('', ' OR NOT (', MapType::GROUP_START); |
| 458 | |
| 459 | return $this; |
| 460 | } |
| 461 | |
| 462 | /** |
| 463 | * Ends a query group |
| 464 | */ |
| 465 | public function groupEnd(): self |
| 466 | { |
| 467 | $this->state->appendMap('', ')', MapType::GROUP_END); |
| 468 | |
| 469 | return $this; |
| 470 | } |
| 471 | |
| 472 | // -------------------------------------------------------------------------- |
| 473 | // ! Query execution methods |
| 474 | // -------------------------------------------------------------------------- |
| 475 | /** |
| 476 | * Select and retrieve all records from the current table, and/or |
| 477 | * execute current compiled query |
| 478 | */ |
| 479 | public function get(string $table='', ?int $limit=NULL, ?int $offset=NULL): PDOStatement |
| 480 | { |
| 481 | // Set the table |
| 482 | if ( ! empty($table)) |
| 483 | { |
| 484 | $this->from($table); |
| 485 | } |
| 486 | |
| 487 | // Set the limit, if it exists |
| 488 | if (is_int($limit)) |
| 489 | { |
| 490 | $this->limit($limit, $offset); |
| 491 | } |
| 492 | |
| 493 | return $this->_run(QueryType::SELECT, $table); |
| 494 | } |
| 495 | |
| 496 | /** |
| 497 | * Convenience method for get() with a where clause |
| 498 | * |
| 499 | * @param mixed $where |
| 500 | */ |
| 501 | public function getWhere(string $table, $where=[], ?int $limit=NULL, ?int $offset=NULL): PDOStatement |
| 502 | { |
| 503 | // Create the where clause |
| 504 | $this->where($where); |
| 505 | |
| 506 | // Return the result |
| 507 | return $this->get($table, $limit, $offset); |
| 508 | } |
| 509 | |
| 510 | /** |
| 511 | * Retrieve the number of rows in the selected table |
| 512 | */ |
| 513 | public function countAll(string $table): int |
| 514 | { |
| 515 | $sql = 'SELECT * FROM '.$this->driver->quoteTable($table); |
| 516 | $res = $this->driver->query($sql); |
| 517 | return (int) (is_countable($res->fetchAll()) ? count($res->fetchAll()) : 0); |
| 518 | } |
| 519 | |
| 520 | /** |
| 521 | * Retrieve the number of results for the generated query - used |
| 522 | * in place of the get() method |
| 523 | */ |
| 524 | public function countAllResults(string $table='', bool $reset = TRUE): int |
| 525 | { |
| 526 | // Set the table |
| 527 | if ( ! empty($table)) |
| 528 | { |
| 529 | $this->from($table); |
| 530 | } |
| 531 | |
| 532 | $result = $this->_run(QueryType::SELECT, $table, NULL, NULL, $reset); |
| 533 | $rows = $result->fetchAll(); |
| 534 | |
| 535 | return (int) count($rows); |
| 536 | } |
| 537 | |
| 538 | /** |
| 539 | * Creates an insert clause, and executes it |
| 540 | */ |
| 541 | public function insert(string $table, mixed $data=[]): PDOStatement |
| 542 | { |
| 543 | if ( ! empty($data)) |
| 544 | { |
| 545 | $this->set($data); |
| 546 | } |
| 547 | |
| 548 | return $this->_run(QueryType::INSERT, $table); |
| 549 | } |
| 550 | |
| 551 | /** |
| 552 | * Creates and executes a batch insertion query |
| 553 | */ |
| 554 | public function insertBatch(string $table, $data=[]): ?PDOStatement |
| 555 | { |
| 556 | // Get the generated values and sql string |
| 557 | [$sql, $data] = $this->driver->insertBatch($table, $data); |
| 558 | |
| 559 | return $sql !== NULL |
| 560 | ? $this->_run(QueryType::INSERT_BATCH, $table, $sql, $data) |
| 561 | : NULL; |
| 562 | } |
| 563 | |
| 564 | /** |
| 565 | * Creates an update clause, and executes it |
| 566 | */ |
| 567 | public function update(string $table, mixed $data=[]): PDOStatement |
| 568 | { |
| 569 | if ( ! empty($data)) |
| 570 | { |
| 571 | $this->set($data); |
| 572 | } |
| 573 | |
| 574 | return $this->_run(QueryType::UPDATE, $table); |
| 575 | } |
| 576 | |
| 577 | /** |
| 578 | * Creates a batch update, and executes it. |
| 579 | * Returns the number of affected rows |
| 580 | */ |
| 581 | public function updateBatch(string $table, array $data, string $where): ?int |
| 582 | { |
| 583 | if (empty($table) || empty($data) || empty($where)) |
| 584 | { |
| 585 | return NULL; |
| 586 | } |
| 587 | |
| 588 | // Get the generated values and sql string |
| 589 | [$sql, $data, $affectedRows] = $this->driver->updateBatch($table, $data, $where); |
| 590 | |
| 591 | $this->_run(QueryType::UPDATE_BATCH, $table, $sql, $data); |
| 592 | return $affectedRows; |
| 593 | } |
| 594 | |
| 595 | /** |
| 596 | * Deletes data from a table |
| 597 | */ |
| 598 | public function delete(string $table, mixed $where=''): PDOStatement |
| 599 | { |
| 600 | // Set the where clause |
| 601 | if ( ! empty($where)) |
| 602 | { |
| 603 | $this->where($where); |
| 604 | } |
| 605 | |
| 606 | return $this->_run(QueryType::DELETE, $table); |
| 607 | } |
| 608 | |
| 609 | // -------------------------------------------------------------------------- |
| 610 | // ! SQL Returning Methods |
| 611 | // -------------------------------------------------------------------------- |
| 612 | /** |
| 613 | * Returns the generated 'select' sql query |
| 614 | */ |
| 615 | public function getCompiledSelect(string $table='', bool $reset=TRUE): string |
| 616 | { |
| 617 | // Set the table |
| 618 | if ( ! empty($table)) |
| 619 | { |
| 620 | $this->from($table); |
| 621 | } |
| 622 | |
| 623 | return $this->_getCompile(QueryType::SELECT, $table, $reset); |
| 624 | } |
| 625 | |
| 626 | /** |
| 627 | * Returns the generated 'insert' sql query |
| 628 | */ |
| 629 | public function getCompiledInsert(string $table, bool $reset=TRUE): string |
| 630 | { |
| 631 | return $this->_getCompile(QueryType::INSERT, $table, $reset); |
| 632 | } |
| 633 | |
| 634 | /** |
| 635 | * Returns the generated 'update' sql query |
| 636 | */ |
| 637 | public function getCompiledUpdate(string $table='', bool $reset=TRUE): string |
| 638 | { |
| 639 | return $this->_getCompile(QueryType::UPDATE, $table, $reset); |
| 640 | } |
| 641 | |
| 642 | /** |
| 643 | * Returns the generated 'delete' sql query |
| 644 | */ |
| 645 | public function getCompiledDelete(string $table='', bool $reset=TRUE): string |
| 646 | { |
| 647 | return $this->_getCompile(QueryType::DELETE, $table, $reset); |
| 648 | } |
| 649 | } |