File helpers\db_helper.mysqli.php | PHPLucidFrame 3.5 API

1:	<?php
2:	/**
3:	* This file is part of the PHPLucidFrame library.
4:	* Core utility for the database layer. Basic functioning of the database system.
5:	*
6:	* @package PHPLucidFrame\Core
7:	* @since PHPLucidFrame v 1.0.0
8:	* @copyright Copyright (c), PHPLucidFrame.
9:	* @link http://phplucidframe.com
10:	* @license http://www.opensource.org/licenses/mit-license.php MIT License
11:	*
12:	* This source file is subject to the MIT license that is bundled
13:	* with this source code in the file LICENSE
14:	*/
15:
16:	use LucidFrame\Core\Database;
17:	use LucidFrame\Core\QueryBuilder;
18:
19:	/**
20:	* @internal
21:	* @ignore
22:	*
23:	* Return the current database namespace
24:	* if $namespace is not provided, $lc_defaultDbSource will be returned
25:	* if $lc_defaultDbSource is empty, `default` will be returned
26:	*
27:	* @param string $namespace The given namespace
28:	* @return string The database namespace
29:	*/
30:	function db_namespace($namespace = null)
31:	{
32:	return _app('db')->getNamespace($namespace);
33:	}
34:
35:	/**
36:	* @internal
37:	* @ignore
38:	*
39:	* Return the database configuration of the given namespace
40:	* @param string $namespace Namespace of the configuration to read from
41:	* @return array The array of database configuration
42:	*/
43:	function db_config($namespace = null)
44:	{
45:	return _app('db')->getConfig($namespace);
46:	}
47:
48:	/**
49:	* Return the database engine of the given namespace
50:	* @param string $namespace Namespace of the configuration to read from
51:	* @return string Database engine name
52:	*/
53:	function db_engine($namespace = null)
54:	{
55:	return _app('db')->getDriver($namespace);
56:	}
57:
58:	/**
59:	* Return the database host name of the given namespace
60:	* @param string $namespace Namespace of the configuration to read from
61:	* @return string Database host name
62:	*/
63:	function db_host($namespace = null)
64:	{
65:	return _app('db')->getHost($namespace);
66:	}
67:
68:	/**
69:	* Return the database name of the given namespace
70:	* @param string $namespace Namespace of the configuration to read from
71:	* @return string Database name
72:	*/
73:	function db_name($namespace = null)
74:	{
75:	return _app('db')->getName($namespace);
76:	}
77:
78:	/**
79:	* Return the database username of the given namespace
80:	* @param string $namespace Namespace of the configuration to read from
81:	* @return string Database username
82:	*/
83:	function db_user($namespace = null)
84:	{
85:	return _app('db')->getUser($namespace);
86:	}
87:
88:	/**
89:	* Return the database table prefix of the given namespace
90:	* @param string $namespace Namespace of the configuration to read from
91:	* @return string The table prefix
92:	*/
93:	function db_prefix($namespace = null)
94:	{
95:	return _app('db')->getPrefix($namespace);
96:	}
97:
98:	/**
99:	* Return the database collation of the given namespace
100:	* @param string $namespace Namespace of the configuration to read from
101:	* @return string Database collation
102:	*/
103:	function db_collation($namespace = null)
104:	{
105:	return _app('db')->getCollation($namespace);
106:	}
107:
108:	/**
109:	* @internal
110:	* @ignore
111:	* Check and get the database configuration settings
112:	* @param string $namespace Namespace of the configuration to read from
113:	* @return array
114:	*/
115:	function db_prerequisite($namespace = null)
116:	{
117:	$db = _app('db');
118:	$namespace = $db->getNamespace($namespace);
119:
120:	if ($db->getHost($namespace) && $db->getUser($namespace) && $db->getName($namespace)) {
121:	return $db->getConfig($namespace);
122:	} else {
123:	_header(400);
124:	throw new \InvalidArgumentException('Required to configure <code class="inline">db</code> in <code class="inline">/inc/parameters/'._cfg('env').'.php</code>.');
125:	}
126:	}
127:
128:	/**
129:	* Switch to the given database from the currently active database
130:	* @param string $namespace Namespace of the configuration to read from
131:	* @return void
132:	*/
133:	function db_switch($namespace = null)
134:	{
135:	_app('db', new Database($namespace));
136:	}
137:
138:	/**
139:	* Closes a previously opened database connection
140:	* @return void
141:	*/
142:	function db_close()
143:	{
144:	_app('db')->close();
145:	}
146:
147:	/**
148:	* Make the generated query returned from the query executing functions
149:	* such as db_query, db_update, db_delete, etc. without executing the query
150:	* especially for debugging and testing. Call `db_prq(true)` before and `db_prq(false)` after.
151:	* `db_queryStr()` is same purpose but after executing the query.
152:	*
153:	* @param bool $enable Enable to return the query built; defaults to `true`.
154:	*/
155:	function db_prq($enable = true)
156:	{
157:	_g('db_printQuery', $enable);
158:	}
159:
160:	/**
161:	* Perform a query on the database
162:	*
163:	* @param string $sql SQL query string
164:	* @param array $args Array of placeholders and their values
165:	* array(
166:	* ':placeholder1' => $value1,
167:	* ':placeholder2' => $value2
168:	* )
169:	* The prefix colon ":" for placeholder is optional
170:	*
171:	* @return mixed PDOStatement\|boolean\|string Returns PDOStatement on success or FALSE on failure
172:	*/
173:	function db_query($sql, $args = array())
174:	{
175:	return _app('db')->query($sql, $args);
176:	}
177:
178:	/**
179:	* Get the last executed SQL string or one of the executed SQL strings by providing the index
180:	*
181:	* @param int The index number of the query returned; if not given, the last query is returned
182:	* @return string Return the built and executed SQL string
183:	*/
184:	function db_queryStr()
185:	{
186:	return _app('db')->getQueryStr();
187:	}
188:
189:	/**
190:	* Returns a string description of the last error
191:	* @return string
192:	*/
193:	function db_error()
194:	{
195:	return _app('db')->getError();
196:	}
197:
198:	/**
199:	* Returns the error code for the most recent MySQLi function call
200:	* @return int
201:	*/
202:	function db_errorNo()
203:	{
204:	return _app('db')->getErrorCode();
205:	}
206:
207:	/**
208:	* Gets the number of rows in a result
209:	* @param PDOStatement $result
210:	* @return int Returns the number of rows in the result set.
211:	*/
212:	function db_numRows($result)
213:	{
214:	return _app('db')->getNumRows($result);
215:	}
216:
217:	/**
218:	* Fetch a result row as an associative, a numeric array, or both
219:	* @param PDOStatement $result
220:	* @return array An array that corresponds to the fetched row or
221:	* NULL if there are no more rows for the result set represented by the result parameter.
222:	*/
223:	function db_fetchArray($result)
224:	{
225:	return _app('db')->fetchArray($result);
226:	}
227:
228:	/**
229:	* Fetch a result row as an associative array
230:	* @param PDOStatement $result
231:	* @return array An associative array that corresponds to the fetched row or NULL if there are no more rows.
232:	*/
233:	function db_fetchAssoc($result)
234:	{
235:	return _app('db')->fetchAssoc($result);
236:	}
237:
238:	/**
239:	* Returns the current row of a result set as an object
240:	* @param PDOStatement $result
241:	* @return object An object that corresponds to the fetched row or NULL if there are no more rows in resultset.
242:	*/
243:	function db_fetchObject($result)
244:	{
245:	return _app('db')->fetchObject($result);
246:	}
247:
248:	/**
249:	* Returns the auto generated id used in the last query
250:	* @return int The value of the `AUTO_INCREMENT` field that was updated by the previous query;
251:	* `0` if there was no previous query on the connection or if the query did not update an `AUTO_INCREMENT` value.
252:	*/
253:	function db_insertId()
254:	{
255:	return _app('db')->getInsertId();
256:	}
257:
258:	/**
259:	* Returns the generated slug used in the last query
260:	* @return string The last inserted slug
261:	*/
262:	function db_insertSlug()
263:	{
264:	return session_get('lastInsertSlug');
265:	}
266:
267:	/**
268:	* Initialize a query builder to perform a SELECT query on the database
269:	*
270:	* @param string $table The table name
271:	* @param string $alias The optional table alias
272:	*
273:	* @return object QueryBuilder
274:	*/
275:	function db_select($table, $alias = null)
276:	{
277:	return new QueryBuilder($table, $alias);
278:	}
279:
280:	/**
281:	* Perform a count query on the database and return the count
282:	*
283:	* @param string $arg1 The SQL query string or table name
284:	* @param string\|array $arg2 The field name to count on
285:	* or the array of placeholders and their values if the first argument is SQL
286:	*
287:	* array(
288:	* ':placeholder1' => $value1,
289:	* ':placeholder2' => $value2
290:	* )
291:	*
292:	* @param string\|null $arg3 The field alias if the first argument is table name
293:	* or the second argument is field name
294:	*
295:	* @return int\|QueryBuilder The result count or QueryBuilder
296:	*/
297:	function db_count($arg1, $arg2 = null, $arg3 = null)
298:	{
299:	return _app('db')->getCount($arg1, $arg2, $arg3);
300:	}
301:
302:	/**
303:	* Initialize a query builder to perform a MAX query on the database
304:	*
305:	* @param string $table The table name
306:	* @param string $field The field name to find max
307:	* @param string $alias The optional field alias; defaults to "max"
308:	*
309:	* @return object QueryBuilder
310:	*/
311:	function db_max($table, $field, $alias = null)
312:	{
313:	$qb = new QueryBuilder($table);
314:
315:	return $qb->max($field, $alias ? $alias : 'max');
316:	}
317:
318:	/**
319:	* Initialize a query builder to perform a MIN query on the database
320:	*
321:	* @param string $table The table name
322:	* @param string $field The field name to find min
323:	* @param string $alias The optional field alias; defaults to "min"
324:	*
325:	* @return object QueryBuilder
326:	*/
327:	function db_min($table, $field, $alias = null)
328:	{
329:	$qb = new QueryBuilder($table);
330:
331:	return $qb->min($field, $alias ? $alias : 'min');
332:	}
333:
334:	/**
335:	* Initialize a query builder to perform a SUM query on the database
336:	*
337:	* @param string $table The table name
338:	* @param string $field The field name to find sum
339:	* @param string $alias The optional field alias; defaults to "sum"
340:	*
341:	* @return object QueryBuilder
342:	*/
343:	function db_sum($table, $field, $alias = null)
344:	{
345:	$qb = new QueryBuilder($table);
346:
347:	return $qb->sum($field, $alias ? $alias : 'sum');
348:	}
349:
350:	/**
351:	* Initialize a query builder to perform an AVG query on the database
352:	*
353:	* @param string $table The table name
354:	* @param string $field The field name to find average
355:	* @param string $alias The optional field alias; defaults to "avg"
356:	*
357:	* @return object QueryBuilder
358:	*/
359:	function db_avg($table, $field, $alias = null)
360:	{
361:	$qb = new QueryBuilder($table);
362:
363:	return $qb->avg($field, $alias ? $alias : 'avg');
364:	}
365:
366:	/**
367:	* Perform a query on the database and return the first field value only.
368:	*
369:	* It adds the `LIMIT 1` clause if the query has no record limit
370:	* This will be useful for `COUNT()`, `MAX()`, `MIN()` queries
371:	*
372:	* @param string $sql The SQL query string
373:	* @param array $args The array of placeholders and their values
374:	*
375:	* array(
376:	* ':placeholder1' => $value1,
377:	* ':placeholder2' => $value2
378:	* )
379:	*
380:	* @return mixed The value of the first field
381:	*/
382:	function db_fetch($sql, $args = array())
383:	{
384:	return _app('db')->fetchColumn($sql, $args);
385:	}
386:
387:	/**
388:	* Perform a query on the database and return the first result row as object
389:	*
390:	* It adds the `LIMIT 1` clause if the query has no record limit
391:	* This is useful for one-row fetching. No need explicit `db_query()` call as this invokes it internally.
392:	*
393:	* @param string $sql The SQL query string
394:	* @param array $args The array of placeholders and their values
395:	*
396:	* array(
397:	* ':placeholder1' => $value1,
398:	* ':placeholder2' => $value2
399:	* )
400:	*
401:	* @return object The result object
402:	*/
403:	function db_fetchResult($sql, $args = array())
404:	{
405:	return _app('db')->fetchResult($sql, $args);
406:	}
407:
408:	/**
409:	* Perform a query on the database and return the array of all results
410:	*
411:	* @param string $sql The SQL query string
412:	* @param array $args The array of placeholders and their values
413:	* @param int $resultType The optional constant indicating what type of array should be produced.
414:	* The possible values for this parameter are the constants
415:	* LC_FETCH_OBJECT, LC_FETCH_ASSOC, or LC_FETCH_ARRAY.
416:	* Default to LC_FETCH_OBJECT.
417:	*
418:	* @return array\|boolean The result array of objects or associated arrays or index arrays.
419:	* If the result not found, return false.
420:	*/
421:	function db_extract($sql, $args = array(), $resultType = LC_FETCH_OBJECT)
422:	{
423:	return _app('db')->fetchAll($sql, $args, $resultType);
424:	}
425:
426:	/**
427:	* Get the full table name with prefix
428:	* @param string $table The table name with or without prefix
429:	* @return string The table name with prefix
430:	*/
431:	function db_table($table)
432:	{
433:	return _app('db')->getTable($table);
434:	}
435:
436:	/**
437:	* Check the table has slug field
438:	*
439:	* @param string $table The table name without prefix
440:	* @param boolean $useSlug True to include the slug field or False to not exclude it
441:	* @return boolean true or false
442:	*/
443:	function db_tableHasSlug($table, $useSlug = true)
444:	{
445:	return _app('db')->hasSlug($table, $useSlug);
446:	}
447:
448:	/**
449:	* Check the table has timestamp fields
450:	*
451:	* @param string $table The table name without prefix
452:	* @return boolean true or false
453:	*/
454:	function db_tableHasTimestamps($table)
455:	{
456:	return _app('db')->hasTimestamps($table);
457:	}
458:
459:	/**
460:	* Handy db insert/update operation
461:	* @param string $table The table name without prefix
462:	* @param array $data The array of data field names and values
463:	*
464:	* array(
465:	* 'fieldNameToSlug' => $valueToSlug,
466:	* 'fieldName1' => $fieldValue1,
467:	* 'fieldName2' => $fieldValue2
468:	* )
469:	*
470:	* @param int $id The value for ID field to update
471:	* @param boolean $useSlug TRUE to include the slug field or FALSE to not exclude it
472:	* The fourth argument can be provided here if you want to omit this.
473:	* @param array $condition The condition for the UPDATE query. If you provide this,
474:	* the first field of `$data` will not be built for condition
475:	*
476:	* ### Example
477:	*
478:	* array(
479:	* 'fieldName1' => $value1,
480:	* 'fieldName2' => $value2
481:	* )
482:	*
483:	* @return bool\|int\|mixed For insert, returns inserted id on success or FALSE on failure;
484:	* For update, returns updated id on success or FALSE on failure
485:	*/
486:	function db_save($table, $data = array(), $id = 0, $useSlug = true, array $condition = array())
487:	{
488:	if ($id) {
489:	$data = array_merge(array('id' => $id), $data);
490:
491:	if (db_update($table, $data, $useSlug, $condition)) {
492:	return $id;
493:	}
494:
495:	return false;
496:	} else {
497:	return db_insert($table, $data, $useSlug);
498:	}
499:	}
500:
501:	if (!function_exists('db_insert')) {
502:	/**
503:	* Handy MYSQL insert operation
504:	*
505:	* @param string $table The table name without prefix
506:	* @param array $data The array of data field names and values
507:	*
508:	* array(
509:	* 'fieldNameToSlug' => $valueToSlug,
510:	* 'fieldName1' => $fieldValue1,
511:	* 'fieldName2' => $fieldValue2
512:	* )
513:	*
514:	* @param boolean $useSlug True to include the slug field or False to not exclude it
515:	* @return mixed Returns inserted id on success or FALSE on failure
516:	*/
517:	function db_insert($table, $data = array(), $useSlug = true)
518:	{
519:	QueryBuilder::clearBindValues();
520:
521:	if (count($data) == 0) {
522:	return false;
523:	}
524:
525:	$db = _app('db');
526:
527:	$table = db_table($table);
528:
529:	# Invoke the hook db_insert_[table_name] if any
530:	$hook = 'db_insert_' . strtolower($table);
531:	if (function_exists($hook)) {
532:	return call_user_func_array($hook, array($table, $data, $useSlug));
533:	}
534:
535:	# if slug is already provided in the data array, use it
536:	if (array_key_exists('slug', $data)) {
537:	$slug = _slug($data['slug']);
538:	$data['slug'] = $slug;
539:	session_set('lastInsertSlug', $slug);
540:	$useSlug = false;
541:	}
542:
543:	$dsm = $db->schemaManager;
544:	if (is_object($dsm) && $dsm->isLoaded()) {
545:	foreach ($data as $field => $value) {
546:	$fieldType = $db->schemaManager->getFieldType($table, $field);
547:	if (is_array($value) && $fieldType == 'array') {
548:	$data[$field] = serialize($value);
549:	} elseif (is_array($value) && $fieldType == 'json') {
550:	$jsonValue = json_encode($value);
551:	$data[$field] = $jsonValue ? $jsonValue : null;
552:	} elseif ($fieldType == 'boolean') {
553:	$data[$field] = $value ? 1 : 0;
554:	}
555:	}
556:	}
557:
558:	$fields = array_keys($data);
559:	$dataValues = array_values($data);
560:
561:	if (db_tableHasSlug($table, $useSlug)) {
562:	$fields[] = 'slug';
563:	}
564:
565:	if (db_tableHasTimestamps($table)) {
566:	if (!array_key_exists('created', $data)) {
567:	$fields[] = 'created';
568:	}
569:	if (!array_key_exists('updated', $data)) {
570:	$fields[] = 'updated';
571:	}
572:	}
573:
574:	$fields = array_unique($fields);
575:
576:	$sqlFields = implode(', ', $fields);
577:	$placeHolders = implode(', ', array_fill(0, count($fields), '?'));
578:	$values = array();
579:	$i = 0;
580:
581:	# escape the data
582:	foreach ($dataValues as $val) {
583:	if ($i == 0 && $useSlug) {
584:	$slug = $val;
585:	}
586:
587:	$values[] = is_null($val) ? null : $val;
588:
589:	$i++;
590:	}
591:
592:	if (db_tableHasSlug($table, $useSlug)) {
593:	$slug = _slug($slug, $table);
594:	session_set('lastInsertSlug', $slug);
595:	$values[] = $slug;
596:	}
597:
598:	if (db_tableHasTimestamps($table)) {
599:	if (!array_key_exists('created', $data)) {
600:	$values[] = date('Y-m-d H:i:s');
601:	}
602:	if (!array_key_exists('updated', $data)) {
603:	$values[] = date('Y-m-d H:i:s');
604:	}
605:	}
606:
607:	$sql = sprintf('INSERT INTO %s (%s) VALUES (%s)', QueryBuilder::quote($table), $sqlFields, $placeHolders);
608:
609:	return db_query($sql, $values) ? db_insertId() : false;
610:	}
611:	}
612:
613:	if (!function_exists('db_update')) {
614:	/**
615:	* Handy MYSQL update operation
616:	*
617:	* @param string $table The table name without prefix
618:	* @param array $data The array of data field names and values
619:	* The first field/value pair will be used as condition when you do not provide the fourth argument
620:	*
621:	* array(
622:	* 'condition_field' => $conditionFieldValue,
623:	* 'field_name_to_slug' => $valueToSlug,
624:	* 'field_name_1' => $value1,
625:	* 'field_name_2' => $value2
626:	* )
627:	*
628:	* @param boolean $useSlug TRUE to include the slug field or FALSE to not exclude it
629:	* The fourth argument can be provided here if you want to omit this.
630:	* @param array $condition The condition for the UPDATE query. If you provide this,
631:	* the first field of `$data` will not be built for condition but used to be updated
632:	*
633:	* ### Example
634:	*
635:	* array(
636:	* 'field_name_1' => $value1,
637:	* 'field_name_2 >=' => $value2,
638:	* 'field_name_3' => NULL
639:	* )
640:	*
641:	* array of OR condition syntax,
642:	*
643:	* array(
644:	* '$or' => array(
645:	* 'field_name_1' => $value1,
646:	* 'field_name_2 >=' => $value2,
647:	* 'field_name_3' => NULL
648:	* )
649:	* )
650:	*
651:	* @return boolean Returns TRUE on success or FALSE on failure
652:	*/
653:	function db_update($table, $data = array(), $useSlug = true, array $condition = array())
654:	{
655:	QueryBuilder::clearBindValues();
656:
657:	if (count($data) == 0) {
658:	return false;
659:	}
660:
661:	$db = _app('db');
662:
663:	if (func_num_args() === 3 && (gettype($useSlug) === 'string' \|\| is_array($useSlug))) {
664:	$condition = $useSlug;
665:	$useSlug = true;
666:	}
667:
668:	$table = db_table($table);
669:
670:	# Invoke the hook db_update_[table_name] if any
671:	$hook = 'db_update_' . strtolower($table);
672:	if (function_exists($hook)) {
673:	return call_user_func_array($hook, array($table, $data, $useSlug, $condition));
674:	}
675:
676:	# if slug is already provided in the data array, use it
677:	if (array_key_exists('slug', $data)) {
678:	$slug = _slug($data['slug']);
679:	$data['slug'] = $slug;
680:	session_set('lastInsertSlug', $slug);
681:	$useSlug = false;
682:	}
683:
684:	$fields = array();
685:	$slug = '';
686:	$cond = '';
687:	$i = 0;
688:	$slugIndex = 1;
689:
690:	if ($condition) {
691:	$slugIndex = 0;
692:	}
693:
694:	$dsm = $db->schemaManager;
695:	foreach ($data as $field => $value) {
696:	if ($i === 0 && !$condition) {
697:	# $data[0] is for PK condition, but only if $condition is not provided
698:	$cond = array($field => $value); # for PK condition
699:	$i++;
700:	continue;
701:	}
702:
703:	if (is_object($dsm) && $dsm->isLoaded()) {
704:	$fieldType = $dsm->getFieldType($table, $field);
705:	if (is_array($value) && $fieldType == 'array') {
706:	$value = serialize($value);
707:	} elseif (is_array($value) && $fieldType == 'json') {
708:	$jsonValue = json_encode($value);
709:	$value = $jsonValue ? $jsonValue : null;
710:	} elseif ($fieldType == 'boolean') {
711:	$value = $value ? 1 : 0;
712:	}
713:	}
714:
715:	$fields[$field] = is_null($value) ? null : $value;
716:
717:	if ($i === $slugIndex && $useSlug === true) {
718:	# $data[1] is slug
719:	$slug = $value;
720:	}
721:
722:	$i++;
723:	}
724:
725:	# must have condition
726:	# this prevents unexpected update happened to all records
727:	if ($cond \|\| $condition) {
728:	$clause = '';
729:	$notCond = array();
730:	$values = array();
731:
732:	if ($cond && is_array($cond) && count($cond)) {
733:	QueryBuilder::clearBindValues();
734:	list($clause, $values) = db_condition($cond);
735:	$notCond = array(
736:	'$not' => $cond
737:	);
738:	} elseif ($condition && is_array($condition) && count($condition)) {
739:	QueryBuilder::clearBindValues();
740:	list($clause, $values) = db_condition($condition);
741:	$notCond = array(
742:	'$not' => $condition
743:	);
744:	}
745:
746:	if (empty($clause)) {
747:	return false;
748:	}
749:
750:	if (db_tableHasSlug($table, $useSlug)) {
751:	$slug = _slug($slug, $table, $notCond);
752:	session_set('lastInsertSlug', $slug);
753:	$fields['slug'] = $slug;
754:	}
755:
756:	if (db_tableHasTimestamps($table)) {
757:	$fields['updated'] = date('Y-m-d H:i:s');
758:	}
759:
760:	$sql = 'UPDATE ' . QueryBuilder::quote($table) . ' SET ';
761:	foreach ($fields as $key => $value) {
762:	$placeholder = ':upd_' . $key;
763:	$sql .= sprintf('`%s` = %s, ', $key, $placeholder);
764:	$values[$placeholder] = $value;
765:	}
766:	$sql = rtrim($sql, ', ');
767:	$sql .= ' WHERE ' . $clause;
768:
769:	return db_query($sql, $values) ? true : false;
770:	}
771:
772:	return false;
773:	}
774:	}
775:
776:	if (!function_exists('db_delete')) {
777:	/**
778:	* Handy MYSQL delete operation for single record.
779:	* It checks FK delete RESTRICT constraint, then SET deleted if it cannot be deleted
780:	*
781:	* @param string $table Table name without prefix
782:	* @param array $condition The array of condition for delete - field names and values, for example
783:	*
784:	* array of AND condition syntax,
785:	*
786:	* array(
787:	* 'field_name_1' => $value1,
788:	* 'field_name_2 >=' => $value2,
789:	* 'field_name_3' => NULL
790:	* )
791:	*
792:	* array of OR condition syntax,
793:	*
794:	* array(
795:	* '$or' => array(
796:	* 'field_name_1' => $value1,
797:	* 'field_name_2 >=' => $value2,
798:	* 'field_name_3' => NULL
799:	* )
800:	* )
801:	*
802:	* @param boolean $softDelete Soft delete or not
803:	* @return boolean Returns TRUE on success or FALSE on failure
804:	*/
805:	function db_delete($table, array $condition = array(), $softDelete = false)
806:	{
807:	QueryBuilder::clearBindValues();
808:
809:	$table = db_table($table);
810:
811:	# Invoke the hook db_delete_[table_name] if any
812:	$hook = 'db_delete_' . strtolower($table);
813:	if (function_exists($hook)) {
814:	return call_user_func_array($hook, array($table, $condition));
815:	}
816:
817:	$values = array();
818:
819:	if (is_array($condition)) {
820:	list($condition, $values) = db_condition($condition);
821:	}
822:
823:	if ($condition) {
824:	$condition = ' WHERE '.$condition;
825:	}
826:
827:	if ($softDelete) {
828:	$sql = 'UPDATE '. QueryBuilder::quote($table) . '
829:	SET `deleted` = :deleted ' . $condition . '
830:	LIMIT 1';
831:	$values[':deleted'] = date('Y-m-d H:i:s');
832:	if (_g('db_printQuery')) {
833:	return $sql;
834:	}
835:
836:	return db_query($sql, $values) ? true : false;
837:	}
838:
839:	$sql = 'DELETE FROM ' . QueryBuilder::quote($table) . $condition . ' LIMIT 1';
840:	if (_g('db_printQuery')) {
841:	return $sql;
842:	}
843:
844:	ob_start(); # to capture error return
845:	db_query($sql, $values);
846:	$return = ob_get_clean();
847:	if ($return) {
848:	# If there is FK delete RESTRICT constraint, make soft delete
849:	if (db_errorNo() == 1451) {
850:	if (db_tableHasTimestamps($table)) {
851:	$sql = 'UPDATE '. QueryBuilder::quote($table) . '
852:	SET `deleted` = :deleted ' . $condition . '
853:	LIMIT 1';
854:	$values[':deleted'] = date('Y-m-d H:i:s');
855:
856:	return db_query($sql, $values);
857:	}
858:
859:	return false;
860:	} else {
861:	echo $return;
862:
863:	return false;
864:	}
865:	}
866:
867:	return db_errorNo() == 0;
868:	}
869:	}
870:
871:	if (!function_exists('db_delete_multi')) {
872:	/**
873:	* Handy MYSQL delete operation for multiple records
874:	*
875:	* @param string $table Table name without prefix
876:	* @param array $condition The array of condition for delete - field names and values, for example
877:	*
878:	* array of AND condition syntax,
879:	*
880:	* array(
881:	* 'field_name_1' => $value1,
882:	* 'field_name_2 >=' => $value2,
883:	* 'field_name_3' => NULL
884:	* )
885:	*
886:	* array of OR condition syntax,
887:	*
888:	* array(
889:	* '$or' => array(
890:	* 'field_name_1' => $value1,
891:	* 'field_name_2 >=' => $value2,
892:	* 'field_name_3' => NULL
893:	* )
894:	* )
895:	*
896:	* @param boolean $softDelete Soft delete or not
897:	* @return boolean Returns TRUE on success or FALSE on failure
898:	*/
899:	function db_delete_multi($table, $condition = null, $softDelete = false)
900:	{
901:	QueryBuilder::clearBindValues();
902:
903:	$table = db_table($table);
904:
905:	# Invoke the hook db_delete_[table_name] if any
906:	$hook = 'db_delete_multi_' . strtolower($table);
907:	if (function_exists($hook)) {
908:	return call_user_func_array($hook, array($table, $condition));
909:	}
910:
911:	$values = array();
912:	if (is_array($condition)) {
913:	list($condition, $values) = db_condition($condition);
914:	}
915:
916:	if ($condition) {
917:	$condition = ' WHERE '. $condition;
918:	}
919:
920:	if ($softDelete) {
921:	$sql = 'UPDATE '. QueryBuilder::quote($table) . '
922:	SET `deleted` = :deleted ' . $condition;
923:	$values[':deleted'] = date('Y-m-d H:i:s');
924:	if (_g('db_printQuery')) {
925:	return $sql;
926:	}
927:
928:	return db_query($sql, $values);
929:	}
930:
931:	$sql = 'DELETE FROM ' . QueryBuilder::quote($table) . $condition;
932:	if (_g('db_printQuery')) {
933:	return $sql;
934:	}
935:
936:	ob_start(); # to capture error return
937:	db_query($sql, $values);
938:	$return = ob_get_clean();
939:
940:	if ($return && db_errorNo() > 0) {
941:	# if there is any error
942:	return false;
943:	}
944:
945:	return db_errorNo() == 0;
946:	}
947:	}
948:
949:	if (!function_exists('db_truncate')) {
950:	/**
951:	* Truncate the table
952:	* @param string $table Table name without prefix
953:	*/
954:	function db_truncate($table)
955:	{
956:	$table = db_table($table);
957:
958:	db_query('TRUNCATE ' . QueryBuilder::quote($table));
959:	}
960:	}
961:
962:	/**
963:	* Set foreign key check
964:	* @param int $flag 0 or 1
965:	*/
966:	function db_setForeignKeyCheck($flag)
967:	{
968:	db_query('SET FOREIGN_KEY_CHECKS =' . $flag);
969:	}
970:
971:	/**
972:	* Enable foreign key check
973:	*/
974:	function db_enableForeignKeyCheck()
975:	{
976:	db_setForeignKeyCheck(1);
977:	}
978:
979:	/**
980:	* Disable foreign key check
981:	*/
982:	function db_disableForeignKeyCheck()
983:	{
984:	db_setForeignKeyCheck(0);
985:	}
986:
987:	/**
988:	* @internal
989:	* @ignore
990:	*
991:	* Build the SQL WHERE clause from the various condition arrays
992:	*
993:	* @param array $cond The condition array, for example
994:	*
995:	* array(
996:	* 'field_name_1' => $value1,
997:	* 'field_name_2 >=' => $value2,
998:	* 'field_name_3 => NULL
999:	* )
1000:	*
1001:	* @param string $type The condition type "AND" or "OR"; Default is "AND"
1002:	*
1003:	* @return array The built condition WHERE AND/OR
1004:	* [0] string The built condition WHERE AND/OR clause
1005:	* [1] array The values to bind in the condition
1006:	*/
1007:	function db_condition($cond = array(), $type = 'AND')
1008:	{
1009:	return QueryBuilder::buildCondition($cond, $type);
1010:	}
1011:
1012:	/**
1013:	* Build the SQL WHERE clause AND condition from array of conditions
1014:	*
1015:	* @param array $condition The condition array, for example
1016:	*
1017:	* array(
1018:	* 'field_name_1' => $value1,
1019:	* 'field_name_2 >=' => $value2,
1020:	* 'field_name_3 => NULL
1021:	* )
1022:	*
1023:	* ### Operators allowed in condition array
1024:	* >, >=, <, <=, !=, between, nbetween, like, like%%, like%~, like~%, nlike, nlike%%, nlike%~, nlike~%
1025:	*
1026:	* @return array The built condition WHERE AND
1027:	* [0] string The built condition WHERE AND clause
1028:	* [1] array The values to bind in the condition
1029:	*/
1030:	function db_and($condition = array())
1031:	{
1032:	return db_condition($condition, 'AND');
1033:	}
1034:
1035:	/**
1036:	* Build the SQL WHERE clause OR condition from array of conditions
1037:	*
1038:	* @param array $condition The condition array, for example
1039:	*
1040:	* array(
1041:	* 'field_name_1' => $value1,
1042:	* 'field_name_2 >=' => $value2,
1043:	* 'field_name_3 => NULL
1044:	* )
1045:	*
1046:	* ### Operators allowed in condition array
1047:	* >, >=, <, <=, !=, between, nbetween, like, like%%, like%~, like~%, nlike, nlike%%, nlike%~, nlike~%
1048:	*
1049:	* @return array The built condition WHERE OR
1050:	* [0] string The built condition WHERE OR clause
1051:	* [1] array The values to bind in the condition
1052:	*/
1053:	function db_or($condition = array())
1054:	{
1055:	return db_condition($condition, 'OR');
1056:	}
1057:
1058:	/**
1059:	* Start a new transaction
1060:	*/
1061:	function db_transaction()
1062:	{
1063:	db_query('SET AUTOCOMMIT=0');
1064:	db_query('START TRANSACTION');
1065:	}
1066:
1067:	/**
1068:	* Commit the current transaction, making its changes permanent.
1069:	*/
1070:	function db_commit()
1071:	{
1072:	db_query('COMMIT');
1073:	db_query('SET AUTOCOMMIT=1');
1074:	}
1075:
1076:	/**
1077:	* Roll back the current transaction, canceling its changes.
1078:	*/
1079:	function db_rollback()
1080:	{
1081:	db_query('ROLLBACK');
1082:	db_query('SET AUTOCOMMIT=1');
1083:	}
1084:
1085:	/**
1086:	* Get raw expression string
1087:	* @param string $exp Expression
1088:	* @param array $values The values to be replaced with specifier in $exp. See vsprintf.
1089:	* @return string
1090:	*/
1091:	function db_raw($exp, array $values = array())
1092:	{
1093:	return QueryBuilder::raw($exp, $values);
1094:	}
1095:
1096:	/**
1097:	* @internal
1098:	* @ignore
1099:	*
1100:	* Build the SQL expression like SUM, MAX, AVG, etc
1101:	*
1102:	* @param string $field The field name
1103:	* @param mixed $value The value for the field
1104:	* @param string $exp The SQL expression
1105:	* @return array The condition array, for example
1106:	*
1107:	* array(
1108:	* 'value' => $value,
1109:	* 'exp >=' => $exp,
1110:	* 'field => $field
1111:	* )
1112:	*
1113:	*/
1114:	function db_exp($field, $value, $exp = '')
1115:	{
1116:	return _app('db')->exp($field, $value, $exp);
1117:	}
1118:
1119:	/**
1120:	* Get a single entity result where the primary key matches the value passed in as the second parameter for the table name in the first parameter.
1121:	* @param string $table The table name to fetch data from
1122:	* @param int $id The value of the primary key to match
1123:	* @param array $fields The array of fields to select
1124:	* @return object\|null
1125:	*/
1126:	function db_find($table, $id, $fields = [])
1127:	{
1128:	$qb = db_select($table)->where()->condition('id', $id);
1129:
1130:	if (!empty($fields)) {
1131:	$qb->fields($table, $fields);
1132:	}
1133:
1134:	if (db_tableHasTimestamps($table)) {
1135:	$qb->condition('deleted', null);
1136:	}
1137:
1138:	$entity = $qb->getSingleResult();
1139:
1140:	if ($entity) {
1141:	$schema = _schema(_cfg('defaultDbSource'), true);
1142:	$entity = (array) $entity;
1143:	if ($schema && isset($schema[$table])) {
1144:	foreach ($entity as $field => $value) {
1145:	switch ($schema[$table][$field]['type']) {
1146:	case 'int':
1147:	case 'integer':
1148:	case 'smallint':
1149:	case 'mediumint':
1150:	case 'bigint':
1151:	$entity[$field] = is_numeric($value) ? (int) $value : $value;
1152:	break;
1153:
1154:	case 'boolean':
1155:	$entity[$field] = (bool) $value;
1156:	break;
1157:
1158:	case 'array':
1159:	$entity[$field] = $value ? unserialize($value) : array();
1160:	break;
1161:
1162:	case 'json':
1163:	$entity[$field] = $value ? json_decode($value, true) : array();
1164:	break;
1165:	}
1166:	}
1167:	}
1168:	$entity = (object) $entity;
1169:	}
1170:
1171:	return $entity ?: null;
1172:	}
1173:
1174:	/**
1175:	* Get a single entity result where the primary key matches the value passed in as the second parameter for the table name in the first parameter OR throws 404 if any result is not found.
1176:	*
1177:	* @param string $table The table name to fetch data from
1178:	* @param int $id The value of the primary key to match
1179:	* @param array $fields The array of fields to select
1180:	* @return object\|null
1181:	*/
1182:	function db_findOrFail($table, $id, $fields = [])
1183:	{
1184:	$result = db_find($table, $id, $fields);
1185:
1186:	if (!$result) {
1187:	_page404(_t('The entity %s not found.', $table), $table);
1188:	}
1189:
1190:	return $result;
1191:	}
1192:
1193:	/**
1194:	* Get array of data row objects with pagination result
1195:	* @param string $table The table name to fetch data from
1196:	* @param array $condition The condition array for query
1197:	*
1198:	* array(
1199:	* 'fieldName1' => $value1,
1200:	* 'fieldName2 >=' => $value2,
1201:	* 'fieldName3 => NULL
1202:	* )
1203:	*
1204:	* OR
1205:	*
1206:	* array(
1207:	* 'fieldName1' => $value1,
1208:	* 'fieldName2 >=' => $value2,
1209:	* 'fieldName3 => NULL,
1210:	* '$or' => array(
1211:	* 'fieldName4' => array(1, 2, 3)
1212:	* 'fieldName4 <' => 10
1213:	* )
1214:	* )
1215:	*
1216:	* @param array $orderBy The order by clause for query
1217:	*
1218:	* array(
1219:	* 'field' => 'asc\|desc'
1220:	* )
1221:	*
1222:	* OR
1223:	*
1224:	* array('RAW SQL expression')
1225:	*
1226:	* @param array $pagerOptions Array of key/value pairs to Pager options
1227:	* @return array [QueryBuilder, Pager, total]
1228:	*/
1229:	function db_findWithPager($table, array $condition = array(), array $orderBy = array(), array $pagerOptions = array())
1230:	{
1231:	if (db_tableHasTimestamps($table)) {
1232:	$condition['deleted'] = null;
1233:	}
1234:
1235:	# Count query for the pager
1236:	$countQuery = db_count($table);
1237:	if (!empty($condition)) {
1238:	$countQuery->where($condition);
1239:	}
1240:	$rowCount = $countQuery->fetch();
1241:
1242:	# Prerequisite for the Pager
1243:	$pagerOptions = array_merge(array(
1244:	'itemsPerPage' => _cfg('itemsPerPage'),
1245:	'pageNumLimit' => _cfg('pageNumLimit'),
1246:	'ajax' => true
1247:	), $pagerOptions);
1248:
1249:	$pager = _pager();
1250:	$pager->set('total', $rowCount);
1251:	foreach ($pagerOptions as $name => $value) {
1252:	$pager->set($name, $value);
1253:	}
1254:	$pager->calculate();
1255:
1256:	# Simple list query
1257:	$qb = db_select($table);
1258:	if (!empty($condition)) {
1259:	$qb->where($condition);
1260:	}
1261:	$qb->limit($pager->get('offset'), $pager->get('itemsPerPage'));
1262:
1263:	foreach ($orderBy as $field => $sort) {
1264:	if (is_numeric($field)) {
1265:	$qb->orderBy(db_raw($sort));
1266:	} else {
1267:	$qb->orderBy($field, $sort);
1268:	}
1269:	}
1270:
1271:	return array($qb, $pager, $rowCount);
1272:	}
1273:
1274:	/**
1275:	* Get data of a table by condition
1276:	*
1277:	* @param string $table The table name to fetch data from
1278:	* @param array $condition The condition array for query
1279:	*
1280:	* array(
1281:	* 'fieldName1' => $value1,
1282:	* 'fieldName2 >=' => $value2,
1283:	* 'fieldName3 => NULL
1284:	* )
1285:	*
1286:	* OR
1287:	*
1288:	* array(
1289:	* 'fieldName1' => $value1,
1290:	* 'fieldName2 >=' => $value2,
1291:	* 'fieldName3 => NULL,
1292:	* '$or' => array(
1293:	* 'fieldName4' => array(1, 2, 3)
1294:	* 'fieldName4 <' => 10
1295:	* )
1296:	* )
1297:	*
1298:	* @param array $orderBy The order by clause for query
1299:	*
1300:	* array(
1301:	* 'field' => 'asc\|desc'
1302:	* )
1303:	*
1304:	* OR
1305:	*
1306:	* array('RAW SQL expression')
1307:	*
1308:	* @param int $limit The number of records to return; No limit by default
1309:	* @return array
1310:	*/
1311:	function db_findBy($table, array $condition, array $orderBy = array(), $limit = null)
1312:	{
1313:	if (db_tableHasTimestamps($table)) {
1314:	$condition['deleted'] = null;
1315:	}
1316:
1317:	$qb = db_select($table)->where($condition);
1318:
1319:	foreach ($orderBy as $field => $sort) {
1320:	if (is_numeric($field)) {
1321:	$qb->orderBy(db_raw($sort));
1322:	} else {
1323:	$qb->orderBy($field, $sort);
1324:	}
1325:	}
1326:
1327:	if ($limit) {
1328:	$qb->limit($limit);
1329:	}
1330:
1331:	return $qb->getResult();
1332:	}
1333:
1334:	/**
1335:	* Get one record of a table by condition
1336:	* @param string $table The table name to fetch data from
1337:	* @param array $condition The condition array for query
1338:	*
1339:	* array(
1340:	* 'fieldName1' => $value1,
1341:	* 'fieldName2 >=' => $value2,
1342:	* 'fieldName3 => NULL
1343:	* )
1344:	*
1345:	* OR
1346:	*
1347:	* array(
1348:	* 'fieldName1' => $value1,
1349:	* 'fieldName2 >=' => $value2,
1350:	* 'fieldName3 => NULL,
1351:	* '$or' => array(
1352:	* 'fieldName4' => array(1, 2, 3)
1353:	* 'fieldName4 <' => 10
1354:	* )
1355:	* )
1356:	*
1357:	* @param array $orderBy The order by clause for query
1358:	*
1359:	* array(
1360:	* 'field' => 'asc\|desc'
1361:	* )
1362:	*
1363:	* @return object\|null
1364:	*/
1365:	function db_findOneBy($table, array $condition, array $orderBy = array())
1366:	{
1367:	$result = db_findBy($table, $condition, $orderBy, 1);
1368:	if (!empty($result)) {
1369:	return $result[0];
1370:	}
1371:
1372:	return null;
1373:	}
1374:
1375:	/**
1376:	* Get one record of a table by condition or throw 404 if not found
1377:	* @param string $table The table name to fetch data from
1378:	* @param array $condition The condition array for query
1379:	*
1380:	* array(
1381:	* 'fieldName1' => $value1,
1382:	* 'fieldName2 >=' => $value2,
1383:	* 'fieldName3 => NULL
1384:	* )
1385:	*
1386:	* OR
1387:	*
1388:	* array(
1389:	* 'fieldName1' => $value1,
1390:	* 'fieldName2 >=' => $value2,
1391:	* 'fieldName3 => NULL,
1392:	* '$or' => array(
1393:	* 'fieldName4' => array(1, 2, 3)
1394:	* 'fieldName4 <' => 10
1395:	* )
1396:	* )
1397:	*
1398:	* @param array $orderBy The order by clause for query
1399:	*
1400:	* array(
1401:	* 'field' => 'asc\|desc'
1402:	* )
1403:	*
1404:	* @return object\|null
1405:	*/
1406:	function db_findOneByOrFail($table, array $condition, array $orderBy = array())
1407:	{
1408:	$result = db_findOneBy($table, $condition, $orderBy);
1409:	if (empty($result)) {
1410:	_page404();
1411:	}
1412:
1413:	return $result;
1414:	}
1415:
1416:	/**
1417:	* Get all records for a table
1418:	* @param string $table The table name to fetch data from
1419:	* @param array $fields The list of the field names to select
1420:	* @param array $orderBy The order by clause for query
1421:	*
1422:	* array(
1423:	* 'field' => 'asc\|desc'
1424:	* )
1425:	*
1426:	* OR
1427:	*
1428:	* array('RAW SQL expression')
1429:	*
1430:	* @return array
1431:	*/
1432:	function db_findAll($table, $fields = array(), $orderBy = array())
1433:	{
1434:	$qb = db_select($table);
1435:
1436:	if (db_tableHasTimestamps($table)) {
1437:	$qb->where()->condition('deleted', null);
1438:	}
1439:
1440:	if (!empty($fields)) {
1441:	$qb->fields($table, $fields);
1442:	}
1443:
1444:	if (!empty($orderBy)) {
1445:	foreach ($orderBy as $field => $sort) {
1446:	if (is_numeric($field)) {
1447:	$qb->orderBy(db_raw($sort));
1448:	} else {
1449:	$qb->orderBy($field, $sort);
1450:	}
1451:	}
1452:	}
1453:
1454:	return $qb->getResult();
1455:	}
1456:
1457:	/**
1458:	* Get a column result value of a single entity result
1459:	*
1460:	* @param string $table The table name to fetch data from
1461:	* @param string $field The field name to select
1462:	* @param array $condition The condition array for query
1463:	* @return mixed
1464:	*/
1465:	function db_findColumn($table, $field, array $condition)
1466:	{
1467:	$entity = db_findOneBy($table, $condition);
1468:
1469:	return $entity ? $entity->$field : null;
1470:	}
1471:

Namespaces

Functions