Dieser Beitrag ist Teil meiner Sourecode a Day-Aktion.

Ich möchte eine Klasse vorstellen, die ich mir mal geschrieben habe um einfach auf mySQL-Datenbanken zuzugreifen. Ich verwende sie selbst allerdings nicht mehr, da CodeIgniter eine eigene Datenbank-Abstraktion liefert und die meisten PHP-Hosts jetzt auch mysqli unterstützen!

<?php
/**
 * mySQL Database Class
 *
 * @author Alexander Thiemann 
 */
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 "
";
		echo "
RealSQL Error Occured
";
		
		if ($this->last_query != "") {
			echo "
Query
".htmlspecialchars($this->last_query)."";
		}
		
		echo "
Error
".htmlspecialchars(mysql_error($this->conn));
		
		echo "";
		print_r(debug_backtrace());
		echo "";
		
		echo "
";
		
		exit;
	}
}
?>

Die Verwendung:

$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();

Die aller erste Manager’s Life Version verwendete diese Klasse.