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;
71 function &MetaIndexes ($table, $primary = FALSE, $owner=false)
73 // save old fetch mode
74 global $ADODB_FETCH_MODE;
76 $save = $ADODB_FETCH_MODE;
77 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
78 if ($this->fetchMode !== FALSE) {
79 $savem = $this->SetFetchMode(FALSE);
83 $rs = $this->Execute(sprintf('SHOW INDEX FROM %s',$table));
87 $this->SetFetchMode($savem);
89 $ADODB_FETCH_MODE = $save;
91 if (!is_object($rs)) {
97 // parse index data into array
98 while ($row = $rs->FetchRow()) {
99 if ($primary == FALSE AND $row[2] == 'PRIMARY') {
103 if (!isset($indexes[$row[2]])) {
104 $indexes[$row[2]] = array(
105 'unique' => ($row[1] == 0),
110 $indexes[$row[2]]['columns'][$row[3] - 1] = $row[4];
113 // sort columns by order in the index
114 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);
428 // parameters use PostgreSQL convention, not MySQL
429 function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs=0)
431 $offsetStr =($offset>=0) ? "$offset," : '';
434 $rs =& $this->CacheExecute($secs,$sql." LIMIT $offsetStr$nrows",$inputarr);
436 $rs =& $this->Execute($sql." LIMIT $offsetStr$nrows",$inputarr);
441 // returns queryID or false
442 function _query($sql,$inputarr)
444 //global $ADODB_COUNTRECS;
445 //if($ADODB_COUNTRECS)
446 return mysql_query($sql,$this->_connectionID);
447 //else return @mysql_unbuffered_query($sql,$this->_connectionID); // requires PHP >= 4.0.6
450 /* Returns: the last error message from previous database operation */
454 if ($this->_logsql) return $this->_errorMsg;
455 if (empty($this->_connectionID)) $this->_errorMsg = @mysql_error();
456 else $this->_errorMsg = @mysql_error($this->_connectionID);
457 return $this->_errorMsg;
460 /* Returns: the last error number from previous database operation */
463 if ($this->_logsql) return $this->_errorCode;
464 if (empty($this->_connectionID)) return @mysql_errno();
465 else return @mysql_errno($this->_connectionID);
470 // returns true or false
473 @mysql_close($this->_connectionID);
474 $this->_connectionID = false;
479 * Maximum size of C field
487 * Maximum size of X field
496 /*--------------------------------------------------------------------------------------
497 Class Name: Recordset
498 --------------------------------------------------------------------------------------*/
500 class ADORecordSet_mysql extends ADORecordSet{
502 var $databaseType = "mysql";
505 function ADORecordSet_mysql($queryID,$mode=false)
507 if ($mode === false) {
508 global $ADODB_FETCH_MODE;
509 $mode = $ADODB_FETCH_MODE;
513 case ADODB_FETCH_NUM: $this->fetchMode = MYSQL_NUM; break;
514 case ADODB_FETCH_ASSOC:$this->fetchMode = MYSQL_ASSOC; break;
516 case ADODB_FETCH_DEFAULT:
517 case ADODB_FETCH_BOTH:$this->fetchMode = MYSQL_BOTH; break;
520 $this->ADORecordSet($queryID);
525 //GLOBAL $ADODB_COUNTRECS;
526 // $this->_numOfRows = ($ADODB_COUNTRECS) ? @mysql_num_rows($this->_queryID):-1;
527 $this->_numOfRows = @mysql_num_rows($this->_queryID);
528 $this->_numOfFields = @mysql_num_fields($this->_queryID);
531 function &FetchField($fieldOffset = -1)
534 if ($fieldOffset != -1) {
535 $o = @mysql_fetch_field($this->_queryID, $fieldOffset);
536 $f = @mysql_field_flags($this->_queryID,$fieldOffset);
537 $o->max_length = @mysql_field_len($this->_queryID,$fieldOffset); // suggested by: Jim Nicholson (jnich@att.com)
538 //$o->max_length = -1; // mysql returns the max length less spaces -- so it is unrealiable
539 $o->binary = (strpos($f,'binary')!== false);
541 else if ($fieldOffset == -1) { /* The $fieldOffset argument is not provided thus its -1 */
542 $o = @mysql_fetch_field($this->_queryID);
543 $o->max_length = @mysql_field_len($this->_queryID); // suggested by: Jim Nicholson (jnich@att.com)
544 //$o->max_length = -1; // mysql returns the max length less spaces -- so it is unrealiable
550 function &GetRowAssoc($upper=true)
552 if ($this->fetchMode == MYSQL_ASSOC && !$upper) return $this->fields;
553 $row =& ADORecordSet::GetRowAssoc($upper);
557 /* Use associative array to get fields array */
558 function Fields($colname)
560 // added @ by "Michael William Miller" <mille562@pilot.msu.edu>
561 if ($this->fetchMode != MYSQL_NUM) return @$this->fields[$colname];
564 $this->bind = array();
565 for ($i=0; $i < $this->_numOfFields; $i++) {
566 $o = $this->FetchField($i);
567 $this->bind[strtoupper($o->name)] = $i;
570 return $this->fields[$this->bind[strtoupper($colname)]];
575 if ($this->_numOfRows == 0) return false;
576 return @mysql_data_seek($this->_queryID,$row);
580 // 10% speedup to move MoveNext to child class
583 //global $ADODB_EXTENSION;if ($ADODB_EXTENSION) return adodb_movenext($this);
585 if ($this->EOF) return false;
587 $this->_currentRow++;
588 $this->fields = @mysql_fetch_array($this->_queryID,$this->fetchMode);
589 if (is_array($this->fields)) return true;
593 /* -- tested raising an error -- appears pointless
594 $conn = $this->connection;
595 if ($conn && $conn->raiseErrorFn && ($errno = $conn->ErrorNo())) {
596 $fn = $conn->raiseErrorFn;
597 $fn($conn->databaseType,'MOVENEXT',$errno,$conn->ErrorMsg().' ('.$this->sql.')',$conn->host,$conn->database);
605 $this->fields = @mysql_fetch_array($this->_queryID,$this->fetchMode);
606 return is_array($this->fields);
610 @mysql_free_result($this->_queryID);
611 $this->_queryID = false;
614 function MetaType($t,$len=-1,$fieldobj=false)
618 $t = $fieldobj->type;
619 $len = $fieldobj->max_length;
622 $len = -1; // mysql max_length is not accurate
623 switch (strtoupper($t)) {
631 if ($len <= $this->blobSize) return 'C';
638 // php_mysql extension always returns 'blob' even if 'text'
639 // so we have to check whether binary...
644 return !empty($fieldobj->binary) ? 'B' : 'X';
647 case 'DATE': return 'D';
651 case 'TIMESTAMP': return 'T';
660 if (!empty($fieldobj->primary_key)) return 'R';
674 // c-hanging-comment-ender-p: nil
675 // indent-tabs-mode: nil