mysql-wrapper-class.php

<?php
/*
#   
#   Trida zajistujici praci s databazi.
#   Pokud se planuje pouziti jine databaze, pak je nutne vytvorit kopii teto tridy s prikazi pro zvolenou
#   databazi, ulozit ji pod nazvem_databaze.class.php
#   
*/

class MySQL { 

	private $nastaveni = array();
	private $handler = false;
	private $loguj = false;
	private $mysqlshow = false;
	private $mysqlshowGet = true;
	private $pdo = NULL;
	private $lastExec = NULL;
	private $prefix = "";
	private $lastResult = NULL;
	public $show_errors = false;
	
    public $show_sql = false;
        
	private $organizeByColumn = false;
	private $organizeOnlyColumn = false;
	
        private $profilTable = array ();
        const DO_PROFILING_DEATH = false;
        const PROFILING_FILE = "mysql_profiling.txt";
        
        public $REDIS = false;
        
    # pripojeni k databazi
	function __construct ( $nastaveni = NULL, $loguj = false, $REDIS = false ){
		
            if ( $REDIS )
                $this -> REDIS = $REDIS;

            if ( isset ( $_GET["mysqlshow"] ) and $this -> mysqlshowGet )
                $this -> mysqlshow = true;

            $this -> nastaveni = $nastaveni;
            $this -> loguj = $loguj;

            if ( $nastaveni === NULL ) {

                    echo "Nebylo nastaveno pripojeni k databazi.";
                    exit;
            }

            if ( defined ( "DB_SHOW_ERRORS") and DB_SHOW_ERRORS )
                $this -> show_errors = true;

            try {
                $this -> pdo = new PDO( "mysql:host=". $nastaveni["SQL_HOST"].";dbname=".$nastaveni["SQL_DBNAME"], $nastaveni["SQL_USERNAME"], $nastaveni["SQL_PASSWORD"] );
                unset ( $this -> nastaveni["SQL_PASSWORD"] );

            } catch ( PDOException $e ) {
                die ( "Cant connect to database." );
            }

            $error = $this -> pdo -> errorInfo();
            if( $error[0] != "") {
                    print "<p>DATABASE CONNECTION ERROR:</p>";
                    die ( print_r($error, true ) );
            }

            $this -> sql ( "SET character_set_client=" . (( isset ( $nastaveni["character_set_client"] ) ) ? $nastaveni["character_set_client"] : "'utf8'" ));
            $this -> sql ( "SET character_set_connection=" . ( ( isset ( $nastaveni["character_set_connection"] ) ) ? $nastaveni["character_set_connection"] : "'utf8'" ) );
            $this -> sql ( "SET character_set_results=" . ( ( isset ( $nastaveni["character_set_results"] ) ) ? $nastaveni["character_set_results"] : "'utf8'"  ) );

            if ( isset ( $_GET["mysqlshow"] ) and isset ( $nastaveni["mysqlshow"] ) and $nastaveni["mysqlshow"] === true  ) $this -> mysqlshow = true;
	}
	
	function getError () {
		
		return $this -> pdo -> errorInfo();
	}
	
	function processParams ( $params ) {
		
		$newParam = array ();
		foreach ( $params as $var => $val ) {
			
			if ( ! is_int ( $var ) )
				$newParam[":".$var] = $val;
			else
				$newParam[$var] = $val;
		}
		return $newParam;
	}
	
	function setFetchByColumn ( $cn ) {
	
		$this -> organizeByColumn = $cn;
	}
	
	function setFetchOnlyColumn ( $cn ) {
	
		$this -> organizeOnlyColumn = $cn;
	}
	
	function fetchByColumn ( $data, $cn ) {
		$newData = array ();
		
		foreach ( $data as $var => $val ) {
			
			if ( ! isset ( $val[$cn] ) ) {
				
				echo "Column '".$cn."' not find.<br />";
				return false;
			}	
			
			if ( $this -> organizeOnlyColumn ) {
				
				if ( ! isset ( $val[ $this -> organizeOnlyColumn] ) )
					return false;
						
				$newData[ $val[ $cn ] ] = $val[ $this -> organizeOnlyColumn];
			} else
				$newData[ $val[ $cn ] ] = $val;
		}
		$this -> organizeByColumn = false;
		return $newData;
	}
	
	function execPDO ( $SQL, $params ) {
		
            $fetched = false;

            if ( self::DO_PROFILING_DEATH ) {

                if ( ! isset ( $this ->profilTable[$SQL]  ))
                    $this ->profilTable[$SQL]  = 0;

                $this ->profilTable[$SQL]++;
            }

            try {  

                if ( $this -> REDIS ) {

                    $c = $this -> REDIS -> dbGetCache ( $SQL, $params );
                    if ( $c )
                        return $c;
                }

                $ps = $this -> pdo-> prepare( $SQL);
                $params = $this -> processParams ( $params );

                if ( $this -> mysqlshow or $this -> show_sql) 
                        echo $SQL." | Params: " . print_r ( $params, true )."<br />\n";

                if ( $ps ) {
                        $this -> lastExec = $ps -> execute( $params );
                }

                if ( $ps ->errorCode() != '0000' and $this -> show_errors )
                {
                        echo "PDOStatement::errorCode(): ";
                        print_r( $ps ->errorInfo() );

                        $backtrace = debug_backtrace();
                        if ( isset ( $backtrace[1] ) ){
                                 $c = $backtrace[1];
                                 echo "FILE: ".$c["file"]." | Line: ".$c["line"] ." | Function: " .$c["function"]."<br />\n";
                        }


                        return false;
                } 

                $ps -> setFetchMode(PDO::FETCH_ASSOC );
                $this -> lastResult = $ps;
                $fetched = $ps ->fetchAll();

                if ( $this -> organizeByColumn != false )
                        $fetched = $this -> fetchByColumn ( $fetched, $this -> organizeByColumn );

            } catch( PDOException $e) {  

                echo $e->getMessage();
                return false;
            }  


            if ( $this -> REDIS )
                $this -> REDIS -> dbSaveCache ( $SQL, $params, $fetched );

            return $fetched;
	}
	
	# vnitrni funkce databazove tridy
	function proved_sql ( $sql, $args ) {
        	$args = $this -> getArguments ( $args );
		$vysledek = $this -> execPDO ( $sql, $args );
		
		return $vysledek;
	}
	
	function sql ( $sql, $args = null ) {
        	
		return $this -> proved_sql ( $sql, $args );
	}
    
    function processData ( $vysledek ) {
    
    
	$i = 0;
        while ( $udaje[$i++] = mysql_fetch_assoc($vysledek) );
	if ( $i == 1) return false;
	
	/*
	#	zmena - 21.8.2010
	#	Smazani posledniho (vzdy) prazdneho prvku pole.
	*/
	unset ( $udaje[$i-1] );
	return $udaje;
    }
    function writeToLog ( $line) {
	return false;
		if ( ! $fh = fopen( DIR."logs/mysql.log", 'a+') )
			return false;
		
		$line = date ( "d.m.Y h.i.s" )."|".$line;
		
		if ( isset ( $_SERVER["REMOTE_ADDR"] ) )
			$line = $_SERVER["REMOTE_ADDR"]."|".$line;
		
		fwrite( $fh, $line);
		fclose( $fh ); 

		return true;
	}
    
    # insert do databaze
    function insert ($tabulka, $hodnoty ) {
      	$params = array ();
		$paramsIndex = 0;
		$str1 = $str2 = "";
	
        foreach ( $hodnoty as $var =>  $val ) {
		
		if ( $var[0] == "." ) {
			
			$var = ltrim ( $var, "." );
			
			$str1 .= "`{$var}`, ";
			$str2 .= "{$val} , ";
		} else {
		
			$str1 .= "`{$var}`, ";
			$str2 .= "? , ";
			$params[ $paramsIndex++ ] = $val;
		}
        }

        $str1 = trim ( $str1, ", " );
        $str2 = trim ( $str2, ", " );

        $sql = "INSERT INTO {$tabulka} ( ";
        $sql .= $str1;
        $sql .= ") VALUES ( ";
        $sql .= $str2;
        $sql .= " );";
	
	$this -> execPDO ( $sql, $params );
	
	return $this -> lastExec;
    }
    
	function comma ( $str ) {
	
		return "'".$str."'";
	}

	function commaAll ( $arr ) {
		
		foreach ( $arr as $var =>  $val )
			$arr[$var] = $this -> comma ( $val );
			
		return $arr;
	}
	
    # update radku v databazi
    function update  ($tabulka, array $hodnoty, $podminky, $args =  array(), $limit = false ) {
	
	$str1 = $str2 = "";
	$params = array ();
	$paramsIndex = 0;
      
	
	$args = $this -> getArguments ( $args );
	
        foreach ( $hodnoty as $var =>  $val ) {
		
		
		if ( $var[0] == "." ) {
			
			$var = ltrim ( $var, "." );
			$str1 .= "`{$var}` = {$val}  , ";
		} else {
		
			$str1 .= "`{$var}` = ? , ";
			$params[ $paramsIndex++ ] = $val;
		}
		
		
        }
	
        foreach ( $args as $var =>  $val ) {
		if ( is_string ( $var ) )
			$params[ $var ] = $val;
		else
			$params[ $paramsIndex++ ] = $val;
        }
	
        $str1 = trim ( $str1, ", " );
	
        $sql = "UPDATE {$tabulka} SET ";
        $sql .= $str1;
        $sql .= " WHERE ";
        $sql .= $podminky ;
	
        if ( isset ( $limit ) and is_int ( $limit ) ) 
		$sql .= " LIMIT ".$limit;
        $sql .= ";"; 
	
	$this -> execPDO ( $sql, $params );
   
	return $this -> lastExec;
    }
	
	function getRow () {
		
		if ( ! $this ->lastResult ) 
			return false;
		
		$data = $this ->lastResult -> fetchAll();
		return $data[0];
	}
	
	function getRows () {
		
		if ( ! $this ->lastResult ) 
			return false;
		
		$data = $this ->lastResult -> fetchAll();
		return $data;
	}
	
	
	# podle, smer 
	function getDirection ( $podle = "", $smer = "" ) {
		
		$razeni = "";
		if( $podle != "" ) {
			$razeni = "ORDER BY $podle";
			
			if ( $smer != "" )
				$razeni .= " ".$smer;
		}
		
		return $razeni;
	}
    
	function getArguments ( $args = array () ) {
		$newArgs = array ();

		if ( $args === NULL ) 
			$args = array ();

		if ( ! is_array ( $args ) )
			$newArgs[0] = $args;
		else
			$newArgs = $args;
		
		return $newArgs;
	}
    # vypisovaci funkce:
    function get ( $co, $kde, $podminka = "1=1", $args = array () , $podle = "", $smer = "", $limit = 1 ) {
        
        $args = $this -> getArguments ( $args );
        $razeni = $this -> getDirection ( $podle, $smer );
        $sql = "SELECT $co FROM {$kde} WHERE $podminka ".$razeni." LIMIT {$limit};";

        $vysledek = $this -> execPDO ( $sql, $args );
        if ( ! $vysledek ) 
		return false;
	
	if ( $limit == 1 ) 
		return $vysledek[0];
	
	return $vysledek;
    }
    
   function getItem ( $co, $kde, $podminka, $args = array () , $podle = "", $smer = "", $limit = 1 ) {
   
	return $this -> get (  $co, $kde, $podminka, $args , $podle, $smer, $limit );
   }
    
   function getValue ( $co, $kde, $podminka, $args = array () , $podle = "", $smer = "", $limit = 1 ) {
   
	$c = $this -> get (  $co, $kde, $podminka, $args , $podle, $smer, $limit );
	if ( isset ( $c[$co] ) )
		return $c[$co];
	return $c;
   }
   
    function getData ($co, $kde, $podminka = "1=1", $args = array (),  $podle = "", $smer = "", $limit = "" ){
        if ( $limit != "" ) $limit = " LIMIT ".$limit;
	
	$args = $this -> getArguments ( $args );
	$razeni = $this -> getDirection ( $podle, $smer );
	
        $sql = "SELECT $co FROM {$kde} WHERE $podminka ".$razeni.$limit.";";
	$vysledek = $this -> execPDO ( $sql, $args );
	
	return $vysledek;
    }
    
    function getDataBySet ($co, $kde, $podminka, $set, $podle = "", $smer = "", $limit = "" ){
        if ( $limit != "" ) $limit = " LIMIT ".$limit;
        $razeni = $this -> getDirection ( $podle, $smer );
	
	foreach ( $set as $var => $val ) {
		$c = implode ( ", ", $val );
		$podminka .= " ".$var." IN( {$c} ) ";
	}
	
        $sql = "SELECT $co FROM {$kde} WHERE $podminka ".$razeni.$limit.";";
	$vysledek = $this -> execPDO ( $sql, $args );
	return $vysledek;
    }
    
    function getDataUnion ( $co, $co2, $kde, $kde2,  $podminka, $podminka2, $podle_ceho = "ID",$podle_ceho2 = "ID", $smerem = "ASC",$smerem2 = "ASC", $limit = "",$limit2 = "" ){
        if ( $limit != "" ) $limit = " LIMIT ".$limit;
        if ( $limit2 != "" ) $limit2 = " LIMIT ".$limit2;
        
		echo "DB function ". __FUNCTION__ ." NOT IMPLEMENTED\n<br />";

	
        $sql = "SELECT $co FROM {$kde} WHERE $podminka UNION ALL SELECT $co2 FROM {$kde2} WHERE $podminka2  ORDER BY $podle_ceho $smerem".$limit.";";
		$vysledek = $this -> proved_sql ( $sql );

		return $this -> processData ( $vysledek );;
    }
	
    function getAll ($co, $kde, $podminka, $args = array () ){
		$args = $this -> getArguments ( $args );

		$sql = "SELECT $co FROM {$kde} WHERE $podminka;";
		$vysledek = $this -> execPDO ( $sql, $args );

		return $vysledek;
    }
    
    function getRangeData ($co, $kde, $podminka, $args = array (),  $podle = "", $smer = "", $od = 0, $kolik = 100 ){
		$args = $this -> getArguments ( $args );
		$razeni = $this -> getDirection ( $podle, $smer );

		$sql = "SELECT $co FROM {$kde} WHERE {$podminka} {$razeni} LIMIT $od, $kolik;";
		$vysledek = $this -> execPDO ( $sql, $args );

		return $vysledek;
    }
   
    function getRandData ($co, $kde, $podminka, $smer, $pocet ) {
		
			//~ "SELECT TOP 5 * FROM e_anglictina WHERE Typ='0' ORDER BY RAND()" 
				echo "DB function ". __FUNCTION__ ." NOT IMPLEMENTED\n<br />";

		$sql = "SELECT $co FROM {$kde} WHERE $podminka ORDER BY RAND() $smer limit $pocet;";
		$vysledek = $this -> proved_sql ( $sql );

		$i = 0;
		while ($udaje[$i++] = mysql_fetch_assoc($vysledek) );
		unset ( $udaje[$i-1] );
		return $udaje;
	}
    
    # pocetni skripty
	function getCount ( $co, $kde, $podminka, $args = array () ) {
		$args = $this -> getArguments ( $args );

		$sql = "SELECT COUNT($co) AS pocet FROM {$kde} WHERE $podminka";
		
		if ( $vysledek = $this -> execPDO ( $sql, $args ) );
			return $vysledek[0]["pocet"];
		return 0;
	}
	
    function getCountID ( $kde, $podminka, $args = array () ) {
	
		return $this -> getCount ( "ID", $kde, $podminka, $args );
	}

	function getIndexed ( $index, $col, $kde, $podminka = "1=1", $args = array (),  $podle = "", $smer = "", $limit = "" ){
        if ( $limit != "" ) $limit = " LIMIT ".$limit;

		$args = $this -> getArguments ( $args );
		$razeni = $this -> getDirection ( $podle, $smer );

        $sql = "SELECT {$col}, {$index} FROM {$kde} WHERE $podminka ".$razeni.$limit.";";
		$vysledek = $this -> execPDO ( $sql, $args );
		
		$out = array ();
		if ( $vysledek ) foreach ( $vysledek as $var => $val )
			$out[$val[$index]] = $val[$col];
		
		return $out;
    }
    # overovaci skripty
    function exist ( $kde, $podminka, $args = array (), $co = "ID" ) {
	$args = $this -> getArguments ( $args );

	$sql = "SELECT {$co} FROM `{$kde}` WHERE $podminka LIMIT 1;";
        
	if ( $vysledek = $this -> execPDO ( $sql, $args ) );
		return true;
	return false;
    }
    
	function vratID () {
		
		return $this -> getLastID ();
	}

	function getLastID () {
		
		return $this -> pdo -> lastInsertID();
	}
	
	function lastInsertedID () {
		
		return $this -> getLastID();
	}
    
	function lastInsertId () {
		
		return $this -> getLastID();
	}
    
	function lastID () {
		
		return $this -> getLastID();
	}
    
	function insertedID () {
		
		return $this -> getLastID();
	}
    
	function getTableLastVal ( $table, $collumn = "ID" ) {

		if ( $c = $this -> get ( $collumn, $table, "1=1", $collumn, "DESC" ) )
		return $c[ $collumn ];

		return 0;
	}
	function getTableFirstVal ( $table, $collumn = "ID" ) {

		if ( $c = $this -> get ( $collumn, $table, "1=1", $collumn, "ASC" ) )
		return $c[ $collumn ];

		return 0;
	}
	
	function getTableLastID ( $table ) {

		$this -> getTableLastVal ( $table, "ID" );
		return 0;
	}

	function getTableFirstID ( $table ) {

		$this -> getTableFirstVal ( $table, "ID" );
		return 0;
	}
    
    # mazani z databaze
    function deleteID ( $kde, $id, $limit = NULL ) {
		
		return $this -> deleter ( $kde, "ID=?", $id, $limit );
    }
    
	# smazani v databazi
    function delete ( $tabulka, $podminka, $args = array (), $limit = NULL) {
		if ( $limit != NULL ) 
			$limit = "LIMIT ".$limit;
		$args = $this -> getArguments ( $args );
		$sql = "DELETE FROM {$tabulka} WHERE $podminka $limit;";
		
		$this -> execPDO ( $sql, $args );
		return $this -> lastExec;
    }
    
	# alias k delete
	function deleter ($tabulka, $podminka, $args = array (), $limit = NULL) {
		
            return $this -> delete ( $tabulka, $podminka, $args, $limit);
	}
	
	
    # destructor
    function __destruct ( ) {
	
	// destroy PDO object => close DB connection
	$this -> pdo = null;
        
        if ( self::DO_PROFILING_DEATH ) {
            
            if ( !file_exists( DIR.self::PROFILING_FILE ))
                $profilingFileContent = "";
            else
                $profilingFileContent = file_get_contents( DIR.self::PROFILING_FILE );
           
           $profilingFileContent = explode ( "\n", $profilingFileContent );
           
           $profiling = array ();
           foreach ( $profilingFileContent as $var => $val ) {
               
               $c = explode ( "~", $val );
               if ( isset ( $c[1] )) 
                $profiling[$c[1]]=$c[0];
           }
            
            
            foreach ( $this -> profilTable as $p => $c ) {
                
                if ( isset ( $profiling[$p]))
                    $profiling[$p] += $c;
                else
                    $profiling[$p] = $c;
            }
            
            $out = array ();
            foreach ( $profiling as $v => $c )
                $out[] = $c."~".$v;
            
            
            file_put_contents( DIR.self::PROFILING_FILE, implode( "\n", $out) );
        }
    }
}