3 @version v5.20.19 13-Dec-2020
4 @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
5 @copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community
6 Released under both BSD license and Lesser GPL library license.
7 Whenever there is any discrepancy between the two licenses,
8 the BSD license will take precedence.
11 This driver only supports the original non-transactional MySQL driver. It
12 is deprected in PHP version 5.5 and removed in PHP version 7. It is deprecated
13 as of ADOdb version 5.20.0. Use the mysqli driver instead, which supports both
14 transactional and non-transactional updates
16 Requires mysql client. Works on Windows and Unix.
18 28 Feb 2001: MetaColumns bug fix - suggested by Freek Dijkstra (phpeverywhere@macfreek.com)
21 // security - hide paths
22 if (!defined('ADODB_DIR')) die();
24 if (! defined("_ADODB_MYSQL_LAYER")) {
25 define("_ADODB_MYSQL_LAYER", 1 );
27 class ADODB_mysql extends ADOConnection {
28 var $databaseType = 'mysql';
29 var $dataProvider = 'mysql';
30 var $hasInsertID = true;
31 var $hasAffectedRows = true;
32 var $metaTablesSQL = "SELECT
34 CASE WHEN TABLE_TYPE = 'VIEW' THEN 'V' ELSE 'T' END
35 FROM INFORMATION_SCHEMA.TABLES
37 var $metaColumnsSQL = "SHOW COLUMNS FROM `%s`";
38 var $fmtTimeStamp = "'Y-m-d H:i:s'";
40 var $hasMoveFirst = true;
42 var $isoDates = true; // accepts dates in ISO format
43 var $sysDate = 'CURDATE()';
44 var $sysTimeStamp = 'NOW()';
45 var $hasTransactions = false;
46 var $forceNewConnect = false;
47 var $poorAffectedRows = true;
50 var $substr = "substring";
51 var $nameQuote = '`'; /// string to use to quote identifiers and names
52 var $compat323 = false; // true if compat with mysql 3.23
54 function __construct()
56 if (defined('ADODB_EXTENSION')) $this->rsPrefix .= 'ext_';
60 // SetCharSet - switch the client encoding
61 function SetCharSet($charset_name)
63 if (!function_exists('mysql_set_charset')) {
67 if ($this->charSet !== $charset_name) {
68 $ok = @mysql_set_charset($charset_name,$this->_connectionID);
70 $this->charSet = $charset_name;
80 $arr['description'] = ADOConnection::GetOne("select version()");
81 $arr['version'] = ADOConnection::_findvers($arr['description']);
85 function IfNull( $field, $ifNull )
87 return " IFNULL($field, $ifNull) "; // if MySQL
90 function MetaProcedures($NamePattern = false, $catalog = null, $schemaPattern = null)
92 // save old fetch mode
93 global $ADODB_FETCH_MODE;
96 $save = $ADODB_FETCH_MODE;
97 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
99 if ($this->fetchMode !== FALSE) {
100 $savem = $this->SetFetchMode(FALSE);
103 $procedures = array ();
109 $likepattern = " LIKE '".$NamePattern."'";
111 $rs = $this->Execute('SHOW PROCEDURE STATUS'.$likepattern);
112 if (is_object($rs)) {
114 // parse index data into array
115 while ($row = $rs->FetchRow()) {
116 $procedures[$row[1]] = array(
117 'type' => 'PROCEDURE',
120 'remarks' => $row[7],
125 $rs = $this->Execute('SHOW FUNCTION STATUS'.$likepattern);
126 if (is_object($rs)) {
127 // parse index data into array
128 while ($row = $rs->FetchRow()) {
129 $procedures[$row[1]] = array(
130 'type' => 'FUNCTION',
140 $this->SetFetchMode($savem);
142 $ADODB_FETCH_MODE = $save;
148 * Retrieves a list of tables based on given criteria
150 * @param string $ttype Table type = 'TABLE', 'VIEW' or false=both (default)
151 * @param string $showSchema schema name, false = current schema (default)
152 * @param string $mask filters the table by name
154 * @return array list of tables
156 function MetaTables($ttype=false,$showSchema=false,$mask=false)
158 $save = $this->metaTablesSQL;
159 if ($showSchema && is_string($showSchema)) {
160 $this->metaTablesSQL .= $this->qstr($showSchema);
162 $this->metaTablesSQL .= "schema()";
166 $mask = $this->qstr($mask);
167 $this->metaTablesSQL .= " AND table_name LIKE $mask";
169 $ret = ADOConnection::MetaTables($ttype,$showSchema);
171 $this->metaTablesSQL = $save;
176 function MetaIndexes ($table, $primary = FALSE, $owner=false)
178 // save old fetch mode
179 global $ADODB_FETCH_MODE;
182 $save = $ADODB_FETCH_MODE;
183 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
184 if ($this->fetchMode !== FALSE) {
185 $savem = $this->SetFetchMode(FALSE);
189 $rs = $this->Execute(sprintf('SHOW INDEX FROM %s',$table));
193 $this->SetFetchMode($savem);
195 $ADODB_FETCH_MODE = $save;
197 if (!is_object($rs)) {
203 // parse index data into array
204 while ($row = $rs->FetchRow()) {
205 if ($primary == FALSE AND $row[2] == 'PRIMARY') {
209 if (!isset($indexes[$row[2]])) {
210 $indexes[$row[2]] = array(
211 'unique' => ($row[1] == 0),
216 $indexes[$row[2]]['columns'][$row[3] - 1] = $row[4];
219 // sort columns by order in the index
220 foreach ( array_keys ($indexes) as $index )
222 ksort ($indexes[$index]['columns']);
229 // if magic quotes disabled, use mysql_real_escape_string()
230 function qstr($s,$magic_quotes=false)
232 if (is_null($s)) return 'NULL';
233 if (!$magic_quotes) {
235 if (ADODB_PHPVER >= 0x4300) {
236 if (is_resource($this->_connectionID))
237 return "'".mysql_real_escape_string($s,$this->_connectionID)."'";
239 if ($this->replaceQuote[0] == '\\'){
240 $s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\0"),$s);
242 return "'".str_replace("'",$this->replaceQuote,$s)."'";
245 // undo magic quotes for "
246 $s = str_replace('\\"','"',$s);
252 return ADOConnection::GetOne('SELECT LAST_INSERT_ID()');
253 //return mysql_insert_id($this->_connectionID);
256 function GetOne($sql,$inputarr=false)
258 global $ADODB_GETONE_EOF;
259 if ($this->compat323 == false && strncasecmp($sql,'sele',4) == 0) {
260 $rs = $this->SelectLimit($sql,1,-1,$inputarr);
263 if ($rs->EOF) return $ADODB_GETONE_EOF;
264 return reset($rs->fields);
267 return ADOConnection::GetOne($sql,$inputarr);
272 function BeginTrans()
274 if ($this->debug) ADOConnection::outp("Transactions not supported in 'mysql' driver. Use 'mysqlt' or 'mysqli' driver");
277 function _affectedrows()
279 return mysql_affected_rows($this->_connectionID);
282 // See http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
283 // Reference on Last_Insert_ID on the recommended way to simulate sequences
284 var $_genIDSQL = "update %s set id=LAST_INSERT_ID(id+1);";
285 var $_genSeqSQL = "create table if not exists %s (id int not null)";
286 var $_genSeqCountSQL = "select count(*) from %s";
287 var $_genSeq2SQL = "insert into %s values (%s)";
288 var $_dropSeqSQL = "drop table if exists %s";
290 function CreateSequence($seqname='adodbseq',$startID=1)
292 if (empty($this->_genSeqSQL)) return false;
293 $u = strtoupper($seqname);
295 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
296 if (!$ok) return false;
297 return $this->Execute(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
301 function GenID($seqname='adodbseq',$startID=1)
303 // post-nuke sets hasGenID to false
304 if (!$this->hasGenID) return false;
306 $savelog = $this->_logsql;
307 $this->_logsql = false;
308 $getnext = sprintf($this->_genIDSQL,$seqname);
309 $holdtransOK = $this->_transOK; // save the current status
310 $rs = @$this->Execute($getnext);
312 if ($holdtransOK) $this->_transOK = true; //if the status was ok before reset
313 $u = strtoupper($seqname);
314 $this->Execute(sprintf($this->_genSeqSQL,$seqname));
315 $cnt = $this->GetOne(sprintf($this->_genSeqCountSQL,$seqname));
316 if (!$cnt) $this->Execute(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
317 $rs = $this->Execute($getnext);
321 $this->genID = mysql_insert_id($this->_connectionID);
326 $this->_logsql = $savelog;
330 function MetaDatabases()
332 $qid = mysql_list_dbs($this->_connectionID);
335 $max = mysql_num_rows($qid);
337 $db = mysql_tablename($qid,$i);
338 if ($db != 'mysql') $arr[] = $db;
345 // Format date column in sql string given an input format that understands Y M D
346 function SQLDate($fmt, $col=false)
348 if (!$col) $col = $this->sysTimeStamp;
349 $s = 'DATE_FORMAT('.$col.",'";
352 for ($i=0; $i < $len; $i++) {
359 $ch = substr($fmt,$i,1);
385 $s .= "'),Quarter($col)";
387 if ($len > $i+1) $s .= ",DATE_FORMAT($col,'";
427 if ($concat) $s = "CONCAT($s)";
432 // returns concatenated string
433 // much easier to run "mysqld --ansi" or "mysqld --sql-mode=PIPES_AS_CONCAT" and use || operator
437 $arr = func_get_args();
439 // suggestion by andrew005@mnogo.ru
440 $s = implode(',',$arr);
441 if (strlen($s) > 0) return "CONCAT($s)";
445 function OffsetDate($dayFraction,$date=false)
447 if (!$date) $date = $this->sysDate;
449 $fraction = $dayFraction * 24 * 3600;
450 return '('. $date . ' + INTERVAL ' . $fraction.' SECOND)';
452 // return "from_unixtime(unix_timestamp($date)+$fraction)";
455 // returns true or false
456 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
458 if (!empty($this->port)) $argHostname .= ":".$this->port;
460 if (ADODB_PHPVER >= 0x4300)
461 $this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword,
462 $this->forceNewConnect,$this->clientFlags);
463 else if (ADODB_PHPVER >= 0x4200)
464 $this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword,
465 $this->forceNewConnect);
467 $this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword);
469 if ($this->_connectionID === false) return false;
470 if ($argDatabasename) return $this->SelectDB($argDatabasename);
474 // returns true or false
475 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
477 if (!empty($this->port)) $argHostname .= ":".$this->port;
479 if (ADODB_PHPVER >= 0x4300)
480 $this->_connectionID = mysql_pconnect($argHostname,$argUsername,$argPassword,$this->clientFlags);
482 $this->_connectionID = mysql_pconnect($argHostname,$argUsername,$argPassword);
483 if ($this->_connectionID === false) return false;
484 if ($this->autoRollback) $this->RollbackTrans();
485 if ($argDatabasename) return $this->SelectDB($argDatabasename);
489 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
491 $this->forceNewConnect = true;
492 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
495 function MetaColumns($table, $normalize=true)
497 $this->_findschema($table,$schema);
499 $dbName = $this->database;
500 $this->SelectDB($schema);
502 global $ADODB_FETCH_MODE;
503 $save = $ADODB_FETCH_MODE;
504 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
506 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
507 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table));
510 $this->SelectDB($dbName);
513 if (isset($savem)) $this->SetFetchMode($savem);
514 $ADODB_FETCH_MODE = $save;
515 if (!is_object($rs)) {
522 $fld = new ADOFieldObject();
523 $fld->name = $rs->fields[0];
524 $type = $rs->fields[1];
526 // split type into type(length):
528 if (preg_match("/^(.+)\((\d+),(\d+)/", $type, $query_array)) {
529 $fld->type = $query_array[1];
530 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
531 $fld->scale = is_numeric($query_array[3]) ? $query_array[3] : -1;
532 } elseif (preg_match("/^(.+)\((\d+)/", $type, $query_array)) {
533 $fld->type = $query_array[1];
534 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
535 } elseif (preg_match("/^(enum)\((.*)\)$/i", $type, $query_array)) {
536 $fld->type = $query_array[1];
537 $arr = explode(",",$query_array[2]);
539 $zlen = max(array_map("strlen",$arr)) - 2; // PHP >= 4.0.6
540 $fld->max_length = ($zlen > 0) ? $zlen : 1;
543 $fld->max_length = -1;
545 $fld->not_null = ($rs->fields[2] != 'YES');
546 $fld->primary_key = ($rs->fields[3] == 'PRI');
547 $fld->auto_increment = (strpos($rs->fields[5], 'auto_increment') !== false);
548 $fld->binary = (strpos($type,'blob') !== false || strpos($type,'binary') !== false);
549 $fld->unsigned = (strpos($type,'unsigned') !== false);
550 $fld->zerofill = (strpos($type,'zerofill') !== false);
554 if ($d != '' && $d != 'NULL') {
555 $fld->has_default = true;
556 $fld->default_value = $d;
558 $fld->has_default = false;
562 if ($save == ADODB_FETCH_NUM) {
565 $retarr[strtoupper($fld->name)] = $fld;
574 // returns true or false
575 function SelectDB($dbName)
577 $this->database = $dbName;
578 $this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
579 if ($this->_connectionID) {
580 return @mysql_select_db($dbName,$this->_connectionID);
585 // parameters use PostgreSQL convention, not MySQL
586 function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs=0)
588 $nrows = (int) $nrows;
589 $offset = (int) $offset;
590 $offsetStr =($offset>=0) ? ((integer)$offset)."," : '';
591 // jason judge, see PHPLens Issue No: 9220
592 if ($nrows < 0) $nrows = '18446744073709551615';
595 $rs = $this->CacheExecute($secs,$sql." LIMIT $offsetStr".((integer)$nrows),$inputarr);
597 $rs = $this->Execute($sql." LIMIT $offsetStr".((integer)$nrows),$inputarr);
601 // returns queryID or false
602 function _query($sql,$inputarr=false)
605 return mysql_query($sql,$this->_connectionID);
607 global $ADODB_COUNTRECS;
609 return mysql_query($sql,$this->_connectionID);
611 return @mysql_unbuffered_query($sql,$this->_connectionID); // requires PHP >= 4.0.6
615 /* Returns: the last error message from previous database operation */
619 if ($this->_logsql) return $this->_errorMsg;
620 if (empty($this->_connectionID)) $this->_errorMsg = @mysql_error();
621 else $this->_errorMsg = @mysql_error($this->_connectionID);
622 return $this->_errorMsg;
625 /* Returns: the last error number from previous database operation */
628 if ($this->_logsql) return $this->_errorCode;
629 if (empty($this->_connectionID)) return @mysql_errno();
630 else return @mysql_errno($this->_connectionID);
633 // returns true or false
636 @mysql_close($this->_connectionID);
639 $this->_connectionID = false;
644 * Maximum size of C field
652 * Maximum size of X field
659 // "Innox - Juan Carlos Gonzalez" <jgonzalez#innox.com.mx>
660 function MetaForeignKeys( $table, $owner = FALSE, $upper = FALSE, $associative = FALSE )
662 global $ADODB_FETCH_MODE;
663 if ($ADODB_FETCH_MODE == ADODB_FETCH_ASSOC || $this->fetchMode == ADODB_FETCH_ASSOC) $associative = true;
665 if ( !empty($owner) ) {
666 $table = "$owner.$table";
668 $a_create_table = $this->getRow(sprintf('SHOW CREATE TABLE %s', $table));
670 $create_sql = isset($a_create_table["Create Table"]) ? $a_create_table["Create Table"] : $a_create_table["Create View"];
672 $create_sql = $a_create_table[1];
677 if (!preg_match_all("/FOREIGN KEY \(`(.*?)`\) REFERENCES `(.*?)` \(`(.*?)`\)/", $create_sql, $matches)) return false;
678 $foreign_keys = array();
679 $num_keys = count($matches[0]);
680 for ( $i = 0; $i < $num_keys; $i ++ ) {
681 $my_field = explode('`, `', $matches[1][$i]);
682 $ref_table = $matches[2][$i];
683 $ref_field = explode('`, `', $matches[3][$i]);
686 $ref_table = strtoupper($ref_table);
689 // see https://sourceforge.net/p/adodb/bugs/100/
690 if (!isset($foreign_keys[$ref_table])) {
691 $foreign_keys[$ref_table] = array();
693 $num_fields = count($my_field);
694 for ( $j = 0; $j < $num_fields; $j ++ ) {
695 if ( $associative ) {
696 $foreign_keys[$ref_table][$ref_field[$j]] = $my_field[$j];
698 $foreign_keys[$ref_table][] = "{$my_field[$j]}={$ref_field[$j]}";
703 return $foreign_keys;
709 /*--------------------------------------------------------------------------------------
710 Class Name: Recordset
711 --------------------------------------------------------------------------------------*/
714 class ADORecordSet_mysql extends ADORecordSet{
716 var $databaseType = "mysql";
719 function __construct($queryID,$mode=false)
721 if ($mode === false) {
722 global $ADODB_FETCH_MODE;
723 $mode = $ADODB_FETCH_MODE;
727 case ADODB_FETCH_NUM: $this->fetchMode = MYSQL_NUM; break;
728 case ADODB_FETCH_ASSOC:$this->fetchMode = MYSQL_ASSOC; break;
729 case ADODB_FETCH_DEFAULT:
730 case ADODB_FETCH_BOTH:
732 $this->fetchMode = MYSQL_BOTH; break;
734 $this->adodbFetchMode = $mode;
735 parent::__construct($queryID);
740 //GLOBAL $ADODB_COUNTRECS;
741 // $this->_numOfRows = ($ADODB_COUNTRECS) ? @mysql_num_rows($this->_queryID):-1;
742 $this->_numOfRows = @mysql_num_rows($this->_queryID);
743 $this->_numOfFields = @mysql_num_fields($this->_queryID);
746 function FetchField($fieldOffset = -1)
748 if ($fieldOffset != -1) {
749 $o = @mysql_fetch_field($this->_queryID, $fieldOffset);
750 $f = @mysql_field_flags($this->_queryID,$fieldOffset);
751 if ($o) $o->max_length = @mysql_field_len($this->_queryID,$fieldOffset); // suggested by: Jim Nicholson (jnich#att.com)
752 //$o->max_length = -1; // mysql returns the max length less spaces -- so it is unrealiable
753 if ($o) $o->binary = (strpos($f,'binary')!== false);
755 else { /* The $fieldOffset argument is not provided thus its -1 */
756 $o = @mysql_fetch_field($this->_queryID);
757 //if ($o) $o->max_length = @mysql_field_len($this->_queryID); // suggested by: Jim Nicholson (jnich#att.com)
758 $o->max_length = -1; // mysql returns the max length less spaces -- so it is unrealiable
764 function GetRowAssoc($upper = ADODB_ASSOC_CASE)
766 if ($this->fetchMode == MYSQL_ASSOC && $upper == ADODB_ASSOC_CASE_LOWER) {
767 $row = $this->fields;
770 $row = ADORecordSet::GetRowAssoc($upper);
775 /* Use associative array to get fields array */
776 function Fields($colname)
778 // added @ by "Michael William Miller" <mille562@pilot.msu.edu>
779 if ($this->fetchMode != MYSQL_NUM) return @$this->fields[$colname];
782 $this->bind = array();
783 for ($i=0; $i < $this->_numOfFields; $i++) {
784 $o = $this->FetchField($i);
785 $this->bind[strtoupper($o->name)] = $i;
788 return $this->fields[$this->bind[strtoupper($colname)]];
793 if ($this->_numOfRows == 0) return false;
794 return @mysql_data_seek($this->_queryID,$row);
799 //return adodb_movenext($this);
800 //if (defined('ADODB_EXTENSION')) return adodb_movenext($this);
801 if (@$this->fields = mysql_fetch_array($this->_queryID,$this->fetchMode)) {
802 $this->_updatefields();
803 $this->_currentRow += 1;
807 $this->_currentRow += 1;
815 $this->fields = @mysql_fetch_array($this->_queryID,$this->fetchMode);
816 $this->_updatefields();
817 return is_array($this->fields);
821 @mysql_free_result($this->_queryID);
822 $this->_queryID = false;
825 function MetaType($t,$len=-1,$fieldobj=false)
829 $t = $fieldobj->type;
830 $len = $fieldobj->max_length;
833 $len = -1; // mysql max_length is not accurate
834 switch (strtoupper($t)) {
842 if ($len <= $this->blobSize) return 'C';
849 // php_mysql extension always returns 'blob' even if 'text'
850 // so we have to check whether binary...
856 return !empty($fieldobj->binary) ? 'B' : 'X';
859 case 'DATE': return 'D';
863 case 'TIMESTAMP': return 'T';
872 if (!empty($fieldobj->primary_key)) return 'R';
881 class ADORecordSet_ext_mysql extends ADORecordSet_mysql {
882 function __construct($queryID,$mode=false)
884 parent::__construct($queryID,$mode);
889 return @adodb_movenext($this);