<?php

class k_Mysql 
{
    
    private $ms_Query_ = null; 
	private $mv_Result_ = null;	
	private $mv_Connection_ = array();
	private $ms_CurrentDatabase = '';	
	private $ms_Errors_ = array();
	protected $mi_TransactionStarted_ = array(); 
	
	private $ms_Username;
	private $ms_Password;
	private $ms_Host;
	private $ms_DatabaseName;
    
	public function __construct($as_Username, $as_Password, $as_Host, $as_DatabaseName, $ab_AutoConnect = true)
	{
		$this->ms_Username = $as_Username;
		$this->ms_Password = $as_Password;
		$this->ms_Host = $as_Host;
		$this->ms_DatabaseName = $as_DatabaseName;
		if ($ab_AutoConnect)
        $this->Connect();
	}

	public function __destruct()
	{
		foreach ($this->mi_TransactionStarted_ as $ls_Database => $li_TransactionLevel)
		{
			if ($li_TransactionLevel)
			{
				if ($this->ms_DatabaseName != $ls_Database) 
				{
					$this->SelectDatabase($ls_Database);
				}
				$this->RollbackTransaction();
				$this->SaveError("Attempting to quit with pending transaction, rolling back.");
			}
		}
	}
	
	public function Connect()
    {
        if (!isset($this->mv_Connection_[$this->ms_DatabaseName])) {
            if (!$this->mv_Connection_[$this->ms_DatabaseName] = @mysql_connect($this->ms_Host, $this->ms_Username, $this->ms_Password)) {
                $this->SaveError("Connection with with server ". $this->ms_Host ." is not possible!");
                return false;
            } else {
            	$this->mi_TransactionStarted_[$this->ms_DatabaseName] = 0;
                $this->SelectDatabase($this->ms_DatabaseName);
            }
        }
        return $this->mv_Connection_[$this->ms_DatabaseName];
	}
	
	public function SelectDatabase($as_DatabaseName)
	{
		if (!@mysql_select_db($as_DatabaseName)) {
			$this->SaveError("Can not connect to MySQL database (". $as_DatabaseName .")!");
			return false;
		} else if (!isset($this->mv_Connection_[$as_DatabaseName])) {
			$this->mv_Connection_[$as_DatabaseName] = $this->mv_Connection_[$this->ms_CurrentDatabase];
		}
		$this->ms_CurrentDatabase = $as_DatabaseName;
		return true;
	}
	
	public function Query($as_Query, $ab_AutoFetch = false)
	{
		$this->mv_Result_[$this->ms_CurrentDatabase] = mysql_query($as_Query, $this->mv_Connection_[$this->ms_CurrentDatabase]);
		
		if ($ls_ErrorMessage = $this->error()) {
        	$this->SaveError($ls_ErrorMessage, $as_Query);		
		}
		
		$this->ms_Query_[$this->ms_CurrentDatabase][] = $as_Query;
		
		if ($ab_AutoFetch) {
		    $ls_Row_ = $this->FetchArray();
			$this->FreeResult();
		    return $ls_Row_;
		} else {
		    return $this->mv_Result_[$this->ms_CurrentDatabase];
		}
	}
	
	public function Get($as_Query, $as_Var = '')
	{
		$this->Query($as_Query);
		if ($as_Var) {
			$ls_Row_ = $this->FetchArray(false, true);
			if (isset($ls_Row_[$as_Var])) {
				return $ls_Row_[$as_Var];
			} else {
				return false;
			}
		}
		return $this->MultiFetch();
	}		
	
	public function FetchVar($as_Var, $av_ResultId = false)
	{
		$ls_Row_ = $this->FetchArray($av_ResultId, true);
		if (isset($ls_Row_[$as_Var])) {
			return $ls_Row_[$as_Var];
		} 
		return false;		
	}	
	
	public function FetchArray($av_ResultId = false, $ab_FetchAssoc = true)
	{
	    $lv_Result = $av_ResultId ? $av_ResultId : $this->mv_Result_[$this->ms_CurrentDatabase];
		if (!$lv_Result) {
            return false;
		}
		if (!is_resource($lv_Result))
		{
			$this->Query("INSERT INTO DatenbankFehler VALUES ('', NOW(), '', 'Invalid MySql resource','" . var_export(debug_backtrace(), true) . "') ");
			return false;
		}
		if ($ab_FetchAssoc) {
			if ($lv_Array_ = mysql_fetch_array($lv_Result, MYSQL_ASSOC)) {
				return $lv_Array_;
			}
		} else {
			if ($lv_Array_ = mysql_fetch_array($lv_Result)) {
				return $lv_Array_;
			}
		}
        $this->FreeResult($lv_Result);
		return false;
	}
	
	public function MultiFetch($av_ResultId = false, $ab_FetchAssoc = true)
	{
	    $lv_Rows_ = null;
		while ($lv_Row_ = $this->FetchArray($av_ResultId, $ab_FetchAssoc)) {
			$lv_Rows_[] = $lv_Row_; 
		}
		return $lv_Rows_;
	}
	
	public function ListFetch($av_ResultId = false, $as_Var)
	{
        $ab_FetchAssoc = true;
	    $lv_Rows_ = array();
		while ($lv_Row_ = $this->FetchArray($av_ResultId, $ab_FetchAssoc)) {
			$lv_Rows_[$lv_Row_[$as_Var]] = $lv_Row_; 
		}
		return $lv_Rows_;
	}
	
	public function NumberedFetch($av_ResultId = false, $as_Var, $lowercase=true)
	{
        $ab_FetchAssoc = true;
	    $lv_Rows_ = array();
        $i = 1;
		while ($lv_Row_ = $this->FetchArray($av_ResultId, $ab_FetchAssoc)) {
			$lv_Row_['Number'] = $i;
			if ($lowercase)
            $lv_Rows_[strtolower($lv_Row_[$as_Var])] = $lv_Row_;
			else
            $lv_Rows_[$lv_Row_[$as_Var]] = $lv_Row_;
			$i++;
		}
		return $lv_Rows_;
	}
	
	public function FreeResult($av_ResultId = false)
	{
	    $lv_Result = $av_ResultId ? $av_ResultId : $this->mv_Result_[$this->ms_CurrentDatabase];
		if (!$lv_Result) 
        return;
		if (!is_resource($lv_Result))
		{
			$this->Query("INSERT INTO DatenbankFehler VALUES ('', NOW(), '', 'Invalid MySql resource', '" . var_export(debug_backtrace(), true) . "') ");
			return;
		}
    	mysql_free_result($lv_Result);
		$this->mv_Result_[$this->ms_CurrentDatabase] = null;
	}
	
	public function NumberOfRows($av_ResultId = false)
    {
	    $lv_Result = $av_ResultId ? $av_ResultId : $this->mv_Result_[$this->ms_CurrentDatabase]; 
    	$li_Number = mysql_num_rows($lv_Result);
		return $li_Number;		
	}
	
	public function AffectedRows()
	{
		$lv_AffectedRows = mysql_affected_rows();
		return $lv_AffectedRows;		
	}		
	
    public function ListTables()
	{
	    $lv_Array_ = array();
		$this->mv_Result_[$this->ms_CurrentDatabase] = mysql_list_tables($this->ms_CurrentDatabase);
		while ($lv_Row_ = $this->FetchArray(false, false)) {
            $lv_Array_[] = $lv_Row_[0];
		}
		return $lv_Array_;	
	}
	
    public function Fields($as_Table = '')
	{
	    $lv_Array_ = array();
		if ($as_Table) {
			$this->Query("select * from ". $as_Table); 
		} 
		$li_Fields = mysql_num_fields($this->mv_Result_[$this->ms_CurrentDatabase]);
		for ($i = 0; $i < $li_Fields; $i++) {
            $lv_Array_[$i]['type'] = mysql_field_type($this->mv_Result_[$this->ms_CurrentDatabase], $i);
            $lv_Array_[$i]['name'] = mysql_field_name($this->mv_Result_[$this->ms_CurrentDatabase], $i);
            $lv_Array_[$i]['size'] = mysql_field_len($this->mv_Result_[$this->ms_CurrentDatabase], $i);
            $lv_Array_[$i]['flags'] = mysql_field_flags($this->mv_Result_[$this->ms_CurrentDatabase], $i);
		}
		return $lv_Array_;	
	}
	
	
	public function NextId($as_Table)
	{
		$lv_Id = $this->Get("Show table status like '". $as_Table ."'", "Auto_increment");
		return $lv_Id;
	}	
	
    
	public function Error($ab_Echo = false)
	{
		$ls_Error = mysql_error();
		if ($ab_Echo) {
			echo $ls_Error;
		}
		return $ls_Error;
	}
	
	public function SaveError($as_Error, $as_Query = '')
	{
	    $ls_Error = str_replace("\n", "", $as_Error);
		$this->ms_Errors_[$this->ms_CurrentDatabase][] = ($as_Query != '' ? $as_Query . ': ' : '') . $ls_Error;

		$lv_CallStack_ = debug_backtrace();

		$ls_Url = $_SERVER["REQUEST_URI"];
		$ls_Report = "";

		$li_Count = 0;
		
		foreach ($lv_CallStack_ as $lv_Function_)
		{
			$ls_FunctionReport = "";
			$ls_FunctionReport .= $lv_Function_["file"] . " (";
			$ls_FunctionReport .= $lv_Function_["line"] . "): ";
			$ls_FunctionReport .= $lv_Function_["function"] . "(...);";
			$ls_Report .= $ls_FunctionReport . "<br />";
		}

		$ls_Query = "INSERT INTO DatenbankFehler (Zeit, Query, Fehler, Stacktrace) ".
        "VALUES (NOW(), '".mysql_escape_string($as_Query)."', '".mysql_escape_string($as_Error)."', '" . mysql_escape_string($ls_Report) . "')";
        //echo $ls_Query;
		mysql_query($ls_Query, $this->mv_Connection_[$this->ms_CurrentDatabase]);
        
	}
	
	public function ErrorStr($as_String)
	{
		$ls_String = str_replace("\n", "", $as_String);
		$ls_String = str_replace("\t", "", $ls_String);
		$ls_String = str_replace("\r", "", $ls_String);
		$ls_String = preg_replace('/ {2,}/', ' ', $ls_String);
		return $ls_String;
	}
	
	
	public function Errors()
	{
        while (list ($ls_Database, $ls_Errors_) = each ($this->ms_Errors_)) {
            echo "<p><b>". $ls_Database ."</b>";
            for ($i = 0; $i < sizeof($ls_Errors_); $i++) {
                echo "<br />". $ls_Errors_[$i];
            }
            echo "<br />Total: <b>". $i ."</b> errors</p>";
		}
		reset($this->ms_Errors_);
	}
	
	public function NumberOfQuerys()
	{
	    $li_Number = 0;
		while (list ($ls_Database, $ls_Queries_) = each ($this->ms_Query_)) {
            $li_Number += sizeof($ls_Queries_);
		}
		reset($this->ms_Query_);
		return $li_Number;
	} 	
    
	// Hinzugefuegt 23.01.08 Jens
	/**
	 * Startet eine SQL-Transaktion in der aktuellen Datenbank.
	 */
	public function StartTransaction()
	{
		if (!$this->mi_TransactionStarted_[$this->ms_DatabaseName])
		{
			$this->Query("START TRANSACTION");
		}
		else
		{
			// Tried to start nested transactions, log occurence
			$this->SaveError("Attempted to start a nested transaction. Using outer transaction instead.");
		}
		$this->mi_TransactionStarted_[$this->ms_DatabaseName]++;
	}

	/**
	 * Schreibt die aktuelle Transaktion in der aktuellen Datenbank fest.
	 */
	public function CommitTransaction()
	{
		if (!$this->mi_TransactionStarted_[$this->ms_DatabaseName])
		{
			$this->SaveError("Trying to commit non-existing transaction.");
			trigger_error("Trying to commit non-existing transaction.", E_USER_ERROR);			
		}
		$this->mi_TransactionStarted_[$this->ms_DatabaseName]--;
		if (!$this->mi_TransactionStarted_[$this->ms_DatabaseName])
		{
			$this->Query("COMMIT");
		}
		else
		{
			// Tried to start nested transactions, log occurence
			$this->SaveError("Attempted to commit a nested transaction. Not committing and waiting for outer transaction instead.");
		}
	}
	
	/**
	 * Bricht die aktuelle Transaktion in der aktuellen Datenbank ab.
	 */
	public function RollbackTransaction()
	{
		if (!$this->mi_TransactionStarted_[$this->ms_DatabaseName])
		{
			$this->SaveError("Trying to roll back non-existing transaction.");
			trigger_error("Trying to roll back non-existing transaction.", E_USER_ERROR);			
		}
		$this->mi_TransactionStarted_[$this->ms_DatabaseName]--;
		if (!$this->mi_TransactionStarted_[$this->ms_DatabaseName])
		{
			$this->Query("ROLLBACK");
		}
		else
		{
			// Tried to start nested transactions, log occurence
			$this->Query("ROLLBACK");
			$this->SaveError("Attempted to roll back a nested transaction. Rolling back outer transaction.");
			trigger_error("Attempted to roll back a nested transaction. Rolling back outer transaction and aborting.", E_USER_ERROR);
		}
	}
	
	
    // Hinzugefügt 02.03.07 Jens
    /**
    * Selects data from a table.
    *
    * Selects data from a table. This method takes two or three parameters: The first one is the
    * table name, the second one an array of the column names from which the values should be
    * returned and the last, optional one may be a WHERE clause (without the WHERE, e.g. 
    * ID=1 AND Name='Test'). Returns an array of rows which are both numerically and associatively
    * indexed.
    *
    * Please note that the input data in this function is <b>NOT</b> checked, so you will have
    * to escape it yourself if you take it from user input or you will risk a SQL injection
    * vulnerability.
    *
    * @param string $as_Table Name of the table to fetch data from.
    * @param array $as_Columns_ Names of the columns to fetch data from.
    * @param string $as_WhereClause An optional SQL WHERE clause without the WHERE.
    * @return array A two-dimensional array in row-major order including the result set.
    */
    public function Select($as_Table, array $as_Columns_, $as_WhereClause = FALSE) {
        $lr_Result = $this->Query(
        'SELECT ' . implode(', ', $as_Columns_) . 
        ' FROM `' . $as_Table . '`' .
        ($as_WhereClause !== FALSE ? ' WHERE ' . $as_WhereClause : '')
        );
        $ls_ResultArray_ = $this->MultiFetch($lr_Result);
        return is_array($ls_ResultArray_) ? $ls_ResultArray_ : array();
    }
    
    /**
     * Selects the given columns from a table and returns the values as an k_Entity instance. 
     * Please note that only the first result is returned, if there are more, they are discarded.
     * Please also note that in order to work with the k_Entity class, the table has to have
     * an column named ID as a primary key
     *
     * @param string $as_Table Name of the table to fetch from
    * @param array $as_Columns_ Names of the columns to fetch data from.
    * @param string $as_WhereClause An optional SQL WHERE clause without the WHERE.
    * @return k_Entity An entity representing the result or NULL if no result was found. 
    */
    public function SelectAsEntity($as_Table, array $as_Columns_, $as_WhereClause = FALSE, $as_AdditionalClause = FALSE)
    {
        if(!array_search('ID', $as_Columns_))
        {
            $as_Columns_[] = 'ID';
        }
        $lr_Result_ = $this->Query(
            'SELECT ' . implode(', ', $as_Columns_) . 
            ' FROM `' . $as_Table . '`' .
            ($as_WhereClause !== FALSE ? ' WHERE ' . $as_WhereClause : '').
            ($as_AdditionalClause !== FALSE ? ' ' . $as_AdditionalClause : '').
            ' LIMIT 1',
            true
        );
        if($lr_Result_)
        {
            return new k_Entity($this, $as_Table, $lr_Result_['ID'], $lr_Result_);
        }
        else
        {
            return null;
        }
    }
    
    /**
     * Selects the given columns from a table and returns the values as an array of k_Entity 
     * instances. Please note that in order to work with the k_Entity class, the table has to have
     * an column named ID as a primary key
     *
     * @param string $as_Table Name of the table to fetch from
     * @param array $as_Columns_ Names of the columns to fetch data from.
     * @param string $as_WhereClause An optional SQL WHERE clause without the WHERE.
     * @param string $as_AdditionalClause An additional clause such as an ORDER BY clause.
     * @return array An array of k_Entity objects holding the results-
     */
    public function SelectAsEntities($as_Table, array $as_Columns_, $as_WhereClause = FALSE, $as_AdditionalClause = FALSE)
    {
        if(!array_search('ID', $as_Columns_))
        {
            $as_Columns_[] = 'ID';
        }
        $this->Query(
            'SELECT ' . implode(', ', $as_Columns_) . 
            ' FROM `' . $as_Table . '`' .
            ($as_WhereClause !== FALSE ? ' WHERE ' . $as_WhereClause : '').
            ($as_AdditionalClause !== FALSE ? ' ' . $as_AdditionalClause : '')
        );
        $lk_Entities_ = array();
        while($lr_Result_ = $this->FetchArray())
        {
            $lk_Entities_[] = new k_Entity($this, $as_Table, $lr_Result_['ID'], $lr_Result_);
        }
        return $lk_Entities_;
    }
    
    /**
    * Updates data in a table.
    *
    * Updates data in a table. This method takes four arguments: The first one is the table name,
    * the second one is an array of the column names and the third one is an array of the values 
    * to update into the table. Both arrays have to have the same length. The forth argument is
    * a where clause without the WHERE (e.g. ID = 2) which specifies the rows to be updated.
    * Please note that the values will be quoted and escaped, so you do not have to do that before.
    * Because of the quoting and escaping, you will not be able to issue MySQL functions, subqueries, 
    * NULL values etc. in the value array. If you need to do so, use the {@link Query} 
    * method.
    * 
    * @param string $as_Table Name of the table to update.
    * @param array $as_Columns_ Names of the columns to update.
    * @param array $av_Values_ Values to update.
    * @param string $as_WhereClause A SQL WHERE clause without the WHERE specifying the rows
    *    to be updated.
    */
    public function Update($as_Table, array $as_Columns_, array $av_Values_, $as_WhereClause) {
        if(count($as_Columns_) != count($av_Values_)) {
            // In any case, quit the method
            return;
        }
        // Construct query
        $ls_Query = "UPDATE `$as_Table` SET ";
        for($li_Col = 0; $li_Col < count($as_Columns_); $li_Col++) {
            if($li_Col > 0)
            $ls_Query .= ', ';
            $ls_Query .= 
            "`{$as_Columns_[$li_Col]}` = ".
            	($av_Values_[$li_Col] === NULL ? 
            	"NULL" : 
            	"'" .
	            mysql_real_escape_string(
    	            $av_Values_[$li_Col], 
        	        $this->mv_Connection_[$this->ms_CurrentDatabase]
            	) . 
		"'"
		);
        }
        $ls_Query .= " WHERE $as_WhereClause";
        // Execute query
        $this->Query($ls_Query);
        
    }
    
    /**
    * Inserts data into a table.
    *
    * Inserts data into a table. This method takes three arguments: The first one is the table name,
    * the second one is an array of the column names and the third one is an array of the values 
    * to insert into the table. Both arrays have to have the same length. Please note that the
    * values will be quoted and escaped, so you do not have to do that before. Because of the 
    * quoting and escaping, you will not be able to issue MySQL functions, subqueries, 
    * NULL values etc. in the value array. If you need to do so, use the {@link Query} 
    * method.
    * 
    * @param string $as_Table Name of the table to insert into.
    * @param array $as_Columns_ Names of the columns to insert the data into.
    * @param array $av_Values_ Values to insert into the columns.
    */
    public function Insert($as_Table, array $as_Columns_, array $av_Values_) {
        if(count($as_Columns_) != count($av_Values_)) {
            // In any case, quit the method
            return;
        }
        // Construct query
        $ls_Query = "INSERT INTO `$as_Table` (";
        $lb_First = true;
        foreach($as_Columns_ as $sColumn) {
            if(!$lb_First) 
            $ls_Query .= ', ';
            $ls_Query .= "`$sColumn`";
            $lb_First = false;
        }
        $ls_Query .= ") VALUES (";
        $lb_First = true;
        foreach($av_Values_ as $vValue) {
            if(!$lb_First) 
            $ls_Query .= ', ';
            $ls_Query .= 
            	($vValue === NULL ? 
            	'NULL' : 
            	"'".mysql_real_escape_string(
                	$vValue, $this->mv_Connection_[$this->ms_CurrentDatabase]
            	)."'");
            $lb_First = false;
        }
        
        $ls_Query .= ")";
        // Execute query
        $this->Query($ls_Query);
    }
    
    /**
    * Returns the last generated auto_increment ID.
    *
    * @return The last generated auto_increment ID.
    * @see mysql_insert_id()
    */
    public function LastInsertID() {
        return @mysql_insert_id($this->mv_Connection_[$this->ms_CurrentDatabase]);
    }

    /**
     * Gibt einen Dump der wichtigsten Daten zur�ck (ASCII-Plain-Text).
     * @return string Ein Dump der wichtigsten Daten.
     */
	public function Dump()
	{
		return print_r($this->ms_Query_, true) . "\n" . print_r($this->ms_Errors_, true);
	}
}

?>