1<?php 2/** 3* Alias per vmsql.mysqli.php 4* 5* @package VFront 6* @subpackage DB-Libraries 7* @author Mario Marcello Verona <marcelloverona@gmail.com> 8* @copyright 2007-2010 M.Marcello Verona 9* @version 0.96 $Id: vmsql.mysqlold.php 933 2011-03-26 15:48:54Z marciuz $ 10* @see vmsql.mysqli.php 11* @license http://www.gnu.org/licenses/gpl.html GNU Public License 12*/ 13 14 15 16class mysql_vmsql { 17 18 public $vmsqltype='mysql'; 19 20 public $link_db; 21 22 protected $connected=false; 23 24 protected $error_handler=null; 25 26 protected $last_error=null; 27 28 29 30 /** 31 * @desc DB Connection 32 * @param array $array_db 33 * @return resource 34 */ 35 public function connect($array_db){ 36 37 $this->link_db = @mysql_connect($array_db['host'].":".$array_db['port'], 38 $array_db['user'], 39 $array_db['passw']); 40 41 if(!is_resource($this->link_db)){ 42 43 die("Connection error: is MySQL running? Otherwise please check your conf file"); 44 } 45 46 @mysql_select_db($array_db['dbname'],$this->link_db) or die(_("Database does not exist")); 47 48 $this->connected=true; 49 50 return $this->link_db; 51 52 } 53 54 55 /** 56 * @desc Esegue una query $sql 57 * @param string $sql 58 * @param bool $transazione 59 * @return object 60 */ 61 public function query($sql,$transazione=false){ 62 63 $getmicro=microtime(true); 64 65 $obj = @mysql_query($sql,$this->link_db) or $this->error($sql); 66 67 if($GLOBALS['DEBUG_SQL']){ 68 $GLOBALS['DEBUG_SQL_STRING'][] = round((microtime(true) - $getmicro),4) . " --- ". $sql; 69 } 70 71 return $obj; 72 73 } 74 75 76 77 /** 78 * Esegue una query $sql e restisce vero|falso a seconda dell'esito 79 * il secure_mode (di default) permette l'uso di sole query SELECT. 80 * Se l'sql contiene errori la funzione restituisce false, ma l'esecuzione prosegue. 81 * 82 * @param string $sql Query SQL da testare 83 * @param bool $secure_mode Imposta il secure mode per le query, invalidando tutte le query con comandi pericolosi 84 * @return bool Esito della query 85 */ 86 public function query_try($sql,$secure_mode=true){ 87 88 $sql=trim(str_replace(array("\n","\r")," ",$sql)); 89 90 if($secure_mode){ 91 // piccolo accorgimento per la sicurezza... 92 if(!preg_match("'^SELECT 'i",$sql)) return 0; 93 $sql2=preg_replace("'([\W](UPDATE)|(DELETE)|(INSERT)|(DROP)|(ALTER)|(UNION)|(TRUNCATE)|(SHOW)|(CREATE)|(INFORMATION_SCHEMA)[\W])'ui","",$sql); 94 if($sql2!=$sql){ 95 return -1; 96 } 97 } 98 99 if(is_resource($this->link_db)){ 100 $res = @mysql_query($sql,$this->link_db) or $this->error($sql); 101 if($res) @mysql_free_result($res); 102 103 if($GLOBALS['DEBUG_SQL']){ 104 $GLOBALS['DEBUG_SQL_STRING'][] = round((microtime(true) - $getmicro),4) . " --- ". $sql; 105 } 106 } 107 108 return ($res) ? 1:0; 109 } 110 111 112 113 /** 114 * @return array 115 * @param resource $res 116 * @desc Funzione di fetch_row 117 */ 118 public function fetch_row(&$res){ 119 120 if(is_resource($res)){ 121 122 $RS= @mysql_fetch_row($res); 123 if($RS) return $RS; 124 else return false; 125 126 } 127 128 129 } 130 131 /** 132 * @return array 133 * @param resource $res 134 * @desc Funzione di fetch_assoc 135 */ 136 public function fetch_assoc(&$res){ 137 138 if(is_resource($res)){ 139 140 $RS= @mysql_fetch_assoc($res); 141 if($RS) return $RS; 142 else return false; 143 144 } 145 146 147 } 148 149 150 /** 151 * @return array 152 * @param resource $res 153 * @desc Funzione di fetch_array 154 */ 155 public function fetch_array(&$res){ 156 157 if(is_resource($res)){ 158 159 $RS= @mysql_fetch_array($res); 160 if($RS) return $RS; 161 else return false; 162 163 } 164 165 166 } 167 168 169 /** 170 * @desc Funzione di fetch_object 171 * @return object 172 * @param resource $res 173 */ 174 public function fetch_object(&$res,$class_name=null){ 175 176 if(is_object($res)){ 177 178 $RS= ($class_name==null) ? @mysql_fetch_object($res) : @mysql_fetch_object($res, $class_name); 179 180 if($RS) return $RS; 181 else return false; 182 } 183 } 184 185 186 187 public function num_rows(&$res){ 188 189 if(is_resource($res)){ 190 return @mysql_num_rows($res); 191 192 } 193 } 194 195 196 /** 197 * @return int 198 * @param resource $res 199 * @desc Funzione di insert ID che restituisce l'ultimo ID autoincrement inserito (MySQL) 200 */ 201 public function insert_id(){ 202 203 204 if(is_resource($this->link_db)){ 205 return @mysql_insert_id($this->link_db); 206 207 } 208 209 } 210 211 212 213 /** 214 * @return int 215 * @desc Funzione affected rows 216 */ 217 public function affected_rows(){ 218 219 220 if(is_resource($this->link_db)){ 221 return @mysql_affected_rows($this->link_db); 222 223 } 224 225 } 226 227 228 229 public function list_tables($dbname){ 230 231 return @mysql_list_tables($dbname); 232 233 } 234 235 236 237 public function num_fields($dbname){ 238 239 return @mysql_num_fields($dbname); 240 241 } 242 243 244 public function fetch_field($dbname){ 245 246 return @mysql_fetch_field($dbname); 247 248 } 249 250 251 /** 252 * @desc Funzione di free_result 253 * @return void 254 * @param string $result 255 */ 256 public function free_result($result){ 257 258 mysql_free_result($result); 259 } 260 261 262 263 264 265 266 ######################################################################################### 267 # 268 # 269 # FUNZIONI DI ELABORAZIONE 270 # 271 272 273 /** 274 * Recupera informazioni dal file e dalla query ed apre la funzione openError del file design/layouts.php dove cancella il buffer e manda a video l'errore codificato 275 * 276 * @return void 277 * @param string $sql 278 * @desc Handler degli errori per le query. 279 */ 280 public function error($sql, $message=''){ 281 282 if(!is_object($this->error_handler)){ 283 284 $this->error_handler= new stdClass(); 285 286 $this->error_handler->dbtype=$this->vmsqltype; 287 $this->error_handler->errors=array(); 288 } 289 290 $trace=debug_backtrace(); 291 $last=count($trace)-1; 292 $file_line=str_replace(FRONT_ROOT, '', $trace[$last]['file']).":".$trace[$last]['line']; 293 294 $ee=array('date'=>date("c"), 295 'sql'=>$sql, 296 'code'=>mysql_errno($this->link_db), 297 'msg'=>mysql_error($this->link_db), 298 'file'=>$file_line 299 ); 300 301 $this->error_handler->errors[]=$ee; 302 303 $this->last_error=$ee; 304 305 306 if(isset($GLOBALS['DEBUG_SQL']) && $GLOBALS['DEBUG_SQL']){ 307 308 $this->error_debug(); 309 } 310 else{ 311 312 if(!function_exists('openError')){ 313 include_once(FRONT_REALPATH."/inc/layouts.php"); 314 } 315 // richiamo la funzione openError 316 openError($this->last_error); 317 exit; 318 } 319 320 321 } 322 323 324 public function error_debug($format='string'){ 325 326 if($format=='string'){ 327 328 var_dump($this->last_error); 329 } 330 331 } 332 333 334 335 336 /** 337 * @return matrix 338 * @param matrix $matrix 339 * @desc restituisce una traslata della matrice partendo da indici numerici 340 */ 341 public function reverse_matrix($matrix){ 342 343 if(!is_array($matrix)) return false; 344 345 $keys = array_keys($matrix[0]); 346 347 for($i=0;$i<count($matrix);$i++){ 348 349 for($j=0;$j<count($keys);$j++) $rev[$keys[$j]][$i] = $matrix[$i][$keys[$j]]; 350 } 351 352 return $rev; 353 } 354 355 356 357 358 /** 359 * @return array (matrice) 360 * @param resource $res 361 * @desc Funzione utility di fetch_assoc che restituisce tutta la matrice dei risultati 362 */ 363 public function fetch_assoc_all(&$res, $reverse=false){ 364 365 if(is_resource($res)){ 366 367 $matrice=array(); 368 369 while($RS= $this->fetch_assoc($res)) $matrice[]=$RS; 370 371 $this->free_result($res); 372 373 if($reverse) 374 return $this->reverse_matrix($matrice); 375 376 else 377 return $matrice; 378 379 } 380 } 381 382 383 384 385 /** 386 * @return resource 387 * @param resource $res 388 * @desc Funzione utility di fetch_row che restituisce tutta la matrice dei risultati 389 */ 390 public function fetch_row_all(&$res,$reverse=false){ 391 392 if(is_resource($res)){ 393 394 while($RS= $this->fetch_row($res)) $matrice[]=$RS; 395 396 $this->free_result($res); 397 398 if($reverse) 399 return $this->reverse_matrix($matrice); 400 401 else 402 return $matrice; 403 404 } 405 } 406 407 408 409 410 /** 411 * @desc Funzione di transazione che corrisponde ad un BEGIN 412 */ 413 public function begin(){ 414 415 $this->query("BEGIN"); 416 } 417 418 /** 419 * @desc Funzione di transazione di ROLLBACK 420 */ 421 public function rollback(){ 422 423 $this->query("ROLLBACK"); 424 } 425 426 427 /** 428 * @desc Funzione di transazione di COMMIT 429 */ 430 public function commit(){ 431 432 $this->query("COMMIT"); 433 } 434 435 436 /** 437 * Funzione di utilità 438 * Testa l'esistenza di un $valore (di solito l'ID) nel $campo di una $tabella, 439 * con eventuali clausole $and 440 * 441 * @param string $campo 442 * @param mixed $valore_id 443 * @param string $tabella 444 * @param string $and 445 * @return bool 446 */ 447 public function test_id($campo,$valore_id,$tabella,$and="",$secure_test=false){ 448 449 $sql= "SELECT * FROM $tabella WHERE $campo=$valore_id $and"; 450 451 if($secure_test){ 452 if($this->query_try($sql)){ 453 $q=$this->query($sql); 454 455 return ($this->num_rows($q)>0) ? true:false; 456 } 457 else return null; 458 } 459 else{ 460 461 $q=$this->query($sql); 462 return ($this->num_rows($q)>0) ? true:false; 463 } 464 } 465 466 467 468 469 470 /** 471 * @desc Esegue diverse query $sql (forzata per mysql e sperimentale) 472 * @param string $sql 473 * @return object 474 */ 475 public function multi_query($sql_multi){ 476 477 $sql_array=explode(";",$sql_multi); 478 479 foreach ($sql_array as $sql){ 480 481 $obj[] = @mysql_query($sql,$this->link_db) or $this->error($sql,mysql_error($this->link_db),false); 482 483 if($GLOBALS['DEBUG_SQL']){ 484 $getmicro=microtime(true); 485 $GLOBALS['DEBUG_SQL_STRING'][] = round((microtime(true) - $getmicro),3) . " --- ". $sql; 486 } 487 } 488 489 return $obj; 490 491 } 492 493 494 /** 495 * Funzione di fetch row in caso di multiple query (forzata per mysql e sperimentale) 496 * 497 * @param resource $res 498 * @return array 499 */ 500 public function fetch_row_multi(&$res){ 501 502 503 $output=array(); 504 $res =(!is_array($res)) ? $res=array() : $res; 505 506 foreach ($res as $result){ 507 /* store first result set */ 508 while ($row = mysql_fetch_row($result)) { 509 $output[]=$row; 510 } 511 512 } 513 514 return $output; 515 } 516 517 /** 518 * Escape function 519 * 520 * @param string $string 521 * @return string 522 */ 523 public function escape($string=null){ 524 525 return mysql_real_escape_string(stripslashes($string),$this->link_db); 526 } 527 528 529 /** 530 * Unescape function 531 * 532 * @param string $string 533 * @return string 534 */ 535 public function unescape($string=null){ 536 537 return str_replace(mysql_real_escape_string("'",$this->link_db),"'",$string); 538 } 539 540 541 542 543 /** 544 * Recursive escape. Work on strings, numbers, array, objects 545 * 546 * @param mixed $mixed 547 * @return mixed 548 */ 549 public function recursive_escape($mixed){ 550 551 if(is_string($mixed)){ 552 553 $escaped= $this->escape($mixed); 554 } 555 else if(is_numeric($mixed)){ 556 557 $escaped= $mixed; 558 } 559 else if(is_array($mixed)){ 560 561 foreach ($mixed as $k=>$val) 562 $escaped[$k]=$this->recursive_escape($val); 563 } 564 else if(is_object ($mixed)){ 565 566 foreach ($mixed as $k=>$val) 567 $escaped->{$k}=$this->recursive_escape($val); 568 } 569 570 return $escaped; 571 } 572 573 574 575 /** 576 * Concat DB sintax 577 * 578 * @param string $args 579 * @param string $args 580 * @return string 581 */ 582 public function concat($args,$as=''){ 583 584 $str="CONCAT($args)"; 585 586 if($as!='') $str.=" AS $as"; 587 588 return $str; 589 } 590 591 592 /** 593 * Set the LIMIT|OFFSET sintax 594 * 595 * @param int $limit 596 * @param int $offset 597 * @return string 598 */ 599 public function limit($limit,$offset=''){ 600 601 if($offset!='') $str="LIMIT $offset,$limit"; 602 else $str="LIMIT $limit"; 603 604 return $str; 605 } 606 607 608 609 public function db_version(){ 610 611 $q=$this->query("SELECT VERSION()"); 612 list($db_version)=$this->fetch_row($q); 613 614 return $db_version; 615 } 616 617 618 619 public function close(){ 620 621 if($this->error_handler!==null) db_error_log($this->error_handler); 622 623 return ($this->connected) ? mysql_close($this->link_db) : null; 624 } 625 626 /** 627 * For Oracle and MySQLi compatibility 628 * 629 * @param statement $stmt 630 * @return bool 631 */ 632 public function stmt_close($stmt){ 633 634 return true; 635 } 636 637 public function __destruct() { 638 639 $this->close(); 640 } 641 642 643 644} 645?>