3 V4.22 15 Apr 2004 (c) 2000-2004 John Lim (jlim@natsoft.com.my). All rights reserved.
4 Released under both BSD license and Lesser GPL library license.
5 Whenever there is any discrepancy between the two licenses,
6 the BSD license will take precedence.
9 MySQL code that does not support transactions. Use mysqlt if you need transactions.
10 Requires mysql client. Works on Windows and Unix.
12 21 October 2003: MySQLi extension implementation by Arjen de Rijke (a.de.rijke@xs4all.nl)
15 if (! defined("_ADODB_MYSQL_LAYER")) {
16 define("_ADODB_MYSQL_LAYER", 1 );
18 class ADODB_mysqli extends ADOConnection {
19 var $databaseType = 'mysqli';
20 var $dataProvider = 'native';
21 var $hasInsertID = true;
22 var $hasAffectedRows = true;
23 var $metaTablesSQL = "SHOW TABLES";
24 var $metaColumnsSQL = "SHOW COLUMNS FROM %s";
25 var $fmtTimeStamp = "'Y-m-d H:i:s'";
27 var $hasMoveFirst = true;
29 var $upperCase = 'upper';
30 var $isoDates = true; // accepts dates in ISO format
31 var $sysDate = 'CURDATE()';
32 var $sysTimeStamp = 'NOW()';
33 var $hasTransactions = false;
34 var $forceNewConnect = false;
35 var $poorAffectedRows = true;
37 var $executeOnly = true;
38 var $substr = "substring";
39 var $nameQuote = '`'; /// string to use to quote identifiers and names
40 //var $_bindInputArray = true;
42 function ADODB_mysqli()
44 if(!extension_loaded("mysqli"))
46 trigger_error("You must have the MySQLi extension.", E_USER_ERROR);
50 function IfNull( $field, $ifNull )
52 return " IFNULL($field, $ifNull) "; // if MySQL
57 $arr['description'] = $this->GetOne("select version()");
58 $arr['version'] = ADOConnection::_findvers($arr['description']);
64 if ($this->transOff) return true;
66 $this->Execute('SET AUTOCOMMIT=0');
67 $this->Execute('BEGIN');
71 function CommitTrans($ok=true)
73 if ($this->transOff) return true;
74 if (!$ok) return $this->RollbackTrans();
76 if ($this->transCnt) $this->transCnt -= 1;
77 $this->Execute('COMMIT');
78 $this->Execute('SET AUTOCOMMIT=1');
82 function RollbackTrans()
84 if ($this->transOff) return true;
85 if ($this->transCnt) $this->transCnt -= 1;
86 $this->Execute('ROLLBACK');
87 $this->Execute('SET AUTOCOMMIT=1');
91 // if magic quotes disabled, use mysql_real_escape_string()
93 // Quotes a string to be sent to the database. The $magic_quotes_enabled
94 // parameter may look funny, but the idea is if you are quoting a
95 // string extracted from a POST/GET variable, then
96 // pass get_magic_quotes_gpc() as the second parameter. This will
97 // ensure that the variable is not quoted twice, once by qstr and once
98 // by the magic_quotes_gpc.
100 //Eg. $s = $db->qstr(HTTP_GET_VARS['name'],get_magic_quotes_gpc());
101 function qstr($s, $magic_quotes = false)
103 if (!$magic_quotes) {
104 if (ADODB_PHPVER >= 0x5000) {
105 // $this->_connectionID = $this->mysqli_resolve_link($this->_connectionID);
106 return "'" . mysqli_real_escape_string($this->_connectionID, $s) . "'";
110 trigger_error("phpver < 5 not implemented", E_USER_ERROR);
113 if ($this->replaceQuote[0] == '\\')
115 $s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\0"),$s);
117 return "'".str_replace("'",$this->replaceQuote,$s)."'";
119 // undo magic quotes for "
120 $s = str_replace('\\"','"',$s);
126 // $this->_connectionID = $this->mysqli_resolve_link($this->_connectionID);
127 $result = @mysqli_insert_id($this->_connectionID);
129 if ($this->debug) ADOConnection::outp("mysqli_insert_id() failed : " . $this->ErrorMsg());
134 // Only works for INSERT, UPDATE and DELETE query's
135 function _affectedrows()
137 // $this->_connectionID = $this->mysqli_resolve_link($this->_connectionID);
138 $result = @mysqli_affected_rows($this->_connectionID);
140 if ($this->debug) ADOConnection::outp("mysqli_affected_rows() failed : " . $this->ErrorMsg());
145 // See http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
146 // Reference on Last_Insert_ID on the recommended way to simulate sequences
147 var $_genIDSQL = "update %s set id=LAST_INSERT_ID(id+1);";
148 var $_genSeqSQL = "create table %s (id int not null)";
149 var $_genSeq2SQL = "insert into %s values (%s)";
150 var $_dropSeqSQL = "drop table %s";
152 function CreateSequence($seqname='adodbseq',$startID=1)
154 if (empty($this->_genSeqSQL)) return false;
155 $u = strtoupper($seqname);
157 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
158 if (!$ok) return false;
159 return $this->Execute(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
162 function GenID($seqname='adodbseq',$startID=1)
164 // post-nuke sets hasGenID to false
165 if (!$this->hasGenID) return false;
167 $getnext = sprintf($this->_genIDSQL,$seqname);
168 $holdtransOK = $this->_transOK; // save the current status
169 $rs = @$this->Execute($getnext);
171 if ($holdtransOK) $this->_transOK = true; //if the status was ok before reset
172 $u = strtoupper($seqname);
173 $this->Execute(sprintf($this->_genSeqSQL,$seqname));
174 $this->Execute(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
175 $rs = $this->Execute($getnext);
177 $this->genID = mysqli_insert_id($this->_connectionID);
179 if ($rs) $rs->Close();
184 function &MetaDatabases()
186 $query = "SHOW DATABASES";
187 $ret =& $this->Execute($query);
192 function &MetaIndexes ($table, $primary = FALSE)
194 // save old fetch mode
195 global $ADODB_FETCH_MODE;
197 $save = $ADODB_FETCH_MODE;
198 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
199 if ($this->fetchMode !== FALSE) {
200 $savem = $this->SetFetchMode(FALSE);
204 $rs = $this->Execute(sprintf('SHOW INDEXES FROM %s',$table));
208 $this->SetFetchMode($savem);
210 $ADODB_FETCH_MODE = $save;
212 if (!is_object($rs)) {
218 // parse index data into array
219 while ($row = $rs->FetchRow()) {
220 if ($primary == FALSE AND $row[2] == 'PRIMARY') {
224 if (!isset($indexes[$row[2]])) {
225 $indexes[$row[2]] = array(
226 'unique' => ($row[1] == 0),
231 $indexes[$row[2]]['columns'][$row[3] - 1] = $row[4];
234 // sort columns by order in the index
235 foreach ( array_keys ($indexes) as $index )
237 ksort ($indexes[$index]['columns']);
244 // Format date column in sql string given an input format that understands Y M D
245 function SQLDate($fmt, $col=false)
247 if (!$col) $col = $this->sysTimeStamp;
248 $s = 'DATE_FORMAT('.$col.",'";
251 for ($i=0; $i < $len; $i++) {
260 $s .= "'),Quarter($col)";
262 if ($len > $i+1) $s .= ",DATE_FORMAT($col,'";
303 $ch = substr($fmt,$i,1);
310 if ($concat) $s = "CONCAT($s)";
314 // returns concatenated string
315 // much easier to run "mysqld --ansi" or "mysqld --sql-mode=PIPES_AS_CONCAT" and use || operator
319 $arr = func_get_args();
321 // suggestion by andrew005@mnogo.ru
322 $s = implode(',',$arr);
323 if (strlen($s) > 0) return "CONCAT($s)";
327 // dayFraction is a day in floating point
328 function OffsetDate($dayFraction,$date=false)
331 $date = $this->sysDate;
332 return "from_unixtime(unix_timestamp($date)+($dayFraction)*24*3600)";
335 // returns true or false
336 // To add: parameter int $port,
337 // parameter string $socket
338 function _connect($argHostname = NULL,
341 $argDatabasename = NULL)
343 // @ means: error surpression on
344 $this->_connectionID = @mysqli_init();
346 if (is_null($this->_connectionID))
348 // mysqli_init only fails if insufficient memory
350 ADOConnection::outp("mysqli_init() failed : " . $this->ErrorMsg());
353 // Set connection options
354 // Not implemented now
355 // mysqli_options($this->_connection,,);
356 if (mysqli_real_connect($this->_connectionID,
362 if ($argDatabasename)
364 return $this->SelectDB($argDatabasename);
372 ADOConnection::outp("Could't connect : " . $this->ErrorMsg());
377 // returns true or false
378 // How to force a persistent connection
379 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
381 // not implemented in mysqli (yet)?
382 $this->_connectionID = mysqli_connect($argHostname,
386 if ($this->_connectionID === false) return false;
387 // if ($this->autoRollback) $this->RollbackTrans();
388 if ($argDatabasename) return $this->SelectDB($argDatabasename);
392 // When is this used? Close old connection first?
393 // In _connect(), check $this->forceNewConnect?
394 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
396 $this->forceNewConnect = true;
397 $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
400 function &MetaColumns($table)
402 if ($this->metaColumnsSQL) {
403 global $ADODB_FETCH_MODE;
404 $save = $ADODB_FETCH_MODE;
406 switch($ADODB_FETCH_MODE)
408 case ADODB_FETCH_NUM:
409 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
410 $rs = $this->Execute(sprintf($this->metaColumnsSQL,
413 $ADODB_FETCH_MODE = $save;
414 if ($rs === false) break;
417 $fld = new ADOFieldObject();
418 $fld->name = $rs->fields[0];
419 $fld->type = $rs->fields[1];
420 // split type into type(length):
421 if (preg_match("/^(.+)\((\d+)\)$/", $fld->type, $query_array))
423 $fld->type = $query_array[1];
424 $fld->max_length = $query_array[2];
428 $fld->max_length = -1;
430 $fld->not_null = ($rs->fields[2] != 'YES');
431 $fld->primary_key = ($rs->fields[3] == 'PRI');
432 $fld->auto_increment = (strpos($rs->fields[5], 'auto_increment') !== false);
433 $fld->binary = (strpos($fld->type,'blob') !== false);
437 $d = $rs->fields['Default'];
438 if ($d != "" && $d != "NULL")
440 $fld->has_default = true;
441 $fld->default_value = $d;
445 $fld->has_default = false;
448 $retarr[strtoupper($fld->name)] = $fld;
452 case ADODB_FETCH_ASSOC:
453 case ADODB_FETCH_DEFAULT:
454 case ADODB_FETCH_BOTH:
455 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
456 $rs = $this->Execute(sprintf($this->metaColumnsSQL,
458 $ADODB_FETCH_MODE = $save;
459 if ($rs === false) break;
462 $fld = new ADOFieldObject();
463 $fld->name = $rs->fields['Field'];
464 $fld->type = $rs->fields['Type'];
466 // split type into type(length):
467 if (preg_match("/^(.+)\((\d+)\)$/", $fld->type, $query_array))
469 $fld->type = $query_array[1];
470 $fld->max_length = $query_array[2];
474 $fld->max_length = -1;
476 $fld->not_null = ($rs->fields['Null'] != 'YES');
477 $fld->primary_key = ($rs->fields['Key'] == 'PRI');
478 $fld->auto_increment = (strpos($rs->fields['Extra'], 'auto_increment') !== false);
479 $fld->binary = (strpos($fld->type,'blob') !== false);
482 $d = $rs->fields['Default'];
483 if ($d != "" && $d != "NULL")
485 $fld->has_default = true;
486 $fld->default_value = $d;
490 $fld->has_default = false;
493 $retarr[strtoupper($fld->name)] = $fld;
500 if ($rs === false) return false;
507 // returns true or false
508 function SelectDB($dbName)
510 // $this->_connectionID = $this->mysqli_resolve_link($this->_connectionID);
511 $this->databaseName = $dbName;
512 if ($this->_connectionID) {
513 $result = @mysqli_select_db($this->_connectionID, $dbName);
515 ADOConnection::outp("Select of database " . $dbName . " failed. " . $this->ErrorMsg());
522 // parameters use PostgreSQL convention, not MySQL
523 function &SelectLimit($sql,
530 $offsetStr = ($offset >= 0) ? "$offset," : '';
533 $rs =& $this->CacheExecute($secs, $sql . " LIMIT $offsetStr$nrows" , $inputarr , $arg3);
535 $rs =& $this->Execute($sql . " LIMIT $offsetStr$nrows" , $inputarr , $arg3);
541 function Prepare($sql)
545 $stmt = mysqli_prepare($this->_connectionID,$sql);
546 if (!$stmt) return false;
547 return array($sql,$stmt);
551 // returns queryID or false
552 function _query($sql, $inputarr)
554 global $ADODB_COUNTRECS;
556 if (is_array($sql)) {
558 foreach($inputarr as $k => $v) {
559 if (is_string($v)) $a[] = MYSQLI_BIND_STRING;
560 else if (is_integer($v)) $a[] = MYSQLI_BIND_INT;
561 else $a[] = MYSQLI_BIND_DOUBLE;
563 $fnarr =& array_merge( array($stmt,$a) , $inputarr);
564 $ret = call_user_func_array('mysqli_bind_param',$fnarr);
566 $ret = mysqli_execute($stmt);
569 if (!$mysql_res = mysqli_query($this->_connectionID, $sql, ($ADODB_COUNTRECS) ? MYSQLI_STORE_RESULT : MYSQLI_USE_RESULT)) {
570 if ($this->debug) ADOConnection::outp("Query: " . $sql . " failed. " . $this->ErrorMsg());
577 /* Returns: the last error message from previous database operation */
580 if (empty($this->_connectionID))
581 $this->_errorMsg = @mysqli_error();
583 $this->_errorMsg = @mysqli_error($this->_connectionID);
584 return $this->_errorMsg;
587 /* Returns: the last error number from previous database operation */
590 if (empty($this->_connectionID))
591 return @mysqli_errno();
593 return @mysqli_errno($this->_connectionID);
596 // returns true or false
599 @mysqli_close($this->_connectionID);
600 $this->_connectionID = false;
604 * Maximum size of C field
612 * Maximum size of X field
622 /*--------------------------------------------------------------------------------------
623 Class Name: Recordset
624 --------------------------------------------------------------------------------------*/
626 class ADORecordSet_mysqli extends ADORecordSet{
628 var $databaseType = "mysqli";
631 function ADORecordSet_mysqli($queryID, $mode = false)
635 global $ADODB_FETCH_MODE;
636 $mode = $ADODB_FETCH_MODE;
640 case ADODB_FETCH_NUM:
641 $this->fetchMode = MYSQLI_NUM;
643 case ADODB_FETCH_ASSOC:
644 $this->fetchMode = MYSQLI_ASSOC;
646 case ADODB_FETCH_DEFAULT:
647 case ADODB_FETCH_BOTH:
649 $this->fetchMode = MYSQLI_ASSOC;
652 $this->ADORecordSet($queryID);
657 // mysqli_num_rows only return correct number, depens
658 // on the use of mysql_store_result and mysql_use_result
659 if (!$this->Connection->executeOnly) {
660 $this->_numOfRows = @mysqli_num_rows($this->_queryID);
661 $this->_numOfFields = @mysqli_num_fields($this->_queryID);
664 $this->_numOfRows = 0;
665 $this->_numOfFields = 0;
669 function &FetchField($fieldOffset = -1)
671 $fieldnr = $fieldOffset;
672 if ($fieldOffset != -1) {
673 $fieldOffset = mysqi_field_seek($this->_queryID, $fieldnr);
675 $o = mysqli_fetch_field($this->_queryID);
679 function &GetRowAssoc($upper = true)
681 if ($this->fetchMode == MYSQLI_ASSOC && !$upper)
682 return $this->fields;
683 $row =& ADORecordSet::GetRowAssoc($upper);
687 /* Use associative array to get fields array */
688 function Fields($colname)
690 if ($this->fetchMode != MYSQLI_NUM)
691 return @$this->fields[$colname];
694 $this->bind = array();
695 for ($i = 0; $i < $this->_numOfFields; $i++) {
696 $o = $this->FetchField($i);
697 $this->bind[strtoupper($o->name)] = $i;
700 return $this->fields[$this->bind[strtoupper($colname)]];
705 if ($this->_numOfRows == 0)
711 mysqli_data_seek($this->_queryID, $row);
716 // 10% speedup to move MoveNext to child class
717 // This is the only implementation that works now (23-10-2003).
718 // Other functions return no or the wrong results.
723 $this->_currentRow++;
724 switch($this->fetchMode)
727 $this->fields = mysqli_fetch_array($this->_queryID);
731 $this->fields = mysqli_fetch_assoc($this->_queryID);
735 if (is_array($this->fields))
743 // mysqli_fetch_array($this->_queryID, MYSQLI_NUM) does not
744 // work (22-10-2003). But mysqli_fetch_array($this->_queryID) gives
745 // int resulttype should default to MYSQLI_BOTH,but give MYSQLI_NUM.
747 // $this->fields = mysqli_fetch_fields($this->_queryID);
748 // $this->fields = mysqli_fetch_array($this->_queryID); //, $this->fetchMode);
750 $this->fields = mysqli_fetch_assoc($this->_queryID); // $this->fetchMode);
751 return is_array($this->fields);
756 mysqli_free_result($this->_queryID);
757 $this->_queryID = false;
760 function MetaType($t, $len = -1, $fieldobj = false)
765 $t = $fieldobj->type;
766 $len = $fieldobj->max_length;
769 $len = -1; // mysql max_length is not accurate
770 switch (strtoupper($t)) {
778 if ($len <= $this->blobSize) return 'C';
785 // php_mysql extension always returns 'blob' even if 'text'
786 // so we have to check whether binary...
791 return !empty($fieldobj->binary) ? 'B' : 'X';
798 case 'TIMESTAMP': return 'T';
807 if (!empty($fieldobj->primary_key)) return 'R';
809 // Added floating-point types
810 // Maybe not necessery.
813 // case 'DOUBLE PRECISION':