Location: PHPKode > scripts > Light Table > light-table/Db_Light_Table.php
<?php
/**
 * This class allows you to access the data from your table, without you create querys for this.
 * Without worrying about the RDBMS you're using.
 * It uses the package Zend_Db of the ZF "Zend Framework".
 * It is necessary to have installed the ZF.
 * @author Fabio Xavier de Lima
 * @version 1.0 - Creation Date: 02/07/2008 - Last Modfied: 02/07/2008
 **/

require_once 'ConnectionDB.php';

class LightTable {

	protected $name;
	protected $PrimaryKey;
	
	private $connect;
	private $db;
	
	/**
	 * Builder of the class.
	 * Receive an array containing a record of the table(optional).
	 * Create an instance of connection class with database.
	 * 
	 * @param array $record - datas returned of the an statement select.
	 */
	function __construct($record=null){
		
		if(isset($record)){
			$this->SetClasse($record);
		}
		
	 //	instance of connection class with database
		$this->connect = new ConnectDB('Oracle');
	}
	
	/**
	* set the value in property informed.
	*/
	function __set($property, $value){
		if (isset($property)){
			$this->$property = $value;
		}
	}
	
	/**
	 * Return the value of property informed.
	 *
	 * @param unknown_type $property - name of property.
	 * @return unknown - value of property
	 */
	function __get($property){
		return $this->$property;
	}
	
	/**
	 * Selects the records of the table.
	 * Mount where clause, with the properties reported.
	 *
	 * @param string $columns - Columns to be returned. If not informed, all returns.
	 * @return array $records - Records found.
	 */
	function select($columns = '*'){
		
		$this->db = $this->connect->getConnection();
		
		$select = $this->db->select();
		$select->from($this->name, $columns);
	//	Mounts of the WHERE clause 
		$select->where($this->SetWhere($this));
		
		$sql		= $select->__toString();
		$records	= $this->db->fetchAll($sql);
		
		$this->db->closeConnection();
		
		return $records;
	}
	
	/**
	 * Includes a record in the table, using the properties have been met.
	 * 
	 * Note: The primary key is disregarded.
	 * 
	 * @return integer $records - number of records included.
	 */
	function insert(){
	
	//	Only if the obejto was initialized
		if (isset($this)){
			
			$this->db = $this->connect->getConnection();
			
			$set_values = $this->Setcolumns();
			
		//	Set PrimaryKey
			if ($this->PrimaryKey != null) {
				array_merge($set_values, array($this->PrimaryKey => $this->db->nextSequenceId('INCREMENTO_'.$this->PrimaryKey),));
			}
			
		//	Execute
			$records = $this->db->insert($this->name, $set_values);
			
			$this->db->closeConnection();
		}
		
		return $records;
	
	}
	
	/**
	 * Update the records of the table, with values of the properties this object.
	 * Mount where clause, with the properties reported in the $table object(Opcional).
	 * Or with the primary key this object.
	 *
	 * @param class LightTable $table - LightTable obejct, with properties seted.
	 * @return integer $records - number of records updated.
	 */
	function update($table=null){
	
	//	Only if the obejto was initialized
		if (isset($this)){
			
			$this->db = $this->connect->getConnection();
			
			$set_values = $this->Setcolumns();
			
		//	Mount WHERE clause
			if (isset($table)){
				$where = $this->SetWhere($table);
			}else{
				$where = $this->__get($this->PrimaryKey);
			}
		//	Execute
			$records = $this->db->update($this->name, $set_values, $where);
			
			$this->db->closeConnection();
		}
		
		return $records;
	}
	
	/**
	 * Deletes the records of the table.
	 * That meet the criterion mounted with the properties reported in the $table object(Opcional).
	 * Or with the properties reported in this object.
	 * 
	 * @param class LightTable $table - LightTable object.
	 * @return integer $records - Number of records erased.
	 */	
	function delete(LightTable $table=null){
	
		if (isset($table)){
		//	Deletes the records, that meet the criterion mounted 
		//	with the properties reported in the $table object.
			$this->db = $this->connect->getConnection();
			
			$where = $this->SetWhere($table);
		}
		elseif (isset($this)){
		//	Deletes the records, that meet the criterion mounted 
		//	with the properties reported in this object.
			$this->db = $this->connect->getConnection();
			
			$where = $this->SetWhere($this);
		}
		
	//	Execute delete
		$rows_affected = $this->db->delete($this->name, $where);
		$this->db->closeConnection();
		
	//	Number of records erased
		return $rows_affected;
	}
	
	/**
	 * Create an array with current values of propertys
	 * Used by the methods insert() e update().
	 *
	 * @return array $set
	 */
	private function Setcolumns(){
	//	the primary key, is set in the insert() method.

		$set = array();
		
        foreach($this as $col => $val) {
		//	whether it has value, he adds in the array
			if ($val != null
				and $col != 'name'
				and $col != 'PrimaryKey'
				and $col != 'db'
				and $col != 'connect'){
            		$set = array_merge($set, array($col => $val));
            	}
        }
	//	just columns valued
		return $set;
	}
	
	/**
	 * set values in the property of the class LightTable.
	 *
	 * @param array $record - An item in the array returned in select() method. 
	 */
	private function SetClasse(array $record){
	
		foreach ($record as $col => $val) {
			$this->__set($col, $val);
		}
	}
	/**
	 * Receive an object LightTable, and creates "where clause", with values of the properties
	 *
	 * @return string $where
	 */
	private function SetWhere(LightTable $table){
	
		unset($where);
		
		foreach($table as $col => $val) {
		//	whether it has value, he adds in the array
			if ($val != null
				and $col != 'name'
				and $col != 'PrimaryKey'
				and $col != 'db'
				and $col != 'connect'){
				if (isset($where)) {
					$where .= ' AND ';
				}
				$where .= $this->db->quoteInto($col.' = ?', $val);
			}
		}
		return $where;
	}
}
?>
Return current item: Light Table