Location: PHPKode > scripts > mysql2object > mysql2object/table.class.php
<?

require_once('column.class.php');

/**
 * Table Class - part of MySQL2Object set of classes
 *
 * @author Arkadiusz Mali&#324;ski <hide@address.com>
 * @package mysql2object
 */
class Table {
	
	/**
	 * Table name
	 *
	 * @var string
	 */
	private $_name;
	
	/**
	 * Array of description values
	 *
	 * @var array
	 */
	private $_description;
	
	/**
	 * datasource object
	 *
	 * @var Datasource
	 */
	private $_db = false;
	
	/**
	 * Array of Column objects
	 *
	 * @var array
	 */
	private $_arColumns = array();
	
	/**
	 * Status of composing statement to gets data
	 *
	 * @var integer
	 */
	private $_statement = array();

	/**
	 * Results of composed statement
	 *
	 * @var array
	 */
	private $_statementResults = array();
	
	/**
	 * Last created statement name
	 *
	 * @var string
	 */
	private $_lastCreatedStatement = null;
	
	/**
	 * Array of valid comparision operands
	 *
	 * @var array
	 */
	private $_validOperators = array('==', '>=', '<=', '<', '>', '<>', '!=');
	
	
	/**
	 * Constructor, on start creates all table structures, inserts data and gets table description.
	 *
	 * @param array $arrTable
	 */
	public function __construct($arrTable, DB $DBobj) {
		$this->_db = $DBobj;
		$this->_name = $arrTable['name'];
		$this->_description = $arrTable['tblDescription'][0];
		
		for($i=0; $i<count($arrTable['colDescription']); $i++) {
			$col['name'] = $arrTable['colDescription'][$i]['Field'];
			$col['description'] = $arrTable['colDescription'][$i];
			$col['values'] = $arrTable['colValues'][$i];
			
			$this->_addColumn(new Column($col));
		}
	}
	
	
	/**
	 * Returns table name
	 *
	 * @return string
	 */
	public function __toString() {
		return $this->_name;
	}

	
	/**
	 * Handling calls
	 *
	 * @param string $func
	 * @return mixed
	 */
	public function __get($func) {
		if(array_key_exists($func, $this->_arColumns)) {
			return $this->_arColumns[$func];
		} elseif(array_key_exists($func, $this->_description)) {
			return $this->_description[$func];
		} elseif(array_key_exists($func, $this->_statementResults) && $this->_lastCreatedStatement == null) {
			return $this->_statementResults[$func];
		} else {
			switch (strtolower($func)) {
				case 'rows':
					return $this->_description['Rows'];
					break;
				case 'columns':
					return count($this->_arColumns);
					break;
				case 'all':
					return array_keys($this->_arColumns);
					break;
				case 'lastid':
					return $this->_db->lastid();
					break;
				default:
					throw new TableException('Unknown call "'.$func.'"');
			}
		}
	}

	
	/**
	 * Handling functions calls
	 *
	 * @param string $func
	 * @param mixed $args
	 * @return mixed
	 */
	public function __call($func, $args) {
		if(array_key_exists($func, $this->_arColumns)) {
			switch (count($args)) {
				case 1:
					switch (gettype($args[0])) {
						case 'integer':
							if (array_key_exists($args[0], $this->_arColumns[$func]->all)) {
								return $this->_arColumns[$func]->getValue($args[0]);
							} else 
								throw new TableException('Index out of bounds in "'.$func.'"');
							break;
						default:
							throw new TableException('Unknown method parameter in "'.$func.'"');
							break;
					}
					break;
			}
		} else {
			switch (strtolower($func)) {
				case 'createcomposedcondition':
					if(func_num_args()!=2)
						throw new TableException('Invalid number of parameters in method "createComposedCondition()"');
					elseif(array_key_exists($func, $this->_arColumns)) {
						throw new TableException('Cannot create condition with name "'.$func.'" because exist columnt with that name.');
					} else {
						if($this->_statement[$args[0]] || array_key_exists($func, $this->_statementResults))
							throw new TableException('Cannot create condition with name "'.$func.'" because it\'s already created.');
						else {
							$this->_statement[$args[0]] = 1;
							foreach ($this->_arColumns as $key=>$column) {
								$ret[$key] = $column->all;
							}
							$this->_statementResults[$args[0]] = $ret;
							$this->_lastCreatedStatement = $args[0];
							return null;
						}
					}
					break;
				case 'endcomposedcondition':
					if(func_num_args()!=2)
						throw new TableException('Invalid number of parameters in method "endComposedCondition()"');
					elseif(!strcmp($args[0],$this->_lastCreatedStatement) && $this->_statement[$args[0]]) {
							$this->_statement[$args[0]] = 0;
							$this->_statementResults[$args[0]];
							$this->_lastCreatedStatement = null;
					} else 
						throw new TableException('Cannot end condition "'.$func.'" because isn\'t started');
					break;
				default:
					throw new TableException('Unknown call "'.$func.'"');
					break;
			}
		}
	}

	
	/**
	 * Add values into table
	 *
	 * @param array $values
	 * @return int
	 */
	public function add($values = array()) {
		if(count($values)) {
			foreach ($values as $fieldname=>$value) {
				if(!array_key_exists($fieldname, $this->_arColumns))
					throw new TableException('Unknown column name "'.$fieldname.'" in "add()" method');
			}
			
			$sql = 'INSERT INTO '.$this->_name.'(';
			foreach ($values as $fieldname=>$value) {
				$sql .= '`'.$fieldname.'`, ';
			}
			$sql = substr($sql, 0, -2);
			$sql .= ') VALUES (';
			foreach ($values as $fieldname=>$value) {
				if(is_string($value) && !preg_match('/\(.*\)/', $value))
					$sql .= '"'.$value.'", ';
				else 
					$sql .= $value.', ';
			}
			$sql = substr($sql, 0, -2);
			$sql .= ')';
			
			return $this->_db->query($sql);
		} else 
			throw new TableException('Unknown values in "add()" method.');
	}
	
	
	/**
	 * Update/Replace values in table based on SQL condition
	 *
	 * @param array $values
	 * @param string $SQLCondition
	 * @param bool $replace
	 * @return integer
	 */
	public function modify($values = array(), $SQLCondition, $replace = false) {
		if(count($values)) {
			foreach ($values as $fieldname=>$value) {
				if(!array_key_exists($fieldname, $this->_arColumns))
					throw new TableException('Unknown column name "'.$fieldname.'" in "modify()" method');
			}
			
			$sql = ($replace ? 'REPLACE' : 'UPDATE').' '.$this->_name.' SET ';
			foreach ($values as $fieldname=>$value) {
				$sql .= '`'.$fieldname.'` = ';
				if(is_string($value) && !preg_match('/\(.*\)/', $value))
					$sql .= '"'.$value.'", ';
				else 
					$sql .= $value.', ';
			}
			$sql = substr($sql, 0, -2);
			if(strlen($SQLCondition))
				$sql .= ' WHERE '.$SQLCondition;
			
			return $this->_db->query($sql);
		}
	}


	/**
	 * Select columns to be vieved
	 *
	 * @return mixed
	 */
	public function columns() {
		$check = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $this->_statementResults[$this->_lastCreatedStatement] : $this->_arColumns);
		
		foreach (func_get_args() as $column) {
			if(!array_key_exists($column, $check))
				throw new TableException('Unknown column name "'.$column.'" in "columns()" method');
			}
		foreach (func_get_args() as $column) {
			$ret[$column] = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $check[$column] : $check[$column]->all);
		}
		
		if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
			$this->_statementResults[$this->_lastCreatedStatement] = $ret;
			return null;
		} else 
			return $ret;
	}
	
	
	/**
	 * Select values from table on defined conditions.
	 * As a parameters uses array(fieldname, operator, value_for_comparision) for each condition.
	 *
	 * @param array $condition1,...
	 * @return array of ColumnValues
	 */
	public function select() {
		if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement])
			$ret = $this->_statementResults[$this->_lastCreatedStatement];
		else 
			foreach ($this->_arColumns as $key=>$column) {
				$ret[$key] = $column->all;
			}
			
		if(func_num_args()) {
			foreach (func_get_args() as $param) {
				if(!array_key_exists($param[0], $ret))
					throw new TableException('Unknown column name "'.$param[0].'" in "select()" method');
				if(!in_array($param[1], $this->_validOperators))
					throw new TableException('Invalid comparision operator "'.$param[1].'"');
			}
			foreach (func_get_args() as $param) {
				$ret = $this->_test($param, $ret);
			}
			
			if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
				$this->_statementResults[$this->_lastCreatedStatement] = $ret;
				return null;
			} else 
				return $ret;
		} else {
			throw new TableException('No condition definied in "select()" method');
		}
	}
	

	/**
	 * Returns data sorted on defined columns
	 *
 	 * @param array $columnName,...
	 * @return mixed
	 */
	public function sort() {
		if(!func_num_args())
			throw new TableException('Required at least one column name in "sort()" method');
		else {
			$check = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $this->_statementResults[$this->_lastCreatedStatement] : $this->_arColumns);
			foreach (func_get_args() as $arg) {
				list($field, $sortMode) = explode(' ', $arg);
				if(!array_key_exists($field, $check))
					throw new TableException('Unknown column "'.$field.'" name in "sort()" method');
				else {
					$evalstr .= '$ret[\''.$field.'\'],';
					switch (strtolower($sortMode)) {
						default:
						case 'asc': $evalstr .= 'SORT_ASC,'; break;
						case 'desc': $evalstr .= 'SORT_DESC,'; break;
							
					}
				}
			}
			
			foreach ($check as $key=>$column) {
				$ret[$key] = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $column : $column->all);
				if(!preg_match('/'.$key.'/', $evalstr))
					$evalstr .= '$ret[\''.$key.'\'],';
			}
			eval('array_multisort('.substr($evalstr,0,-1).');');
			if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
				$this->_statementResults[$this->_lastCreatedStatement] = $ret;
				return null;
			} else
				return $ret;
		}
	}


	/**
	 * Returns data from two joined tables connected by one column from each
	 *
	 * @param Table $foreignTable
	 * @param Column $thisColumn
	 * @param Column $foreignColumn
	 * @param array $foreignColumnNames
	 * @return array
	 */
	public function join(Table $foreignTable, Column $thisColumn, Column $foreignColumn, $foreignColumnNames=array()) {
		if(array_key_exists($thisColumn->name, $this->_arColumns) && in_array($foreignColumn->name, $foreignTable->all)) {
			$check = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $this->_statementResults[$this->_lastCreatedStatement] : $this->_arColumns);

			foreach ($check as $key=>$column) {
				$ret[$key] = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $column : $column->all);
			}
			
			for($i=0;$i<count($ret[$thisColumn->name]);$i++) {
				$findValue = $ret[$thisColumn->name][$i];
				$otherKey  = array_keys($foreignColumn->all,$findValue);
				if(!count($foreignColumnNames)) {
					$columnsToAdd = $foreignTable->all;
				} else 
					$columnsToAdd = $foreignColumnNames;
				foreach ($columnsToAdd as $columnToAdd) {
					$ret[$columnToAdd][$i] = $foreignTable->$columnToAdd($otherKey[0]);
				}
			}
			
			if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
				$this->_statementResults[$this->_lastCreatedStatement] = $ret;
				return null;
			} else
				return $ret;
		} else
			throw new TableException('Invalid columns in "join()" statement.');
	}


	/**
	 * Returns union data from two tables. If tables has the same number of columnt second parameter isn't necessary.
	 *
	 * @param Table $foreignTable
	 * @param array $fieldConnections
	 * @return array
	 */
	public function union(Table $foreignTable, $fieldConnections = array()) {
		$check = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $this->_statementResults[$this->_lastCreatedStatement] : $this->_arColumns);

		foreach ($check as $key=>$column) {
			$ret[$key] = (($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) ? $column : $column->all);
		}

		if(count($fieldConnections)) {
			
			foreach ($fieldConnections as $thisField=>$foreignField)
				if(!array_key_exists($thisField, $ret) || !in_array($foreignField, $foreignTable->all))
					throw new TableException('Unknown columns connection "'.$thisField.'" <==> "'.$foreignField.'" in "union()" method.');
					
			foreach ($fieldConnections as $thisField=>$foreignField) 
				$newret[$thisField] = array_merge($ret[$thisField], $foreignTable->$foreignField->all);
			$ret = $newret;
			
		} elseif($foreignTable->columns == count(array_keys($ret))) {
			
			$foreignColumns = $foreignTable->all; $index = 0;
			foreach ($ret as $key=>$item) {
				$ret[$key] = array_merge($item, $foreignTable->$foreignColumns[$index++]->all);
			}
			
		} else
			throw new TableException('Number of columns in both tables are not identical.');
		
		if($this->_lastCreatedStatement && $this->_statement[$this->_lastCreatedStatement]) {
			$this->_statementResults[$this->_lastCreatedStatement] = $ret;
			return null;
		} else
			return $ret;
	}


	/**
	 * Test values and returns data which passes tests
	 *
	 * @param array $param
	 * @param array $arValues
	 * @return array
	 */
	private function _test($param, $arValues) {
		foreach ($arValues[$param[0]] as $index=>$value) {
			eval("\$returnedValue = ('$value' $param[1] '$param[2]');");
			if($returnedValue) $indexOK[] = $index;
		}
		
		foreach ($arValues as $k=>$v) {
			for ($i=0; $i<count($indexOK); $i++) {
				$return[$k][] = $v[$indexOK[$i]];
			}
		}
		return $return;
	}
	
	
	/**
	 * Adds column to table
	 *
	 * @param Column $col
	 */
	private function _addColumn(Column $col) {
		$this->_arColumns[$col->__toString()] = $col;
	}

}

class TableException extends Exception {}

?>
Return current item: mysql2object