Location: PHPKode > scripts > PhpDtObject > phpdtobject/PhpDtObject_native_mysql.php
<?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;
	}
}
?>
Return current item: PhpDtObject