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 28 Feb 2001: MetaColumns bug fix - suggested by Freek Dijkstra (phpeverywhere@macfreek.com)
15 if (!defined("_ADODB_MYSQL_LAYER")) {
16 define("_ADODB_MYSQL_LAYER", 1);
18 class ADODB_mysql extends ADOConnection
20 var $databaseType = 'mysql';
21 var $dataProvider = 'mysql';
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 $substr = "substring";
39 var $nameQuote = '`'; /// string to use to quote identifiers and names
41 function ADODB_mysql()
47 $arr['description'] = ADOConnection::GetOne("select version()");
48 $arr['version'] = ADOConnection::_findvers($arr['description']);
52 function IfNull($field, $ifNull)
54 return " IFNULL($field, $ifNull) "; // if MySQL
57 function &MetaTables($ttype = false, $showSchema = false, $mask = false)
60 $save = $this->metaTablesSQL;
61 $mask = $this->qstr($mask);
62 $this->metaTablesSQL .= " like $mask";
64 $ret =& ADOConnection::MetaTables($ttype, $showSchema);
67 $this->metaTablesSQL = $save;
72 function &MetaIndexes($table, $primary = FALSE, $owner = false)
74 // save old fetch mode
75 global $ADODB_FETCH_MODE;
77 $save = $ADODB_FETCH_MODE;
78 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
79 if ($this->fetchMode !== FALSE) {
80 $savem = $this->SetFetchMode(FALSE);
84 $rs = $this->Execute(sprintf('SHOW INDEX FROM %s', $table));
88 $this->SetFetchMode($savem);
90 $ADODB_FETCH_MODE = $save;
92 if (!is_object($rs)) {
98 // parse index data into array
99 while ($row = $rs->FetchRow()) {
100 if ($primary == FALSE AND $row[2] == 'PRIMARY') {
104 if (!isset($indexes[$row[2]])) {
105 $indexes[$row[2]] = array(
106 'unique' => ($row[1] == 0),
111 $indexes[$row[2]]['columns'][$row[3] - 1] = $row[4];
114 // sort columns by order in the index
115 foreach (array_keys($indexes) as $index) {
116 ksort($indexes[$index]['columns']);
122 // if magic quotes disabled, use mysql_real_escape_string()
123 function qstr($s, $magic_quotes = false)
125 if (!$magic_quotes) {
127 if (ADODB_PHPVER >= 0x4300) {
128 if (is_resource($this->_connectionID))
129 return "'" . mysql_real_escape_string($s, $this->_connectionID) . "'";
131 if ($this->replaceQuote[0] == '\\') {
132 $s = adodb_str_replace(array('\\', "\0"), array('\\\\', "\\\0"), $s);
134 return "'" . str_replace("'", $this->replaceQuote, $s) . "'";
137 // undo magic quotes for "
138 $s = str_replace('\\"', '"', $s);
144 return mysql_insert_id($this->_connectionID);
147 function GetOne($sql, $inputarr = false)
149 $rs =& $this->SelectLimit($sql, 1, -1, $inputarr);
152 if ($rs->EOF) return false;
153 return reset($rs->fields);
159 function _affectedrows()
161 return mysql_affected_rows($this->_connectionID);
164 // See http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
165 // Reference on Last_Insert_ID on the recommended way to simulate sequences
166 var $_genIDSQL = "update %s set id=LAST_INSERT_ID(id+1);";
167 var $_genSeqSQL = "create table %s (id int not null)";
168 var $_genSeq2SQL = "insert into %s values (%s)";
169 var $_dropSeqSQL = "drop table %s";
171 function CreateSequence($seqname = 'adodbseq', $startID = 1)
173 if (empty($this->_genSeqSQL)) return false;
174 $u = strtoupper($seqname);
176 $ok = $this->Execute(sprintf($this->_genSeqSQL, $seqname));
177 if (!$ok) return false;
178 return $this->Execute(sprintf($this->_genSeq2SQL, $seqname, $startID - 1));
182 function GenID($seqname = 'adodbseq', $startID = 1)
184 // post-nuke sets hasGenID to false
185 if (!$this->hasGenID) return false;
187 $savelog = $this->_logsql;
188 $this->_logsql = false;
189 $getnext = sprintf($this->_genIDSQL, $seqname);
190 $holdtransOK = $this->_transOK; // save the current status
191 $rs = @$this->Execute($getnext);
193 if ($holdtransOK) $this->_transOK = true; //if the status was ok before reset
194 $u = strtoupper($seqname);
195 $this->Execute(sprintf($this->_genSeqSQL, $seqname));
196 $this->Execute(sprintf($this->_genSeq2SQL, $seqname, $startID - 1));
197 $rs = $this->Execute($getnext);
199 $this->genID = mysql_insert_id($this->_connectionID);
201 if ($rs) $rs->Close();
203 $this->_logsql = $savelog;
207 function &MetaDatabases()
209 $qid = mysql_list_dbs($this->_connectionID);
212 $max = mysql_num_rows($qid);
214 $db = mysql_tablename($qid, $i);
215 if ($db != 'mysql') $arr[] = $db;
222 // Format date column in sql string given an input format that understands Y M D
223 function SQLDate($fmt, $col = false)
225 if (!$col) $col = $this->sysTimeStamp;
226 $s = 'DATE_FORMAT(' . $col . ",'";
229 for ($i = 0; $i < $len; $i++) {
238 $s .= "'),Quarter($col)";
240 if ($len > $i + 1) $s .= ",DATE_FORMAT($col,'";
281 $ch = substr($fmt, $i, 1);
288 if ($concat) $s = "CONCAT($s)";
293 // returns concatenated string
294 // much easier to run "mysqld --ansi" or "mysqld --sql-mode=PIPES_AS_CONCAT" and use || operator
298 $arr = func_get_args();
300 // suggestion by andrew005@mnogo.ru
301 $s = implode(',', $arr);
302 if (strlen($s) > 0) return "CONCAT($s)";
306 function OffsetDate($dayFraction, $date = false)
308 if (!$date) $date = $this->sysDate;
309 return "from_unixtime(unix_timestamp($date)+($dayFraction)*24*3600)";
312 // returns true or false
313 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
315 if (ADODB_PHPVER >= 0x4300)
316 $this->_connectionID = mysql_connect($argHostname, $argUsername, $argPassword,
317 $this->forceNewConnect, $this->clientFlags);
318 else if (ADODB_PHPVER >= 0x4200)
319 $this->_connectionID = mysql_connect($argHostname, $argUsername, $argPassword,
320 $this->forceNewConnect);
322 $this->_connectionID = mysql_connect($argHostname, $argUsername, $argPassword);
324 if ($this->_connectionID === false) return false;
325 if ($argDatabasename) return $this->SelectDB($argDatabasename);
329 // returns true or false
330 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
332 if (ADODB_PHPVER >= 0x4300)
333 $this->_connectionID = mysql_pconnect($argHostname, $argUsername, $argPassword, $this->clientFlags);
335 $this->_connectionID = mysql_pconnect($argHostname, $argUsername, $argPassword);
336 if ($this->_connectionID === false) return false;
337 if ($this->autoRollback) $this->RollbackTrans();
338 if ($argDatabasename) return $this->SelectDB($argDatabasename);
342 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
344 $this->forceNewConnect = true;
345 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
348 function &MetaColumns($table)
351 if ($this->metaColumnsSQL) {
352 global $ADODB_FETCH_MODE;
354 $save = $ADODB_FETCH_MODE;
355 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
356 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
358 $rs = $this->Execute(sprintf($this->metaColumnsSQL, $table));
360 if (isset($savem)) $this->SetFetchMode($savem);
361 $ADODB_FETCH_MODE = $save;
363 if ($rs === false) return false;
367 $fld = new ADOFieldObject();
368 $fld->name = $rs->fields[0];
369 $type = $rs->fields[1];
372 // split type into type(length):
374 if (strpos($type, ',') && preg_match("/^(.+)\((\d+),(\d+)/", $type, $query_array)) {
375 $fld->type = $query_array[1];
376 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
377 $fld->scale = is_numeric($query_array[3]) ? $query_array[3] : -1;
378 } elseif (preg_match("/^(.+)\((\d+)/", $type, $query_array)) {
379 $fld->type = $query_array[1];
380 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
382 $fld->max_length = -1;
386 // split type into type(length):
387 if (preg_match("/^(.+)\((\d+)/", $type, $query_array)) {
388 $fld->type = $query_array[1];
389 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
391 $fld->max_length = -1;
394 $fld->not_null = ($rs->fields[2] != 'YES');
395 $fld->primary_key = ($rs->fields[3] == 'PRI');
396 $fld->auto_increment = (strpos($rs->fields[5], 'auto_increment') !== false);
397 $fld->binary = (strpos($fld->type, 'blob') !== false);
401 if ($d != "" && $d != "NULL") {
402 $fld->has_default = true;
403 $fld->default_value = $d;
405 $fld->has_default = false;
408 if ($save == ADODB_FETCH_NUM) $retarr[] = $fld;
409 else $retarr[strtoupper($fld->name)] = $fld;
418 // returns true or false
419 function SelectDB($dbName)
421 $this->databaseName = $dbName;
422 if ($this->_connectionID) {
423 return @mysql_select_db($dbName, $this->_connectionID);
427 // parameters use PostgreSQL convention, not MySQL
428 function &SelectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs = 0)
430 $offsetStr = ($offset >= 0) ? "$offset," : '';
433 $rs =& $this->CacheExecute($secs, $sql . " LIMIT $offsetStr$nrows", $inputarr);
435 $rs =& $this->Execute($sql . " LIMIT $offsetStr$nrows", $inputarr);
440 // returns queryID or false
441 function _query($sql, $inputarr)
443 //global $ADODB_COUNTRECS;
444 //if($ADODB_COUNTRECS)
445 return mysql_query($sql, $this->_connectionID);
446 //else return @mysql_unbuffered_query($sql,$this->_connectionID); // requires PHP >= 4.0.6
449 /* Returns: the last error message from previous database operation */
453 if ($this->_logsql) return $this->_errorMsg;
454 if (empty($this->_connectionID)) $this->_errorMsg = @mysql_error();
455 else $this->_errorMsg = @mysql_error($this->_connectionID);
456 return $this->_errorMsg;
459 /* Returns: the last error number from previous database operation */
462 if ($this->_logsql) return $this->_errorCode;
463 if (empty($this->_connectionID)) return @mysql_errno();
464 else return @mysql_errno($this->_connectionID);
468 // returns true or false
471 @mysql_close($this->_connectionID);
472 $this->_connectionID = false;
477 * Maximum size of C field
485 * Maximum size of X field
494 /*--------------------------------------------------------------------------------------
495 Class Name: Recordset
496 --------------------------------------------------------------------------------------*/
498 class ADORecordSet_mysql extends ADORecordSet
501 var $databaseType = "mysql";
504 function ADORecordSet_mysql($queryID, $mode = false)
506 if ($mode === false) {
507 global $ADODB_FETCH_MODE;
508 $mode = $ADODB_FETCH_MODE;
511 case ADODB_FETCH_NUM:
512 $this->fetchMode = MYSQL_NUM;
514 case ADODB_FETCH_ASSOC:
515 $this->fetchMode = MYSQL_ASSOC;
518 case ADODB_FETCH_DEFAULT:
519 case ADODB_FETCH_BOTH:
520 $this->fetchMode = MYSQL_BOTH;
524 $this->ADORecordSet($queryID);
529 //GLOBAL $ADODB_COUNTRECS;
530 // $this->_numOfRows = ($ADODB_COUNTRECS) ? @mysql_num_rows($this->_queryID):-1;
531 $this->_numOfRows = @mysql_num_rows($this->_queryID);
532 $this->_numOfFields = @mysql_num_fields($this->_queryID);
535 function &FetchField($fieldOffset = -1)
538 if ($fieldOffset != -1) {
539 $o = @mysql_fetch_field($this->_queryID, $fieldOffset);
540 $f = @mysql_field_flags($this->_queryID, $fieldOffset);
541 $o->max_length = @mysql_field_len($this->_queryID, $fieldOffset); // suggested by: Jim Nicholson (jnich@att.com)
542 //$o->max_length = -1; // mysql returns the max length less spaces -- so it is unrealiable
543 $o->binary = (strpos($f, 'binary') !== false);
544 } else if ($fieldOffset == -1) { /* The $fieldOffset argument is not provided thus its -1 */
545 $o = @mysql_fetch_field($this->_queryID);
546 $o->max_length = @mysql_field_len($this->_queryID); // suggested by: Jim Nicholson (jnich@att.com)
547 //$o->max_length = -1; // mysql returns the max length less spaces -- so it is unrealiable
553 function &GetRowAssoc($upper = true)
555 if ($this->fetchMode == MYSQL_ASSOC && !$upper) return $this->fields;
556 $row =& ADORecordSet::GetRowAssoc($upper);
560 /* Use associative array to get fields array */
561 function Fields($colname)
563 // added @ by "Michael William Miller" <mille562@pilot.msu.edu>
564 if ($this->fetchMode != MYSQL_NUM) return @$this->fields[$colname];
567 $this->bind = array();
568 for ($i = 0; $i < $this->_numOfFields; $i++) {
569 $o = $this->FetchField($i);
570 $this->bind[strtoupper($o->name)] = $i;
573 return $this->fields[$this->bind[strtoupper($colname)]];
578 if ($this->_numOfRows == 0) return false;
579 return @mysql_data_seek($this->_queryID, $row);
583 // 10% speedup to move MoveNext to child class
586 //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return adodb_movenext($this);
588 if ($this->EOF) return false;
590 $this->_currentRow++;
591 $this->fields = @mysql_fetch_array($this->_queryID, $this->fetchMode);
592 if (is_array($this->fields)) return true;
596 /* -- tested raising an error -- appears pointless
597 $conn = $this->connection;
598 if ($conn && $conn->raiseErrorFn && ($errno = $conn->ErrorNo())) {
599 $fn = $conn->raiseErrorFn;
600 $fn($conn->databaseType,'MOVENEXT',$errno,$conn->ErrorMsg().' ('.$this->sql.')',$conn->host,$conn->database);
608 $this->fields = @mysql_fetch_array($this->_queryID, $this->fetchMode);
609 return is_array($this->fields);
614 @mysql_free_result($this->_queryID);
615 $this->_queryID = false;
618 function MetaType($t, $len = -1, $fieldobj = false)
622 $t = $fieldobj->type;
623 $len = $fieldobj->max_length;
626 $len = -1; // mysql max_length is not accurate
627 switch (strtoupper($t)) {
635 if ($len <= $this->blobSize) return 'C';
642 // php_mysql extension always returns 'blob' even if 'text'
643 // so we have to check whether binary...
648 return !empty($fieldobj->binary) ? 'B' : 'X';
666 if (!empty($fieldobj->primary_key)) return 'R';
681 // c-hanging-comment-ender-p: nil
682 // indent-tabs-mode: nil