library/adodb/adodb-lib.inc.php

   1 <?php
   2
   3 global $ADODB_INCLUDED_LIB;
   4 $ADODB_INCLUDED_LIB = 1;
   5
   6 /*
   7 V4.20 22 Feb 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.
  12
  13   Less commonly used functions are placed here to reduce size of adodb.inc.php.
  14 */
  15
  16
  17 // Force key to upper.
  18 // See also http://www.php.net/manual/en/function.array-change-key-case.php
  19 function _array_change_key_case($an_array)
  20 {
  21         if (is_array($an_array)) {
  22                 foreach($an_array as $key=>$value)
  23                 $new_array[strtoupper($key)] = $value;
  24
  25         return $new_array;
  26    }
  27
  28         return $an_array;
  29 }
  30
  31 function _adodb_replace(&$zthis, $table, $fieldArray, $keyCol, $autoQuote, $has_autoinc)
  32 {
  33                 if (count($fieldArray) == 0) return 0;
  34                 $first = true;
  35                 $uSet = '';
  36
  37                 if (!is_array($keyCol)) {
  38                         $keyCol = array($keyCol);
  39                 }
  40                 foreach($fieldArray as $k => $v) {
  41                         if ($autoQuote && !is_numeric($v) and strncmp($v,"'",1) !== 0 and strcasecmp($v,'null')!=0) {
  42                                 $v = $zthis->qstr($v);
  43                                 $fieldArray[$k] = $v;
  44                         }
  45                         if (in_array($k,$keyCol)) continue; // skip UPDATE if is key
  46
  47                         if ($first) {
  48                                 $first = false;
  49                                 $uSet = "$k=$v";
  50                         } else
  51                                 $uSet .= ",$k=$v";
  52                 }
  53
  54                 $where = false;
  55                 foreach ($keyCol as $v) {
  56                         if ($where) $where .= " and $v=$fieldArray[$v]";
  57                         else $where = "$v=$fieldArray[$v]";
  58                 }
  59
  60                 if ($uSet && $where) {
  61                         $update = "UPDATE $table SET $uSet WHERE $where";
  62
  63                         $rs = $zthis->Execute($update);
  64                         if ($rs) {
  65                                 if ($zthis->poorAffectedRows) {
  66                                 /*
  67                                  The Select count(*) wipes out any errors that the update would have returned.
  68                                 http://phplens.com/lens/lensforum/msgs.php?id=5696
  69                                 */
  70                                         if ($zthis->ErrorNo()<>0) return 0;
  71
  72                                 # affected_rows == 0 if update field values identical to old values
  73                                 # for mysql - which is silly.
  74
  75                                         $cnt = $zthis->GetOne("select count(*) from $table where $where");
  76                                         if ($cnt > 0) return 1; // record already exists
  77                                 } else
  78                                          if (($zthis->Affected_Rows()>0)) return 1;
  79                         }
  80                 }
  81         //      print "<p>Error=".$this->ErrorNo().'<p>';
  82                 $first = true;
  83                 foreach($fieldArray as $k => $v) {
  84                         if ($has_autoinc && in_array($k,$keyCol)) continue; // skip autoinc col
  85
  86                         if ($first) {
  87                                 $first = false;
  88                                 $iCols = "$k";
  89                                 $iVals = "$v";
  90                         } else {
  91                                 $iCols .= ",$k";
  92                                 $iVals .= ",$v";
  93                         }
  94                 }
  95                 $insert = "INSERT INTO $table ($iCols) VALUES ($iVals)";
  96                 $rs = $zthis->Execute($insert);
  97                 return ($rs) ? 2 : 0;
  98 }
  99
 100 // Requires $ADODB_FETCH_MODE = ADODB_FETCH_NUM
 101 function _adodb_getmenu(&$zthis, $name,$defstr='',$blank1stItem=true,$multiple=false,
 102                         $size=0, $selectAttr='',$compareFields0=true)
 103 {
 104         $hasvalue = false;
 105
 106         if ($multiple or is_array($defstr)) {
 107                 if ($size==0) $size=5;
 108                 $attr = " multiple size=$size";
 109                 if (!strpos($name,'[]')) $name .= '[]';
 110         } else if ($size) $attr = " size=$size";
 111         else $attr ='';
 112
 113         $s = "<select name=\"$name\"$attr $selectAttr>";
 114         if ($blank1stItem)
 115                 if (is_string($blank1stItem))  {
 116                         $barr = explode(':',$blank1stItem);
 117                         if (sizeof($barr) == 1) $barr[] = '';
 118                         $s .= "\n<option value=\"".$barr[0]."\">".$barr[1]."</option>";
 119                 } else $s .= "\n<option></option>";
 120
 121         if ($zthis->FieldCount() > 1) $hasvalue=true;
 122         else $compareFields0 = true;
 123
 124         $value = '';
 125         while(!$zthis->EOF) {
 126                 $zval = trim(reset($zthis->fields));
 127                 if (sizeof($zthis->fields) > 1) {
 128                         if (isset($zthis->fields[1]))
 129                                 $zval2 = trim($zthis->fields[1]);
 130                         else
 131                                 $zval2 = trim(next($zthis->fields));
 132                 }
 133                 $selected = ($compareFields0) ? $zval : $zval2;
 134
 135                 if ($blank1stItem && $zval=="") {
 136                         $zthis->MoveNext();
 137                         continue;
 138                 }
 139                 if ($hasvalue)
 140                         $value = ' value="'.htmlspecialchars($zval2).'"';
 141
 142                 if (is_array($defstr))  {
 143
 144                         if (in_array($selected,$defstr))
 145                                 $s .= "<option selected$value>".htmlspecialchars($zval).'</option>';
 146                         else
 147                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 148                 }
 149                 else {
 150                         if (strcasecmp($selected,$defstr)==0)
 151                                 $s .= "<option selected$value>".htmlspecialchars($zval).'</option>';
 152                         else
 153                                 $s .= "\n<option".$value.'>'.htmlspecialchars($zval).'</option>';
 154                 }
 155                 $zthis->MoveNext();
 156         } // while
 157
 158         return $s ."\n</select>\n";
 159 }
 160
 161 /*
 162         Count the number of records this sql statement will return by using
 163         query rewriting techniques...
 164
 165         Does not work with UNIONs.
 166 */
 167 function _adodb_getcount(&$zthis, $sql,$inputarr=false,$secs2cache=0)
 168 {
 169         $qryRecs = 0;
 170
 171          if (preg_match("/^\s*SELECT\s+DISTINCT/is", $sql) || preg_match('/\s+GROUP\s+BY\s+/is',$sql)) {
 172                 // ok, has SELECT DISTINCT or GROUP BY so see if we can use a table alias
 173                 // but this is only supported by oracle and postgresql...
 174                 if ($zthis->dataProvider == 'oci8') {
 175
 176                         $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$sql);
 177                         $rewritesql = "SELECT COUNT(*) FROM ($rewritesql)";
 178
 179                 } else if ( $zthis->databaseType == 'postgres' || $zthis->databaseType == 'postgres7')  {
 180
 181                         $info = $zthis->ServerInfo();
 182                         if (substr($info['version'],0,3) >= 7.1) { // good till version 999
 183                                 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$sql);
 184                                 $rewritesql = "SELECT COUNT(*) FROM ($rewritesql) _ADODB_ALIAS_";
 185                         }
 186                 }
 187         } else {
 188                 // now replace SELECT ... FROM with SELECT COUNT(*) FROM
 189
 190                 $rewritesql = preg_replace(
 191                                         '/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ',$sql);
 192
 193                 // fix by alexander zhukov, alex#unipack.ru, because count(*) and 'order by' fails
 194                 // with mssql, access and postgresql. Also a good speedup optimization - skips sorting!
 195                 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$rewritesql);
 196         }
 197
 198         if (isset($rewritesql) && $rewritesql != $sql) {
 199                 if ($secs2cache) {
 200                         // we only use half the time of secs2cache because the count can quickly
 201                         // become inaccurate if new records are added
 202                         $qryRecs = $zthis->CacheGetOne($secs2cache/2,$rewritesql,$inputarr);
 203
 204                 } else {
 205                         $qryRecs = $zthis->GetOne($rewritesql,$inputarr);
 206                 }
 207                 if ($qryRecs !== false) return $qryRecs;
 208         }
 209
 210         //--------------------------------------------
 211         // query rewrite failed - so try slower way...
 212
 213         // strip off unneeded ORDER BY
 214         $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$sql);
 215         $rstest = &$zthis->Execute($rewritesql,$inputarr);
 216         if ($rstest) {
 217                 $qryRecs = $rstest->RecordCount();
 218                 if ($qryRecs == -1) {
 219                 global $ADODB_EXTENSION;
 220                 // some databases will return -1 on MoveLast() - change to MoveNext()
 221                         if ($ADODB_EXTENSION) {
 222                                 while(!$rstest->EOF) {
 223                                         adodb_movenext($rstest);
 224                                 }
 225                         } else {
 226                                 while(!$rstest->EOF) {
 227                                         $rstest->MoveNext();
 228                                 }
 229                         }
 230                         $qryRecs = $rstest->_currentRow;
 231                 }
 232                 $rstest->Close();
 233                 if ($qryRecs == -1) return 0;
 234         }
 235
 236         return $qryRecs;
 237 }
 238
 239 /*
 240         Code originally from "Cornel G" <conyg@fx.ro>
 241
 242         This code will not work with SQL that has UNION in it
 243
 244         Also if you are using CachePageExecute(), there is a strong possibility that
 245         data will get out of synch. use CachePageExecute() only with tables that
 246         rarely change.
 247 */
 248 function &_adodb_pageexecute_all_rows(&$zthis, $sql, $nrows, $page,
 249                                                 $inputarr=false, $secs2cache=0)
 250 {
 251         $atfirstpage = false;
 252         $atlastpage = false;
 253         $lastpageno=1;
 254
 255         // If an invalid nrows is supplied,
 256         // we assume a default value of 10 rows per page
 257         if (!isset($nrows) || $nrows <= 0) $nrows = 10;
 258
 259         $qryRecs = false; //count records for no offset
 260
 261         $qryRecs = _adodb_getcount($zthis,$sql,$inputarr,$secs2cache);
 262         $lastpageno = (int) ceil($qryRecs / $nrows);
 263         $zthis->_maxRecordCount = $qryRecs;
 264
 265         // If page number <= 1, then we are at the first page
 266         if (!isset($page) || $page <= 1) {
 267                 $page = 1;
 268                 $atfirstpage = true;
 269         }
 270
 271         // ***** Here we check whether $page is the last page or
 272         // whether we are trying to retrieve
 273         // a page number greater than the last page number.
 274         if ($page >= $lastpageno) {
 275                 $page = $lastpageno;
 276                 $atlastpage = true;
 277         }
 278
 279         // We get the data we want
 280         $offset = $nrows * ($page-1);
 281         if ($secs2cache > 0)
 282                 $rsreturn = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
 283         else
 284                 $rsreturn = &$zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
 285
 286
 287         // Before returning the RecordSet, we set the pagination properties we need
 288         if ($rsreturn) {
 289                 $rsreturn->_maxRecordCount = $qryRecs;
 290                 $rsreturn->rowsPerPage = $nrows;
 291                 $rsreturn->AbsolutePage($page);
 292                 $rsreturn->AtFirstPage($atfirstpage);
 293                 $rsreturn->AtLastPage($atlastpage);
 294                 $rsreturn->LastPageNo($lastpageno);
 295         }
 296         return $rsreturn;
 297 }
 298
 299 // Iván Oliva version
 300 function &_adodb_pageexecute_no_last_page(&$zthis, $sql, $nrows, $page, $inputarr=false, $secs2cache=0)
 301 {
 302
 303         $atfirstpage = false;
 304         $atlastpage = false;
 305
 306         if (!isset($page) || $page <= 1) {      // If page number <= 1, then we are at the first page
 307                 $page = 1;
 308                 $atfirstpage = true;
 309         }
 310         if ($nrows <= 0) $nrows = 10;   // If an invalid nrows is supplied, we assume a default value of 10 rows per page
 311
 312         // ***** Here we check whether $page is the last page or whether we are trying to retrieve a page number greater than
 313         // the last page number.
 314         $pagecounter = $page + 1;
 315         $pagecounteroffset = ($pagecounter * $nrows) - $nrows;
 316         if ($secs2cache>0) $rstest = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
 317         else $rstest = &$zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
 318         if ($rstest) {
 319                 while ($rstest && $rstest->EOF && $pagecounter>0) {
 320                         $atlastpage = true;
 321                         $pagecounter--;
 322                         $pagecounteroffset = $nrows * ($pagecounter - 1);
 323                         $rstest->Close();
 324                         if ($secs2cache>0) $rstest = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $pagecounteroffset, $inputarr);
 325                         else $rstest = &$zthis->SelectLimit($sql, $nrows, $pagecounteroffset, $inputarr, $secs2cache);
 326                 }
 327                 if ($rstest) $rstest->Close();
 328         }
 329         if ($atlastpage) {      // If we are at the last page or beyond it, we are going to retrieve it
 330                 $page = $pagecounter;
 331                 if ($page == 1) $atfirstpage = true;    // We have to do this again in case the last page is the same as the first
 332                         //... page, that is, the recordset has only 1 page.
 333         }
 334
 335         // We get the data we want
 336         $offset = $nrows * ($page-1);
 337         if ($secs2cache > 0) $rsreturn = &$zthis->CacheSelectLimit($secs2cache, $sql, $nrows, $offset, $inputarr);
 338         else $rsreturn = &$zthis->SelectLimit($sql, $nrows, $offset, $inputarr, $secs2cache);
 339
 340         // Before returning the RecordSet, we set the pagination properties we need
 341         if ($rsreturn) {
 342                 $rsreturn->rowsPerPage = $nrows;
 343                 $rsreturn->AbsolutePage($page);
 344                 $rsreturn->AtFirstPage($atfirstpage);
 345                 $rsreturn->AtLastPage($atlastpage);
 346         }
 347         return $rsreturn;
 348 }
 349
 350 function _adodb_getupdatesql(&$zthis,&$rs, $arrFields,$forceUpdate=false,$magicq=false)
 351 {
 352                 if (!$rs) {
 353                         printf(ADODB_BAD_RS,'GetUpdateSQL');
 354                         return false;
 355                 }
 356
 357                 $fieldUpdatedCount = 0;
 358                 $arrFields = _array_change_key_case($arrFields);
 359
 360                 $hasnumeric = isset($rs->fields[0]);
 361                 $updateSQL = '';
 362
 363                 // Loop through all of the fields in the recordset
 364                 for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++) {
 365
 366                         // Get the field from the recordset
 367                         $field = $rs->FetchField($i);
 368
 369                         // If the recordset field is one
 370                         // of the fields passed in then process.
 371                         $upperfname = strtoupper($field->name);
 372                         if (adodb_key_exists($upperfname,$arrFields)) {
 373
 374                                 // If the existing field value in the recordset
 375                                 // is different from the value passed in then
 376                                 // go ahead and append the field name and new value to
 377                                 // the update query.
 378
 379                                 if ($hasnumeric) $val = $rs->fields[$i];
 380                                 else if (isset($rs->fields[$upperfname])) $val = $rs->fields[$upperfname];
 381                                 else if (isset($rs->fields[$field->name])) $val =  $rs->fields[$field->name];
 382                                 else if (isset($rs->fields[strtolower($upperfname)])) $val =  $rs->fields[strtolower($upperfname)];
 383                                 else $val = '';
 384
 385                                 if ($forceUpdate || strcmp($val, $arrFields[$upperfname])) {
 386                                         // Set the counter for the number of fields that will be updated.
 387                                         $fieldUpdatedCount++;
 388
 389                                         // Based on the datatype of the field
 390                                         // Format the value properly for the database
 391                                         $mt = $rs->MetaType($field->type);
 392
 393                                         // "mike" <mike@partner2partner.com> patch and "Ryan Bailey" <rebel@windriders.com>
 394                                         //PostgreSQL uses a 't' or 'f' and therefore needs to be processed as a string ('C') type field.
 395                                         if ((strncmp($zthis->databaseType,"postgres",8) === 0) && ($mt == "L")) $mt = "C";
 396                                         // is_null requires php 4.0.4
 397                                         if ((defined('ADODB_FORCE_NULLS') && is_null($arrFields[$upperfname])) || $arrFields[$upperfname] === 'null')
 398                                                 $updateSQL .= $field->name . " = null, ";
 399                                         else
 400                                         switch($mt) {
 401                                                 case 'null':
 402                                                 case "C":
 403                                                 case "X":
 404                                                 case 'B':
 405                                                         $updateSQL .= $field->name . " = " . $zthis->qstr($arrFields[$upperfname],$magicq) . ", ";
 406                                                         break;
 407                                                 case "D":
 408                                                         $updateSQL .= $field->name . " = " . $zthis->DBDate($arrFields[$upperfname]) . ", ";
 409                                                         break;
 410                                                 case "T":
 411                                                         $updateSQL .= $field->name . " = " . $zthis->DBTimeStamp($arrFields[$upperfname]) . ", ";
 412                                                         break;
 413                                                 default:
 414                                                         $val = $arrFields[$upperfname];
 415                                                         /*if (!is_numeric($val)) {
 416                                                                 if (strncmp($val,'=',1) == 0) $val = substr($val,1);
 417                                                                 else $val = (float) $val;
 418                                                         }*/
 419                                                         if (empty($val)) $val = '0';
 420
 421                                                         $updateSQL .= $field->name . " = " . $val  . ", ";
 422                                                         break;
 423                                         };
 424                                 };
 425                         };
 426                 };
 427
 428                 // If there were any modified fields then build the rest of the update query.
 429                 if ($fieldUpdatedCount > 0 || $forceUpdate) {
 430
 431                                         // Get the table name from the existing query.
 432                         preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName);
 433
 434                         // Get the full where clause excluding the word "WHERE" from
 435                         // the existing query.
 436                         preg_match('/\sWHERE\s(.*)/is', $rs->sql, $whereClause);
 437
 438                         $discard = false;
 439                         // not a good hack, improvements?
 440                         if ($whereClause)
 441                                 preg_match('/\s(LIMIT\s.*)/is', $whereClause[1], $discard);
 442                         else
 443                                 $whereClause = array(false,false);
 444
 445                         if ($discard)
 446                                 $whereClause[1] = substr($whereClause[1], 0, strlen($whereClause[1]) - strlen($discard[1]));
 447
 448                         // updateSQL will contain the full update query when all
 449                         // processing has completed.
 450                         $updateSQL = "UPDATE " . $tableName[1] . " SET ".substr($updateSQL, 0, -2);
 451
 452                         // If the recordset has a where clause then use that same where clause
 453                         // for the update.
 454                         if ($whereClause[1]) $updateSQL .= " WHERE " . $whereClause[1];
 455
 456                         return $updateSQL;
 457                 } else {
 458                         return false;
 459                 };
 460 }
 461
 462 function adodb_key_exists($key, &$arr)
 463 {
 464         if (!defined('ADODB_FORCE_NULLS')) {
 465                 // the following is the old behaviour where null or empty fields are ignored
 466                 return (!empty($arr[$key])) || (isset($arr[$key]) && strlen($arr[$key])>0);
 467         }
 468
 469         if (isset($arr[$key])) return true;
 470         ## null check below
 471         if (ADODB_PHPVER >= 0x4010) return array_key_exists($key,$arr);
 472         return false;
 473 }
 474
 475 function _adodb_getinsertsql(&$zthis,&$rs,$arrFields,$magicq=false)
 476 {
 477         $values = '';
 478         $fields = '';
 479         $arrFields = _array_change_key_case($arrFields);
 480
 481         if (!$rs) {
 482                 printf(ADODB_BAD_RS,'GetInsertSQL');
 483                 return false;
 484         }
 485
 486         $fieldInsertedCount = 0;
 487
 488         // Loop through all of the fields in the recordset
 489         for ($i=0, $max=$rs->FieldCount(); $i < $max; $i++) {
 490
 491                 // Get the field from the recordset
 492                 $field = $rs->FetchField($i);
 493                 // If the recordset field is one
 494                 // of the fields passed in then process.
 495                 $upperfname = strtoupper($field->name);
 496                 if (adodb_key_exists($upperfname,$arrFields)) {
 497
 498                         // Set the counter for the number of fields that will be inserted.
 499                         $fieldInsertedCount++;
 500
 501                         // Get the name of the fields to insert
 502                         $fields .= $field->name . ", ";
 503
 504                         $mt = $rs->MetaType($field->type);
 505
 506                         // "mike" <mike@partner2partner.com> patch and "Ryan Bailey" <rebel@windriders.com>
 507                         //PostgreSQL uses a 't' or 'f' and therefore needs to be processed as a string ('C') type field.
 508                         if ((strncmp($zthis->databaseType,"postgres",8) === 0) && ($mt == "L")) $mt = "C";
 509
 510                         // Based on the datatype of the field
 511                         // Format the value properly for the database
 512                         if ((defined('ADODB_FORCE_NULLS') && is_null($arrFields[$upperfname])) || $arrFields[$upperfname] === 'null')
 513                                         $values .= "null, ";
 514                         else
 515                         switch($mt) {
 516                                 case "C":
 517                                 case "X":
 518                                 case 'B':
 519                                         $values .= $zthis->qstr($arrFields[$upperfname],$magicq) . ", ";
 520                                         break;
 521                                 case "D":
 522                                         $values .= $zthis->DBDate($arrFields[$upperfname]) . ", ";
 523                                         break;
 524                                 case "T":
 525                                         $values .= $zthis->DBTimeStamp($arrFields[$upperfname]) . ", ";
 526                                         break;
 527                                 default:
 528                                         $val = $arrFields[$upperfname];
 529                                         /*if (!is_numeric($val)) {
 530                                                 if (strncmp($val,'=',1) == 0) $val = substr($val,1);
 531                                                 else $val = (float) $val;
 532                                         }*/
 533                                         if (empty($val)) $val = '0';
 534                                         $values .= $val . ", ";
 535                                         break;
 536                         };
 537                 };
 538         };
 539
 540         // If there were any inserted fields then build the rest of the insert query.
 541         if ($fieldInsertedCount <= 0)  return false;
 542
 543         // Get the table name from the existing query.
 544         preg_match("/FROM\s+".ADODB_TABLE_REGEX."/is", $rs->sql, $tableName);
 545
 546         // Strip off the comma and space on the end of both the fields
 547         // and their values.
 548         $fields = substr($fields, 0, -2);
 549         $values = substr($values, 0, -2);
 550
 551         // Append the fields and their values to the insert query.
 552         $insertSQL = "INSERT INTO " . $tableName[1] . " ( $fields ) VALUES ( $values )";
 553
 554         return $insertSQL;
 555 }
 556
 557
 558 ?>