/
class.mysql.php
1060 lines (952 loc) · 34.2 KB
/
class.mysql.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<?php
/**
* @package toolkit
*/
/**
* The DatabaseException class extends a normal Exception to add in
* debugging information when a SQL query fails such as the internal
* database error code and message in additional to the usual
* Exception information. It allows a DatabaseException to contain a human
* readable error, as well more technical information for debugging.
*/
class DatabaseException extends Exception
{
/**
* An associative array with three keys, 'query', 'msg' and 'num'
* @var array
*/
private $_error = array();
/**
* Constructor takes a message and an associative array to set to
* `$_error`. The message is passed to the default Exception constructor
*/
public function __construct($message, array $error = null)
{
parent::__construct($message);
$this->_error = $error;
}
/**
* Accessor function for the original query that caused this Exception
*
* @return string
*/
public function getQuery()
{
return $this->_error['query'];
}
/**
* Accessor function for the Database error code for this type of error
*
* @return integer
*/
public function getDatabaseErrorCode()
{
return $this->_error['num'];
}
/**
* Accessor function for the Database message from this Exception
*
* @return string
*/
public function getDatabaseErrorMessage()
{
return $this->_error['msg'];
}
}
/**
* The MySQL class acts as a wrapper for connecting to the Database
* in Symphony. It utilises mysqli_* functions in PHP to complete the usual
* querying. As well as the normal set of insert, update, delete and query
* functions, some convenience functions are provided to return results
* in different ways. Symphony uses a prefix to namespace it's tables in a
* database, allowing it play nice with other applications installed on the
* database. An errors that occur during a query throw a `DatabaseException`.
* By default, Symphony logs all queries to be used for Profiling and Debug
* devkit extensions when a Developer is logged in. When a developer is not
* logged in, all queries and errors are made available with delegates.
*/
class MySQL
{
/**
* Constant to indicate whether the query is a write operation.
*
* @var integer
*/
const __WRITE_OPERATION__ = 0;
/**
* Constant to indicate whether the query is a write operation
*
* @var integer
*/
const __READ_OPERATION__ = 1;
/**
* Sets the current `$_log` to be an empty array
*
* @var array
*/
private static $_log = array();
/**
* The number of queries this class has executed, defaults to 0.
*
* @var integer
*/
private static $_query_count = 0;
/**
* Whether query caching is enabled or not. By default this is set
* to true which will use SQL_CACHE to cache the results of queries
*
* @var boolean
*/
private static $_cache = true;
/**
* Whether query logging is enabled or not. By default this is set
* to true, which allows profiling of queries
*
* @var boolean
*/
private static $_logging = true;
/**
* An associative array of connection properties for this MySQL
* database including the host, port, username, password and
* selected database.
*
* @var array
*/
private static $_connection = array();
/**
* The resource of the last result returned from mysqli_query
*
* @var resource
*/
private $_result = null;
/**
* The last query that was executed by the class
*/
private $_lastQuery = null;
/**
* The hash value of the last query that was executed by the class
*/
private $_lastQueryHash = null;
/**
* The auto increment value returned by the last query that was executed
* by the class
*/
private $_lastInsertID = null;
/**
* By default, an array of arrays or objects representing the result set
* from the `$this->_lastQuery`
*/
private $_lastResult = array();
/**
* Magic function that will flush the MySQL log and close the MySQL
* connection when the MySQL class is removed or destroyed.
*
* @link http://php.net/manual/en/language.oop5.decon.php
*/
public function __destruct()
{
$this->flush();
$this->close();
}
/**
* Resets the result, `$this->_lastResult` and `$this->_lastQuery` to their empty
* values. Called on each query and when the class is destroyed.
*/
public function flush()
{
$this->_result = null;
$this->_lastResult = array();
$this->_lastQuery = null;
$this->_lastQueryHash = null;
}
/**
* Sets the current `$_log` to be an empty array
*/
public static function flushLog()
{
self::$_log = array();
}
/**
* Returns the number of queries that has been executed
*
* @return integer
*/
public static function queryCount()
{
return self::$_query_count;
}
/**
* Sets query caching to true, this will prepend all READ_OPERATION
* queries with SQL_CACHE. Symphony by default enables caching. It
* can be turned off by setting the `query_cache` parameter to `off` in the
* Symphony config file.
*
* @link http://dev.mysql.com/doc/refman/5.1/en/query-cache.html
*/
public static function enableCaching()
{
self::$_cache = true;
}
/**
* Sets query caching to false, this will prepend all READ_OPERATION
* queries will SQL_NO_CACHE.
*/
public static function disableCaching()
{
self::$_cache = false;
}
/**
* Returns boolean if query caching is enabled or not
*
* @return boolean
*/
public static function isCachingEnabled()
{
return self::$_cache;
}
/**
* Enables query logging and profiling.
*
* @since Symphony 2.6.2
*/
public static function enableLogging()
{
self::$_logging = true;
}
/**
* Disables query logging and profiling. Use this in low memory environments
* to reduce memory usage.
*
* @since Symphony 2.6.2
* @link https://github.com/symphonycms/symphony-2/issues/2398
*/
public static function disableLogging()
{
self::$_logging = false;
}
/**
* Returns boolean if logging is enabled or not
*
* @since Symphony 2.6.2
* @return boolean
*/
public static function isLoggingEnabled()
{
return self::$_logging;
}
/**
* Symphony uses a prefix for all it's database tables so it can live peacefully
* on the same database as other applications. By default this is `sym_`, but it
* can be changed when Symphony is installed.
*
* @param string $prefix
* The table prefix for Symphony, by default this is `sym_`
*/
public function setPrefix($prefix)
{
self::$_connection['tbl_prefix'] = $prefix;
}
/**
* Returns the prefix used by Symphony for this Database instance.
*
* @since Symphony 2.4
* @return string
*/
public function getPrefix()
{
return self::$_connection['tbl_prefix'];
}
/**
* Determines if a connection has been made to the MySQL server
*
* @return boolean
*/
public static function isConnected()
{
try {
$connected = (
isset(self::$_connection['id'])
&& !is_null(self::$_connection['id'])
);
} catch (Exception $ex) {
return false;
}
return $connected;
}
/**
* Called when the script has finished executing, this closes the MySQL
* connection
*
* @return boolean
*/
public function close()
{
if ($this->isConnected()) {
return mysqli_close(self::$_connection['id']);
}
}
/**
* Creates a connect to the database server given the credentials. If an
* error occurs, a `DatabaseException` is thrown, otherwise true is returned
*
* @param string $host
* Defaults to null, which MySQL assumes as localhost.
* @param string $user
* Defaults to null
* @param string $password
* Defaults to null
* @param string $port
* Defaults to 3306.
* @param null $database
* @throws DatabaseException
* @return boolean
*/
public function connect($host = null, $user = null, $password = null, $port = '3306', $database = null)
{
self::$_connection = array(
'host' => $host,
'user' => $user,
'pass' => $password,
'port' => $port,
'database' => $database
);
try {
self::$_connection['id'] = mysqli_connect(
self::$_connection['host'],
self::$_connection['user'],
self::$_connection['pass'],
self::$_connection['database'],
self::$_connection['port']
);
if (!$this->isConnected()) {
$this->__error('connect');
}
} catch (Exception $ex) {
$this->__error('connect');
}
return true;
}
/**
* Accessor for the current MySQL resource from PHP. May be
* useful for developers who want complete control over their
* database queries and don't want anything abstract by the MySQL
* class.
*
* @return resource
*/
public static function getConnectionResource()
{
return self::$_connection['id'];
}
/**
* This will set the character encoding of the connection for sending and
* receiving data. This function will run every time the database class
* is being initialized. If no character encoding is provided, UTF-8
* is assumed.
*
* @link http://au2.php.net/manual/en/function.mysql-set-charset.php
* @param string $set
* The character encoding to use, by default this 'utf8'
*/
public function setCharacterEncoding($set = 'utf8')
{
mysqli_set_charset(self::$_connection['id'], $set);
}
/**
* This function will set the character encoding of the database so that any
* new tables that are created by Symphony use this character encoding
*
* @link http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
* @param string $set
* The character encoding to use, by default this 'utf8'
* @throws DatabaseException
*/
public function setCharacterSet($set = 'utf8')
{
$this->query("SET character_set_connection = '$set', character_set_database = '$set', character_set_server = '$set'");
$this->query("SET CHARACTER SET '$set'");
}
/**
* Sets the MySQL connection to use this timezone instead of the default
* MySQL server timezone.
*
* @throws DatabaseException
* @link https://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html
* @link https://github.com/symphonycms/symphony-2/issues/1726
* @since Symphony 2.3.3
* @param string $timezone
* Timezone will human readable, such as Australia/Brisbane.
*/
public function setTimeZone($timezone = null)
{
if (is_null($timezone)) {
return;
}
// What is the time now in the install timezone
$symphony_date = new DateTime('now', new DateTimeZone($timezone));
// MySQL wants the offset to be in the format +/-H:I, getOffset returns offset in seconds
$utc = new DateTime('now ' . $symphony_date->getOffset() . ' seconds', new DateTimeZone("UTC"));
// Get the difference between the symphony install timezone and UTC
$offset = $symphony_date->diff($utc)->format('%R%H:%I');
$this->query("SET time_zone = '$offset'");
}
/**
* This function will clean a string using the `mysqli_real_escape_string` function
* taking into account the current database character encoding. Note that this
* function does not encode _ or %. If `mysqli_real_escape_string` doesn't exist,
* `addslashes` will be used as a backup option
*
* @param string $value
* The string to be encoded into an escaped SQL string
* @return string
* The escaped SQL string
*/
public static function cleanValue($value)
{
if (function_exists('mysqli_real_escape_string') && self::isConnected()) {
return mysqli_real_escape_string(self::$_connection['id'], $value);
} else {
return addslashes($value);
}
}
/**
* This function will apply the `cleanValue` function to an associative
* array of data, encoding only the value, not the key. This function
* can handle recursive arrays. This function manipulates the given
* parameter by reference.
*
* @see cleanValue
* @param array $array
* The associative array of data to encode, this parameter is manipulated
* by reference.
*/
public static function cleanFields(array &$array)
{
foreach ($array as $key => $val) {
// Handle arrays with more than 1 level
if (is_array($val)) {
self::cleanFields($val);
continue;
} elseif (strlen($val) == 0) {
$array[$key] = 'null';
} else {
$array[$key] = "'" . self::cleanValue($val) . "'";
}
}
}
/**
* Determines whether this query is a read operation, or if it is a write operation.
* A write operation is determined as any query that starts with CREATE, INSERT,
* REPLACE, ALTER, DELETE, UPDATE, OPTIMIZE, TRUNCATE or DROP. Anything else is
* considered to be a read operation which are subject to query caching.
*
* @param string $query
* @return integer
* `self::__WRITE_OPERATION__` or `self::__READ_OPERATION__`
*/
public function determineQueryType($query)
{
return (preg_match('/^(create|insert|replace|alter|delete|update|optimize|truncate|drop)/i', $query) ? self::__WRITE_OPERATION__ : self::__READ_OPERATION__);
}
/**
* Takes an SQL string and executes it. This function will apply query
* caching if it is a read operation and if query caching is set. Symphony
* will convert the `tbl_` prefix of tables to be the one set during installation.
* A type parameter is provided to specify whether `$this->_lastResult` will be an array
* of objects or an array of associative arrays. The default is objects. This
* function will return boolean, but set `$this->_lastResult` to the result.
*
* @uses PostQueryExecution
* @param string $query
* The full SQL query to execute.
* @param string $type
* Whether to return the result as objects or associative array. Defaults
* to OBJECT which will return objects. The other option is ASSOC. If $type
* is not either of these, it will return objects.
* @throws DatabaseException
* @return boolean
* True if the query executed without errors, false otherwise
*/
public function query($query, $type = "OBJECT")
{
if (empty($query) || self::isConnected() === false) {
return false;
}
$start = precision_timer();
$query = trim($query);
$query_type = $this->determineQueryType($query);
$query_hash = md5($query.$start);
if (self::$_connection['tbl_prefix'] !== 'tbl_') {
$query = preg_replace('/tbl_(\S+?)([\s\.,]|$)/', self::$_connection['tbl_prefix'].'\\1\\2', $query);
}
// TYPE is deprecated since MySQL 4.0.18, ENGINE is preferred
if ($query_type == self::__WRITE_OPERATION__) {
$query = preg_replace('/TYPE=(MyISAM|InnoDB)/i', 'ENGINE=$1', $query);
} elseif ($query_type == self::__READ_OPERATION__ && !preg_match('/^SELECT\s+SQL(_NO)?_CACHE/i', $query)) {
if ($this->isCachingEnabled()) {
$query = preg_replace('/^SELECT\s+/i', 'SELECT SQL_CACHE ', $query);
} else {
$query = preg_replace('/^SELECT\s+/i', 'SELECT SQL_NO_CACHE ', $query);
}
}
$this->flush();
$this->_lastQuery = $query;
$this->_lastQueryHash = $query_hash;
$this->_result = mysqli_query(self::$_connection['id'], $query);
$this->_lastInsertID = mysqli_insert_id(self::$_connection['id']);
self::$_query_count++;
if (mysqli_error(self::$_connection['id'])) {
$this->__error();
} elseif (($this->_result instanceof mysqli_result)) {
if ($type == "ASSOC") {
while ($row = mysqli_fetch_assoc($this->_result)) {
$this->_lastResult[] = $row;
}
} else {
while ($row = mysqli_fetch_object($this->_result)) {
$this->_lastResult[] = $row;
}
}
mysqli_free_result($this->_result);
}
$stop = precision_timer('stop', $start);
/**
* After a query has successfully executed, that is it was considered
* valid SQL, this delegate will provide the query, the query_hash and
* the execution time of the query.
*
* Note that this function only starts logging once the ExtensionManager
* is available, which means it will not fire for the first couple of
* queries that set the character set.
*
* @since Symphony 2.3
* @delegate PostQueryExecution
* @param string $context
* '/frontend/' or '/backend/'
* @param string $query
* The query that has just been executed
* @param string $query_hash
* The hash used by Symphony to uniquely identify this query
* @param float $execution_time
* The time that it took to run `$query`
*/
if (self::$_logging === true) {
if (Symphony::ExtensionManager() instanceof ExtensionManager) {
Symphony::ExtensionManager()->notifyMembers('PostQueryExecution', class_exists('Administration', false) ? '/backend/' : '/frontend/', array(
'query' => $query,
'query_hash' => $query_hash,
'execution_time' => $stop
));
// If the ExceptionHandler is enabled, then the user is authenticated
// or we have a serious issue, so log the query.
if (GenericExceptionHandler::$enabled) {
self::$_log[$query_hash] = array(
'query' => $query,
'query_hash' => $query_hash,
'execution_time' => $stop
);
}
// Symphony isn't ready yet. Log internally
} else {
self::$_log[$query_hash] = array(
'query' => $query,
'query_hash' => $query_hash,
'execution_time' => $stop
);
}
}
return true;
}
/**
* Returns the last insert ID from the previous query. This is
* the value from an auto_increment field.
*
* @return integer
* The last interested row's ID
*/
public function getInsertID()
{
return $this->_lastInsertID;
}
/**
* A convenience method to insert data into the Database. This function
* takes an associative array of data to input, with the keys being the column
* names and the table. An optional parameter exposes MySQL's ON DUPLICATE
* KEY UPDATE functionality, which will update the values if a duplicate key
* is found.
*
* @param array $fields
* An associative array of data to input, with the key's mapping to the
* column names. Alternatively, an array of associative array's can be
* provided, which will perform multiple inserts
* @param string $table
* The table name, including the tbl prefix which will be changed
* to this Symphony's table prefix in the query function
* @param boolean $updateOnDuplicate
* If set to true, data will updated if any key constraints are found that cause
* conflicts. By default this is set to false, which will not update the data and
* would return an SQL error
* @throws DatabaseException
* @return boolean
*/
public function insert(array $fields, $table, $updateOnDuplicate = false)
{
// Multiple Insert
if (is_array(current($fields))) {
$sql = "INSERT INTO `$table` (`".implode('`, `', array_keys(current($fields))).'`) VALUES ';
$rows = array();
foreach ($fields as $key => $array) {
// Sanity check: Make sure we dont end up with ',()' in the SQL.
if (!is_array($array)) {
continue;
}
self::cleanFields($array);
$rows[] = '('.implode(', ', $array).')';
}
$sql .= implode(", ", $rows);
// Single Insert
} else {
self::cleanFields($fields);
$sql = "INSERT INTO `$table` (`".implode('`, `', array_keys($fields)).'`) VALUES ('.implode(', ', $fields).')';
if ($updateOnDuplicate) {
$sql .= ' ON DUPLICATE KEY UPDATE ';
foreach ($fields as $key => $value) {
$sql .= " `$key` = $value,";
}
$sql = trim($sql, ',');
}
}
return $this->query($sql);
}
/**
* A convenience method to update data that exists in the Database. This function
* takes an associative array of data to input, with the keys being the column
* names and the table. A WHERE statement can be provided to select the rows
* to update
*
* @param array $fields
* An associative array of data to input, with the key's mapping to the
* column names.
* @param string $table
* The table name, including the tbl prefix which will be changed
* to this Symphony's table prefix in the query function
* @param string $where
* A WHERE statement for this UPDATE statement, defaults to null
* which will update all rows in the $table
* @throws DatabaseException
* @return boolean
*/
public function update($fields, $table, $where = null)
{
self::cleanFields($fields);
$sql = "UPDATE $table SET ";
$rows = array();
foreach ($fields as $key => $val) {
$rows[] = " `$key` = $val";
}
$sql .= implode(', ', $rows) . (!is_null($where) ? ' WHERE ' . $where : null);
return $this->query($sql);
}
/**
* Given a table name and a WHERE statement, delete rows from the
* Database.
*
* @param string $table
* The table name, including the tbl prefix which will be changed
* to this Symphony's table prefix in the query function
* @param string $where
* A WHERE statement for this DELETE statement, defaults to null,
* which will delete all rows in the $table
* @throws DatabaseException
* @return boolean
*/
public function delete($table, $where = null)
{
$sql = "DELETE FROM $table";
if (!is_null($where)) {
$sql .= " WHERE $where";
}
return $this->query($sql);
}
/**
* Returns an associative array that contains the results of the
* given `$query`. Optionally, the resulting array can be indexed
* by a particular column.
*
* @param string $query
* The full SQL query to execute. Defaults to null, which will
* use the _lastResult
* @param string $index_by_column
* The name of a column in the table to use it's value to index
* the result by. If this is omitted (and it is by default), an
* array of associative arrays is returned, with the key being the
* column names
* @throws DatabaseException
* @return array
* An associative array with the column names as the keys
*/
public function fetch($query = null, $index_by_column = null)
{
if (!is_null($query)) {
$this->query($query, "ASSOC");
} elseif (is_null($this->_lastResult)) {
return array();
}
$result = $this->_lastResult;
if (!is_null($index_by_column) && isset($result[0][$index_by_column])) {
$n = array();
foreach ($result as $ii) {
$n[$ii[$index_by_column]] = $ii;
}
$result = $n;
}
return $result;
}
/**
* Returns the row at the specified index from the given query. If no
* query is given, it will use the `$this->_lastResult`. If no offset is provided,
* the function will return the first row. This function does not imply any
* LIMIT to the given `$query`, so for the more efficient use, it is recommended
* that the `$query` have a LIMIT set.
*
* @param integer $offset
* The row to return from the SQL query. For instance, if the second
* row from the result was required, the offset would be 1, because it
* is zero based.
* @param string $query
* The full SQL query to execute. Defaults to null, which will
* use the `$this->_lastResult`
* @throws DatabaseException
* @return array
* If there is no row at the specified `$offset`, an empty array will be returned
* otherwise an associative array of that row will be returned.
*/
public function fetchRow($offset = 0, $query = null)
{
$result = $this->fetch($query);
return (empty($result) ? array() : $result[$offset]);
}
/**
* Returns an array of values for a specified column in a given query.
* If no query is given, it will use the `$this->_lastResult`.
*
* @param string $column
* The column name in the query to return the values for
* @param string $query
* The full SQL query to execute. Defaults to null, which will
* use the `$this->_lastResult`
* @throws DatabaseException
* @return array
* If there is no results for the `$query`, an empty array will be returned
* otherwise an array of values for that given `$column` will be returned
*/
public function fetchCol($column, $query = null)
{
$result = $this->fetch($query);
if (empty($result)) {
return array();
}
$rows = array();
foreach ($result as $row) {
$rows[] = $row[$column];
}
return $rows;
}
/**
* Returns the value for a specified column at a specified offset. If no
* offset is provided, it will return the value for column of the first row.
* If no query is given, it will use the `$this->_lastResult`.
*
* @param string $column
* The column name in the query to return the values for
* @param integer $offset
* The row to use to return the value for the given `$column` from the SQL
* query. For instance, if `$column` form the second row was required, the
* offset would be 1, because it is zero based.
* @param string $query
* The full SQL query to execute. Defaults to null, which will
* use the `$this->_lastResult`
* @throws DatabaseException
* @return string|null
* Returns the value of the given column, if it doesn't exist, null will be
* returned
*/
public function fetchVar($column, $offset = 0, $query = null)
{
$result = $this->fetch($query);
return (empty($result) ? null : $result[$offset][$column]);
}
/**
* This function takes `$table` and `$field` names and returns boolean
* if the `$table` contains the `$field`.
*
* @since Symphony 2.3
* @param string $table
* The table name
* @param string $field
* The field name
* @throws DatabaseException
* @return boolean
* True if `$table` contains `$field`, false otherwise
*/
public function tableContainsField($table, $field)
{
$results = $this->fetch("DESC `{$table}` `{$field}`");
return (is_array($results) && !empty($results));
}
/**
* This function takes `$table` and returns boolean
* if it exists or not.
*
* @since Symphony 2.3.4
* @param string $table
* The table name
* @throws DatabaseException
* @return boolean
* True if `$table` exists, false otherwise
*/
public function tableExists($table)
{
$results = $this->fetch(sprintf("SHOW TABLES LIKE '%s'", $table));
return (is_array($results) && !empty($results));
}
/**
* If an error occurs in a query, this function is called which logs
* the last query and the error number and error message from MySQL
* before throwing a `DatabaseException`
*
* @uses QueryExecutionError
* @throws DatabaseException
* @param string $type
* Accepts one parameter, 'connect', which will return the correct
* error codes when the connection sequence fails
*/
private function __error($type = null)
{
if ($type == 'connect') {
$msg = mysqli_connect_error();
$errornum = mysqli_connect_errno();
} else {
$msg = mysqli_error(self::$_connection['id']);
$errornum = mysqli_errno(self::$_connection['id']);
}
/**
* After a query execution has failed this delegate will provide the query,
* query hash, error message and the error number.
*
* Note that this function only starts logging once the `ExtensionManager`
* is available, which means it will not fire for the first couple of
* queries that set the character set.
*
* @since Symphony 2.3
* @delegate QueryExecutionError
* @param string $context
* '/frontend/' or '/backend/'
* @param string $query
* The query that has just been executed
* @param string $query_hash
* The hash used by Symphony to uniquely identify this query
* @param string $msg
* The error message provided by MySQL which includes information on why the execution failed
* @param integer $num
* The error number that corresponds with the MySQL error message
*/
if (self::$_logging === true) {
if (Symphony::ExtensionManager() instanceof ExtensionManager) {
Symphony::ExtensionManager()->notifyMembers('QueryExecutionError', class_exists('Administration', false) ? '/backend/' : '/frontend/', array(
'query' => $this->_lastQuery,
'query_hash' => $this->_lastQueryHash,
'msg' => $msg,
'num' => $errornum
));
}
}
throw new DatabaseException(__('MySQL Error (%1$s): %2$s in query: %3$s', array($errornum, $msg, $this->_lastQuery)), array(
'msg' => $msg,
'num' => $errornum,
'query' => $this->_lastQuery
));
}
/**
* Returns all the log entries by type. There are two valid types,
* error and debug. If no type is given, the entire log is returned,
* otherwise only log messages for that type are returned
*
* @param null|string $type
* @return array
* An array of associative array's. Log entries of the error type
* return the query the error occurred on and the error number and
* message from MySQL. Log entries of the debug type return the
* the query and the start/stop time to indicate how long it took
* to run
*/
public function debug($type = null)
{
if (!$type) {
return self::$_log;
}
return ($type == 'error' ? self::$_log['error'] : self::$_log['query']);
}
/**
* Returns some basic statistics from the MySQL class about the
* number of queries, the time it took to query and any slow queries.
* A slow query is defined as one that took longer than 0.0999 seconds
* This function is used by the Profile devkit
*
* @return array
* An associative array with the number of queries, an array of slow
* queries and the total query time.
*/
public function getStatistics()