<?php
/**
 * Copyright 2009, 2010 AEGEE-Europe
 *
 * This file is part of AEGEE-Europe Online Membership System.
 *
 * AEGEE-Europe Online Membership System is free software: you can
 * redistribute it and/or modify it under the terms of the GNU General
 * Public License as published by the Free Software Foundation, either
 * version 3 of the License, or (at your option) any later version.
 *
 * AEGEE-Europe Online Membership System is distributed in the hope
 * that it will be useful, but WITHOUT ANY WARRANTY; without even the
 * implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
 * PURPOSE.  See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with AEGEE-Europe Online Membership System.  If not, see
 * <http://www.gnu.org/licenses/>.
 */

/**
 * Provide an access point to MySQL databases. On initialisation of the class the authentication parameters are stored. Only when required, we do the actual login.
 *
 * @version		1.0
 * @date		2.11.2009
 * @author		Wim van Ravesteijn
 * @license		http://opensource.org/licenses/gpl-license.php GNU Public License
 */
class MySQL {
	private $conn; // MySQL link identifier
	private $host; // MySQL host
	private $user; // MySQL user
	private $pass; // MySQL password
	private $db;   // MySQL database
	private $isConnected; // Are we connected already?
	private $res;  // MySQL resource
	private $errorMsg; // Last error generated

	/**
	 * Default constructor
	 *
	 * @param string host: hostname where the MySQL server runs
	 * @param string user: username to be used to log in to MySQL
	 * @param string pass: password to be used to log in to MySQL
	 * @param string db: database to be used in MySQL
	 */
	public function __construct($host, $user, $pass, $db) {
		$this->host = $host;
		$this->user = $user;
		$this->pass = $pass;
		$this->db = $db;
		$this->isConnected = false;
		$this->errorMsg = "";
	}

	/**
	 * Default destructor
	 */
	public function __destruct() {
	}

	/**
	 * Connect to the MySQL server (automatically called when database connection is needed)
	 *
	 * @return boolean true in case connecting is successful, false otherwise
	 */
	public function connect() {
		if( $this->isConnected ) {
			return true;
		}
		if( $this->conn = @mysql_pconnect($this->host, $this->user, $this->pass) ) {
			if( @mysql_select_db($this->db, $this->conn) ) {
				$is_connected = true;
				return true;
			}else {
				$this->errorMsg = "Failed connecting to database mysql://".$this->host."/".$this->db;
				return false;
			}
		}else {
			$this->errorMsg = "Failed connecting to MySQL mysql://".$this->host."/";
			return false;
		}
	}

	/**
	 * Execute a query
	 *
	 * @param string query: query to be executed
	 * @return boolean true in case of success, false otherwise
	 */
	public function query($query) {
		if( !$this->connect() ) {
			return false;
		}
		$this->res = mysql_query($query, $this->conn);
		if( $this->res ) {
			return true;
		}else {
			$this->errorMsg = mysql_error($this->conn);
			return false;
		}
	}

	/**
	 * Escapes special characters in a string for use in a SQL statement
	 *
	 * @param string text: string to be escaped
	 * @return string escaped text
	 */
	public function escape($text) {
		if( !$this->connect() ) {
			return false;
		}
		return mysql_real_escape_string($text, $this->conn);
	}

	/**
	 * Return number of rows in last result
	 *
	 * @return int number of rows in last result
	 */
	public function getNumRows() {
		return @mysql_num_rows($this->res);
	}

	/**
	 * Fetch a result row as an associative array and a numeric array
	 *
	 * @return array containing next result row
	 */
	public function fetchArray() {
		return @mysql_fetch_array($this->res);
	}

	/**
	 * Fetch a result row as an associative array
	 *
	 * @return array containing next result row
	 */
	public function fetchAssoc() {
		return @mysql_fetch_assoc($this->res);
	}
	
	/**
	 * Fetch all result rows as an associative array
	 * 
	 * @return 2-dimensional array containing all result rows
	 */
	public function fetchAssocAll() {
		$rows = array();
		while( $row = $this->fetchAssoc() ) {
			$rows[] = $row;
		}
		return $rows;
	}

	/**
	 * Get number of affected rows in previous MySQL operation
	 *
	 * @return int number of affected rows
	 */
	public function getAffected() {
		return @mysql_affected_rows($this->conn);
	}

	/**
	 * Get the ID generated from the previous INSERT operation
	 *
	 * @return int the ID generated for an AUTO_INCREMENT column by the previous INSERT query
	 */
	public function getInsertId() {
		return @mysql_insert_id($this->conn);
	}

	/**
	 * Move internal result pointer
	 *
	 * @param int rownr: the desired row number of the new result pointer
	 * @return boolean true on success, false otherwise
	 */
	public function gotoRow($rownr) {
		return @mysql_data_seek($this->res, $rownr);
	}

	/**
	 * Get the last error
	 *
	 * @return string last error message generated
	 */
	public function getErrorMsg() {
		return $this->errorMsg;
	}
	
	/**
	 * Insert a row into a table
	 * 
	 * @param string $table
	 * @param array $data
	 * @return int insert id, or false in case of error
	 */
	public function insert($table, $data) {
		$query = "INSERT INTO `".$this->escape($table)."` ";
		$query .= $this->getSetQuery($data);
		if( $this->query($query) ) {
			return $this->getInsertId();
		}else {
			return false;
		}
	}
	
	/**
	 * Updates an existing row in a table
	 * 
	 * @param string $table
	 * @param array $data
	 * @param string $field on which field to build the where
	 * @param string $value on which value to build the where
	 * @return boolean true in case of success, false otherwise
	 */
	public function update($table, $data, $field, $value) {
		$query = "UPDATE `".$this->escape($table)."` ";
		$query .= $this->getSetQuery($data, $field);
		$query .= " WHERE `".$this->escape($field)."`='".$this->escape($value)."'";
		return $this->query($query);
	}
	
	/**
	 * Selects an existing row in a table
	 * 
	 * @param string $table
	 * @param string $field on which field to build the where
	 * @param string $value on which value to build the where
	 * @return array containing the requested row (fetchAssoc) or NULL in case no single row is found
	 */
	public function select($table, $field, $value) {
		$query = "SELECT * FROM `".$this->escape($table)."` ";
		$query .= "WHERE `".$this->escape($field)."`='".$this->escape($value)."'";
		if( $this->query($query) && $this->getNumRows()==1 ) {
			return $this->fetchAssoc();
		}else {
			return NULL;
		}
	}
	
	public function delete($table, $field, $value) {
		$query = "DELETE FROM `".$this->escape($table)."` ";
		$query .= "WHERE `".$this->escape($field)."`='".$this->escape($value)."'";
		return $this->query($query);
	}
	
	private function getSetQuery($data, $exclude="") {
		$r = "SET ";
		foreach( $data as $key => $value ) {
			if( $key!=$exclude ) {
				$r.= "`".$this->escape($key)."`=";
				if( $value==NULL ) {
					$r .= "NULL, ";
				}else {
					$r .= "'".$this->escape($value)."', ";
				}
			}
		}
		return substr($r, 0, -2);
	}
}
?>