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
20 var $databaseType = 'mysqli';
21 var $dataProvider = 'native';
22 var $hasInsertID = true;
23 var $hasAffectedRows = true;
24 var $metaTablesSQL = "SHOW TABLES";
25 var $metaColumnsSQL = "SHOW COLUMNS FROM %s";
26 var $fmtTimeStamp = "'Y-m-d H:i:s'";
28 var $hasMoveFirst = true;
30 var $upperCase = 'upper';
31 var $isoDates = true; // accepts dates in ISO format
32 var $sysDate = 'CURDATE()';
33 var $sysTimeStamp = 'NOW()';
34 var $hasTransactions = false;
35 var $forceNewConnect = false;
36 var $poorAffectedRows = true;
38 var $executeOnly = true;
39 var $substr = "substring";
40 var $nameQuote = '`'; /// string to use to quote identifiers and names
41 //var $_bindInputArray = true;
43 function ADODB_mysqli()
45 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) . "'";
108 trigger_error("phpver < 5 not implemented", E_USER_ERROR);
111 if ($this->replaceQuote[0] == '\\') {
112 $s = adodb_str_replace(array('\\', "\0"), array('\\\\', "\\\0"), $s);
114 return "'" . str_replace("'", $this->replaceQuote, $s) . "'";
116 // undo magic quotes for "
117 $s = str_replace('\\"', '"', $s);
123 // $this->_connectionID = $this->mysqli_resolve_link($this->_connectionID);
124 $result = @mysqli_insert_id($this->_connectionID);
126 if ($this->debug) ADOConnection::outp("mysqli_insert_id() failed : " . $this->ErrorMsg());
131 // Only works for INSERT, UPDATE and DELETE query's
132 function _affectedrows()
134 // $this->_connectionID = $this->mysqli_resolve_link($this->_connectionID);
135 $result = @mysqli_affected_rows($this->_connectionID);
137 if ($this->debug) ADOConnection::outp("mysqli_affected_rows() failed : " . $this->ErrorMsg());
142 // See http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
143 // Reference on Last_Insert_ID on the recommended way to simulate sequences
144 var $_genIDSQL = "update %s set id=LAST_INSERT_ID(id+1);";
145 var $_genSeqSQL = "create table %s (id int not null)";
146 var $_genSeq2SQL = "insert into %s values (%s)";
147 var $_dropSeqSQL = "drop table %s";
149 function CreateSequence($seqname = 'adodbseq', $startID = 1)
151 if (empty($this->_genSeqSQL)) return false;
152 $u = strtoupper($seqname);
154 $ok = $this->Execute(sprintf($this->_genSeqSQL, $seqname));
155 if (!$ok) return false;
156 return $this->Execute(sprintf($this->_genSeq2SQL, $seqname, $startID - 1));
159 function GenID($seqname = 'adodbseq', $startID = 1)
161 // post-nuke sets hasGenID to false
162 if (!$this->hasGenID) return false;
164 $getnext = sprintf($this->_genIDSQL, $seqname);
165 $holdtransOK = $this->_transOK; // save the current status
166 $rs = @$this->Execute($getnext);
168 if ($holdtransOK) $this->_transOK = true; //if the status was ok before reset
169 $u = strtoupper($seqname);
170 $this->Execute(sprintf($this->_genSeqSQL, $seqname));
171 $this->Execute(sprintf($this->_genSeq2SQL, $seqname, $startID - 1));
172 $rs = $this->Execute($getnext);
174 $this->genID = mysqli_insert_id($this->_connectionID);
176 if ($rs) $rs->Close();
181 function &MetaDatabases()
183 $query = "SHOW DATABASES";
184 $ret =& $this->Execute($query);
189 function &MetaIndexes($table, $primary = FALSE)
191 // save old fetch mode
192 global $ADODB_FETCH_MODE;
194 $save = $ADODB_FETCH_MODE;
195 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
196 if ($this->fetchMode !== FALSE) {
197 $savem = $this->SetFetchMode(FALSE);
201 $rs = $this->Execute(sprintf('SHOW INDEXES FROM %s', $table));
205 $this->SetFetchMode($savem);
207 $ADODB_FETCH_MODE = $save;
209 if (!is_object($rs)) {
215 // parse index data into array
216 while ($row = $rs->FetchRow()) {
217 if ($primary == FALSE AND $row[2] == 'PRIMARY') {
221 if (!isset($indexes[$row[2]])) {
222 $indexes[$row[2]] = array(
223 'unique' => ($row[1] == 0),
228 $indexes[$row[2]]['columns'][$row[3] - 1] = $row[4];
231 // sort columns by order in the index
232 foreach (array_keys($indexes) as $index) {
233 ksort($indexes[$index]['columns']);
240 // Format date column in sql string given an input format that understands Y M D
241 function SQLDate($fmt, $col = false)
243 if (!$col) $col = $this->sysTimeStamp;
244 $s = 'DATE_FORMAT(' . $col . ",'";
247 for ($i = 0; $i < $len; $i++) {
256 $s .= "'),Quarter($col)";
258 if ($len > $i + 1) $s .= ",DATE_FORMAT($col,'";
299 $ch = substr($fmt, $i, 1);
306 if ($concat) $s = "CONCAT($s)";
310 // returns concatenated string
311 // much easier to run "mysqld --ansi" or "mysqld --sql-mode=PIPES_AS_CONCAT" and use || operator
315 $arr = func_get_args();
317 // suggestion by andrew005@mnogo.ru
318 $s = implode(',', $arr);
319 if (strlen($s) > 0) return "CONCAT($s)";
323 // dayFraction is a day in floating point
324 function OffsetDate($dayFraction, $date = false)
327 $date = $this->sysDate;
328 return "from_unixtime(unix_timestamp($date)+($dayFraction)*24*3600)";
331 // returns true or false
332 // To add: parameter int $port,
333 // parameter string $socket
334 function _connect($argHostname = NULL,
337 $argDatabasename = NULL)
339 // @ means: error surpression on
340 $this->_connectionID = @mysqli_init();
342 if (is_null($this->_connectionID)) {
343 // mysqli_init only fails if insufficient memory
345 ADOConnection::outp("mysqli_init() failed : " . $this->ErrorMsg());
348 // Set connection options
349 // Not implemented now
350 // mysqli_options($this->_connection,,);
351 if (mysqli_real_connect($this->_connectionID,
357 if ($argDatabasename) {
358 return $this->SelectDB($argDatabasename);
364 ADOConnection::outp("Could't connect : " . $this->ErrorMsg());
369 // returns true or false
370 // How to force a persistent connection
371 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
373 // not implemented in mysqli (yet)?
374 $this->_connectionID = mysqli_connect($argHostname,
378 if ($this->_connectionID === false) return false;
379 // if ($this->autoRollback) $this->RollbackTrans();
380 if ($argDatabasename) return $this->SelectDB($argDatabasename);
384 // When is this used? Close old connection first?
385 // In _connect(), check $this->forceNewConnect?
386 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
388 $this->forceNewConnect = true;
389 $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
392 function &MetaColumns($table)
394 if ($this->metaColumnsSQL) {
395 global $ADODB_FETCH_MODE;
396 $save = $ADODB_FETCH_MODE;
398 switch ($ADODB_FETCH_MODE) {
399 case ADODB_FETCH_NUM:
400 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
401 $rs = $this->Execute(sprintf($this->metaColumnsSQL,
404 $ADODB_FETCH_MODE = $save;
405 if ($rs === false) break;
408 $fld = new ADOFieldObject();
409 $fld->name = $rs->fields[0];
410 $fld->type = $rs->fields[1];
411 // split type into type(length):
412 if (preg_match("/^(.+)\((\d+)\)$/", $fld->type, $query_array)) {
413 $fld->type = $query_array[1];
414 $fld->max_length = $query_array[2];
416 $fld->max_length = -1;
418 $fld->not_null = ($rs->fields[2] != 'YES');
419 $fld->primary_key = ($rs->fields[3] == 'PRI');
420 $fld->auto_increment = (strpos($rs->fields[5], 'auto_increment') !== false);
421 $fld->binary = (strpos($fld->type, 'blob') !== false);
424 $d = $rs->fields['Default'];
425 if ($d != "" && $d != "NULL") {
426 $fld->has_default = true;
427 $fld->default_value = $d;
429 $fld->has_default = false;
432 $retarr[strtoupper($fld->name)] = $fld;
436 case ADODB_FETCH_ASSOC:
437 case ADODB_FETCH_DEFAULT:
438 case ADODB_FETCH_BOTH:
439 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
440 $rs = $this->Execute(sprintf($this->metaColumnsSQL,
442 $ADODB_FETCH_MODE = $save;
443 if ($rs === false) break;
446 $fld = new ADOFieldObject();
447 $fld->name = $rs->fields['Field'];
448 $fld->type = $rs->fields['Type'];
450 // split type into type(length):
451 if (preg_match("/^(.+)\((\d+)\)$/", $fld->type, $query_array)) {
452 $fld->type = $query_array[1];
453 $fld->max_length = $query_array[2];
455 $fld->max_length = -1;
457 $fld->not_null = ($rs->fields['Null'] != 'YES');
458 $fld->primary_key = ($rs->fields['Key'] == 'PRI');
459 $fld->auto_increment = (strpos($rs->fields['Extra'], 'auto_increment') !== false);
460 $fld->binary = (strpos($fld->type, 'blob') !== false);
462 $d = $rs->fields['Default'];
463 if ($d != "" && $d != "NULL") {
464 $fld->has_default = true;
465 $fld->default_value = $d;
467 $fld->has_default = false;
470 $retarr[strtoupper($fld->name)] = $fld;
477 if ($rs === false) return false;
484 // returns true or false
485 function SelectDB($dbName)
487 // $this->_connectionID = $this->mysqli_resolve_link($this->_connectionID);
488 $this->databaseName = $dbName;
489 if ($this->_connectionID) {
490 $result = @mysqli_select_db($this->_connectionID, $dbName);
492 ADOConnection::outp("Select of database " . $dbName . " failed. " . $this->ErrorMsg());
499 // parameters use PostgreSQL convention, not MySQL
500 function &SelectLimit($sql,
507 $offsetStr = ($offset >= 0) ? "$offset," : '';
510 $rs =& $this->CacheExecute($secs, $sql . " LIMIT $offsetStr$nrows", $inputarr, $arg3);
512 $rs =& $this->Execute($sql . " LIMIT $offsetStr$nrows", $inputarr, $arg3);
518 function Prepare($sql)
522 $stmt = mysqli_prepare($this->_connectionID, $sql);
523 if (!$stmt) return false;
524 return array($sql, $stmt);
528 // returns queryID or false
529 function _query($sql, $inputarr)
531 global $ADODB_COUNTRECS;
533 if (is_array($sql)) {
535 foreach ($inputarr as $k => $v) {
536 if (is_string($v)) $a[] = MYSQLI_BIND_STRING;
537 else if (is_integer($v)) $a[] = MYSQLI_BIND_INT;
538 else $a[] = MYSQLI_BIND_DOUBLE;
540 $fnarr =& array_merge(array($stmt, $a), $inputarr);
541 $ret = call_user_func_array('mysqli_bind_param', $fnarr);
543 $ret = mysqli_execute($stmt);
546 if (!$mysql_res = mysqli_query($this->_connectionID, $sql, ($ADODB_COUNTRECS) ? MYSQLI_STORE_RESULT : MYSQLI_USE_RESULT)) {
547 if ($this->debug) ADOConnection::outp("Query: " . $sql . " failed. " . $this->ErrorMsg());
554 /* Returns: the last error message from previous database operation */
557 if (empty($this->_connectionID))
558 $this->_errorMsg = @mysqli_error();
560 $this->_errorMsg = @mysqli_error($this->_connectionID);
561 return $this->_errorMsg;
564 /* Returns: the last error number from previous database operation */
567 if (empty($this->_connectionID))
568 return @mysqli_errno();
570 return @mysqli_errno($this->_connectionID);
573 // returns true or false
576 @mysqli_close($this->_connectionID);
577 $this->_connectionID = false;
581 * Maximum size of C field
589 * Maximum size of X field
599 /*--------------------------------------------------------------------------------------
600 Class Name: Recordset
601 --------------------------------------------------------------------------------------*/
603 class ADORecordSet_mysqli extends ADORecordSet
606 var $databaseType = "mysqli";
609 function ADORecordSet_mysqli($queryID, $mode = false)
611 if ($mode === false) {
612 global $ADODB_FETCH_MODE;
613 $mode = $ADODB_FETCH_MODE;
616 case ADODB_FETCH_NUM:
617 $this->fetchMode = MYSQLI_NUM;
619 case ADODB_FETCH_ASSOC:
620 $this->fetchMode = MYSQLI_ASSOC;
622 case ADODB_FETCH_DEFAULT:
623 case ADODB_FETCH_BOTH:
625 $this->fetchMode = MYSQLI_ASSOC;
628 $this->ADORecordSet($queryID);
633 // mysqli_num_rows only return correct number, depens
634 // on the use of mysql_store_result and mysql_use_result
635 if (!$this->Connection->executeOnly) {
636 $this->_numOfRows = @mysqli_num_rows($this->_queryID);
637 $this->_numOfFields = @mysqli_num_fields($this->_queryID);
639 $this->_numOfRows = 0;
640 $this->_numOfFields = 0;
644 function &FetchField($fieldOffset = -1)
646 $fieldnr = $fieldOffset;
647 if ($fieldOffset != -1) {
648 $fieldOffset = mysqi_field_seek($this->_queryID, $fieldnr);
650 $o = mysqli_fetch_field($this->_queryID);
654 function &GetRowAssoc($upper = true)
656 if ($this->fetchMode == MYSQLI_ASSOC && !$upper)
657 return $this->fields;
658 $row =& ADORecordSet::GetRowAssoc($upper);
662 /* Use associative array to get fields array */
663 function Fields($colname)
665 if ($this->fetchMode != MYSQLI_NUM)
666 return @$this->fields[$colname];
669 $this->bind = array();
670 for ($i = 0; $i < $this->_numOfFields; $i++) {
671 $o = $this->FetchField($i);
672 $this->bind[strtoupper($o->name)] = $i;
675 return $this->fields[$this->bind[strtoupper($colname)]];
680 if ($this->_numOfRows == 0)
686 mysqli_data_seek($this->_queryID, $row);
691 // 10% speedup to move MoveNext to child class
692 // This is the only implementation that works now (23-10-2003).
693 // Other functions return no or the wrong results.
698 $this->_currentRow++;
699 switch ($this->fetchMode) {
701 $this->fields = mysqli_fetch_array($this->_queryID);
705 $this->fields = mysqli_fetch_assoc($this->_queryID);
709 if (is_array($this->fields))
717 // mysqli_fetch_array($this->_queryID, MYSQLI_NUM) does not
718 // work (22-10-2003). But mysqli_fetch_array($this->_queryID) gives
719 // int resulttype should default to MYSQLI_BOTH,but give MYSQLI_NUM.
721 // $this->fields = mysqli_fetch_fields($this->_queryID);
722 // $this->fields = mysqli_fetch_array($this->_queryID); //, $this->fetchMode);
724 $this->fields = mysqli_fetch_assoc($this->_queryID); // $this->fetchMode);
725 return is_array($this->fields);
730 mysqli_free_result($this->_queryID);
731 $this->_queryID = false;
734 function MetaType($t, $len = -1, $fieldobj = false)
738 $t = $fieldobj->type;
739 $len = $fieldobj->max_length;
742 $len = -1; // mysql max_length is not accurate
743 switch (strtoupper($t)) {
751 if ($len <= $this->blobSize) return 'C';
758 // php_mysql extension always returns 'blob' even if 'text'
759 // so we have to check whether binary...
764 return !empty($fieldobj->binary) ? 'B' : 'X';
781 if (!empty($fieldobj->primary_key)) return 'R';
783 // Added floating-point types
784 // Maybe not necessery.
787 // case 'DOUBLE PRECISION':