Location: PHPKode > projects > Nexty > nexty/includes/classes/ORM.php
<?php
class DBTable {
	////////////////////////////////////// Variables /////////////////////////////////
	private $table_name = '';
	private $query = '';
	private $conditions = array();
	private $select = '*';
	private $limit = false;
	private $offset = 0;
	private $order = '';
	private $group = '';
	private $query_result_type = 'all';

	public $field = array();
	public $primary_key_field = 'id';
	public $primary_key_value = 0;
	public static $mode = 'p'; ///Mode - p = Production, d = Development and t = Testing
	
	///Constructor
	function __construct($table_name) {
		$this->table_name = $table_name;
	}

	//////////////////////////////////// Public Functions ////////////////////////////
	/**
	 * Creates a query based on the data given to this object. 
	 * Return : $query - The created Query
	 */
	function createQuery() {
		$query = "SELECT {$this->select} FROM {$this->table_name}";
		
		if(count($this->conditions)) $query .= ' WHERE ' . implode(' AND ',$this->conditions);
		if($this->order) $query .= ' ORDER BY ' . $this->order;
		if($this->group) $query .= ' GROUP BY ' . $this->group;
		if($this->limit) $query .= ' LIMIT ' . $this->offset . ',' . $this->limit;

		return $this->query = $query;
	}
	
	/**
	 * Creates a query and executes based on the data given to this object. 
	 * Return : $result - The result of the query.
	 */
	function createExecQuery($type=false) {
		if($type === false) $type = $this->query_result_type;
		
		$this->createQuery();
		return $this->_execQuery($type);
	}
	
	/**
	 * This function is enough to create and execute a query.
	 * 		You can specify any number of argument. If the argument is a number, this function will assume that 
	 *		it is an ID. If it is an array, it will be send to setRequirement() function. If it is a stirng,
	 *		it will be assumed to be a where clause.
	 * Argument : See description
	 * Return : The result of the executed query
	 * Example :$User->find(15);  = SELECT * FROM ${config['db_prefix']}User WHERE id=15
	 *			$Project->find("user_id=15","name LIKE '%Nexty%'");  = SELECT * FROM ${config['db_prefix']}Project WHERE user_id=15 AND name LIKE '%Nexty'
	 */
	function find() {
		$arguments = func_get_args();
		
		$ids = array();
		foreach($arguments as $arg) {
			if(is_numeric($arg)) {
				$ids[] = $arg;
			} elseif(is_array($arg)) {
				$this->setRequirement($arg);
			} elseif(is_string($arg)) {
				$this->where($arg);
			}
		}
		if($ids)
			return $this->findById($ids);
		
		return $this->createExecQuery('all');
	}
	
	/** 
	 * Specify the WHERE statements here. 
	 * Arguments : A list of all the conditions
	 * Example: $User->where(array("name='Binny'","age=23"));
	 *			$User->where("name='Binny'","age=23");
	 */
	function where() {
		$conditions = $this->_getArguments(func_get_args());
		foreach($conditions as $cond) {
			if(is_string($cond))
				if(!in_array($cond,$this->conditions)) $this->conditions[] = $cond;
		}
	}
	
	/// Returns the results of all the rows whose IDs are provided
	function findById() {
		$ids = $this->_getArguments(func_get_args());
		
		if(!count($ids)) return false;
		
		if(count($ids) == 1) { //Just one ID
			$this->where("`{$this->primary_key_field}`='$ids[0]'");
		} else { //Multiple IDs - use IN to get all the data
			$this->where("`{$this->primary_key_field}` IN (" . implode(",", $ids). ")");
		}

		$data = $this->createExecQuery('assoc');
		
		if(count($ids) == 1) {
			$this->field = $data;
			$this->primary_key_value = $ids[0];
		}

		return $data;
	}
	
	/// Pass which all fields must be selected into this function
	function select() {
		$arguments = func_get_args();
		
		$select = '*';
		if(count($arguments)) {
			if(count($arguments) == 1 and is_array($arguments[0])) { //If the first argument is the list of fields
				$arguments = $arguments[0];
			}
			
			$select = '`' . implode('`,`',$arguments) . '`';
		}
		$this->select = $select;
	}
	
	/// Write the changes to the DB. If its a new row, an insert will happen. If it is an existing row, an update
	function save($id = 0) {
		global $sql;
		if($id) $this->primary_key_value = $id;

		if(!count($this->field)) return false;
		$return_value = -1;
		
		if($this->primary_key_value) { //If we have the private key, it is an existing row - so do an update
			$this->query = "UPDATE `{$this->table_name}` SET ";
			$update_array = array();
			foreach($this->field as $field_name => $field_value) {
				if ($sql->isKeyword($field_value)) { //If the is values has a special meaning - like NOW() give it special consideration
					$update_array[] = "`$field_name`=$field_value";
				} else {
					$update_array[] = "`$field_name`=" . $this->_escape($field_value);
				}
			}
			$this->query .= implode(', ',$update_array);
			
			$this->where("`{$this->primary_key_field}`={$this->primary_key_value}");
			$this->query .= ' WHERE ' . implode(' AND ',$this->conditions);

			$this->_execQuery('exec');
			$return_value = $sql->fetchAffectedRows();

		} else { //New row - do an insert
			$field_names = array_keys($this->field);
			$field_values = array_values($this->field);

			for($i=0; $i<count($field_values); $i++) {
				if(!$sql->isKeyword($field_values[$i])) {//Quote the value if it is not a Function call
					$field_values[$i] = $this->_escape($field_values[$i]);
				}
			}
			
			$this->query = "INSERT INTO `{$this->table_name}` (`" . implode('`,`', $field_names) . '`) '
					. ' VALUES (' .implode(",", $field_values) . ')';

			$this->_execQuery('exec');
			$return_value = $sql->fetchInsertId();
		}
		$this->field = array(); //Reset the data after the save
		
		return $return_value;
	}
	
	/**
	 * Execute a DELETE statement. If the ID of the deleted row is not specified as the argument, 
	 *		the function will use just the where clause. Be careful - if the where clauses are not present, 
	 *		kiss you table goodbye!
	 * Arguments : An ID or a list of IDs - Optional
	 * Returns : Affected Row count
	 */
	function delete() {
		global $sql;

		$this->query = "DELETE FROM `{$this->table_name}` ";
		$ids = $this->_getArguments(func_get_args());
		if(count($ids)) {
			if(count($ids) == 1) {
				$this->query .= " WHERE `{$this->primary_key_field}` = " . $ids[0];
			} else {
				$this->query .= " WHERE `{$this->primary_key_field}` IN (" . implode(",", $ids). ")";
			}
			if(count($this->conditions)) $this->query .= ' AND ' . implode(' AND ',$this->conditions);

		} else { //Remove muliptle rows at once
			if(count($this->conditions)) $this->query .= ' WHERE ' . implode(' AND ',$this->conditions);
		}
		$this->_execQuery('exec');
		return $sql->fetchAffectedRows();
	}

	///Resets all the data of the previous query
	function newRow($id = 0) {
		$this->query = '';
		$this->field = array();
		$this->primary_key_value = $id;
		$this->conditions = array();
		$this->select = '*';
		$this->limit = false;
		$this->offset = 0;
		$this->order = '';
		$this->group = '';
		$this->query_result_type = 'all';
	}
	
	function setRequirement($arg) {
		//The where clauses
		if(isset($arg['conditions']))	$this->where($arg['conditions']);
		if(isset($arg['where']))		$this->where($arg['where']);

		// LIMIT offset, limit
		if(isset($arg['limit'])) $this->limit = $arg['limit'];

		// LIMIT offset, limit
		if(isset($arg['offset'])) $this->offset = $arg['offset'];

		// ORDER BY order
		if(isset($arg['order'])) $this->order = $arg['order'];
		
		// GROUP BY group
		if(isset($arg['group'])) $this->group = $arg['group'];
		
		// SELECT select
		if(isset($arg['select'])) $this->select($arg['select']);
		
		if(isset($arg['result_type'])) $this->query_result_type = $arg['result_type'];
		
		//If its just array with none of our 'special' keys - it is consided to be an array of where clauses
		if(!isset($arg['conditions']) and !isset($arg['where']) and !isset($arg['limit']) 
				and !isset($arg['offset']) and !isset($arg['order']) and !isset($arg['group']) 
				and !isset($arg['select']) and !isset($arg['result_type'])) {
			$this->where($arg);
		}
	}


	//////////////////////////////////////////// The Privates /////////////////////////////////////
	private function _getArguments($id_list) {
		$arguments = $id_list;
		if(count($arguments) == 1 and is_array($arguments[0])) { //If the first argument is the list(array) of IDs
			$arguments = $arguments[0];
		}
		return $arguments;
	}
	
	/// The SQL is executed only here.
	private function _execQuery($return_type) {
		global $sql;
		$result = array();
		
		if(DBTable::$mode == 't') { //Just testing, fools!
			print $this->query . '<br />';

		} else {
			$result = array();
			if($return_type == 'assoc') {
				$result = $sql->getAssoc($this->query);
			} elseif($return_type == 'all') {
				$result = $sql->getAll($this->query);
			} elseif($return_type == 'one') {
				$result = $sql->getOne($this->query);
			} elseif($return_type == 'byid') {
				$result = $sql->getById($this->query);
			} else { //exec
				$sql->getSql($this->query);
			}
		}
		$this->newRow();
		
		return $result;
	}
	
	private function _escape($string) {
		global $sql;
		return "'" . $sql->escape($string) . "'";
	}
}
Return current item: Nexty