<?php
/**
 * mySQL Database Class
 *
 * @author Alexander Thiemann <mail@agrafix.net>
 */
class RealSQL {
	/**
	 * db connector
	 *
	 * @var resource
	 */
	protected $conn;
 
	/**
	 * save last query
	 *
	 * @var string
	 */
	protected $last_query = "";
 
	/**
	 * save query results
	 *
	 * @var array
	 */
	protected $results = array();
 
	/**
	 * Store variables, eg. sql replacements
	 *
	 * @var array
	 */
	private $stored_variables = array();
 
	/**
	 * constructor -> connect
	 *
	 * @param string $host
	 * @param string $user
	 * @param string $pass
	 * @param string $db
	 */
	public function __construct($host, $user, $pass, $db) {
		$this->conn = @mysql_connect($host, $user, $pass);
 
		if (!$this->conn) {
			$this->error();
		}
 
		if (!@mysql_select_db($db, $this->conn)) {
			$this->error();
		}
	}
 
	/**
	 * execute query
	 *
	 * @param string $query
	 * @param int $id
	 */
	public function query($query, $id=0) {
		if (strpos($query, "INSERT INTO") !== false) {
			if (!@mysql_unbuffered_query($query, $this->conn)) {
				$this->last_query = $query;
				$this->error();
			}
		}
		else {
			$this->results[$id] = @mysql_query($query, $this->conn);
 
			if (!$this->results[$id]) {
				$this->last_query = $query;
				$this->error();
			}
		}
	}
 
	/**
	 * fetch as array/numrows/etc
	 *
	 * @param int $id
	 * @param string $fetchtype
	 */
	public function fetch($id=0, $fetchtype="") {
		switch(@$fetchtype) {
			case "num_rows":
				return mysql_num_rows($this->results[$id]);
				break;
 
			default:
				$ass = mysql_fetch_assoc($this->results[$id]);
 
				return $ass;
				break;
		}
	}
 
	/**
	 * fetch all in one array
	 *
	 * @param int $id
	 */
	public function tpl_fetch($id=0) {
		$array = array();
 
		while ($r = $this->fetch($id)) {
			$array[] = $r;
		}
 
		return $array;
	}
 
	/**
	 * insert stuff into db
	 *
	 * @param array $data_array
	 * @param string $table
	 */
	public function insert($data_array, $table) {
		$fields = array_keys($data_array);
 
		foreach ($data_array As $k => $v) {
			if (is_numeric($v)) {
				$data_array[$k] = (double)$v;
			}
			else {
				$data_array[$k] = "'".urlencode($v)."'";
			}
		}
 
		$query = "INSERT INTO `".$table."` (`".implode("`,`", $fields)."`) VALUES (".implode(", ", $data_array).")";
 
		$this->query($query);
	}
 
	/**
	 * update fieldset
	 *
	 * @param array $data_array
	 * @param string $table
	 * @param string $where
	 */
	public function update($data_array, $table, $where) {
 
		$update = array();
 
		foreach ($data_array As $k => $v) {
			if (is_numeric($v)) {
				$data_array[$k] = (int)$v;
			}
			else {
				$data_array[$k] = "'".urlencode($v)."'";
			}
 
			$update[] = "`$k` = ".$data_array[$k];
		}
 
		$query = "UPDATE `$table` SET ".implode(", ", $update)." WHERE $where";
 
		$this->query($query);
	}
 
	/**
	 * set param (automatic urlencode+stripslashes+')
	 *
	 * @param string $key
	 * @param string $value
	 */
	public function setParam($key, $value) {
		if (is_string($value)) {
			$i = "'".urlencode(stripslashes($value))."'";
		}
		elseif (is_numeric($value)) {
			$i = $value;
		}
		else {
			$i = "'".urlencode(stripslashes($value))."'";
		}
 
		$this->stored_variables[$key] = $i;
	}
 
	/**
	 * delete all params
	 *
	 */
	public function clearParams() {
		$this->stored_variables = array();
	}
 
	/**
	 * execute paramed query
	 *
	 * @param string $sql
	 * @param int $id
	 */
	public function exec($sql, $id=0) {
		foreach($this->stored_variables AS $key => $val) {
			$sql = str_replace("[$key]", $val, $sql);
		}
 
		$this->query($sql, $id);
	}
 
	/**
	 * returns last ai-id
	 *
	 * @return int
	 */
	public function lastId() {
		return mysql_insert_id($this->conn);
	}	
 
	/**
	 * error handler
	 *
	 */
	protected function error() {
		// uncomment this line 
		// if you have deployed the app
		#die("Internal Server Error."); 
		
		echo "<div style='border:2px solid red;padding:5px;margin:5px'>";
		echo "<h1>RealSQL Error Occured</h1>";
 
		if ($this->last_query != "") {
			echo "<h2>Query</h2><pr"."e>".htmlspecialchars($this->last_query)."</pr"."e>";
		}
 
		echo "<h2>Error</h2>".htmlspecialchars(mysql_error($this->conn));
 
		echo "<pr"."e>";
		print_r(debug_backtrace());
		echo "</pr"."e>";
 
		echo "</div>";
 
		exit;
	}
}
?>

$db = new RealSQL("localhost", "root", "", "test_db");
 
// einfach eine Query ausführen
$db->query("UPDATE user SET username = 'asdf' WHERE id = 1");
 
// query ausführen und Ergebnisse holen
$db->query("SELECT * FROM user", 1);
 
while ($row = $db->fetch(1))
{
	echo "Benutzer: " . $row['username'];
}
 
// neuen Eintrag in die DB
$db->insert(array('username' => 'test'), 'user');
 
// Eintrag ändern
$db->update(array('username' => 'banana'), 'user', 'id=2');
 
// Query mit Parametern
$db->setParam("user", $_POST['user']);
$db->setParam("pass", md5($_POST['pass']));
 
$db->exec("SELECT * FROM user WHERE username = [user] AND password = [pass]");
 
$db->clearParams();