Location: PHPKode > scripts > Table Data Management > table-data-management/TableDataManagement.class.php
<?php
/**
* The TableDataManagement class is used as an interface to a database table.
* The user of the object is given a tool with which direct ac
*
* <br><i>How does it work?</i><br>
* On initalization the object immediatly delivers itself knowledge about the table
* it is interfacing.
* After the initialization the object knows:
* - what primary keys are defined
* - what the type of a column is (used in the select, save and update queries)
*
*
* After the object has been initialized the user can than either @see load() the object
* with a specific record, or @see save() a new record into the table.
* 
* <br><i>Benefits</i><br>
* - select, update, save, delete records without having to write specific queries
* - keep code clean by passing all needed data trough to the TDM object and calling
*   the specific methods.
*
* @package	database
* @see 		MySQLConnection, TableColumn
* @author	S.Radovanovic
* @package 	libraries_php_classes_database
* @version 	1.0
*/
class TableDataManagement {

	var $_arrSaveColumns 		= array();		//Array containing the columns that are initially saved (mostly all)
	var $_arrUpdateColumns 		= array();		//Array containing the columns that may be updated
	var $_arrPRIColumns 		= array();		//Array containing the primary key columns
	var $_arrVirtualColumns		= array();		//Array containing columns that don't exist in the database (so we can keep a record of inserted data)
	var $_arrStateColumns		= array();		//We need to remember the state of some columns, so that we can take (from the child), neccessary actions
	
	var $_arrObjTableColumns	= array();		//The array containing all the tablecolumn objects		
	
	var $_tablename;			//What table will we interface to?	
	
    var $_objDbConn;			//Database object
    
	/**
	* Constructor
	* @param 		DbConn Connection to database (reference)
	* @param 		String Name of the table to be managed
	*/
	function TableDataManagement(&$objDbConn, $tablename) {
		$this->_tablename = $tablename;
		$this->_objDbConn = &$objDbConn;
        
		//Initialize the objects before any values are inserted
        $this->_init();        
    }
    
    /** 
     * Call through to the 'real' constructor (if implemented for PHP5)
	 * @param 	objDbConn Connection to database (reference)
	 * @param 	String Name of the table to be managed     
     */
    function __constructor(&$objDbConn, $tablename) {
    	$this->TableDataManagement(&$objDbConn, $tablename);
    }
    
	/**
	* External initialization to set the database connection
	* @param	objDbConn connection to the database
	*/	    
    function init(&$objDbConn) {
        $this->_objDbConn = &$objDbConn;
    }
    
    /**
    * Return the name of the table used for managing
    * @return 		String Name of the table
    */
    function getTableName() {
    	return $this->_tablename;
    }
    
    /**
    * Set the data of the TableDataManagement
    * @param 		column String the column where the value has to be stored into
    * @param 		value String the value of the column
    * @access		public
    */
    function set($column, $value) {
        $ref = &$this->_arrObjTableColumns[$column];
        if(is_object($ref)) {
        	//Check to see if we have to keep the state of the newly set column
        	$refState = &$this->_arrStateColumns[$column];
        	if(is_object($refState)) {
        		$refState->setValue($ref->getValue());
        	}
        	//Set the new value
            $ref->setValue($value);
            
        } else {
        	//If the column doesn't exists in the db, we store the data in a backup array (we may want to read it)
        	//The data isn't available through objects, but directly
        	$this->_arrVirtualColumns[$column] = $value;
        }
    }
    
    /**    
    * Set the data of the TableDataManagement passed through an array
    * @param 		arrData Array array containing all data (+ overhead, but that will be skipped)
    * @access		public
    */
    function setArray($arrData) {
    	if(is_array($arrData)) {
    		foreach($arrData as $column=>$value) {
    			$this->set($column, $value);
    		}
    	} else {
    		trigger_error("This is not an array ($arrData)", WARNING);
    	}
    }    
    
    
    /**
     * Set the label of a specific column
     * @param String $label
     */
    function setColumnLabel($column, $label) {
        $ref = &$this->_arrObjTableColumns[$column];
        if(is_object($ref)) {
        	//Set it
            $ref->setLabel($label);            
        }    	
    }        
    
    
    /**
    * 			Get the TableDataManagement information
    * @param 		column String the column to be returned
    * @param 		db Bool do we have to return the db value?
    * @return 		String the value of the selected column
    * @access		public
    */
    function get($column, $db = false) {
        $ref = &$this->_arrObjTableColumns[$column];
        if(is_object($ref)) {
            if($db) return $ref->getDBValue(); else return $ref->getValue();
        } else {
        	return 'X_NULL_X';
        }
    }
    
    
    /**
    * 			Get data for columns that aren't specified in the TDM object
    * @param 		column String the column to be returned
    * @param 		db Bool do we have to return the db value?
    * @return 		String the value of the selected column
    * @access		public
    */
    function getVirtual($column) {
        if(isset($this->_arrVirtualColumns[$column])) {
        	return $this->_arrVirtualColumns[$column];
        } else {
        	return 'X_NULL_X';
        }
    }    
    
    
    /**
     * Get data for columns that is specified through rememberState
     * @param 	column String the column to be returned
     * @return 	String the value of the selected column
     * @access	protected
     */
    function _getState($column) {
        if(isset($this->_arrStateColumns[$column])) {
        	return $this->_arrStateColumns[$column];
        } else {
        	return 'X_NULL_X';
        }
    }    
    
    
    /**
    * 			Get the object specified by the column in column.
    * @param		column String the object to be returned
    * @return 		Object return the object specified by column
    * @access		Public
    */
    function getObject($column) {
    	$ref = &$this->_arrObjTableColumns[$column];
    	return $ref;
    }
    
    
    /** 
     * Remember the state of the column specified in the parameter.
     * The state columns are only interesting for columnvalues that may be updated 
     * (so the updatecolumns array).
     * 
     * @param	String columnname
     * @access 	Protected
     */
    function _rememberState($columnname) {
    	$this->_arrStateColumns[$columnname] = $this->_arrUpdateColumns[$columnname];	
    }   
    
    
    /**
    * Save the TableDataManagement data to the database.
    * @access		Public
    */
    function save() {
       	$arrColumns = $this->_arrSaveColumns;

        $this->_objDbConn->query($this->_objDbConn->generateQuery('insert', $this->_tablename, $arrColumns));
        
        //Set the id through back-retrieval from the database
        //This is also a bit of a hack, since the rest of the code works with the primary keys,
        //but for now this must do
        if(isset($this->_arrObjTableColumns["id"])) {
        	$this->_arrObjTableColumns["id"]->setValue($this->_objDbConn->insertId());
        } else {
        	//use the primary columns instead (this will only work, if we have one primary column defined 
        	//(we can't add an instert id to more than one column)
        	//Don't forget to rewind the array (in case we are ahead)
        	reset($this->_arrPRIColumns);
        	$key = key($this->_arrPRIColumns);
        	//Retrieve the last insert ID
        	$insertId = $this->_objDbConn->insertId();
        	$this->_arrPRIColumns[$key]->setValue($insertId);
        }
        $this->_reload();
    }
    
    
    /**    
     * Update the TableDataManagement data in the database
     * @param	Array Array containing the columns to be updated (can also be set through @see TableDataManagement.seeArray)
     */
    function update($arrExtColumns = null) {  
    	
    	//Update data can also be delivered directly (and through setArray)
    	if(isset($arrExtColumns)) {
    		//depending on if a flat array was sent through (containing column=>value matches),
    		//or a array containing tablecolumn objects, we must act differently
    		$arrayValue = current($arrExtColumns);
    		reset($arrExtColumns); //rewind
    		if(is_object($arrayValue)) {
    			//We are already dealing with objects (so we'll asume they are tablecolumn objects)
    			$arrColumns = &$arrExtColumns;
    			
    		} else {
    			//it's flat
    			$this->setArray($arrExtColumns);	
        		$arrColumns = &$this->_arrUpdateColumns;        			
    		}
    		
    	} else {
    		//If no param was entered, we must retrieved the otherwise loaded columns
    		$arrColumns = &$this->_arrUpdateColumns;
    	}
    	
    	//Retrieve the where part
        $arrWhere = $this->_arrPRIColumns;
        $this->_objDbConn->query($this->_objDbConn->generateQuery('update', $this->_tablename, $arrColumns, $arrWhere));              
        $this->_reload();
    }

    
    /**
    * 			Delete the data from the database (specified by it's on set key)
    */
    function delete() {
    	$sqlDelete = "DELETE FROM ".$this->_tablename." WHERE ".$this->_getWhere();
    	$this->_objDbConn->query($sqlDelete);
    	//after a delete we can clean the object (that was probably loaded)
    	$this->_init();
    }
        
    /**
    * 			When 'TableDataManagement' information is requested for a specific id, then we have to load all 'TableDataManagement' information.
    * @param 		Array Array containg the keyvalues of the requested record
    * @access		Public
    * @return 	Bool Succes or not?
    */
    function load($arrPRIColumns = null) {
    	if(!is_null($arrPRIColumns)) {
    		//Set the received id('s)
			$this->setArray($arrPRIColumns);
    	}
    	    		
    	//Create the load query
    	$this->_objDbConn->query("SELECT * FROM ".$this->_tablename." WHERE ".$this->_getWhere());
		if($row = $this->_objDbConn->result()) {
			//For each column in the medewerker table data is inserted into the medewerker class, allthough not every variable
			//will be used, for future use, this can come in handy
			foreach($row as $column=>$value) {
				$this->set($column, $value);			
			}					
			
			//Let the caller now the load was succesful
			return true;
		} else {
			//This means that a load has been done with no succes, this will mostly fail if the where part
			//consists out of more then one key and the second key doesn't match
			//If the load fails, then we empty the primary key data (so that it isn't used in some way (f.e. in a form)
			foreach($this->_arrPRIColumns as $colname) {
				$this->set($colname->getName(), 0);
			}
			
			//we must also empty all data columns, since no data was found on base of the specified keys
			foreach($this->_arrObjTableColumns as $column=>$obj) {
				$this->set($column, '');
			}
			
			//Let the caller now the load wasn't succesful
			return false;
		}   				
    }
    
    /**
    * 			Reload the object with data from the database
    * @access		Public
    */
    function _reload() {   
    	$this->load();
    }
    
    /**
    * 			Creates the where part of a query necessary for delete and update statements
    * @return		String whereData The string containing the where query   
    * @access		Private
    */
    function _getWhere() {    	
    	//Generate the where query
    	foreach($this->_arrPRIColumns as $tablecolumn) {
			if(is_object($tablecolumn)) {
		    	if(isset($whereData)) $whereData.= " AND ";
		    	$whereData.= $tablecolumn->getName()." = ".$tablecolumn->getDBValue();
			}
    	}
    	return $whereData;    	
    }
    
    /**
    * 		Retrieve the update column array
    * @return 	Array Update column array
    */
    function getUpdateColumnArray() {
    	return $this->_arrUpdateColumns;
    }
    
    /**    
    * 		Internal initialization of the tablecolumns (all columns used by the TableDataManagement table)
    * @access	Private
    */
    function _init() {
    	//Retrieve the column information    	
    	$this->_objDbConn->query("SHOW COLUMNS FROM ".$this->_tablename);
    	while($row = $this->_objDbConn->result()) {
    		$columnName = $row["Field"];
    		
    		//create the tablecolumn object
    		$this->_arrObjTableColumns[$columnName] = new TableColumn ($columnName, $row["Type"]);

    		//DON'T FORGET : We must make references to the table column objects, since the data is prone to change
    		//just a thing that I forgot :)
    		   		
    		//add the column to the save arr (we can just save all, since it's an insert and auto_increment fields will update themselves
    		$this->_arrSaveColumns[$columnName] = &$this->_arrObjTableColumns[$columnName];
    		
    		//add the column to the update arr (primary keys may not be updated)
    		if(!strcmp($row["Key"], "PRI")) {    
    			//add to the primary key array (an array, since there can be more then one primary key)
    			$this->_arrPRIColumns[$columnName] = &$this->_arrObjTableColumns[$columnName];
    		} else if (!isset($this->_arrPRIColumns[$columnName])) {
    			//add to the update array
    			$this->_arrUpdateColumns[$columnName] = &$this->_arrObjTableColumns[$columnName];
    		}    		
    	}
    	
    }
  
}
?>
Return current item: Table Data Management