Location: PHPKode > scripts > Query > query2-4/Query2.php
<?php
/**
 * Query2 is a minimalist MySQL layer which can get maximum of MySQL database in minimal code
 *
 * @author Adam Zivner <hide@address.com>
 * @license http://www.opensource.org/licenses/bsd-license.php New BSD license
 * @package Query2
 * @link http://query2.0o.cz
 */

defined("QUERY2_VERSION") || define("QUERY2_VERSION", "1.0.1");

class Query2 {
	/** @var string - Query string */
	public $sql;
	/** @var resource - MySQL connection */
	public $con; // MySQL connection

	/** @var callback - see setLogCallback() */
	protected $log_callback = null;

	/**
	 * Create object and optionally connect do database server and select database.
	 * If no arguments are given, we don't try to connect, if $database is empty, we
	 * don't select database. See also connect() method for details.
	 *
	 * @param string $host
	 * @param string $login
	 * @param string $password
	 * @param string $database
	 * @param boolean $new_link - enforce creation of the new link
	 * @param integer $client_flags - see mysql_connect() documentation
	 *
	 * @throws Query2Exception - codes 100 (can't connect to server), 101 (can't select database)
	 * @return Query2
	 */

	public function __construct($host = '', $login = '', $password = '', $database = '', $new_link = false, $client_flags = 0)
	{
		if(func_num_args() > 0)
			$this->connect($host, $login, $password, $database, $new_link, $client_flags);
	}

	public function connect($host, $login, $password, $database = '', $new_link = false, $client_flags = 0)
	{
		if(!$this->con = mysql_connect($host, $login, $password, $new_link, $client_flags))
			throw new Query2Exception(100, "Can't connect to database server.");
		else if(strlen($database) && !mysql_select_db($database, $this->con))
			throw new Query2Exception(101, "Can't select database.");

		return $this;
	}

	/**
	 * Close database connections
	 *
	 * @return boolean - success/failure
	 */

	public function close()
	{
		return $this->con && mysql_close($this->con);
	}

	/**
	 * Callback is called right after execution of every query. Function should
	 * expect 3 arguments: success (bool), query (string), elapsed time (in ms)
	 *
	 * @param callback $callback
	 */

	public function setLogCallback($callback) { $this->log_callback = $callback; }
	public function getLogCallback() { return $this->log_callback; }

	// Transaction handling routines

	public function beginTransaction()
	{
		$this->query("SET AUTOCOMMIT=0");
		$this->query("START TRANSACTION");
	}

	public function commit()   { $this->query("COMMIT"); $this->query("SET AUTOCOMMIT=1"); }
	public function rollBack() { $this->query("ROLLBACK"); $this->query("SET AUTOCOMMIT=1"); }

	public function affectedRows() { return mysql_affected_rows($this->con); }
	public function lastInsertId() { return mysql_insert_id($this->con); }

	/**
	 * Compose SQL query from given arguments. Apply modifiers etc.
	 * Used by query and pquery.
	 *
	 * @throws Query2Exception - codes 200 (wrong argument list/order) and 201 (unknown modifier)
	 * @return string - final, ready to be executed SQL query
	 */

	public function composeQuery()
	{
		$args = array(); // SQL chunks/arguments
		$first = func_get_arg(0); // first argument

		// if first argument is array, take it as a list of arguments
		foreach(is_array($first) ? $first : func_get_args() as $arg)
			if(is_object($arg)) // is this Query2Builder?
				$args = array_merge($args, $first->mergeQuery());
			else
				$args[] = $arg;

		$sql = "";

		// Now, let's deal with modifiers

		for($i = 0, $argc = count($args); $i < $argc; $i++) {
			$part = $args[$i];

			if(!is_string($part))
				throw new Query2Exception(200, 'Wrong argument list/order to query() function: ' . var_export($args, true));

			// find modifiers
			preg_match_all("/%(?:%|([a-zA-Z]*)(?![a-zA-Z]))/", $part, $matches, PREG_SET_ORDER);

			foreach($matches as $m) {
				if($m[0] == '%%')
					$arg = '%';
				else {
					$arg = $args[++$i];
					
					if($arg === null)
						$arg = 'NULL';
					else if($m[1] == 'i') // integer
						$arg = (int) $arg;
					else if($m[1] == 'f') // float
						$arg = (float) $arg;
					else if(strtolower($m[1]) == 's') // string
						$arg = "'" . ($m[1] == 's' ? $this->escape($arg) : $arg) . "'";
					else if(strtolower($m[1]) == 't') // table or column name
						$arg = $this->escapeTableName($arg, $m[1] == 't');
					else if($m[1] == 'q'); // insert pure SQL query, no escaping
					else if(strtolower($m[1]) == 'in' || strtolower($m[1]) == 'nin') // inserts IN ('a', 'b', 'c')
						$arg = $this->in($arg, strtolower($m[1]) == 'in', $m[1] == 'in' || $m[1] == 'nin');
					else if(strtolower($m[1]) == 'a') // update modifier, returns name='John', surname='Black'
						$arg = $this->assocUpdate($arg, $m[1] == 'a');
					else if(strtolower($m[1]) == 'v') // insert modifier, returns (name, surname) VALUES ('John', 'Black')
						$arg = $this->assocInsert($arg, $m[1] == 'v');
					else if(strtolower($m[1]) == 'va') // INSERT ... ON DUPLICATE KEY UPDATE
						$arg = $this->assocInsertUpdate($arg, $m[1] == 'va');
					else
						throw new Query2Exception(201, "Wrong modifier '$m[1]' to query() function.");
				}

				// Replace just one occurence (that's why we use preg_replace instead of str_replace)
				$part = preg_replace("/$m[0]/", $arg, $part, 1);
			}

			$sql .= $part . ' ';
		}

		return trim($sql);
	}

	/**
	 * Main Query function. Query is composed by helper function composeQuery()
	 *
	 * Arguments can be:
	 * - chunks of SQL (with modifiers)
	 * - modifier arguments
	 * - instances of Query2Builder
	 * - an array as first argument containing anything from this list
	 *
	 * @throws Query2Exception - code 300 (MySQL error) and the same one as composeQuery()
	 * @return Query2Result|Query2 - Query2Result for SELECT, DESCRIBE etc. and Query2 for INSERT, UPDATE etc.
	 */

	public function query()
	{
		$args = func_get_args();

		// get pure SQL with composeQuery, call it with exactly the same arguments we got
		$this->sql = call_user_func_array(array($this, "composeQuery"), $args);

		$started_time = microtime(); // we'll measure how much time execution of a query took

		$res = mysql_query($this->sql, $this->con); // here we go!

		if($this->log_callback) // calling the callback (useful for logging, performance tuning etc.)
			call_user_func($this->log_callback, !mysql_error($this->con), $this->sql, microtime() - $started_time);

		if(mysql_error($this->con)) // oops, query failed
			throw new Query2Exception(300, mysql_error($this->con), $this->sql);

		return is_resource($res) ? new Query2Result($res) : $this;
	}

	/**
	 * Prints query and then executes it. It's intended for in-place use instead
	 * of query, just replace "query(" with "pquery(".
	 *
	 * If you just want to get query string (e.g. to write to file), use composeQuery()
	 *
	 * @throws Query2Exception - same codes as query()
	 * @return Query2Result|Query2
	 */

	public function pquery()
	{
		$args = func_get_args();
		echo call_user_func_array(array($this, "composeQuery"), $args);
		return call_user_func_array(array($this, "query"), $args);
	}

	/**
	 * Factory method which returns newly created instance of Query2Builder
	 *
	 * @return Query2Builder
	 */

	public function builder()
	{
		return new Query2Builder();
	}

	/**
	 * Escape schema object names. ` is escaped by doubling it, ' and " are not because the don't have to be escaped.
	 * E.g. table.column => `table`.`column`
	 * 
	 * @param string $str
	 * @param boolean $escape
	 * @return string
	 */

	public function escapeTableName($str, $escape)
	{
		if($escape)
			$str = str_replace("`", "``", $str);

		$arr = explode(".", $str);

		return "`" . implode("`.`", $arr) . "`";
	}

	/**
	 * @param string $str
	 * @return string
	 */

	public function escape($str) { return mysql_real_escape_string($str, $this->con); }

	/**
	 * Escapes value according to its type and $escape
	 *
	 * @param mixed $v
	 * @param boolean $escape
	 * @return string
	 */

	protected function escapeValue($v, $escape)
	{
		if($v === null)
			return "NULL";
		else if(is_object($v) && get_class($v) == "Query2Statement")
			return $v->statement;
		else
			return "'" . ($escape ? $this->escape($v) : $v) . "'";
	}

	/**
	 * Generate IN('a', 'b', 'c') from given array. It works also with an empty array.
	 *
	 * @param array $arr
	 * @param boolean $in - is it IN or NOT IN?
	 * @param boolean $escape
	 * @return string - either "FALSE" or "IN(...)"
	 */

	protected function in($arr, $in, $escape)
	{
		if(count($arr)) {
			foreach($arr as $key => $value)
				$arr[$key] = $this->escapeValue($value, $escape);

			return ($in ? '' : 'NOT ') . 'IN (' . implode(", ", $arr) . ')';
		}
		else
			return $in ? 'AND FALSE' : 'IS NOT NULL';
	}

	/**
	 * Transforms associative array into something like this:
	 * name = 'John', surname = 'Black', age = '33'
	 *
	 * @params array $assoc
	 * @params boolean $escape - escape contents?
	 * @return string
	 */

	protected function assocUpdate($assoc, $escape)
	{
		$arr = array();

		foreach($assoc as $key => $value)
			$arr[] = "`" . $this->escape($key) . "` = " . $this->escapeValue($value, $escape);

		return implode(", ", $arr);
	}

	/**
	 * Transforms associative array into something like this:
	 * (name, surname, age) VALUES ('John', 'Black', '33')
	 *
	 * @params array $assoc
	 * @params boolean $escape - escape contents?
	 * @return string
	 */

	protected function assocInsert($arr, $escape)
	{
		$vars = $ret = array();

		foreach((isset($arr[0]) && is_array($arr[0])) ? $arr : array($arr) as $assoc) {
			$vars = $values = array();

			foreach($assoc as $key => $value) {
				$vars[] = "`" . $this->escape($key) . "`";

				$values[] = $this->escapeValue($value, $escape);
			}

			$ret[] = "(" . implode(", ", $values) . ")";
		}

		return "(" . implode(", ", $vars) . ") VALUES " . implode(", ", $ret);
	}

	/**
	 * This method implements useful INSERT ... ON DUPLICATE KEY UPDATE.
	 * There are two ways to use this function, simple and "complex".
	 * See http://query2.0o.cz for explanation.
	 *
	 * @param array $arr - two possibilities
	 * @param boolean $escape - escape content?
	 * @return string - SQL part
	 */

	protected function assocInsertUpdate($arr, $escape)
	{
		$complex = isset($arr["data"]) && is_array($arr["data"]);
		$update = ($complex && isset($arr["update"])) ? $arr["update"] : array_keys($complex ? $arr["data"] : $arr);

		foreach($update as $idx => $name) // we'll do it in-place
			$update[$idx] = "`$name` = VALUES(`$name`)";

		if($complex && isset($arr["auto_increment"]))
			$update[] = "`$arr[auto_increment]` = LAST_INSERT_ID(`$arr[auto_increment]`)";

		return $this->assocInsert($complex ? $arr["data"] : $arr, $escape) . " ON DUPLICATE KEY UPDATE " . implode(", ", $update);
	}
}

class Query2Exception extends Exception {
	public $code, $error, $sql;

	function __construct($code, $error, $sql = "")
	{
		$this->code = $code; // error code (e.g. 300)
		$this->error = $error;
		$this->sql = $sql;

		parent::__construct($code . " " . $error . (strlen($sql) ? ": $sql" : ""), $code);
	}
}

/**
 * QueryStatement provides a way to use unescaped value like (MySQL NOW()) in
 * otherwise escaped role (in %v, %V and %a, %A).
 */

class Query2Statement {
	public $statement;

	function __construct($statement)
	{
		$this->statement = $statement;
	}
}
Return current item: Query