<?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];
}
}
}
}
?>