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 {
19 var $databaseType = 'mysql';
20 var $dataProvider = 'mysql';
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 $substr = "substring";
38 var $nameQuote = '`'; /// string to use to quote identifiers and names
40 function ADODB_mysql()
46 $arr['description'] = ADOConnection::GetOne("select version()");
47 $arr['version'] = ADOConnection::_findvers($arr['description']);
51 function IfNull( $field, $ifNull )
53 return " IFNULL($field, $ifNull) "; // if MySQL
56 function &MetaTables($ttype=false,$showSchema=false,$mask=false)
59 $save = $this->metaTablesSQL;
60 $mask = $this->qstr($mask);
61 $this->metaTablesSQL .= " like $mask";
63 $ret =& ADOConnection::MetaTables($ttype,$showSchema);
66 $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 )
117 ksort ($indexes[$index]['columns']);
124 // if magic quotes disabled, use mysql_real_escape_string()
125 function qstr($s,$magic_quotes=false)
127 if (!$magic_quotes) {
129 if (ADODB_PHPVER >= 0x4300) {
130 if (is_resource($this->_connectionID))
131 return "'".mysql_real_escape_string($s,$this->_connectionID)."'";
133 if ($this->replaceQuote[0] == '\\'){
134 $s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\0"),$s);
136 return "'".str_replace("'",$this->replaceQuote,$s)."'";
139 // undo magic quotes for "
140 $s = str_replace('\\"','"',$s);
146 return mysql_insert_id($this->_connectionID);
149 function GetOne($sql,$inputarr=false)
151 $rs =& $this->SelectLimit($sql,1,-1,$inputarr);
154 if ($rs->EOF) return false;
155 return reset($rs->fields);
161 function _affectedrows()
163 return mysql_affected_rows($this->_connectionID);
166 // See http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
167 // Reference on Last_Insert_ID on the recommended way to simulate sequences
168 var $_genIDSQL = "update %s set id=LAST_INSERT_ID(id+1);";
169 var $_genSeqSQL = "create table %s (id int not null)";
170 var $_genSeq2SQL = "insert into %s values (%s)";
171 var $_dropSeqSQL = "drop table %s";
173 function CreateSequence($seqname='adodbseq',$startID=1)
175 if (empty($this->_genSeqSQL)) return false;
176 $u = strtoupper($seqname);
178 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
179 if (!$ok) return false;
180 return $this->Execute(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
184 function GenID($seqname='adodbseq',$startID=1)
186 // post-nuke sets hasGenID to false
187 if (!$this->hasGenID) return false;
189 $savelog = $this->_logsql;
190 $this->_logsql = false;
191 $getnext = sprintf($this->_genIDSQL,$seqname);
192 $holdtransOK = $this->_transOK; // save the current status
193 $rs = @$this->Execute($getnext);
195 if ($holdtransOK) $this->_transOK = true; //if the status was ok before reset
196 $u = strtoupper($seqname);
197 $this->Execute(sprintf($this->_genSeqSQL,$seqname));
198 $this->Execute(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
199 $rs = $this->Execute($getnext);
201 $this->genID = mysql_insert_id($this->_connectionID);
203 if ($rs) $rs->Close();
205 $this->_logsql = $savelog;
209 function &MetaDatabases()
211 $qid = mysql_list_dbs($this->_connectionID);
214 $max = mysql_num_rows($qid);
216 $db = mysql_tablename($qid,$i);
217 if ($db != 'mysql') $arr[] = $db;
224 // Format date column in sql string given an input format that understands Y M D
225 function SQLDate($fmt, $col=false)
227 if (!$col) $col = $this->sysTimeStamp;
228 $s = 'DATE_FORMAT('.$col.",'";
231 for ($i=0; $i < $len; $i++) {
240 $s .= "'),Quarter($col)";
242 if ($len > $i+1) $s .= ",DATE_FORMAT($col,'";
283 $ch = substr($fmt,$i,1);
290 if ($concat) $s = "CONCAT($s)";
295 // returns concatenated string
296 // much easier to run "mysqld --ansi" or "mysqld --sql-mode=PIPES_AS_CONCAT" and use || operator
300 $arr = func_get_args();
302 // suggestion by andrew005@mnogo.ru
303 $s = implode(',',$arr);
304 if (strlen($s) > 0) return "CONCAT($s)";
308 function OffsetDate($dayFraction,$date=false)
310 if (!$date) $date = $this->sysDate;
311 return "from_unixtime(unix_timestamp($date)+($dayFraction)*24*3600)";
314 // returns true or false
315 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
317 if (ADODB_PHPVER >= 0x4300)
318 $this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword,
319 $this->forceNewConnect,$this->clientFlags);
320 else if (ADODB_PHPVER >= 0x4200)
321 $this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword,
322 $this->forceNewConnect);
324 $this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword);
326 if ($this->_connectionID === false) return false;
327 if ($argDatabasename) return $this->SelectDB($argDatabasename);
331 // returns true or false
332 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
334 if (ADODB_PHPVER >= 0x4300)
335 $this->_connectionID = mysql_pconnect($argHostname,$argUsername,$argPassword,$this->clientFlags);
337 $this->_connectionID = mysql_pconnect($argHostname,$argUsername,$argPassword);
338 if ($this->_connectionID === false) return false;
339 if ($this->autoRollback) $this->RollbackTrans();
340 if ($argDatabasename) return $this->SelectDB($argDatabasename);
344 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
346 $this->forceNewConnect = true;
347 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
350 function &MetaColumns($table)
353 if ($this->metaColumnsSQL) {
354 global $ADODB_FETCH_MODE;
356 $save = $ADODB_FETCH_MODE;
357 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
358 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
360 $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table));
362 if (isset($savem)) $this->SetFetchMode($savem);
363 $ADODB_FETCH_MODE = $save;
365 if ($rs === false) return false;
369 $fld = new ADOFieldObject();
370 $fld->name = $rs->fields[0];
371 $type = $rs->fields[1];
374 // split type into type(length):
376 if (strpos($type,',') && preg_match("/^(.+)\((\d+),(\d+)/", $type, $query_array)) {
377 $fld->type = $query_array[1];
378 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
379 $fld->scale = is_numeric($query_array[3]) ? $query_array[3] : -1;
380 } elseif (preg_match("/^(.+)\((\d+)/", $type, $query_array)) {
381 $fld->type = $query_array[1];
382 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
384 $fld->max_length = -1;
388 // split type into type(length):
389 if (preg_match("/^(.+)\((\d+)/", $type, $query_array)) {
390 $fld->type = $query_array[1];
391 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
393 $fld->max_length = -1;
396 $fld->not_null = ($rs->fields[2] != 'YES');
397 $fld->primary_key = ($rs->fields[3] == 'PRI');
398 $fld->auto_increment = (strpos($rs->fields[5], 'auto_increment') !== false);
399 $fld->binary = (strpos($fld->type,'blob') !== false);
403 if ($d != "" && $d != "NULL") {
404 $fld->has_default = true;
405 $fld->default_value = $d;
407 $fld->has_default = false;
410 if ($save == ADODB_FETCH_NUM) $retarr[] = $fld;
411 else $retarr[strtoupper($fld->name)] = $fld;
420 // returns true or false
421 function SelectDB($dbName)
423 $this->databaseName = $dbName;
424 if ($this->_connectionID) {
425 return @mysql_select_db($dbName,$this->_connectionID);
430 // parameters use PostgreSQL convention, not MySQL
431 function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs=0)
433 $offsetStr =($offset>=0) ? "$offset," : '';
436 $rs =& $this->CacheExecute($secs,$sql." LIMIT $offsetStr$nrows",$inputarr);
438 $rs =& $this->Execute($sql." LIMIT $offsetStr$nrows",$inputarr);
443 // returns queryID or false
444 function _query($sql,$inputarr)
446 //global $ADODB_COUNTRECS;
447 //if($ADODB_COUNTRECS)
448 return mysql_query($sql,$this->_connectionID);
449 //else return @mysql_unbuffered_query($sql,$this->_connectionID); // requires PHP >= 4.0.6
452 /* Returns: the last error message from previous database operation */
456 if ($this->_logsql) return $this->_errorMsg;
457 if (empty($this->_connectionID)) $this->_errorMsg = @mysql_error();
458 else $this->_errorMsg = @mysql_error($this->_connectionID);
459 return $this->_errorMsg;
462 /* Returns: the last error number from previous database operation */
465 if ($this->_logsql) return $this->_errorCode;
466 if (empty($this->_connectionID)) return @mysql_errno();
467 else return @mysql_errno($this->_connectionID);
472 // returns true or false
475 @mysql_close($this->_connectionID);
476 $this->_connectionID = false;
481 * Maximum size of C field
489 * Maximum size of X field
498 /*--------------------------------------------------------------------------------------
499 Class Name: Recordset
500 --------------------------------------------------------------------------------------*/
502 class ADORecordSet_mysql extends ADORecordSet{
504 var $databaseType = "mysql";
507 function ADORecordSet_mysql($queryID,$mode=false)
509 if ($mode === false) {
510 global $ADODB_FETCH_MODE;
511 $mode = $ADODB_FETCH_MODE;
515 case ADODB_FETCH_NUM: $this->fetchMode = MYSQL_NUM; break;
516 case ADODB_FETCH_ASSOC:$this->fetchMode = MYSQL_ASSOC; break;
518 case ADODB_FETCH_DEFAULT:
519 case ADODB_FETCH_BOTH:$this->fetchMode = MYSQL_BOTH; break;
522 $this->ADORecordSet($queryID);
527 //GLOBAL $ADODB_COUNTRECS;
528 // $this->_numOfRows = ($ADODB_COUNTRECS) ? @mysql_num_rows($this->_queryID):-1;
529 $this->_numOfRows = @mysql_num_rows($this->_queryID);
530 $this->_numOfFields = @mysql_num_fields($this->_queryID);
533 function &FetchField($fieldOffset = -1)
536 if ($fieldOffset != -1) {
537 $o = @mysql_fetch_field($this->_queryID, $fieldOffset);
538 $f = @mysql_field_flags($this->_queryID,$fieldOffset);
539 $o->max_length = @mysql_field_len($this->_queryID,$fieldOffset); // suggested by: Jim Nicholson (jnich@att.com)
540 //$o->max_length = -1; // mysql returns the max length less spaces -- so it is unrealiable
541 $o->binary = (strpos($f,'binary')!== false);
543 else if ($fieldOffset == -1) { /* The $fieldOffset argument is not provided thus its -1 */
544 $o = @mysql_fetch_field($this->_queryID);
545 $o->max_length = @mysql_field_len($this->_queryID); // suggested by: Jim Nicholson (jnich@att.com)
546 //$o->max_length = -1; // mysql returns the max length less spaces -- so it is unrealiable
552 function &GetRowAssoc($upper=true)
554 if ($this->fetchMode == MYSQL_ASSOC && !$upper) return $this->fields;
555 $row =& ADORecordSet::GetRowAssoc($upper);
559 /* Use associative array to get fields array */
560 function Fields($colname)
562 // added @ by "Michael William Miller" <mille562@pilot.msu.edu>
563 if ($this->fetchMode != MYSQL_NUM) return @$this->fields[$colname];
566 $this->bind = array();
567 for ($i=0; $i < $this->_numOfFields; $i++) {
568 $o = $this->FetchField($i);
569 $this->bind[strtoupper($o->name)] = $i;
572 return $this->fields[$this->bind[strtoupper($colname)]];
577 if ($this->_numOfRows == 0) return false;
578 return @mysql_data_seek($this->_queryID,$row);
582 // 10% speedup to move MoveNext to child class
585 //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return adodb_movenext($this);
587 if ($this->EOF) return false;
589 $this->_currentRow++;
590 $this->fields = @mysql_fetch_array($this->_queryID,$this->fetchMode);
591 if (is_array($this->fields)) return true;
595 /* -- tested raising an error -- appears pointless
596 $conn = $this->connection;
597 if ($conn && $conn->raiseErrorFn && ($errno = $conn->ErrorNo())) {
598 $fn = $conn->raiseErrorFn;
599 $fn($conn->databaseType,'MOVENEXT',$errno,$conn->ErrorMsg().' ('.$this->sql.')',$conn->host,$conn->database);
607 $this->fields = @mysql_fetch_array($this->_queryID,$this->fetchMode);
608 return is_array($this->fields);
612 @mysql_free_result($this->_queryID);
613 $this->_queryID = false;
616 function MetaType($t,$len=-1,$fieldobj=false)
620 $t = $fieldobj->type;
621 $len = $fieldobj->max_length;
624 $len = -1; // mysql max_length is not accurate
625 switch (strtoupper($t)) {
633 if ($len <= $this->blobSize) return 'C';
640 // php_mysql extension always returns 'blob' even if 'text'
641 // so we have to check whether binary...
646 return !empty($fieldobj->binary) ? 'B' : 'X';
649 case 'DATE': return 'D';
653 case 'TIMESTAMP': return 'T';
662 if (!empty($fieldobj->primary_key)) return 'R';
676 // c-hanging-comment-ender-p: nil
677 // indent-tabs-mode: nil