<?php
/**********************************************************************************************
* CLASSES ON FILE: PhpDtObject & PhpDtObject_DB
*********************************************************************************************/
/**
* @author Marko Manninen <hide@address.com>
* @copyright Copyright © 2004, Marko Manninen
* @licence LGPL - Lesser General Public License
* @created 1th of June, 2004 by Marko
* @modified 11th of Nov, 2004 by Marko
* @version 1.0
* @changelog Enhanced PhpDtObject class with a new and performance tuned
database table access methods. Database query layer that implements
native mysql functions of php is on a same file so this class does
not require ADOdb to work. Just include the file and get table contents
even without generating separate classes for single tables and without
making separate xml / sql configuration files. So "one class works for
multiple tables." Extend class for more heavy and complex use.
Added public methods compairing to former published PhpDtObject class are:
initByKey, gv, gvm, sv, getNumRows, getAffectedNumRows, getMixed,
getIndexMixed, getLastSelectNumRows, singleUpdate, setMask, clearMask,
getErrors and assignArray.
Multiple keys are handled now by calling initByKey method instead of
normal init that is used when only one primary key field is found from the
database table.
*/
/**
* set up default database connection parameters you can override these by passing
* second parameter on phpdtobject construction. Array must be associative containing
* next few keys:
* $custom_dbprefs = array( 'server'=>'', 'port'=>'', 'db'=>'', 'user'=>'', 'pass'=>'' );
*/
define( 'PDO_DEFAULT_SERVER', 'localhost' );
define( 'PDO_DEFAULT_PORT', '' );
define( 'PDO_DEFAULT_DB', 'test' );
define( 'PDO_DEFAULT_USER', 'root' );
define( 'PDO_DEFAULT_PASS', '' );
//
class PhpDtObject extends PhpDtObject_DB
{
function PhpDtObject( $target_table, $dbconn_parameters = array() )
{
$this->PhpDtObject_DB( $target_table, $dbconn_parameters );
$this->index_mixed = array();
$this->mask = false;
$this->masked_columns = array();
$this->errors = array();
}
/************************************************
* PUBLIC METHODS
***********************************************/
// GETTERS
// simple primary key with id initialization
// masks must be set before init method, if they are meant
// to affect queries!
function init( $id )
{
if( $key = $this->_getPrimaryKey() ) {
$where = $key . " = '" . $id . "'";
if( $arr = $this->_getOne( $where ) ) {
$this->_setKeyVal( $arr );
return true;
} else {
$this->errors[] = 'Cannot initialize object with id (' . $id . ').';
}
}
return false;
}
// simple key value pair initialization
function initByKey( $key, $val )
{
$where = $key . " = '" . $val . "'";
if( $arr = $this->_getOne( $where ) ) {
$this->_setKeyVal( $arr );
return true;
} else {
$this->errors[] = 'Cannot initialize object with given key/val pair [' . $key . '/' . $val . '].';
return false;
}
}
// short form of get value method
function gv( $key ) { return $this->getValue( $key ); }
// get value from initialized object with key
function getValue( $key )
{
if( isset( $this->index[$key] ) ) {
return $this->index[$key];
} else {
$this->errors[] = 'Key (' . $key . ') was not found from the table (' . $this->target_table . ') field list.';
return false;
}
}
// short form of get value mixed method
function gvm( $key ) { return $this->getValueMixed( $key ); }
// get value from initialized object with key
function getValueMixed( $key )
{
if( isset( $this->index_mixed[$key] ) ) {
return $this->index_mixed[$key];
} else {
$this->errors[] = 'Key (' . $key . ') was not found from the table (' . $this->target_table . ') field list.';
return false;
}
}
// get row fields in normal associative array
function getIndex()
{
return $this->index;
}
// get row fields in normal associative array
function getIndexMixed()
{
return $this->index_mixed;
}
// for more complex where searches than init and initByKey
// method returns only one row. If none or multiple will
// occur in result, then false will be returned
function getOne( $where )
{
if( $arr = $this->_getOne( $where ) ) {
$this->_setKeyVal( $arr );
return $this;
} else {
$this->errors[] = 'Could not get one row from the table (' .
$this->target_table . ') with the specified where clause (' .
$where . ').';
return false;
}
}
// returns multiple rows if found from the database table
// accepted parameters:
// params['select'],
// params['where'],
// params['order'],
// params['order_by']
// params['limit'],
// params['group_by'],
// params['having'],
// params['custom_query']
function getMany( $params = array() )
{
if( $arrs = $this->_getMany( $params ) ) {
$temp = array();
foreach( $arrs as $arr ) {
foreach( $arr as $key => $val ) {
$this->_setKeyVal( $arr );
}
array_push( $temp, $this );
}
return $temp;
}
else {
$this->errors[] = 'Rows were not found from the table (' .
$this->target_table . ') with the specified parameters.';
return false;
}
}
// returns multiple rows with custom query
// other index_midex array is populated because this
// method can return data from multiple tables!
function getMixed( $custom_sql_query )
{
if( $arrs = $this->_getMixed( $custom_sql_query ) ) {
$temp = array();
foreach( $arrs as $arr ) {
foreach( $arr as $key => $val ) {
$this->index_mixed[$key] = $val;
}
array_push( $temp, $this );
}
return $temp;
}
else {
$this->errors[] = 'Rows were not found from the table (' .
$this->target_table . ') with the specified query (' . $custom_sql_query . ').';
return false;
}
}
// returns number (count) of rows specified on where parameter clause
function getNumRows( $where = 1 )
{
return $this->_getNumRows( $where );
}
// returns number of rows affected on insert, update and delete operations
function getAffectedNumRows()
{
return $this->_getAffectedNumRows();
}
// returns number of rows selected on getMany method
function getLastSelectNumRows()
{
return $this->_getLastSelectNumRows();
}
// if some of the public methods return false, you can check
// possible errors occured with this
function getErrors()
{
return $this->_getErrors();
}
// MODIFIERS
// short form of get value method
function sv( $key, $val, $handle_quotes = false ) { return $this->setValue( $key, $val, $handle_quotes ); }
// set single value.
// save method must be called after setValue methods as
// only then database is updated
function setValue( $key, $val, $handle_quotes = false )
{
if( isset( $this->index[$key] ) ) {
if( $handle_quotes ) {
$this->index[$key] = addslashes( $val );
} else {
$this->index[$key] = $val;
}
return true;
} else {
$this->errors[] = 'Column name not found from the table (' .
$this->target_table . ') with specified key (' . $key . ').';
return false;
}
}
// set multiplöe values from associative array. useful when
// inserting and updating REQUEST variables than comes from
// the form etc. only the fields, that are schematized on
// database table, are updated!
function assignArray( $arr, $handle_quotes = false )
{
if( !empty( $arr ) && gettype( $arr ) == 'array' ) {
foreach( $arr as $key => $val ) {
if( isset( $this->index[$key] ) ) {
if( $handle_quotes )
$this->index[$key] = addslashes( $val );
else
$this->index[$key] = $val;
}
}
}
}
// this method is used after init or initByKey method to update
// one single field on table. if you don't want make update query with
// all field information, but still want several fields to update, then you
// need to use mask feature. using singleUpdate and mask prevents unnesessary
// fields to be populated on sql query -> data bandwidth decreases!
function singleUpdate( $key, $val, $handle_quotes = false )
{
if( isset( $this->index[$key] ) ) {
$where = $this->_getPrimaryKey() . " = '" . $this->index[$this->_getPrimaryKey()] . "'";
if( $handle_quotes ) {
return $this->_singleUpdate( $key, addslashes( $val ), $where );
} else {
return $this->_singleUpdate( $key, $val, $where );
}
} else {
$this->errors[] = 'Column name not found from the table (' .
$this->target_table . ') with specified key (' . $key . ').';
return false;
}
}
// actual database insert and update method
// custom updates can be made with supported parameters:
// $params['where']
// $params['limit']
function save( $params = array() )
{
// if no parameters are included, then method tries to
// update, insert current object!
if( !isset( $params['where'] ) || $params['where'] == '' ) {
$params['where'] = $this->_getPrimaryKey() . " = '" . $this->index[$this->_getPrimaryKey()] . "'";
$params['limit'] = 1;
}
return $this->_save( $params );
}
// database table row deleting
// custom deletes can be made with supported parameters:
// $params['where']
// $params['limit']
// $params['custom_query']
function delete( $params = array() )
{
// if no parameters are included, then method tries to delete
// current object!
if( !isset( $params['where'] ) || $params['where'] == '' ) {
$params['where'] = $this->_getPrimaryKey() . " = '" . $this->index[$this->_getPrimaryKey()] . "'";
$params['limit'] = 1;
}
return $this->_delete( $params );
}
function setMask( $fields )
{
$this->mask = true;
$this->masked_fields = $fields;
}
function clearMask()
{
$this->mask = false;
$this->masked_fields = array();
}
/************************************************
* PRIVATE METHODS
***********************************************/
// factorized for init, initByKey, getOne and getMany...
function _setKeyVal( $arr )
{
foreach( $arr as $key => $val ) {
$this->index[$key] = $val;
}
}
}
?>
<?php
/**
* Database query layer handles all query building and database logic
*/
/**
* @author Marko Manninen <hide@address.com>
* @copyright Copyright © 2004, Marko Manninen
* @licence LGPL - Lesser General Public License
* @created 1th of June, 2004 by Marko
* @modified 11th of Nov, 2004 by Marko
* @version 1.0
*/
class PhpDtObject_DB
{
var $dbconn_link;
var $target_table;
var $index;
var $index_mixed;
var $mask;
var $masked_fields;
var $errors;
var $affected_num_rows;
var $last_select_num_rows;
function PhpDtObject_DB( $target_table, $dbconn_parameters = array() )
{
// initialize connection variables
if( empty( $dbconn_parameters ) ) {
$server = PDO_DEFAULT_SERVER;
$user = PDO_DEFAULT_USER;
$pass = PDO_DEFAULT_PASS;
$db = PDO_DEFAULT_DB;
} else {
$server = isset( $dbconn_parameters['server'] ) ? $dbconn_parameters['server'] : '';
$user = isset( $dbconn_parameters['user'] ) ? $dbconn_parameters['user'] : '';
$pass = isset( $dbconn_parameters['pass'] ) ? $dbconn_parameters['pass'] : '';
$db = isset( $dbconn_parameters['db'] ) ? $dbconn_parameters['db'] : '';
}
// make database connection link
$this->dbconn_link = mysql_connect( $server, $user, $pass ) or die( mysql_error() );
mysql_select_db( $db, $this->dbconn_link ) or die( mysql_error() );
// check, that target table exists on selected database
if( $this->_checkTargetTable( $db, $target_table ) ) {
$this->target_table = $target_table;
} else {
die( 'Target table (' . $target_table . ') not found from the database (' . $db . ')' );
}
// populate index variable with target table column names
// -> index as an associated array
$this->_populateIndex( $db );
// unset temp variables
unset( $server ); unset( $user ); unset( $pass ); unset( $db );
}
function _checkTargetTable( $db, $target_table )
{
$exists = mysql_query( "SHOW TABLES FROM `" . $db . "` LIKE '" . $target_table . "'", $this->dbconn_link );
return mysql_num_rows($exists) == 1;
}
function _populateIndex( $db )
{
// get fields from target table and set them as keys to the index array
$fields = mysql_list_fields( $db, $this->target_table, $this->dbconn_link );
$columns = mysql_num_fields( $fields );
for ( $i = 0; $i < $columns; $i++ ) {
$field = mysql_field_name( $fields, $i );
$this->index[$field] = '';
}
// unset temp variables
unset( $fields ); unset( $columns ); unset( $i ); unset( $field );
}
function _getPrimaryKey()
{
$pkeys = $this->_getPrimaryKeys();
if( count( $pkeys ) > 1 ) {
$this->errors[] = 'Could not initialize phpdtobject. Multiple primary keys are not supported on init method.';
return false;
} else if( count( $pkeys ) < 1 ) {
$this->errors[] = 'Could not initialize phpdtobject. One primary key is mandatory on init method.';
} else {
return $pkeys[0];
}
}
function _getPrimaryKeys()
{
$query = "SHOW KEYS FROM `" . $this->target_table . "`";
$result = mysql_query( $query ) or die( mysql_error() );
while ( $row = mysql_fetch_assoc( $result ) ) {
if ( $row['Key_name'] == 'PRIMARY' ) {
$keys[$row['Seq_in_index'] - 1] = $row['Column_name'];
}
}
return $keys;
}
// only where lause can be declared in _getOne method
function _getOne( $where )
{
if( $this->mask ) {
$query = "SELECT " . implode( ', ', $this->masked_fields );
} else {
$query = "SELECT *";
}
//
$query .= " FROM `". $this->target_table . "`";
$query .= " WHERE " . $where;
$query .= ' LIMIT 1';
//
$result = mysql_query( $query );
//
if( $result != '' ) {
$this->last_select_num_rows = mysql_num_rows( $result );
if( $this->last_select_num_rows == 1 ) {
return mysql_fetch_assoc( $result );
} else {
return false;
}
} else {
die( 'Error on SQL query ('. $query . '): ' . mysql_error() );
return false;
}
}
function _getMany( $params )
{
if( !isset( $params['custom_query'] ) ) {
// select part
if( $this->mask ) {
$query = "SELECT " . implode( ", ", $this->masked_fields );
} else if( isset( $params['select'] ) && $params['select'] != '' ) {
$query = "SELECT " . $params['select'];
} else {
$query = "SELECT *";
}
// from and where parts
$query .= " FROM `". $this->target_table . "`";
if( isset( $params['where'] ) && $params['where'] != '' ) {
$query .= " WHERE " . $params['where'];
} else {
$query .= " WHERE 1";
}
// group having parts
if( isset( $params['group_by'] ) && $params['group_by'] != '' ) {
$query .= " GROUP BY " . $params['group_by'];
if( isset( $params['having'] ) && $params['having'] != '' ) {
$query .= " HAVING " . $params['having'];
}
}
// order by and order parts
if( isset( $params['order_by'] ) && $params['order_by'] != '' ) {
$query .= " ORDER BY '" . $params['order_by'] . "'";
if( isset( $params['order'] ) && $params['order'] != '' ) {
$query .= " " . $params['order'];
}
}
// limit
if( isset( $params['limit'] ) && $params['limit'] != '' ) {
$query .= " LIMIT " . $params['limit'];
}
} else {
$query = $params['custom_query'];
}
//
$result = mysql_query( $query );
//
if( $result != '' ) {
if( mysql_num_rows( $result ) > 0 ) {
while ( $row = mysql_fetch_assoc( $result ) ) {
$rows[] = $row;
}
$this->last_select_num_rows = count( $rows );
return $rows;
}
if( mysql_num_rows( $result ) < 1 ) {
return false;
}
} else {
die( 'Error on SQL query ('. $query . '): ' . mysql_error() );
return false;
}
}
function _getMixed( $custom_query )
{
$result = mysql_query( $custom_query );
//
if( $result != '' ) {
if( mysql_num_rows( $result ) > 0 ) {
while ( $row = mysql_fetch_array( $result ) ) {
$rows[] = $row;
}
$this->last_select_num_rows = count( $rows );
return $rows;
}
if( mysql_num_rows( $result ) < 1 ) {
return false;
}
} else {
die( 'Error on SQL query ('. $custom_query . '): ' . mysql_error() );
return false;
}
}
function _isInsertCase()
{
return $this->index[$this->_getPrimaryKey()] == 0 || $this->index[$this->_getPrimaryKey()] == NULL ? true : false;
}
function _insertClause()
{
$insert_values = '';
foreach( $this->index as $key => $val ) {
$insert_values .= "'" . $val . "',";
}
return substr( $insert_values, 0, strlen( $insert_values ) - 1 );
}
function _updateClause()
{
$update_values = '';
foreach( $this->index as $key => $val ) {
if( $this->mask ) {
if( in_array( $key, $this->masked_fields ) ) {
$update_values .= "`" . $key . "` = '" . $val . "',";
}
} else {
$update_values .= "`" . $key . "` = '" . $val . "',";
}
}
return substr( $update_values, 0, strlen( $update_values ) - 1 );
}
function _save( $params = array( 'where'=>'', 'limit'=>'1' ) )
{
if( isset( $params['custom_query'] ) ) {
$query = $params['custom_query'];
} else {
if( $this->_isInsertCase() ) {
$insert_values = $this->_insertClause();
$query = "INSERT INTO `". $this->target_table . "` VALUES ( " . $insert_values . " )";
} else {
$update_values = $this->_updateClause();
// update option
$query = "UPDATE `". $this->target_table . "` SET " . $update_values;
// where part
$query .= " WHERE " . $params['where'];
// limit part
if( isset( $params['limit'] ) && $params['limit'] != '' ) {
$query .= " LIMIT " . $params['limit'];
}
}
}
$result = mysql_query( $query );
// return id or true/false
if( $result != '' && $this->_isInsertCase() && !isset( $params['custom_query'] ) ) {
// insert case
$this->affected_num_rows = mysql_affected_rows();
return mysql_insert_id( $this->dbconn_link );
} else if( $result != '' ) {
// update and custom query case
$this->affected_num_rows = mysql_affected_rows();
return true;
} else {
die( 'Error on SQL query ('. $query . '): ' . mysql_error() );
return false;
}
}
function _singleUpdate( $key, $val, $where )
{
return mysql_query( "UPDATE " . $this->target_table . " SET $key = '$val' WHERE " . $where ) != '' ? true : false;
}
function _delete( $params )
{
if( !isset( $params['custom_query'] ) ) {
$query = "DELETE FROM `". $this->target_table . "`";
$query .= " WHERE " . $params['where'];
if( isset( $params['limit'] ) && $params['limit'] != '' ) {
$query .= " LIMIT " . $params['limit'];
}
} else {
$query = $params['custom_query'];
}
$result = mysql_query( $query );
if ( $result != '' ) {
$this->affected_num_rows = mysql_affected_rows();
return true;
} else {
die( 'Error on SQL query ('. $query . '): ' . mysql_error() );
return false;
}
}
function _getNumRows( $where )
{
$query = "SELECT count(*) FROM " . $this->target_table . " WHERE " . $where;
if( $result = mysql_query( $query ) ) {
return mysql_result( $result, 0, 0 );
} else {
die( 'Error on SQL query ('. $query . '): ' . mysql_error() );
return false;
}
}
function _getAffectedNumRows()
{
return $this->affected_num_rows;
}
function _getLastSelectNumRows()
{
return $this->last_select_num_rows;
}
function _getErrors()
{
return $this->errors;
}
}
?>