Rev 4 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
/* database.class.php
Author: R. W. Rodolico (rodo at dailydata.net)
GNU Public License
20091023
Encapsulates a database, specifically targeting allowing a database and HTML forms to
interact. Main function is to allow editing of databases simply by defining the database
structure in a variable
This code was written in procedural form in my library.php, but the decision to "objectify"
it was made for ease of maintenance in the future. For example, I had thought to define
columns that would be editable
in an HTML widget, or special needs column types such as columns with special content (ie,
one application we have stores PHP in a text column, and another stores menus and reports in
two tables with very weird storage requirements). By simply adding extensions to the basic
DBColumn class, these special purpose uses can be rapidly created.
A database object may be created manually, or may be created as an array of information
*/
define( 'DEBUG', true );
require_once "Logging.class.php"; // For logging
/*
This is the basic column type. It is a simple string,
left justified on HTML display
<input type='text'> for form input
varchar for db storage
It is also used as the basis for all of the other column types
*/
/*
CSS Classes used
db_string display and input a string
db_field_name display table column header or td
db_textarea display and input textarea (td or <textarea>)
db_bool display and input true/false (radio)
db_date display and input date (text)
db_datetime display and input date and time (text)
db_int display and input integers
db_real display and input floating point numbers
db_password display and input password (password, display is a series of 8 asterisks)
db_file display and input file (type="file")
*/
class DBColumn { // base class for various column types
/*
A column has
name
type (used to define how it is processed)
nullable (if it can be null or not)
default value (used if creating a row)
required (must have a non-null value entered)
readOnly (will be displayed as text in an input form)
width (size of the actual field)
*/
public $columnName; // name in database
public $primaryKey; // true if this is a member of the primary key
public $displayName; // human readable name, uses $columnName if not defined
public $value; // in most cases, can hold the actual value (not type file, MTM)
public $required; // if false, may be set to null
public $default; // for not null columns, what value to use if it is null.
public $readOnly; // if set, field will be displayed and not editable
public $width; // width of input field and/or html display
public $nullable; // true/false if it can be null (as opposed to empty)
// Accepts a column name (required), and array of definitions, and a value (both optional)
public function __construct ( $columnName, $definition = null, $value=null) {
$this->columnName = $columnName;
$this->value = isset($value) ? $value : '';
$this->displayName = isset($definition['display name']) ?
$definition['display name'] :
$this->columnName;
$this->required = isset($definition['required']) ? $definition['required'] : false;
$this->default = isset($definition['default']) ? $definition['default'] : '';
$this->width = isset($definition['width']) ? $definition['width'] : 00;
$this->readOnly = isset($definition['readonly']) ? $definition['readonly'] : false;
if ( $this->default == 'null' ) {
$this->null = true;
$this->default = '';
}
$this->primaryKey = ( isset($definition['keyfield']) ? $definition['keyfield'] : false);
} // function __construct
function __toString() {
return isset( $this->value ) ? $this->value : 'null';
}
/*
* function simply returns the column name and the display name
* formatted so as to work correctly in an SQL select statement
* Will also prepend the table name passed in as a parameter
* (if it exists) to allow explicit definition of the column
* for multi-table queries
*/
public function getQueryColumn ( $tableName = null, $useDisplayName = false ) {
return array( 'fieldnames' => ($tableName ? "$tableName." : '' )
. $this->columnName
. ( $useDisplayName ? " '$this->displayName'" : '' )
);
} // getQueryColumn
/*
function takes a string, and looks for the array names in $values, replacing occurrences
of it with
*/
private function replaceTokens ( $string, $values ) {
foreach( $values as $token => $value ) {
$string = preg_replace("/$token/", $value, $string);
}
return $string;
}
function makeSafeSQLValue ( $value = null ) {
if ($value) {
$this->value = $value;
}
if (strlen($this->value)) { // it actually has a value
if(get_magic_quotes_gpc()) {
$this->value = stripslashes($this->value);
}
$this->value = mysql_real_escape_string( $this->value );
$this->value = "'" . $this->value . "'"; // put the quotes around it
} else {
$this->value = ($this->nullable ? 'null' : "''");
}
return $this->value;
}
function toHTML( $type = 'view', $parameters = '' ) {
$return = '';
switch ( $type ) {
case 'view': $return = "<tr><td>$this->columnName</td><td>$this->value</td></tr>";
break;
case 'list': $return = '<td>' . $this->value . '</td>';
break;
case 'edit': $return = "<tr><td>$this->columnName</td><td>$this->value</td></tr>";
break;
} // switch
return $return;
} // toHTML;
}
/* ============================================================================================
class DBColumnBool
Used for multi row columns, ie HTML TextArea's and DB Text columns
*/
class DBColumnText extends DBColumn {
} // class DBColumnText
/* ============================================================================================
class DBColumnBool
used for True/False, 1/0, etc...
html display T or F (can be overridden)
Input is a True/False drop down on form input
Stored in a char
*/
class DBColumnBool extends DBColumn {
/*
* array containing the values considered as the keys. If '' is included, an empty string is
* considered false also
*/
public $falseValues;
public function __construct ( $columnName, $definitions = null, $value=null) {
parent::__construct( $columnName, $definitions , $value ); // first call base class
$this->falseValues = isset( $definitions['null values'] ) ?
$definitions['null values'] :
array ( 'f' => 1, 'F' => 1, 'n' => 1, 'N' => 1, '0' => 1, '' => 1 );
} // function __construct
public function makeSafeSQLValue ( $value = null ) {
// definition of values which will be interpreted as false
if ($value) {
$this->value = ($value == 'null' ? '' : $value);
}
if ($this->value) {
$this->value = $this->falseValues[$this->value] ? '0' : '1';
} else {
$this->value = ($this->nullable ? 'null' : "'0'");
}
return $this->value;
} // function makeSafeSQLValue
/*
* override default to return a function that displays "true" or "false"
*/
public function getQueryColumn ( $tableName = null, $useDisplayName = false ) {
$columnName = ($tableName ? "$tableName." : '' ) . $this->columnName;
return array( 'fieldnames' => "if ($columnName = 1, 'true','false')" .
( $useDisplayName ? " '$this->displayName'" : '' )
);
} // getQueryColumn
} // class DBColumnBool
/* ============================================================================================
class DBColumnDate
holds a date only (ie, no time)
html display is yyyy-mm-dd (can be overridden)
input uses advanced library
stored in a date
*/
class DBColumnDate extends DBColumn {
function makeSafeSQLValue ( $value = null ) {
if ($value) {
$this->value = ($value == 'null' ? '' : $value);
}
if ( $result = strtotime( $value ) ) {
$this->value = "'" . Date( 'Y-m-d', $result) . "'";
} else {
$this->value = ($this->nullable ? 'null' : "'0000-00-00'");
}
return $this->value;
}
} // class DBColumnDate
/* ============================================================================================
class DBColumnDateTime
holds a date time stamp
html display is yyyy-mm-dd hh:mm:ss (can be overridden)
input uses advanced library
stored in datetime
*/
class DBColumnDateTime extends DBColumn {
function makeSafeSQLValue ( $value = null ) {
if ($value) {
$this->value = ($value == 'null' ? '' : $value);
}
if ( $result = strtotime( $this->value ) ) {
$this->value = "'" . Date( 'Y-m-d H:i:s', $result) . "'";
} else {
$this->value = ($this->nullable ? 'null' : "'0000-00-00 00:00:00'");
}
return $this->value;
}
} // class DBColumnDateTime
/* ============================================================================================
class DBColumnInt
holds an integer
html display is right justified
input verifies numerics only
stored in int (may be stored in int unsigned)
*/
class DBColumnInt extends DBColumn {
public $range; // if defined, number must be within this range
public $signed = true; // if false, uses int unsigned
public function __construct( $columnName, $definitions = null, $value = null) {
parent::__construct( $columnName, $definitions, $value ); // first call base class
$this->range['min'] = isset( $definitions['min'] ) ? $definitions['min'] : null;
$this->range['max'] = isset( $definitions['max'] ) ? $definitions['max'] : null;
$this->signed = isset( $definitions['signed'] ) ? $definitions['signed'] : true;
} // function __construct
function makeSafeSQLValue ( $value = null ) { // simply remove all non-numerics
if ($value) {
$this->value = $value == 'null' ? '' : $value;
}
$this->value = preg_replace( '/[^0-9]/', '', $this->value );
if ( ! strlen($this->value) ) {
$this->value = $this->nullable ? 'null' : '0';
}
return $this->value;
}
} // class DBColumnInt
/* ============================================================================================
class DBColumnReal
holds a floating point number
html display is right justified
may be padded
input verfies floating point number
stored in float
*/
class DBColumnReal extends DBColumnInt {
public $decimalPlaces;
public function __construct( $columnName, $definitions = null, $value = null) {
parent::__construct( $columnName, $definitions, $value ); // first call base class
$this->decimalPlaces = $definition['decimal places'] ? $definition['decimal places'] : 0;
} // function __construct
function makeSafeSQLValue ( $value = null ) { // same as int version, but allows periods
if ($value) {
$this->value = $value = 'null' ? '' : $value;
}
$this->value = preg_replace( '/[^0-9.]/', '', $this->value );
if ( ! strlen($this->value) ) {
$this->value = $this->nullable ? 'null' : '0';
}
return $this->value;
}
} // class DBColumnReal
/* ============================================================================================
class DBColumnPassword
holds a hash of a password
HTML display is a series of 8 stars
html input is type='password'
stored in a char(32). This is the MD5 sum of the password
*/
class DBColumnPassword extends DBColumn {
function makeSafeSQLValue ( $value = null ) {
parent::__makeSafeSQLValue( $value ); // first, process as a string
// then, set it to call the MD5 function MySQL, PostgreSQL, Oracle. MS SQL-Server does
// not have this function
$this->value = 'MD5(' . $this->value . ')';
}
function __toString() {
return '****************';
} // function __toString override
/*
* override default to return just a string of 8 asterisks
*/
public function getQueryColumn ( $tableName = null, $useDisplayName = false ) {
return array( 'fieldnames' => "'********'" .
($useDisplayName ? " '$this->displayName'" : '' )
);;
} // getQueryColumn
} // class DBColumnPassword
/* ============================================================================================
class DBColumnFile
holds file
html display is file name, click to download
input is type='file'
stored either in a blob, or in a varchar as the filename on disk
*/
class DBColumnFile extends DBColumn {
public $viewable; // if true, can be displayed on screen, ie pictures
// if false, this is a blob column which contains the file. If true, it is a varchar which
// contains the path
public $onDisk = true;
public function __construct( $columnName, $definitions = null, $value = null) {
parent::__construct( $columnName, $definitions, $value ); // first call base class
$this->viewable = $definition['viewable'];
} // function __construct
} // class DBColumnFile
/* ============================================================================================
class DBColumnLookup
Holds a foreign key value
html display is target entry
input is a dropdown
stored as an int unsigned foreign key (tablename.primary key)
The display will be a drop down box which will be filled with display values from the foreign
table
Basically, will generate the query
select $this->$remotePrimaryKey,$this->remoteDisplayField
from $this->remoteTableName
where $this->filter
sort by $this->sortOrder
where
'$this->filter' will only be used if it is non-null (otherwise, there will be no
where clause)
'sort by $this->sortOrder' will only be used if $sortOrder is non-null (otherwise,
there will be no sort by)
$remoteDisplayField must resolve to a single column in the query result, ie concat(field1,
field2), not field1,field2
The <select> box will have the remote field highlighted that corresponds to $this->value
If $this->value is null and this is a nullable field, the special text "No Value"
is added to the <select> box
If $this->value is null and this is not a nullable field, the first item in the drop down
will be selected.
*/
class DBColumnLookup extends DBColumn {
// the actual table name in the database used for lookups
protected $remoteTableName;
// column name in $remoteTableName that matches this columns value
protected $remotePrimaryKey;
// a string used in select statement to build a display
protected $remoteDisplayField;
// if set, will generate an additional where clause (anded) to limit the display of fields
protected $filter;
// if set, will generate a sort by clause to determine display order
protected $sortOrder;
// used for self-referential joins
protected $remoteTableAlias;
public function __construct( $columnName, $definitions = null, $value = null) {
parent::__construct( $columnName, $definitions, $value ); // first call base class
$this->remoteTableName = isset( $definitions['table'] ) ?
$definitions['table'] : null;
$this->remotePrimaryKey = isset($definitions['keyfield']) ?
$definitions['keyfield'] : null;
$this->remoteDisplayField = isset( $definitions['display_field']) ?
$definitions['display_field'] : null;
$this->remoteTableAlias = isset( $definitions['table alias'] ) ?
$definitions['table alias'] : null;
$this->filter = isset( $definitions['filter'] ) ? $definitions['filter'] : null;
$this->sortOrder = isset( $definitions['sort by'] ) ? $definitions['sort by'] : null;
} // function __construct
/*
* override default to return a function that displays "true" or "false"
*/
public function getQueryColumn ( $tableName = null, $useDisplayName = false ) {
$return = array();
if ( $tableName == $this->remoteTableName ) {
// this is a self-referential table!! Everything must be done via an alias
// verify an alias has been created
if ( ! isset( $this->remoteTableAlias ) )
throw new Exception ("DBColumnLookup->getQueryColumn has self referential table, " .
"but 'table alias' not defined for " .
"$tableName.$this->columnName" );
$return['from']["$this->remoteTableName as $this->remoteTableAlias"]['on'] =
"$tableName.$this->columnName = $this->remoteTableAlias.$this->remotePrimaryKey";
$return['fieldnames'] =
"$this->remoteTableAlias.$this->remoteDisplayField '$this->displayName'";
} else { // standard join
$return['from'][$this->remoteTableName]['on'] =
"$tableName.$this->columnName = $this->remoteTableName.$this->remotePrimaryKey";
$return['fieldnames'] =
"$this->remoteTableName.$this->remoteDisplayField" .
( $useDisplayName ? " '$this->displayName'" : '' );
}
return $return;
} // getQueryColumn
} // class DBColumnLookup
/* ============================================================================================
class DBColumnManyToMany
simply indicates a one to many relationship
HTML display is a list of remote table values
input is a multi-select
stored as a secondary table with
one set of columns containing the primary key of this table
second set of column containing the primary key of a second table.
*/
class DBColumnManyToMany extends DBColumn {
} // class DBColumnManyToMany
/*
* A table is made up of columns, has relationships with other tables,,
* has an array (possibly null) of columns that make up the primary key
* the queries property is an array of arrays. The array has (currenty)
* two possible values, list and record. Each row contains an
* associative array with rows
* fieldnames - array of field names for select clause
* from - array of tables to select from
* where - array of clauses for the where part of the query
* order - array of field names used for order by
* the form of the generated query is
* select {fieldnames} from {tables} where {where} order by {order}
* See makeQueryDefinition for the method used to dynamically create this
* NOTE: from is a complex array. It keys off of the table name, and
* optionally has two sub-arrays, 'join' and 'on'. 'join' gives the
* type of join (default is left outer) and on gives the conditions. The
* on conditions are exactly as placed in the parentheses, ie
* fieldname=fieldname
*
* $columns is an indexed array of DBColumn or one of its descendan
* classes. Thus, $columns['columnname']->_toString() will have different
* output depending on the actual type of the underlying object.
*/
class DBTable {
public $tableName; // name of the table in the database
public $columns; // array of columns in the table
public $displayName; // display name of the table (human readable)
public $displayColumns; // list of columns which should be displayed. DEPRECATED
public $displayQuery; // query for creating a list. DEPRECATED
public $queries; // array of queries for list and record
public $currentRecords; // contains the query, key values and data returned from a query
/*
* constructor. Accepts multiple parameters and creates an instance
*/
public function __construct( $tableName, $definition ) {
$this->tableName = $tableName;
// if a display name is not passed in, use the table name
$this->displayName = ($definition['display name'] ?
$definition['display name'] : $tableName );
/*
* if a list of display columns is not passed in, use the column names from the
* column definition
*/
$this->displayColumns = ( $definition['display columns'] ?
$definition['display columns'] : array_keys($definition['field info']) );
// if a display query is not passwd in, just do a select *
$this->displayQuery = ($definition['display query'] ?
$definition['display query'] : "select * from $this->tableName" );
// get the column definitions
$this->columnDefinitionsArrayToObject( $definition['field info'] );
$this->queries = isset($definition['queries']) ? $definition['queries'] : array();
$this->currentRecords = null;
} // function __construct
public function getRecord( $keys ) {
print "<p>In getRecord, keys are</p><pre>"; print_r( $keys ); print "</pre>";
$this->loadFromDatabase( $keys );
//$this->currentRecords['query'] = $this->makeWhereClause($keys);
//$this->currentRecord = new DBRecord( $this, $keys );
//$this->currentRecord->loadFromDatabase();
} // function getRecord
public function makeWhereClause ( $keys ) {
$return = array();
foreach ( $keys as $keyField => $value ) {
if ( isset( $this->columns[$keyField] ) ) {
$return[] = "$keyField = '$value'";
}
}
return ' where ' . implode( ' and ', $return );
}
/*
* loads record from database. either $this->keyValues or the
* parameter $keyValues must contain enough information to uniquely
* identify this record.
* Upon successful query, the primary keys are stored in
* $this->keyValues, and an array of all returned columns
* is placed in $this->values.
*/
public function loadFromDatabase ( $keys, $searchValues = null ) {
if ( ! isset( $keys ) ) {
throw new Exception ( 'in DBRecord->loadFromDatabase, no record has been' .
' loaded and no key fields have been defined' );
} else {
require_once( 'DBQuery.class.php' );
$this->currentRecords['query'] = $this->SQLQuery( 'record' ) .
$this->makeWhereClause($keys);
$this->currentRecords['query'] = new DBQuery( $this->currentRecords['query'] );
$this->currentRecords['query']->useAssociativeArray = true;
try {
if ( $this->currentRecords['query']->run() ) {
if ( $this->currentRecords['query']->rowsAffected == 1 ) {
// if ( DEBUG ) print_r( $query);
foreach ($this->currentRecords['query']->returnData[0] as
$thisColumn => $thisValue ) {
$this->currentRecords['data'][$thisColumn]->value = $thisValue;
}
} else {
throw new Exception ('DBRecord->loadFromDatabase returned ' .
$info['count'] . ' rows, expected 1, query was ' .
$this->table->getSelectQuery( 'record',
$this->keyValues ) );
}
} else {
print "Error executing query $temp\n$query->error\n";
} // if $query->run .. else
} catch ( Exception $e ) {
print "Error executing query $temp\n$query->error\n";
}
} // if..else
} // loadFromDatabase
/*
* iterates through an array. For each row in the array, determines
* the type of the column and generates an instance of the
* appropriate class for it, adding it to the $columns property of
* this class.
*/
public function columnDefinitionsArrayToObject ( $arr ) {
foreach ( $arr as $columnName => $definitions ) {
switch ( $definitions['type'] ) {
//case 'string' :
case 'text' : $thisColumn = new DBColumnText($columnName, $definitions );
break;
case 'date' : $thisColumn = new DBColumnDate($columnName, $definitions );
break;
case 'datetime': $thisColumn = new DBColumnDateTime($columnName, $definitions );
break;
case 'int' : $thisColumn = new DBColumnInt($columnName, $definitions );
break;
case 'bool' : $thisColumn = new DBColumnBool($columnName, $definitions );
break;
case 'real' : $thisColumn = new DBColumnReal($columnName, $definitions );
break;
case 'password': $thisColumn = new DBColumnPassword($columnName, $definitions );
break;
case 'file' : $thisColumn = new DBColumnFile($columnName, $definitions );
break;
case 'lookup' : $thisColumn = new DBColumnLookup($columnName, $definitions );
break;
case 'multi' : $thisColumn = new DBColumnManyToMany($columnName, $definitions );
break;
default : $thisColumn = new DBColumn( $columnName, $definitions );
} // switch
$this->columns[$thisColumn->columnName] = $thisColumn;
} // foreach
} // function arrayToObject
/*
* creates a query by iterating through all columns in the current
* table definition. Then stores the resulting query in property
* $queries[$type]
* WARNING: overwrites any existing query of the type requested
*/
private function makeQueryDefinition( $type = 'list' ) {
// insert the primary table name
$this->queries[$type]['from'][$this->tableName] = array( );
// process each column defined
foreach ( $this->columns as $columnName => $columnDefinition ) {
// let the column tell us what should go here
$temp = $columnDefinition->getQueryColumn( $this->tableName );
// that returns an aray with fieldnames, where, etc..
// so process each returned value
foreach ( $temp as $key => $value ) {
if ( $key == 'from' ) {
foreach ($temp['from'] as $table => $definition ) {
$this->queries[$type][$key][$table] = $definition;
}
} else {
$this->queries[$type][$key][] = $value;
}
} // foreach
} // foreach
} // makeQueryDefinition
/*
* Reads the queries structure and returns a standard
* SQL Query.
* $additionalClauses is an array that may contain the keys
* 'fieldnames', 'from', 'where' and 'order' which will be
* appended to the appropriate entries in queries structure
*/
public function SQLQuery( $type= 'list', $additionalClauses = null ) {
// if the particular type of query has not been defined, do so
if ( ! ( isset( $this->queries) and count($this->queries) ) )
$this->makeQueryDefinition( $type );
// now, merge $this->queries and $additionalClauses
foreach ( array( 'fieldnames', 'from', 'where', 'order' ) as $index ) {
if ( isset( $additionalClauses[$index] )
and count( $additionalClauses[$index] )
and isset( $this->queries[$type][$index] )
and count( $this->queries[$type][$index] ) ) {
$additionalClauses[$index] = array_merge(
(array)$this->queries[$type][$index],
(array)$additionalClauses[$index] );
} elseif ( isset( $this->queries[$type][$index] )
and count( $this->queries[$type][$index] ) ) {
$additionalClauses[$index] = $this->queries[$type][$index];
} // if, note that if $this doesn't exist then it simply takes on the
//value of $addtionalClauses
} // foreach
$query = 'select ' . implode( ",\n ", $additionalClauses['fieldnames'] );
$from = '';
foreach ( $additionalClauses['from'] as $table => $join ) {
if ( $from ) {
$from = " ($from)\n ";
$from .= (isset( $join['join'] ) ? $join['join'] : 'left outer join');
}
$from .= " $table ";
$from .= ( ( isset( $join['on'] ) && $join['on']) ?
" on ( " . $join['on'] . ") " : '' );
} // foreach
$query .= "\nfrom $from\n";
if (isset( $additionalClauses['where'] ) and count( $additionalClauses['where'] ) )
$query .= 'where ' . implode( "\n and " , $additionalClauses['where'] );
if (isset( $additionalClauses['order'] ) and count( $additionalClauses['order'] ) )
$query .= ' order by ' . implode( ",\n ", $additionalClauses['order'] );
return $query;
} // SQLQuery
/*
* function returns as an array all data returned by a query.
* The returned array is an indexed array of associativeArray,
* with a row containing an associative array of data, where keys
* are the field names and the value is the value.
* The outer array is indexed in order returned by the query, so
* order by clauses in the SQL are honored
*/
public function toArray( $parameters = null ) {
$query = $this->SQLQuery( 'list', $parameters );
$this->currentRecords['query'] = $query;
return $this->currentRecords;
$queryResults = new DBQuery( $query );
$queryResults->useAssociativeArray = true;
$queryResults->run();
if ( DEBUG ) $_SESSION['debug'][] = $query;
$this->currentRecords = $queryResults->returnData;
return $this->currentRecords;
}
/*
* function will generate two types of return. If "id" is passed in
* it is assumed to be an array of values that will be used to select
* a unique row from the table, in which case a new DBRecord is created
* and it's toHTML is called.
*
* If $id is empty, will return a list of all records in the table
* based on the display record entry for this table. It calls
* DBQueryHTML:queryToTable on that instance to generate the table
*/
function toHTML( $type = 'view', $parameters = '' ) {
$return = '';
switch ( $type ) {
case 'view': $return = "<tr><td>$this->columnName</td><td>$this->value</td></tr>";
break;
case 'list': $queryResults = new DBQuery( $this->displayQuery, $parameters['where'], $parameters['order'] );
$queryResults->run();
if ( $queryResults->rowsAffected ) {
foreach ( $queryResults->returnData as $key => $row ) {
$return .= '<tr>';
foreach ( $row as $fieldName => $value ) {
$thisColumn = $this->columns[$fieldName];
$thisColumn->value = $value;
$return .= $thisColumn->toHTML('list', $parameters['link']);
//$thisColumn->toHTML( 'list' ),true );
} // foreach
$return .= '</tr>';
} // foreach
} // if
$return = '<table>' . $return . '</table>';
/* new DBQueryHTML( $this->displayQuery );
return print_r($queryResults,true);
return $queryResults->queryToTable('',true,"<a href=" . $_SERVER['PHP_SELF'] .
"?table=$this->tableName&id=%id%>View</a>");
*/ break;
case 'edit': $return = "<tr><td>$this->columnName</td><td>$this->value</td></tr>";
break;
} // switch
return $return;
} // toHTML;
/* function toHTML( $type = 'view' ) {
if ( isset( $id ) && $id ) {
$theRow = new DBRecord( $this, array( 'id' => $id ) );
return $theRow->toHTML();
} else {
$queryResults = new DBQueryHTML( $this->displayQuery );
return $queryResults->queryToTable('',true,"<a href=" . $_SERVER['PHP_SELF'] .
"?table=$this->tableName&id=%id%>View</a>");
} // if..else
} // function toHTML
*/
} // class DBTable
class DBDatabase {
public $databaseName;
protected $tables;
public $defaultTable;
public function __construct( $databaseName, $tableDefinitionArray = null ) {
$this->databaseName = $databaseName;
if ( $tableDefinitionArray ) $this->loadTables ( $tableDefinitionArray );;
$this->defaultTable = DEFAULT_TABLE;
} // function __construct
/*
* function accepts an array of table definitions. For each element
* in the array, creates a new DBTable and inserts it into the
* $tables property
*/
public function loadTables ( $tableDefinitionArray ) {
foreach ( $tableDefinitionArray as $tableName => $definition ) {
$this->tables[$tableName] = new DBTable($tableName, $definition );
}
} // function loadTables
/*
* Function accepts a string name of a table, and returns the
* appropriate DBTable instance from the tables array
*/
public function getTable( $name ) {
return ( $this->tables[$name] ? $this->tables[$name] : null );
}
/*
* Function which returns a very basic HTML element to the caller
* which contains an unordered list. Each element of that list
* is the name of a table, and a link to the calling program
* with the name of the table as a parameter
*/
function toHTML ( ) {
$output = '<ul>';
foreach ( $this->tables as $name => $values ) {
$output .= "<li><a href=" . $_SERVER['PHP_SELF'] . "?table=$name>$name</a></li>";
}
$output .= '</ul>';
return $output;
} // toHTML
/*
* function returns an array with the names of all tables in the
* database definition
*/
public function toArray() {
return array_keys( $this->tables );
}
/*
* function returns all table names in a space separated one line
* string
*/
public function __toString() {
return implode( ' ', $this->toArray() );
}
/*
* function will create a quick and dirty set of edit/update screens, allowing untrained
* user to modify database files will give list of tables as links. If link is clicked,
* will display tabular list of table contents. table contents list will have edit/delete
* buttons, and an Add button at the top. If edit button is clicked, will show one row of
* information, allow user to modify it, then update the database
* If add button is clicked, will show one row of information, with defaults filled in,
* then values will be inserted into database
*/
public function doAdmin( $returnPage = 'admin.php' ) {
$html = "<ul class='db_database_admin_tablelist'>\n";
foreach ( $this->tables as $tableEntry ) {
$html .= "<li><a href='$returnPage?command=showtable&tablename=".
"$tableEntry->tableName'>$tableEntry->displayName</a></li>\n";
}
$html .= "</ul>";
return $html;
}
} // class DBDatabase
?>