Database

class.db.php

<?php

/**
 * Wrapper class for database access
 * 
 * Simple database wrapper. While it handles some 
 * stuff pretty okay at the moment. The big draws are the
 * update_records, insert_record, insert_records, get_records,
 * and get_record. Other than that, it can also wrap query calls, etc.
 * 
 * @author Raymond Minge <the@rayminge.com>
 * @version 0.1
 * 
 */

class db {
	
	/**
	 * Wrapper for mysql_connect(), and optionally selects a database
	 * 
	 * @param String $host Server Host
	 * @param String $username Database Username
	 * @param String $password Database Password
	 * @param String $dbname Database Name
	 * 
	 * @see mysql_connect()
	 * @see mysql_select_db()
	 */
	public static function connect($host, $username, $password, $dbname = '') {
		mysql_connect($host, $username, $password);
		if ($dbname) {
			self::select_db($dbname);
		}
		mysql_query( "SET NAMES utf8" );
        mysql_query( "SET CHARACTER SET utf8" );
	}

	/**
	 * Wrapper for mysql_select_db()
	 * 
	 * @param String $dbname Database Name
	 * 
	 * @see mysql_select_db()
	 * @see qp::connect()
	 */
	public static function select_db($dbname) {
		mysql_select_db($dbname);		
	}

	/**
	 * Escapes a table name for automatically generated sql
	 * 
	 * @param String $table The table name
	 */
	public static function escape_table($table) {
		return '`'.mysql_real_escape_string($table).'`';
	}

	/**
	 * Escapes a column name for automatically generated sql
	 * 
	 * @param String $column The column name
	 */
	public static function escape_column($column) {
		if ($column == '*') {
			return $column;
		} else {
			return '`'.mysql_real_escape_string($column).'`';
		}
	}

	/**
	 * Escapes text for automatically generated sql
	 * 
	 * @param String $value The text to be escaped.
	 */
	public static function escape_value($value) {

		if (is_bool($value)) {
			return ($value?'TRUE':'FALSE');
		} else if (is_null($value)) {
			return 'NULL';
		} else  {

			if (strtolower($value) == 'now()') {
				$value = 'NOW()';
				return $value;
			} 

			if (get_magic_quotes_gpc()) {
				$value = stripslashes($value);
			}

			return '"'.mysql_real_escape_string($value).'"';
		} 
	}

	/**
	 * Gets the last queried sql;
	 */
	public static function get_last_sql() {
		global $last_db_sql;
		return $last_db_sql;
	}

	/**
	 * Wrapper function for mysql_query;
	 * 
	 * @param String $sql The sql to be queried
	 * @see mysql_query();
	 */
	public static function query($sql) {
		global $last_db_sql;

		$last_db_sql = $sql;

		$query = mysql_query($sql);
		if ($error_message = self::error()) {
			throw new Exception($error_message." \n".'Last Query: '.self::get_last_sql());
		}

		return $query;
	}

	/**
	 * Wrapper function for mysql_fetch_array
	 * 
	 * @param Resource $query The query to fetch from.
	 * @see mysql_fetch_array();
	 */
	public static function fetch($query) {
		return mysql_fetch_array($query, MYSQL_ASSOC);
	}

	/**
	 * Wrapper function for mysql_insert_id
	 * 
	 * @see mysql_insert_id()
	 */
	public static function insert_id() {
		return mysql_insert_id();
	}

	/**
	  * Wrapper function for mysql_num_rows
	  * 
	  * @param Resource $query The mysql query;
	  * @see mysql_num_rows();	  * 
	  */ 
	public static function num_rows($query) {
			
		return mysql_num_rows($query);
	}

	/**
	 * Wrapper function for mysql_error
	 * 
	 * @see mysql_error()
	 */
	public static function error() {
		return mysql_error();
	}

	/**
	 * Builds and executes a query to update the database
	 * 
	 * @param String $table The table to update
	 * @param Array $data The values to set the table
	 * @param Mixed $conditional The conditionals to limit the query
	 * 
	 * @see qp::build_conditional
	 * @see qp::build_update_sql
	 */
	public static function update_records($table, $data, $conditional = ''){
		$sql = self::build_update_sql($table, $data, $conditional);
		return self::query($sql);
	}

	/**
	 * Builds and executes a query to insert a record into the database
	 * 
	 * @param String $table The table to update
	 * @param Array $data The values to set the table
	 * 
	 * @see qp::build_insert_sql()
	 */
	public static function insert_record($table, $data) {
		$sql = self::build_insert_sql($table, $data);
		$query = self::query($sql);
		return $query;
	}

	/**
	 * Builds and executes a query to insert multiple records into the database
	 * 
	 * @param String $table The table to update
	 * @param Array $data The values to set the table
	 * 
	 * @see qp::build_inserts_sql()
	 */
	public static function insert_records($table, $data) {
		if (! sizeof($data)) {
			return false;
		}
		$sql = self::build_inserts_sql($table, $data);
		$query = self::query($sql);
		return $query;
	}

	public static function delete_records($table, $conditional = NULL) {
		$sql = 'DELETE FROM '.self::escape_table($table).self::build_conditional($conditional, $table);
		return self::query($sql);
	}

	/**
	 * Builds and executes a query to get a single record from the database
	 * 
	 * @param String $table The Table to query
	 * @param Mixed $fields The field or fields to query
	 * @param Mixed $conditional An array of key values for an AND query, or sql
	 * 
	 * @see qp::get_record_sql()
	 * @see qp::get_records()
	 */
	public static function get_record($table, $fields = '*', $conditional = '') {
		$sql = self::build_select_sql($table, $fields, $conditional, 1);

		return self::get_record_sql($sql);
	}

	/**
	 * Queries a database and returns a single record
	 * 
	 * @param String $sql The SQL to execute
	 * 
	 * @see qp::get_record();
	 * @see qp::get_records_sql();
	 */
	public static function get_record_sql($sql) {
		$query = self::query($sql);

		if (! self::num_rows($query)) {
			return null;
		}

		return self::fetch($query);
	}



	/**
	 * Builds and executes a query to get a single record from the database
	 * 
	 * @param String $table The Table to query
	 * @param Mixed $fields The field or fields to query
	 * @param Mixed $conditional An array of key values for an AND query, or sql
	 * 
	 * @see qp::get_record_sql()
	 * @see qp::get_records()
	 */
	public static function get_field($table, $field, $conditional = '') {
		$sql = self::build_select_sql($table, Array($table.'_id', $field), $conditional, 1);

		$record = self::get_record_sql($sql);
		if ($record) {
			return $record[$field];
		} else{
			return null;
		}
	}

	/**
	 * Builds and executes a query to return a single field from multiple records
	 * 
	 * @param String $table The Table to query
	 * @param Mixed $fields The field or fields to query
	 * @param Mixed $conditional An array of key values for an AND query, or sql
	 * @param Number $limit The number of records to limit the query to
	 * @param Number $offset The number to offset the query by
	 * 
	 * @see qp::get_records_sql()
	 * @see qp::get_record()
	 */
	public static function get_fields($table, $field, $conditional = '', $limit = 0, $offset = 0) {
		$sql = self::build_select_sql($table, Array($table.'_id', $field), $conditional, $limit, $offset);
		$query = self::query($sql);

		$primary_key = $table.'_id';

		$records = Array();

		while ($record = self::fetch($query)) {
			if (isset($record[$primary_key])) {
				$records[$record[$primary_key]] = $record[$field];
			} else {
				$records[]= $record[$field];
			}
		}

		return $records;
	}

	/**
	 * Builds and executes a query to return a single field from multiple records indexed by a key
	 * 
	 * @param String $table The Table to query
	 * @param Mixed $fields The field or fields to query
	 * @param String $key The key to use as an index
	 * @param Mixed $conditional An array of key values for an AND query, or sql
	 * @param Number $limit The number of records to limit the query to
	 * @param Number $offset The number to offset the query by
	 * 
	 * @see qp::get_records_sql()
	 * @see qp::get_record()
	 */
	public static function get_fields_key($table, $field, $key, $conditional = '', $limit = 0, $offset = 0) {
		$sql = self::build_select_sql($table, Array($key, $field), $conditional, $limit, $offset);
		$query = self::query($sql);

		$records = Array();

		while ($record = self::fetch($query)) {
			$records[$record[$key]] = $record[$field];
		}

		return $records;
	}



	/**
	 * Builds and executes a query to return multiple records
	 * 
	 * @param String $table The Table to query
	 * @param Mixed $fields The field or fields to query
	 * @param Mixed $conditional An array of key values for an AND query, or sql
	 * @param Number $limit The number of records to limit the query to
	 * @param Number $offset The number to offset the query by
	 * 
	 * @see qp::get_records_sql()
	 * @see qp::get_record()
	 */
	public static function get_records($table, $fields ='*', $conditional = '', $limit = 0, $offset = 0) {
		
		$sql = self::build_select_sql($table, $fields, $conditional, $limit, $offset);
		return self::get_records_sql($sql, $table.'_id');
	}

	/**
	 * Queries a database and returns multiple records
	 * 
	 * @param String $sql The SQL to execute
	 * 
	 * @see qp::get_records();
	 * @see qp::get_record_sql();
	 */
	public static function get_records_sql($sql, $pkey = null) {
		$query = self::query($sql);
		if (! $query) {
			return Array();
		}
		$records = Array();

		while ($record = self::fetch($query)) {
			if (isset($record[$pkey])) {
				$records [$record[$pkey]] = $record;
			} else {
				$records []= $record;		
			}
		}
		return $records;		
	}

	/**
	 * Builds the SQL for an INSERT query for multiple records
	 * 
	 * @param String $table The Table to insert into
	 * @param Array $data The data to insert
	 * 
	 * @see qp::insert_records()
	 */
	public static function build_inserts_sql($table, $data) {
		
		$sql = 'INSERT INTO '.self::escape_table($table).PHP_EOL.
			'('.join(',', array_map(Array(get_class(), 'escape_column'), array_keys(reset($data)))).')'.PHP_EOL.
			' VALUES ';

		$inserts = Array();
		foreach ($data as $datum) {
			$inserts []= '('.join(',', array_map(Array(get_class(), 'escape_value'), $datum)).')';
		}

		$sql .= join(', ', $inserts);

		return $sql;
	}

	/**
	 * Builds the SQL for an INSERT query for only one record
	 * 
	 * @param String $table The Table to insert into
	 * @param Array $data The data to insert
	 * 
	 * @see qp::insert_record()
	 */
	public static function build_insert_sql($table, $data) {
		$sql = 'INSERT INTO '.self::escape_table($table).PHP_EOL.
			'('.join(',', array_map(Array(get_class(), 'escape_column'), array_keys($data))).')'.PHP_EOL.
			' VALUES ('.join(',', array_map(Array(get_class(), 'escape_value'), $data)).')';

		return $sql;
	}

	/**
	 * Builds the SQL for an UPDATE query
	 * 
	 * @param String $table The table to update
	 * @param Array $data The values to update the table to.
	 * @param Mixed $conditional The conditionals to use to update
	 * 
	 * @see qp::build_conditional()
	 * @see qp::update_records();
	 */
	public static function build_update_sql($table, $data, $conditional = '') {
		$sql = 'UPDATE '.
			self::escape_table($table).PHP_EOL.
			self::build_set_sql($data).PHP_EOL.
			self::build_conditional($conditional, $table);

		return $sql;
	}

	/**
	 * Builds the SQL for a SELECT query
	 * 
	 * @param String $table The table name to select from
	 * @param Mixed $fields The field or fields to select
	 * @param Mixed $conditional The conditionals to use to select
	 * @param Number $limit The number of records to return
	 * @param Number $offset The offset for pagination purposes
	 * 
	 * @see qp::build_conditional()
	 * @see qp::get_records()
	 * @see qp::get_record()
	 */
	public static function build_select_sql($table, $fields = '*', $conditional = '', $limit = 0, $offset = 0) {
		
		$field_str = self::build_field_sql($fields);

		$sql = 'SELECT '.$field_str.' FROM '.self::escape_table($table).self::build_conditional($conditional, $table);

		if ($limit) {
			if ($offset) {
				$sql .= ' LIMIT '.$offset.', '.$limit;
			} else {
				$sql .= ' LIMIT '.$limit;
			}
		}
		return $sql;
	}

	/**
	 * Builds the SET SQL for update queries
	 * 
	 * @param Array $data for the update
	 */
	private static function build_set_sql($data) {
		$sets = Array();
		foreach ($data as $column => $value) {
			$sets []= self::escape_column($column).'='.self::escape_value($value);
		}
		if ($sets) {
			return ' SET '.join(', ', $sets);	
		} else {
			return '';
		}
	}

	/**
	 * Builds the SQL to list the fields for a generated query
	 * 
	 * @param mixed $fields A field or list of fields
	 */
	private static function build_field_sql($fields) {
		return is_array($fields)?join(', ', array_map(Array(get_class(), 'escape_column'), $fields)):self::escape_column($fields);
	}

	/**
	 * Builds the SQL for a general purpose conditional string.
	 * 
	 * Given a hash, this function will combine the conditions as
	 * an AND, if any of those values are an array, write it as an 
	 * IN command. 
	 * 
	 * If the input text is a string, use that as the only condition.
	 * 
	 * @param mixed $where The string or list of conditions to build the sequel from.
	 */
	private static function build_conditional($where, $table = NULL) {
		if (! $where) {
			return '';
		}
		$str = ' WHERE ';
		if (is_array($where) || is_object($where)) {
			$conds = Array();
			foreach ($where as $column => $value) {
				if (is_string($column)) {
					if (is_array($value)) {
						$conds []= self::escape_column($column).' IN ('.join(', ', array_map(Array(get_class(), 'escape_value'), $value)).')';
					} else {
						$conds []= self::escape_column($column).'='.self::escape_value($value);
					}
				} else {
					$conds []= $value;
				}
			}
			$str .= join(' AND ', $conds);
		} else if (is_numeric($where) && ! is_null($table)) {
			$str .= self::escape_column($table.'_id').'='.self::escape_value($where);
		} else {
			$str .= $where;
		}
		return $str;
	}
}