3 global $ADODB_INCLUDED_LIB;
4 $ADODB_INCLUDED_LIB = 1;
7 @version V4.22 15 Apr 2004 (c) 2000-2004 John Lim (jlim\@natsoft.com.my). All rights reserved.
8 Released under both BSD license and Lesser GPL library license.
9 Whenever there is any discrepancy between the two licenses,
10 the BSD license will take precedence. See License.txt.
11 Set tabs to 4 for best viewing.
13 Less commonly used functions are placed here to reduce size of adodb.inc.php.
16 // Force key to upper.
17 // See also http://www.php.net/manual/en/function.array-change-key-case.php
18 function _array_change_key_case($an_array)
20 if (is_array($an_array)) {
21 foreach ($an_array as $key => $value)
22 $new_array[strtoupper($key)] = $value;
30 function _adodb_replace(&$zthis, $table, $fieldArray, $keyCol, $autoQuote, $has_autoinc)
32 if (count($fieldArray) == 0) return 0;
36 if (!is_array($keyCol)) {
37 $keyCol = array($keyCol);
39 foreach ($fieldArray as $k => $v) {
40 if ($autoQuote && !is_numeric($v) and strncmp($v, "'", 1) !== 0 and strcasecmp($v, 'null') != 0) {
41 $v = $zthis->qstr($v);
44 if (in_array($k, $keyCol)) continue; // skip UPDATE if is key
54 foreach ($keyCol as $v) {
55 if ($where) $where .= " and $v=$fieldArray[$v]";
56 else $where = "$v=$fieldArray[$v]";
59 if ($uSet && $where) {
60 $update = "UPDATE $table SET $uSet WHERE $where";
62 $rs = $zthis->Execute($update);
64 if ($zthis->poorAffectedRows) {
66 The Select count(*) wipes out any errors that the update would have returned.
67 http://phplens.com/lens/lensforum/msgs.php?id=5696
69 if ($zthis->ErrorNo() <> 0) return 0;
71 # affected_rows == 0 if update field values identical to old values
72 # for mysql - which is silly.
74 $cnt = $zthis->GetOne("select count(*) from $table where $where");
75 if ($cnt > 0) return 1; // record already exists
77 if (($zthis->Affected_Rows() > 0)) return 1;
80 // print "<p>Error=".$this->ErrorNo().'<p>';
82 foreach ($fieldArray as $k => $v) {
83 if ($has_autoinc && in_array($k, $keyCol)) continue; // skip autoinc col
94 $insert = "INSERT INTO $table ($iCols) VALUES ($iVals)";
95 $rs = $zthis->Execute($insert);
99 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
100 function _adodb_getmenu(&$zthis, $name, $defstr = '', $blank1stItem = true, $multiple = false,
101 $size = 0, $selectAttr = '', $compareFields0 = true)
105 if ($multiple or is_array($defstr)) {
106 if ($size == 0) $size = 5;
107 $attr = " multiple size=$size";
108 if (!strpos($name, '[]')) $name .= '[]';
109 } elseif ($size) $attr = " size=$size"; else $attr = '';
111 $s = "<select name=\"$name\"$attr $selectAttr>";
113 if (is_string($blank1stItem)) {
114 $barr = explode(':', $blank1stItem);
115 if (sizeof($barr) == 1) $barr[] = '';
116 $s .= "\n<option value=\"" . $barr[0] . "\">" . $barr[1] . "</option>";
117 } else $s .= "\n<option></option>";
119 if ($zthis->FieldCount() > 1) $hasvalue = true;
120 else $compareFields0 = true;
123 while (!$zthis->EOF) {
124 $zval = rtrim(reset($zthis->fields));
125 if (sizeof($zthis->fields) > 1) {
126 if (isset($zthis->fields[1]))
127 $zval2 = rtrim($zthis->fields[1]);
129 $zval2 = rtrim(next($zthis->fields));
131 $selected = ($compareFields0) ? $zval : $zval2;
133 if ($blank1stItem && $zval == "") {
138 $value = ' value="' . htmlspecialchars($zval2) . '"';
140 if (is_array($defstr)) {
142 if (in_array($selected, $defstr))
143 $s .= "<option selected$value>" . htmlspecialchars($zval) . '</option>';
145 $s .= "\n<option" . $value . '>' . htmlspecialchars($zval) . '</option>';
147 if (strcasecmp($selected, $defstr) == 0)
148 $s .= "<option selected$value>" . htmlspecialchars($zval) . '</option>';
150 $s .= "\n<option" . $value . '>' . htmlspecialchars($zval) . '</option>';
155 return $s . "\n</select>\n";
159 Count the number of records this sql statement will return by using
160 query rewriting techniques...
162 Does not work with UNIONs.
164 function _adodb_getcount(&$zthis, $sql, $inputarr = false, $secs2cache = 0)
168 if (preg_match("/^\s*SELECT\s+DISTINCT/is", $sql) || preg_match('/\s+GROUP\s+BY\s+/is', $sql)) {
169 // ok, has SELECT DISTINCT or GROUP BY so see if we can use a table alias
170 // but this is only supported by oracle and postgresql...
171 if ($zthis->dataProvider == 'oci8') {
173 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is', '', $sql);
174 $rewritesql = "SELECT COUNT(*) FROM ($rewritesql)";
176 } elseif ($zthis->databaseType == 'postgres' || $zthis->databaseType == 'postgres7') {
178 $info = $zthis->ServerInfo();
179 if (substr($info['version'], 0, 3) >= 7.1) { // good till version 999
180 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is', '', $sql);
181 $rewritesql = "SELECT COUNT(*) FROM ($rewritesql) _ADODB_ALIAS_";
185 // now replace SELECT ... FROM with SELECT COUNT(*) FROM
187 $rewritesql = preg_replace(
188 '/^\s*SELECT\s.*\s+FROM\s/Uis', 'SELECT COUNT(*) FROM ', $sql);
190 // fix by alexander zhukov, alex#unipack.ru, because count(*) and 'order by' fails
191 // with mssql, access and postgresql. Also a good speedup optimization - skips sorting!
192 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is', '', $rewritesql);
195 if (isset($rewritesql) && $rewritesql != $sql) {
197 // we only use half the time of secs2cache because the count can quickly
198 // become inaccurate if new records are added
199 $qryRecs = $zthis->CacheGetOne($secs2cache / 2, $rewritesql, $inputarr);
202 $qryRecs = $zthis->GetOne($rewritesql, $inputarr);
204 if ($qryRecs !== false) return $qryRecs;
207 //--------------------------------------------
208 // query rewrite failed - so try slower way...
210 // strip off unneeded ORDER BY
211 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is', '', $sql);
212 $rstest = &$zthis->Execute($rewritesql, $inputarr);
214 $qryRecs = $rstest->RecordCount();
215 if ($qryRecs == -1) {
216 global $ADODB_EXTENSION;
217 // some databases will return -1 on MoveLast() - change to MoveNext()
218 if ($ADODB_EXTENSION) {
219 while (!$rstest->EOF) {
220 adodb_movenext($rstest);
223 while (!$rstest->EOF) {
227 $qryRecs = $rstest->_currentRow;
230 if ($qryRecs == -1) return 0;
237 Code originally from "Cornel G" <conyg@fx.ro>
239 This code will not work with SQL that has UNION in it
241 Also if you are using CachePageExecute(), there is a strong possibility that
242 data will get out of synch. use CachePageExecute() only with tables that
245 function &_adodb_pageexecute_all_rows(&$zthis, $sql, $nrows, $page,
246 $inputarr = false, $secs2cache = 0)
248 $atfirstpage = false;
252 // If an invalid nrows is supplied,
253 // we assume a default value of 10 rows per page
254 if (!isset($nrows) || $nrows <= 0) $nrows = 10;
256 $qryRecs = false; //count records for no offset
258 $qryRecs = _adodb_getcount($zthis, $sql, $inputarr, $secs2cache);
259 $lastpageno = (int)ceil($qryRecs / $nrows);
260 $zthis->_maxRecordCount = $qryRecs;
262 // If page number <= 1, then we are at the first page
263 if (!isset($page) || $page <= 1) {
268 // ***** Here we check whether $page is the last page or
269 // whether we are trying to retrieve
270 // a page number greater than the last page number.
271 if ($page >= $lastpageno) {
276 // We get the data we want
277 $offset = $nrows * ($page - 1);
279 $rsreturn = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
281 $rsreturn = &$zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
283 // Before returning the RecordSet, we set the pagination properties we need
285 $rsreturn->_maxRecordCount = $qryRecs;
286 $rsreturn->rowsPerPage = $nrows;
287 $rsreturn->AbsolutePage($page);
288 $rsreturn->AtFirstPage($atfirstpage);
289 $rsreturn->AtLastPage($atlastpage);
290 $rsreturn->LastPageNo($lastpageno);
295 // Iván Oliva version
296 function &_adodb_pageexecute_no_last_page(&$zthis, $sql, $nrows, $page, $inputarr = false, $secs2cache = 0)
299 $atfirstpage = false;
302 if (!isset($page) || $page <= 1) { // If page number <= 1, then we are at the first page
306 if ($nrows <= 0) $nrows = 10; // If an invalid nrows is supplied, we assume a default value of 10 rows per page
308 // ***** Here we check whether $page is the last page or whether we are trying to retrieve a page number greater than
309 // the last page number.
310 $pagecounter = $page + 1;
311 $pagecounteroffset = ($pagecounter * $nrows) - $nrows;
312 if ($secs2cache > 0) $rstest = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
313 else $rstest = &$zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
315 while ($rstest && $rstest->EOF && $pagecounter > 0) {
318 $pagecounteroffset = $nrows * ($pagecounter - 1);
320 if ($secs2cache > 0) $rstest = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
321 else $rstest = &$zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
323 if ($rstest) $rstest->Close();
325 if ($atlastpage) { // If we are at the last page or beyond it, we are going to retrieve it
326 $page = $pagecounter;
327 if ($page == 1) $atfirstpage = true; // We have to do this again in case the last page is the same as the first
328 //... page, that is, the recordset has only 1 page.
331 // We get the data we want
332 $offset = $nrows * ($page - 1);
333 if ($secs2cache > 0) $rsreturn = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
334 else $rsreturn = &$zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
336 // Before returning the RecordSet, we set the pagination properties we need
338 $rsreturn->rowsPerPage = $nrows;
339 $rsreturn->AbsolutePage($page);
340 $rsreturn->AtFirstPage($atfirstpage);
341 $rsreturn->AtLastPage($atlastpage);
346 function _adodb_getupdatesql(&$zthis, &$rs, $arrFields, $forceUpdate = false, $magicq = false)
349 printf(ADODB_BAD_RS, 'GetUpdateSQL');
353 $fieldUpdatedCount = 0;
354 $arrFields = _array_change_key_case($arrFields);
356 $hasnumeric = isset($rs->fields[0]);
359 // Loop through all of the fields in the recordset
360 for ($i = 0, $max = $rs->FieldCount(); $i < $max; $i++) {
361 // Get the field from the recordset
362 $field = $rs->FetchField($i);
364 // If the recordset field is one
365 // of the fields passed in then process.
366 $upperfname = strtoupper($field->name);
367 if (adodb_key_exists($upperfname, $arrFields)) {
369 // If the existing field value in the recordset
370 // is different from the value passed in then
371 // go ahead and append the field name and new value to
374 if ($hasnumeric) $val = $rs->fields[$i];
375 else if (isset($rs->fields[$upperfname])) $val = $rs->fields[$upperfname];
376 else if (isset($rs->fields[$field->name])) $val = $rs->fields[$field->name];
377 else if (isset($rs->fields[strtolower($upperfname)])) $val = $rs->fields[strtolower($upperfname)];
380 if ($forceUpdate || strcmp($val, $arrFields[$upperfname])) {
381 // Set the counter for the number of fields that will be updated.
382 $fieldUpdatedCount++;
384 // Based on the datatype of the field
385 // Format the value properly for the database
386 $type = $rs->MetaType($field->type);
388 // is_null requires php 4.0.4
389 if ((defined('ADODB_FORCE_NULLS') && is_null($arrFields[$upperfname])) ||
390 $arrFields[$upperfname] === 'null'
392 $setFields .= $field->name . " = null, ";
394 if ($type == 'null') {
397 //we do this so each driver can customize the sql for
398 //DB specific column types.
399 //Oracle needs BLOB types to be handled with a returning clause
400 //postgres has special needs as well
401 $setFields .= _adodb_column_sql($zthis, 'U', $type, $upperfname,
402 $arrFields, $magicq);
408 // If there were any modified fields then build the rest of the update query.
409 if ($fieldUpdatedCount > 0 || $forceUpdate) {
410 // Get the table name from the existing query.
411 preg_match("/FROM\s+" . ADODB_TABLE_REGEX . "/is", $rs->sql, $tableName);
413 // Get the full where clause excluding the word "WHERE" from
414 // the existing query.
415 preg_match('/\sWHERE\s(.*)/is', $rs->sql, $whereClause);
418 // not a good hack, improvements?
420 preg_match('/\s(LIMIT\s.*)/is', $whereClause[1], $discard);
422 $whereClause = array(false, false);
425 $whereClause[1] = substr($whereClause[1], 0, strlen($whereClause[1]) - strlen($discard[1]));
427 $sql = 'UPDATE ' . $tableName[1] . ' SET ' . substr($setFields, 0, -2);
428 if (strlen($whereClause[1]) > 0)
429 $sql .= ' WHERE ' . $whereClause[1];
438 function adodb_key_exists($key, &$arr)
440 if (!defined('ADODB_FORCE_NULLS')) {
441 // the following is the old behaviour where null or empty fields are ignored
442 return (!empty($arr[$key])) || (isset($arr[$key]) && strlen($arr[$key]) > 0);
445 if (isset($arr[$key])) return true;
447 if (ADODB_PHPVER >= 0x4010) return array_key_exists($key, $arr);
452 * There is a special case of this function for the oci8 driver.
453 * The proper way to handle an insert w/ a blob in oracle requires
454 * a returning clause with bind variables and a descriptor blob.
458 function _adodb_getinsertsql(&$zthis, &$rs, $arrFields, $magicq = false)
464 $arrFields = _array_change_key_case($arrFields);
465 $fieldInsertedCount = 0;
467 if (is_string($rs)) {
468 //ok we have a table name
469 //try and get the column info ourself.
472 //we need an object for the recordSet
473 //because we have to call MetaType.
474 //php can't do a $rsclass::MetaType()
475 $rsclass = $zthis->rsPrefix . $zthis->databaseType;
476 $recordSet =& new $rsclass(-1, $zthis->fetchMode);
477 $recordSet->connection = &$zthis;
479 $columns = $zthis->MetaColumns($tableName);
480 } elseif (is_subclass_of($rs, 'adorecordset')) {
481 for ($i = 0, $max = $rs->FieldCount(); $i < $max; $i++)
482 $columns[] = $rs->FetchField($i);
486 printf(ADODB_BAD_RS, 'GetInsertSQL');
490 // Loop through all of the fields in the recordset
491 foreach ($columns as $field) {
492 $upperfname = strtoupper($field->name);
493 if (adodb_key_exists($upperfname, $arrFields)) {
495 // Set the counter for the number of fields that will be inserted.
496 $fieldInsertedCount++;
498 // Get the name of the fields to insert
499 $fields .= $field->name . ", ";
501 $type = $recordSet->MetaType($field->type);
503 if ((defined('ADODB_FORCE_NULLS') && is_null($arrFields[$upperfname])) ||
504 $arrFields[$upperfname] === 'null'
508 //we do this so each driver can customize the sql for
509 //DB specific column types.
510 //Oracle needs BLOB types to be handled with a returning clause
511 //postgres has special needs as well
512 $values .= _adodb_column_sql($zthis, 'I', $type, $upperfname,
513 $arrFields, $magicq);
519 // If there were any inserted fields then build the rest of the insert query.
520 if ($fieldInsertedCount <= 0) return false;
522 // Get the table name from the existing query.
524 preg_match("/FROM\s+" . ADODB_TABLE_REGEX . "/is", $rs->sql, $tableName);
525 $tableName = $tableName[1];
528 // Strip off the comma and space on the end of both the fields
530 $fields = substr($fields, 0, -2);
531 $values = substr($values, 0, -2);
533 // Append the fields and their values to the insert query.
534 return 'INSERT INTO ' . $tableName . ' ( ' . $fields . ' ) VALUES ( ' . $values . ' )';
539 * This private method is used to help construct
540 * the update/sql which is generated by GetInsertSQL and GetUpdateSQL.
541 * It handles the string construction of 1 column -> sql string based on
542 * the column type. We want to do 'safe' handling of BLOBs
544 * @param string the type of sql we are trying to create
546 * @param string column data type from the db::MetaType() method
547 * @param string the column name
548 * @param array the column value
553 function _adodb_column_sql_oci8(&$zthis, $action, $type, $fname, $arrFields, $magicq)
557 // Based on the datatype of the field
558 // Format the value properly for the database
561 //in order to handle Blobs correctly, we need
562 //to do some magic for Oracle
564 //we need to create a new descriptor to handle
566 if (!empty($zthis->hasReturningInto)) {
567 if ($action == 'I') {
568 $sql = 'empty_blob(), ';
570 $sql = $fname . '=empty_blob(), ';
572 //add the variable to the returning clause array
573 //so the user can build this later in
574 //case they want to add more to it
575 $zthis->_returningArray[$fname] = ':xx' . $fname . 'xx';
577 //this is to maintain compatibility
578 //with older adodb versions.
579 $sql = _adodb_column_sql($zthis, $action, $type, $fname, $arrFields, $magicq, false);
584 //we need to do some more magic here for long variables
585 //to handle these correctly in oracle.
587 //create a safe bind var name
588 //to avoid conflicts w/ dupes.
589 if (!empty($zthis->hasReturningInto)) {
590 if ($action == 'I') {
591 $sql = ':xx' . $fname . 'xx, ';
593 $sql = $fname . '=:xx' . $fname . 'xx, ';
595 //add the variable to the returning clause array
596 //so the user can build this later in
597 //case they want to add more to it
598 $zthis->_returningArray[$fname] = ':xx' . $fname . 'xx';
600 //this is to maintain compatibility
601 //with older adodb versions.
602 $sql = _adodb_column_sql($zthis, $action, $type, $fname, $arrFields, $magicq, false);
607 $sql = _adodb_column_sql($zthis, $action, $type, $fname, $arrFields, $magicq, false);
614 function _adodb_column_sql(&$zthis, $action, $type, $fname, $arrFields, $magicq, $recurse = true)
618 switch ($zthis->dataProvider) {
620 if ($type == 'L') $type = 'C';
623 return _adodb_column_sql_oci8($zthis, $action, $type, $fname, $arrFields, $magicq);
634 if ($action == 'I') {
635 $sql = $zthis->qstr($arrFields[$fname], $magicq) . ", ";
637 $sql .= $fname . "=" . $zthis->qstr($arrFields[$fname], $magicq) . ", ";
642 if ($action == 'I') {
643 $sql = $zthis->DBDate($arrFields[$fname]) . ", ";
645 $sql .= $fname . "=" . $zthis->DBDate($arrFields[$fname]) . ", ";
650 if ($action == 'I') {
651 $sql = $zthis->DBTimeStamp($arrFields[$fname]) . ", ";
653 $sql .= $fname . "=" . $zthis->DBTimeStamp($arrFields[$fname]) . ", ";
658 $val = $arrFields[$fname];
659 if (empty($val)) $val = '0';
662 if ($action == 'I') {
665 $sql .= $fname . "=" . $val . ", ";