| [ Index ] |
|
Code source de GeekLog 1.4.1 |
1 <?php 2 3 /* Reminder: always indent with 4 spaces (no tabs). */ 4 // +---------------------------------------------------------------------------+ 5 // | Geeklog 1.4 | 6 // +---------------------------------------------------------------------------+ 7 // | mssql.class.php | 8 // | | 9 // | mysql database class | 10 // +---------------------------------------------------------------------------+ 11 // | Copyright (C) 2000-2006 by the following authors: | 12 // | | 13 // | Authors: Tony Bibbs, tony AT tonybibbs DOT com | 14 // | Randy Kolenko, Randy AT nextide DOT ca | 15 // +---------------------------------------------------------------------------+ 16 // | | 17 // | This program is free software; you can redistribute it and/or | 18 // | modify it under the terms of the GNU General Public License | 19 // | as published by the Free Software Foundation; either version 2 | 20 // | of the License, or (at your option) any later version. | 21 // | | 22 // | This program is distributed in the hope that it will be useful, | 23 // | but WITHOUT ANY WARRANTY; without even the implied warranty of | 24 // | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the | 25 // | GNU General Public License for more details. | 26 // | | 27 // | You should have received a copy of the GNU General Public License | 28 // | along with this program; if not, write to the Free Software Foundation, | 29 // | Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. | 30 // | | 31 // +---------------------------------------------------------------------------+ 32 // 33 // $Id: mssql.class.php,v 1.5 2006/11/25 13:58:37 dhaun Exp $ 34 35 /** 36 * This file is the mssql implementation of the Geeklog abstraction layer. 37 * Unfortunately the Geeklog abstraction layer isn't 100% abstract because a few 38 * key functions use MySQL's REPLACE INTO syntax which is not a SQL standard. 39 * This issue will need to be resolved some time ... 40 * 41 */ 42 class database { 43 44 // PRIVATE PROPERTIES 45 46 /** 47 * @access private 48 */ 49 var $_host = ''; 50 /** 51 * @access private 52 */ 53 var $_name = ''; 54 /** 55 * @access private 56 */ 57 var $_user = ''; 58 /** 59 * @access private 60 */ 61 var $_pass = ''; 62 /** 63 * @access private 64 */ 65 var $_db = ''; 66 /** 67 * @access private 68 */ 69 var $_verbose = false; 70 /** 71 * @access private 72 */ 73 var $_display_error = false; 74 /** 75 * @access private 76 */ 77 var $_errorlog_fn = ''; 78 79 /** 80 * @access private 81 */ 82 var $_limitRows = array(); 83 var $_numberOfRowsAskedFor = array(); 84 var $_lastInsertID=array(); 85 var $_fastForwardRows=array(); 86 var $_NoArraylastInsertID=''; 87 88 89 90 // PRIVATE METHODS 91 92 /** 93 * Logs messages 94 * 95 * Logs messages by calling the function held in $_errorlog_fn 96 * 97 * @param string $msg Message to log 98 * @access private 99 */ 100 function _errorlog($msg) 101 { 102 $function = $this->_errorlog_fn; 103 if (function_exists($function)) { 104 $function($msg); 105 } 106 } 107 108 /** 109 * Connects to the Microsoft database server 110 * 111 * This function connects to the mssql server and returns the connection object 112 * 113 * @return object Returns connection object 114 * @access private 115 * 116 */ 117 function _connect() 118 { 119 if ($this->isVerbose()) { 120 $this->_errorlog("\n*** Inside database->_connect ***<br>"); 121 } 122 123 // Connect to mssql server 124 $this->_db = mssql_connect($this->_host,$this->_user,$this->_pass) or die('Cannnot connect to DB server'); 125 126 // Set the database 127 @mssql_select_db($this->_name) or die ('Cannot Connect to the database provided. Please check the config.php settings.'); 128 129 if (!($this->_db)) { 130 if ($this->isVerbose()) { 131 $this->_errorlog("\n*** Error in database->_connect ***"); 132 } 133 134 // damn, got an error. 135 $this->dbError(); 136 } 137 138 if ($this->isVerbose()) { 139 $this->_errorlog("\n***leaving database->_connect***<br>"); 140 } 141 } 142 143 // PUBLIC METHODS 144 145 /** 146 * constructor for database 147 * 148 * This initializes an instance of the database object 149 * 150 * @param string $dbhost Database host 151 * @param string $dbname Name of database 152 * @param sring $dbuser User to make connection as 153 * @param string $pass Password for dbuser 154 * @param string $errorlogfn Name of the errorlog function 155 * @param string $charset character set to use 156 * 157 */ 158 function database($dbhost,$dbname,$dbuser,$dbpass,$errorlogfn='',$charset='') 159 { 160 $this->_host = $dbhost; 161 $this->_name = $dbname; 162 $this->_user = $dbuser; 163 $this->_pass = $dbpass; 164 $this->_verbose = false; 165 $this->_errorlog_fn = $errorlogfn; 166 167 $this->_connect(); 168 } 169 170 /** 171 * Turns debug mode on 172 * 173 * Set this to true to see debug messages 174 * 175 * @param boolean $flag true or false 176 * 177 */ 178 function setVerbose($flag) 179 { 180 $this->_verbose = $flag; 181 } 182 183 /** 184 * Turns detailed error reporting on 185 * 186 * If set to true, this will display detailed error messages on the site. 187 * Otherwise, it will only that state an error occurred without going into 188 * details. The complete error message (including the offending SQL request) 189 * is always available from error.log. 190 * 191 * @param boolean $flag true or false 192 * 193 */ 194 function setDisplayError($flag) 195 { 196 $this->_display_error = $flag; 197 } 198 199 /** 200 * Checks to see if debug mode is on 201 * 202 * Returns value of $_verbose 203 * 204 * @return boolean true if in verbose mode otherwise false 205 * 206 */ 207 function isVerbose() 208 { 209 if ($this->_verbose && (empty($this->_errorlog_fn) || !function_exists($this->_errorlog_fn))) { 210 print "\n<BR><B>Can't run mssql.class.php verbosely because the errorlog " 211 . "function wasn't set or doesn't exist<BR>\n"; 212 return false; 213 } 214 215 return $this->_verbose; 216 } 217 218 /** 219 * Sets the function this class should call to log debug messages 220 * 221 * @param string $functionname Function name 222 * 223 */ 224 function setErrorFunction($functionname) 225 { 226 $this->_errorlog_fn = $functionname; 227 } 228 229 230 231 function cleanseSQL($originalsql,$skipQuoteReplacement=0){ 232 $sql=$originalsql; 233 234 if(!$skipQuoteReplacement){ 235 $tempsql=str_replace('\"',"''",$sql); 236 $sql=$tempsql; 237 } 238 239 if(!$skipQuoteReplacement){ 240 $tempsql=str_replace("\'","''",$sql); 241 $sql=$tempsql; 242 } 243 244 $sql=$this->changeDESCRIBE($sql); 245 246 $sql=$sql . " "; 247 248 $tempsql=str_replace("`","",$sql); 249 $sql=$tempsql; 250 251 $tempsql=str_replace("unix_timestamp","UNIX_TIMESTAMP",$sql); 252 $sql=$tempsql; 253 254 $tempsql=str_replace("UNIX_TIMESTAMP","dbo.UNIX_TIMESTAMP",$sql); 255 $sql=$tempsql; 256 257 $tempsql=str_replace("date_format","DATE_FORMAT",$sql); 258 $sql=$tempsql; 259 260 $tempsql=str_replace("DATE_FORMAT","dbo.DATE_FORMAT",$sql); 261 $sql=$tempsql; 262 263 $tempsql=str_replace("from_unixtime","FROM_UNIXTIME",$sql); 264 $sql=$tempsql; 265 266 $tempsql=str_replace("FROM_UNIXTIME","dbo.FROM_UNIXTIME",$sql); 267 $sql=$tempsql; 268 269 $tempsql=str_replace("dbo.UNIX_TIMESTAMP()","dbo.UNIX_TIMESTAMP('')",$sql); 270 $sql=$tempsql; 271 272 $tempsql=str_replace("dbo.FROM_UNIXTIME()","dbo.FROM_UNIXTIME('')",$sql); 273 $sql=$tempsql; 274 275 //$tempsql=str_replace("dbo.FROM_UNIXTIME(')","dbo.FROM_UNIXTIME('')",$sql); 276 //$sql=$tempsql; 277 278 $tempsql=$this->cleanse_date_sub($sql); 279 $sql=$tempsql; 280 281 $tempsql=str_replace("NOW()","getUTCDate()",$sql); 282 $sql=$tempsql; 283 284 $tempsql=str_replace("now()","getUTCDate()",$sql); 285 $sql=$tempsql; 286 287 $tempsql=str_replace("STRAIGHT_JOIN","",$sql); 288 $sql=$tempsql; 289 290 $tempsql=str_replace("straight_join","",$sql); 291 $sql=$tempsql; 292 293 $tempsql=str_replace("WHERE 1 ","WHERE 1=1 ",$sql); 294 $sql=$tempsql; 295 296 $tempsql=str_replace("where 1 ","where 1=1 ",$sql); 297 $sql=$tempsql; 298 299 $tempsql=str_replace("to_days","TO_DAYS",$sql); 300 $sql=$tempsql; 301 302 $tempsql=str_replace("TO_DAYS","dbo.TO_DAYS",$sql); 303 $sql=$tempsql; 304 305 $tempsql=str_replace("CURDATE()","getDate()",$sql); 306 $sql=$tempsql; 307 308 $tempsql=str_replace("'0000-00-00 00:00:00'","NULL",$sql); 309 $sql=$tempsql; 310 311 $tempsql=str_replace("'null'","null",$sql); 312 $sql=$tempsql; 313 314 $tempsql=str_replace("dbo.dbo.","dbo.",$sql); 315 $sql=$tempsql; 316 317 return $sql; 318 } 319 320 321 //swaps out the propriatary DESC function in mysql and replaces it with our UDF version 322 function changeDESCRIBE($sql){ 323 $sql=trim($sql); 324 $testSQL=strtolower($sql); 325 $testSQL=" " . $sql; 326 327 $isIn=strpos($testSQL,'describe '); 328 if($isIn>0){ 329 if($sql[strlen($sql)-1]==';'){ 330 //if the last char is a semi colon, get rid of it 331 $sql=str_replace(";","",$sql); 332 } 333 $testSQL=strtolower($sql); 334 $remainder=str_replace("describe ","",$testSQL); 335 $remainder=trim($remainder); 336 $remainder="select * from dbo.DESCRIBE('" . $remainder . "')"; 337 return $remainder; 338 } 339 else{ 340 return $sql; 341 } 342 } 343 344 345 346 347 /** 348 * Executes a query on the server 349 * 350 * This executes the passed SQL and returns the recordset or errors out 351 * 352 * @param string $sql SQL to be executed 353 * @param boolean $ignore_error If 1 this function supresses any error messages 354 * @return object Returns results of query 355 * 356 */ 357 function dbQuery($sql,$ignore_errors=0,$skipQuoteReplacement=0) 358 { 359 360 $sql=$this->cleanseSQL($sql,$skipQuoteReplacement); 361 //limit detection 362 $testSQL=strtolower($sql); 363 $mode=0; 364 $isIn=strpos($testSQL,' limit '); 365 if($isIn>0){ 366 //there is a LIMIT clause in there 367 //lets get its offset as a string to the end of the sql call 368 369 $limitclause=strstr($testSQL,' limit '); 370 $testSQL=substr($sql,0,$isIn); 371 372 $limitclause=trim($limitclause); 373 eregi("limit ([^,]+),(.*)", $limitclause,$arrayStr); 374 375 $left=trim($arrayStr[1]); 376 $rightStr=trim($arrayStr[2]); 377 378 379 if($rigthStr=='' and $left==''){ 380 //this is the case where we have a single limit value 381 //this is IDENTICAL to the usage of the TOP clause 382 $left=strstr($limitclause, ' '); 383 $left=trim($left); 384 $mode=1; 385 386 387 }//end if $rightstr... 388 else{ 389 //left and right have something useful 390 //this means we'll have to do some kind of temp table manipulation.... 391 $rightStr=str_replace(";","",$rightStr); 392 $mode=2; 393 }//end else 394 $sql=$testSQL; 395 } 396 397 //end limit detection 398 399 400 if ($this->isVerbose()) { 401 $this->_errorlog("\n***inside database->dbQuery***<br>"); 402 $this->_errorlog("\n*** sql to execute is $sql ***<br>"); 403 } 404 405 if($mode==0){ 406 407 } 408 elseif($mode==1){//this is a simple limit clause 409 $testSQL=strtolower($sql); 410 $isIn=strpos($testSQL,' distinct '); 411 if($isIn>0){ 412 $sql=str_replace(" distinct ", " distinct top " . $left . " ", $sql); 413 $sql=str_replace(" DISTINCT ", " DISTINCT TOP " . $left . " ", $sql); 414 //echo $sql; 415 } 416 else{ 417 $sql=str_replace("select ", "select top " . $left . " ", $sql); 418 $sql=str_replace("SELECT ", "SELECT TOP " . $left . " ", $sql); 419 //echo $sql; 420 } 421 } 422 else{//this is a tough limit clause 423 424 } 425 426 //check for insert... handle this differently 427 $testSQL=strtolower($sql); 428 $testSQL = ' ' . $testSQL; 429 $isIn=strpos($testSQL,'insert '); 430 $isInsert=0; 431 if($isIn>0){ 432 //next we have to check if it already ends in a semi colon 433 $testSQL=trim($sql); 434 $testSQL=$testSQL[strlen($testSQL)-1]; 435 if($testSQL!=';'){ 436 $sql .="; select SCOPE_IDENTITY()"; 437 } 438 else{ 439 $sql .=" select SCOPE_IDENTITY()"; 440 } 441 442 $isInsert=1; 443 } 444 445 //echo "<xmp>" . $sql . "</xmp>"; 446 447 // Run query 448 if ($ignore_errors == 1) { 449 $result = @mssql_query($sql,$this->_db); 450 451 } else { 452 453 $result = @mssql_query($sql,$this->_db) or die($this->dbError($sql) . ' - ' . $sql); 454 if($result==FALSE){ 455 echo "Query Failed: "; 456 echo "<xmp>".$this->dbError($sql) . "</xmp><HR>"; 457 } 458 } 459 $this->_totalRowCount=$this->dbNumRows($result); 460 if($result!=FALSE && $isInsert==1){ 461 $insert=$this->dbFetchArray($result); 462 $this->array_push_associative($this->_lastInsertID, array("{$result}"=>"{$insert[0]}")) ; 463 $this->_NoArraylastInsertID=$insert[0]; 464 //$this->_lastInsertID=$insert[0]; 465 } 466 467 $this->_fastForwardRows=0; 468 if($result!=FALSE){ 469 if($mode==2){ 470 //got the result set 471 //fast forward thru the set 472 473 @mssql_data_seek($result,$left-1); 474 475 476 477 if(!is_array($this->_limitRows)){ 478 $this->_limitRows=array(); 479 } 480 if (array_key_exists("{$result}",$this->_limitRows)){ 481 $this->_limitRows["{$result}"]=$rightStr; 482 } 483 else{ 484 $this->array_push_associative($this->_limitRows, array("{$result}"=>"{$rightStr}")) ; 485 } 486 487 if(!is_array($this->_numberOfRowsAskedFor)){ 488 $this->_numberOfRowsAskedFor=array(); 489 } 490 if (array_key_exists("{$result}",$this->_numberOfRowsAskedFor)){ 491 $this->_numberOfRowsAskedFor["{$result}"]="0"; 492 } 493 else{ 494 $this->array_push_associative($this->_numberOfRowsAskedFor, array("{$result}"=>"0")) ; 495 } 496 497 if(!is_array($this->_fastForwardRows)){ 498 $this->_fastForwardRows=array(); 499 } 500 if (array_key_exists("{$result}",$this->_fastForwardRows)){ 501 $this->_fastForwardRows["{$result}"]=$left; 502 } 503 else{ 504 $this->array_push_associative($this->_fastForwardRows, array("{$result}"=>"{$left}")) ; 505 } 506 507 //$this->array_push_associative($this->_numberOfRowsAskedFor, array("{$result}"=>"0")) ; 508 //$this->array_push_associative($this->_fastForwardRows, array("{$result}"=>"{$left}")) ; 509 510 //$this->_numberOfRowsAskedFor = 0; 511 //$this->_fastForwardRows=$left; 512 } 513 return $result; 514 } 515 else{ 516 return false; 517 } 518 519 520 521 522 } 523 524 /** 525 * Saves information to the database 526 * 527 * This mimicks the Replace Into mysql call to save a record into the 528 * database 529 * 530 * @param string $table The table to save to 531 * @param string $fields string Comma demlimited list of fields to save 532 * @param string $values Values to save to the database table 533 * 534 */ 535 function dbSave($table,$fields,$values) 536 { 537 if ($this->isVerbose()) { 538 $this->_errorlog("\n*** Inside database->dbSave ***<BR>"); 539 } 540 $tempFields=$fields; 541 $tempFields=str_replace(",","','",$tempFields); 542 //this query will return a row or rows of primary keys for this table for which we can determine 543 //if the current query requires a delete before its insertion 544 $sql="select colName from getPrimaryKey where tableName='{$table}' and colname in ('{$tempFields}')"; 545 $result=$this->dbQuery($sql); 546 $numRows=$this->dbNumRows($result); 547 548 if($numRows>0){ //this is the instance that there is a primary key we know about.. thus, find out the value of the 549 //primary key in the $values field and see what it equals.. do a delete first using this primary key's value 550 //and THEN do an insert. otherwise, just do the insert. 551 $valsArray=$this->parse_csv_sql_string($values); 552 $fieldsArray=$this->parse_csv_sql_string($fields); 553 $arrResults=$this->dbFetchArray($result); 554 $primaryKeyField=strtolower($arrResults[0]); 555 556 while ($colname = current($fieldsArray)) { 557 if ( strtolower($colname) == $primaryKeyField) { //we have a match 558 559 $sql="delete from {$table} where ". current($fieldsArray) . "='". current($valsArray) . "'"; 560 $this->dbQuery($sql); 561 break; 562 }//end if 563 next($fieldsArray); 564 next($valsArray); 565 }//end while 566 } 567 //now to just save/insert the new row. 568 $values=$this->cleanseSQL($values); 569 $values=str_replace("'",'"',$values); 570 $sql="EXEC doIndexInsert '{$table}', '{$fields}', '{$values}'"; 571 //$sql=$this->cleanseSQL($sql); 572 // echo "<XMP>" . $sql . "</XMP><HR>"; 573 // exit(0); 574 $result=$this->dbQuery($sql,1); 575 mssql_free_result($result); 576 if ($this->isVerbose()) { 577 $this->_errorlog("\n*** Leaving database->dbSave ***<BR>"); 578 } 579 } 580 581 582 583 584 585 /** 586 * Deletes data from the database 587 * 588 * This will delete some data from the given table where id = value. If 589 * id and value are arrays then it will traverse the arrays setting 590 * $id[curval] = $value[curval]. 591 * 592 * @param string $table Table to delete data from 593 * @param array|string $id field name(s) to include in where clause 594 * @param array|string $value field value(s) corresponding to field names 595 * @return boolean Returns true on success otherwise false 596 * 597 */ 598 function dbDelete($table,$id,$value) 599 { 600 if ($this->isVerbose()) { 601 $this->_errorlog("\n*** inside database->dbDelete ***<BR>"); 602 } 603 604 $sql = "DELETE FROM $table"; 605 606 if (is_array($id) || is_array($value)) { 607 if (is_array($id) && is_array($value) && count($id) == count($value)) { 608 // they are arrays, traverse them and build sql 609 $sql .= ' WHERE '; 610 for ($i = 1; $i <= count($id); $i++) { 611 if ($i == count($id)) { 612 $sql .= current($id) . " = '" . current($value) . "'"; 613 } else { 614 $sql .= current($id) . " = '" . current($value) . "' AND "; 615 } 616 next($id); 617 next($value); 618 } 619 } else { 620 // error, they both have to be arrays and of the 621 // same size 622 return false; 623 } 624 } else { 625 // just regular string values, build sql 626 if (!empty($id) && ( isset($value) || $value != "")) { 627 $sql .= " WHERE $id = '$value'"; 628 } 629 } 630 631 $this->dbQuery($sql); 632 633 if ($this->isVerbose()) { 634 $this->_errorlog("\n*** inside database->dbDelete ***<BR>"); 635 } 636 637 return true; 638 } 639 640 /** 641 * Changes records in a table 642 * 643 * This will change the data in the given table that meet the given criteria and will 644 * redirect user to another page if told to do so 645 * 646 * @param string $table Table to perform change on 647 * @param string $item_to_set field name of unique ID field for table 648 * @param string $value_to_set Value for id 649 * @param array|string $id additional field name used in where clause 650 * @param array|string $value additional values used in where clause 651 * @param boolean $supress_quotes if false it will not use '<value>' in where clause 652 * @return boolean Returns true on success otherwise false 653 * 654 */ 655 function dbChange($table,$item_to_set,$value_to_set,$id,$value, $supress_quotes=false) 656 { 657 if ($this->isVerbose()) { 658 $this->_errorlog("\n*** Inside dbChange ***"); 659 } 660 661 if ($supress_quotes) { 662 $sql = "UPDATE $table SET $item_to_set = $value_to_set"; 663 } else { 664 $sql = "UPDATE $table SET $item_to_set = '$value_to_set'"; 665 } 666 667 if (is_array($id) || is_array($value)) { 668 if (is_array($id) && is_array($value) && count($id) == count($value)) { 669 // they are arrays, traverse them and build sql 670 $sql .= ' WHERE '; 671 for ($i = 1; $i <= count($id); $i++) { 672 if ($i == count($id)) { 673 $sql .= current($id) . " = '" . current($value) . "'"; 674 } else { 675 $sql .= current($id) . " = '" . current($value) . "' AND "; 676 } 677 next($id); 678 next($value); 679 } 680 } else { 681 // error, they both have to be arrays and of the 682 // same size 683 return false; 684 } 685 } else { 686 // These are regular strings, build sql 687 if (!empty($id) && ( isset($value) || $value != "")) { 688 $sql .= " WHERE $id = '$value'"; 689 } 690 } 691 692 if ($this->isVerbose()) { 693 $this->_errorlog("dbChange sql = $sql<BR>"); 694 } 695 696 $this->dbQuery($sql); 697 698 if ($this->isVerbose()) { 699 $this->_errorlog("\n*** Leaving database->dbChange ***"); 700 } 701 702 } 703 704 /** 705 * Returns the number of records for a query that meets the given criteria 706 * 707 * This will build a SELECT count(*) statement with the given criteria and 708 * return the result 709 * 710 * @param string $table Table to perform count on 711 * @param array|string $id field name(s) of fields to use in where clause 712 * @param array|string $value Value(s) to use in where clause 713 * @return boolean returns count on success otherwise false 714 * 715 */ 716 function dbCount($table,$id='',$value='') 717 { 718 if ($this->isVerbose()) { 719 $this->_errorlog("\n*** Inside database->dbCount ***<br>"); 720 } 721 722 $sql = "SELECT COUNT(*) FROM $table"; 723 724 if (is_array($id) || is_array($value)) { 725 if (is_array($id) && is_array($value) && count($id) == count($value)) { 726 // they are arrays, traverse them and build sql 727 $sql .= ' WHERE '; 728 for ($i = 1; $i <= count($id); $i++) { 729 if ($i == count($id)) { 730 $sql .= current($id) . " = '" . current($value) . "'"; 731 } else { 732 $sql .= current($id) . " = '" . current($value) . "' AND "; 733 } 734 next($id); 735 next($value); 736 } 737 } else { 738 // error, they both have to be arrays and of the 739 // same size 740 return false; 741 } 742 } else { 743 if (!empty($id) && ( isset($value) || $value != "")) { 744 $sql .= " WHERE $id = '$value'"; 745 } 746 } 747 748 749 750 751 if ($this->isVerbose()) { 752 print "\n*** sql = $sql ***<br>"; 753 } 754 755 $result = $this->dbQuery($sql); 756 757 if ($this->isVerbose()) { 758 $this->_errorlog("\n*** Leaving database->dbCount ***<BR>"); 759 } 760 761 return ($this->dbResult($result,0)); 762 763 } 764 765 766 /* Copies a record from one table to another (can be the same table) 767 * 768 * This will use a INSERT INTO...SELECT FROM to copy a record from one table 769 * to another table. They can be the same table. 770 * 771 * @param string $table Table to insert record into 772 * @param string $fields Comma delmited list of fields to copy over 773 * @param string $values Values to store in database fields 774 * @param string $tablefrom Table to get record from 775 * @param array|string $id field name(s) to use in where clause 776 * @param array|string $value Value(s) to use in where clause 777 * @return boolean Returns true on success otherwise false 778 * 779 */ 780 function dbCopy($table,$fields,$values,$tablefrom,$id,$value) 781 { 782 if ($this->isVerbose()) { 783 $this->_errorlog("\n*** Inside database->dbCopy ***<BR>"); 784 } 785 786 $sql = "INSERT INTO $table ($fields) SELECT $values FROM $tablefrom"; 787 788 if (is_array($id) || is_array($value)) { 789 if (is_array($id) && is_array($value) && count($id) == count($value)) { 790 // they are arrays, traverse them and build sql 791 $sql .= ' WHERE '; 792 for ($i = 1; $i <= count($id); $i++) { 793 if ($i == count($id)) { 794 $sql .= current($id) . " = '" . current($value) . "'"; 795 } else { 796 $sql .= current($id) . " = '" . current($value) . "' AND "; 797 } 798 next($id); 799 next($value); 800 } 801 } else { 802 // error, they both have to be arrays and of the 803 // same size 804 return false; 805 } 806 } else { 807 if (!empty($id) && ( isset($value) || $value != "")) { 808 $sql .= " WHERE $id = '$value'"; 809 } 810 } 811 812 $this->dbQuery($sql); 813 $this->dbDelete($tablefrom,$id,$value); 814 815 if ($this->isVerbose()) { 816 $this->_errorlog("\n*** Leaving database->dbCopy ***<BR>"); 817 } 818 819 } 820 821 /** 822 * Retrieves the number of rows in a recordset 823 * 824 * This returns the number of rows in a recordset 825 * 826 * @param object $recordset The recordset to operate one 827 * @return int Returns number of rows otherwise false (0) 828 * 829 */ 830 function dbNumRows($recordset) 831 { 832 if ($this->isVerbose()) { 833 $this->_errorlog("\n*** Inside database->dbNumRows ***<BR>"); 834 } 835 836 // return only if recordset exists, otherwise 0 837 if ($recordset) { 838 if ($this->isVerbose()) { 839 $this->_errorlog('got ' . @mssql_num_rows($recordset) . ' rows'); 840 $this->_errorlog("\n*** Inside database->dbNumRows ***<BR>"); 841 } 842 if( (($this->_fastForwardRows["{$recordset}"]+$this->_limitRows["{$recordset}"])> @mssql_num_rows($recordset)) && $this->_fastForwardRows["{$recordset}"]!=0 ){ 843 //only return num rows - fast forwarded rows 844 845 return @mssql_num_rows($recordset)-$this->_fastForwardRows["{$recordset}"]+1; 846 } 847 elseif($this->_limitRows["{$recordset}"]>@mssql_num_rows($recordset) || $this->_limitRows["{$recordset}"]==''){ 848 return @mssql_num_rows($recordset); 849 } 850 else{ 851 return $this->_limitRows["{$recordset}"]; 852 } 853 854 855 } else { 856 if ($this->isVerbose()) { 857 $this->_errorlog("got no rows<BR>"); 858 $this->_errorlog("\n*** Inside database->dbNumRows ***<BR>"); 859 } 860 return 0; 861 } 862 } 863 864 /** 865 * Returns the contents of one cell from a mssql result set 866 * 867 * @param object $recordset The recordset to operate on 868 * @param int $row row to get data from 869 * @param string $field field to return 870 * @return (depends on field content) 871 * 872 */ 873 function dbResult($recordset,$row,$field=0) 874 { 875 if ($this->isVerbose()) { 876 $this->_errorlog("\n*** Inside database->dbResult ***<BR>"); 877 if (empty($recordset)) { 878 $this->_errorlog("\n*** Passed recordset isn't valid ***<br>"); 879 } else { 880 $this->_errorlog("\n*** Everything looks good ***<br>"); 881 } 882 $this->_errorlog("\n*** Leaving database->dbResult ***<br>"); 883 } 884 return @mssql_result($recordset,$row,$field); 885 } 886 887 /** 888 * Retrieves the number of fields in a recordset 889 * 890 * This returns the number of fields in a recordset 891 * 892 * @param object $recordset The recordset to operate on 893 * @return int Returns number of rows from query 894 * 895 */ 896 function dbNumFields($recordset) 897 { 898 return @mssql_num_fields($recordset); 899 } 900 901 /** 902 * Retrieves returns the field name for a field 903 * 904 * Returns the field name for a given field number 905 * 906 * @param object $recordset The recordset to operate on 907 * @param int $fnumber field number to return the name of 908 * @return string Returns name of specified field 909 * 910 */ 911 function dbFieldName($recordset,$fnumber) 912 { 913 return @mssql_field_name($recordset,$fnumber); 914 } 915 916 /** 917 * Retrieves returns the number of effected rows for last query 918 * 919 * Retrieves returns the number of effected rows for last query 920 * 921 * @param object $recordset The recordset to operate on 922 * @return int Number of rows affected by last query 923 * 924 */ 925 function dbAffectedRows($recordset) 926 { 927 return @mssql_rows_affected(); 928 } 929 930 /** 931 * Retrieves record from a recordset 932 * 933 * Gets the next record in a recordset and returns in array 934 * 935 * @param object $recordset The recordset to operate on 936 * @param boolean $both get both assoc and numeric indices 937 * @return array Returns data array of current row from recordset 938 * 939 */ 940 //here! 941 function dbFetchArray($recordset, $both = false) 942 { 943 $this->_numberOfRowsAskedFor["{$recordset}"] = $this->_numberOfRowsAskedFor["{$recordset}"] +1; 944 if( ($this->_limitRows["{$recordset}"]) !=($this->_numberOfRowsAskedFor["{$recordset}"]-1) || $this->_limitRows["{$recordset}"]=='' || $this->_limitRows["{$recordset}"]==0){ 945 //echo $this->dbNumRows($recordset) ."-{$this->_numberOfRowsAskedFor["{$recordset}"]}-{$this->_limitRows["{$recordset}"]}<HR>"; 946 return @mssql_fetch_array($recordset);//, $result_type); 947 } 948 else{ 949 return FALSE; 950 } 951 952 953 954 955 } 956 957 /** 958 * Returns the last ID inserted 959 * 960 * Returns the last auto_increment ID generated 961 * 962 * @param resource $link_identifier identifier for opened link 963 * @return int Returns last auto-generated ID 964 * 965 * please note that this is a dangerous function to use without providing the proper resource identifier 966 * your code should ALWAYS call this function with its resource identifier otherwise you'll just pull off the 967 * last insertted ID which MAY or MAY NOT be the one from YOUR specific insert. You've been warned! 968 */ 969 function dbInsertId($link_identifier ='') 970 { 971 972 if ($link_identifier ==''){ //wow is this dangerous... 973 //this is the LAST insertted ID no matter what.. thus, this may not be your resource's insertted id.. 974 //you should, and I'm only telling you this once, ALWAYS USE YOUR RESOURCE IDENTIFIER when calling this function 975 return $this->_NoArraylastInsertID; 976 } 977 else{ 978 return $this->_lastInsertID["{$link_identifier}"]; 979 } 980 } 981 982 /** 983 * this does not have any use in mssql. but kept here for reference 984 * returns a database error string 985 * 986 * Returns an database error message 987 * 988 * @param string $sql SQL that may have caused the error 989 * @return string Text for error message 990 * 991 */ 992 function dbError($sql='') 993 { 994 if (trim(mssql_get_last_message())!='') { 995 $this->_errorlog(@mssql_get_last_message() . ': ' . @mssql_get_last_message() . ". SQL in question: $sql"); 996 if ($this->_display_error) { 997 return @mssql_get_last_message() . ': ' . @mssql_get_last_message(); 998 } else { 999 return 'An SQL error has occurred. Please see error.log for details.'; 1000 } 1001 } 1002 1003 return; 1004 } 1005 1006 1007 1008 /** 1009 * returns a sql string that has the pesky date_sub removed from it 1010 * 1011 * 1012 * @param string $sql SQL that needs conversion 1013 * @return string return sql string 1014 * 1015 */ 1016 function cleanse_date_sub($sql){ 1017 $string=$sql; 1018 $testString=strtolower($string); 1019 $isIn=strpos($testString,'date_sub'); 1020 1021 while($isIn>0){ 1022 $testString=strtolower($string); 1023 $isIn=strpos($testString,'date_sub'); 1024 if($isIn>0){ 1025 1026 $startLoc=strpos($testString,'date_sub'); 1027 $rightStr=ltrim(substr($string,$startLoc+8,strlen($string))); 1028 1029 //eregi("\((.*),([^\)]+\))", $rightStr,$left); 1030 eregi("\(([^,]+),([^\)]+\))", $rightStr,$left); 1031 1032 1033 1034 $firstParm=$left[1]; 1035 $secondParm=trim($left[2]); 1036 1037 $secondParm=str_replace("interval","",$secondParm); 1038 $secondParm=str_replace(")","",$secondParm); 1039 $left[2]=str_replace(")","",$left[2]); 1040 1041 $testMode=strpos($string,'date_sub'); 1042 if($testMode>0){ 1043 $mode=0; 1044 } 1045 else{ 1046 $mode=1; 1047 } 1048 1049 if($mode==0){ 1050 $replaceString='date_sub(' . $firstParm . ',' . $left[2] . ')'; 1051 } 1052 else{ 1053 $replaceString='DATE_SUB(' . $firstParm . ',' . $left[2] . ')'; 1054 } 1055 1056 $secondParmArray=split(" ",$secondParm); 1057 $intervalTime=$secondParmArray[1]; 1058 $typeForInterval=$secondParmArray[2]; 1059 if($intervalTime>0){ 1060 $intervalTime = '-' . $intervalTime; 1061 } 1062 $replaceWITHString= "dateadd({$typeForInterval},{$intervalTime},{$firstParm})"; 1063 1064 $string=str_replace($replaceString,$replaceWITHString,$string); 1065 1066 //$replaceString='DATE_SUB(' . $firstParm . ',' . $left[2] . ')'; 1067 //$string=str_replace($replaceString,$replaceWITHString,$string); 1068 1069 $tempsql=str_replace("now()","getDate()",$string); 1070 $string=$tempsql; 1071 } 1072 1073 } 1074 return $string; 1075 1076 } 1077 1078 1079 1080 1081 1082 1083 //since nothing can do this properly, i had to write it myself. 1084 //trick is that a string csv may have a comma within a delimited csv field which explode 1085 //cant handle 1086 function parse_csv_sql_string($csv){ 1087 $len=strlen($csv); 1088 $mode=0; //mode=0 for non string, mode=1 for string 1089 $retArray=array(); 1090 $thisValue=''; 1091 for($x=0;$x<$len;$x++){ 1092 //loop thru the string 1093 if($csv[$x]=="'"){ 1094 if($x!=0){ 1095 if($csv[$x-1]!="\\"){ 1096 //this means that the preceeding char is not escape.. thus this is either the end of a mode 1 or the beginning of a mode 1 1097 if($mode==1){ 1098 $mode=0; 1099 //this means that we are done this string value 1100 //dont add this character to the string 1101 } 1102 else{ 1103 $mode=1; 1104 //dont add this character to the string.... 1105 } 1106 } 1107 else{//this is a character to add..... 1108 $thisValue=$thisValue . $csv[$x]; 1109 } 1110 } 1111 else{//x==0 1112 $mode=1; 1113 } 1114 }//end if csv 1115 elseif($csv[$x]==","){ 1116 if($mode==1){ 1117 //this means that the comma falls INSIDE of a string. its a keeper 1118 $thisValue=$thisValue . $csv[$x]; 1119 } 1120 else{//this is the dilineation between fields.. pop this value 1121 array_push($retArray, $thisValue); 1122 $thisValue=''; 1123 $mode=0; 1124 } 1125 }//end elseif 1126 1127 else{ 1128 //just add it! 1129 $thisValue=$thisValue . $csv[$x]; 1130 }//end else 1131 }//end for 1132 array_push($retArray, $thisValue); 1133 return $retArray; 1134 }//end function 1135 1136 1137 1138 //thanks to php.net for this 1139 function array_push_associative(&$arr) { 1140 $args = func_get_args(); 1141 foreach ($args as $arg) { 1142 if (is_array($arg)) { 1143 foreach ($arg as $key => $value) { 1144 @$arr[$key] = $value; 1145 $ret++; 1146 } 1147 }else{ 1148 @$arr[$arg] = ""; 1149 } 1150 } 1151 return $ret; 1152 } 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 /** 1164 * Lock a table 1165 * 1166 * Locks a table for write operations 1167 * 1168 * @param string $table Table to lock 1169 * @return void 1170 * @see dbUnlockTable 1171 * 1172 */ 1173 function dbLockTable($table) 1174 { 1175 if ($this->isVerbose()) { 1176 $this->_errorlog("\n*** Inside database->dbLockTable ***"); 1177 } 1178 1179 1180 $sql = "BEGIN TRAN"; 1181 1182 $this->dbQuery($sql); 1183 1184 if ($this->isVerbose()) { 1185 $this->_errorlog("\n*** Leaving database->dbLockTable ***"); 1186 } 1187 } 1188 1189 /** 1190 * Unlock a table 1191 * 1192 * Unlocks a table after a dbLockTable (actually, unlocks all tables) 1193 * 1194 * @param string $table Table to unlock (ignored) 1195 * @return void 1196 * @see dbLockTable 1197 * 1198 */ 1199 function dbUnlockTable($table) 1200 { 1201 if ($this->isVerbose()) { 1202 $this->_errorlog("\n*** Inside database->dbUnlockTable ***"); 1203 } 1204 1205 $sql = 'COMMIT TRAN'; 1206 1207 $this->dbQuery($sql); 1208 1209 if ($this->isVerbose()) { 1210 $this->_errorlog("\n*** Leaving database->dbUnlockTable ***"); 1211 } 1212 } 1213 1214 1215 }//end class 1216 1217 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Wed Nov 21 12:27:40 2007 | par Balluche grâce à PHPXref 0.7 |
|
|