Location: PHPKode > scripts > PhpDtObject > phpdtobject/PhpDtObject_DB.php
<?php
/**
 * Online web application builder class set
 *
 * @package OWAB
 */
/**
 * Class PhpDtObject_DB
 *
 * This is a database locig layer for the PhpDtObject class. All queries are executed here.
 * Class structure is simple. 4 main methods are used to access database and two methods to
 * get meta information from the table. Save method is used to insert and update table.
 * 
 *
 * @package     OWAB
 * @author      Marko Manninen <hide@address.com>
 * @copyright   Copyright © 2004, Marko Manninen  
 * @date        9.6. -2004
 * @license     http://opensource.org/licenses/lgpl-license.php GNU Lesser General Public License
 * @version     0.01
 * @todo        how to handle multiple primary keys, 
 */
class PhpDtObject_DB
{
	/**
	 * Database connection object
	 * @var object ADOdb
     */
	var $_adodb;
	/**
	 * Target table
	 * @var string
     */
	var $_target_table;
	
	/**
	 * Constructor
     */
    function PhpDtObject_DB( $dbconn )
    {
		$this->_adodb = $dbconn->getLink();
		$this->_target_table = $dbconn->getTable();
    }//  END OF CONSTRUCTOR

	/**#@+
	 * PUBLIC METHOD
	 * @access public
	 */
	/**
	 * Get meta column names
     */
    function getMetaColumns()
    {
		return $this->_adodb->MetaColumnNames( $this->_target_table );
    }//  END OF METHOD getMetaColumns

	/**
	 * Get get meta primary keys
     */
    function getPrimaryKeys()
    {
		return $this->_adodb->MetaPrimaryKeys( $this->_target_table );	
    }//  END OF METHOD getPrimaryKeys
	
	/**
	 * Get one row from the table
	 * 
	 * If result set contains several rows, only the first is returned to the caller.
	 * Parameter array must contain select, where and limit keys, if array is given!
	 * 
	 * Parameter array is used to make detailed select query. Where key is mandatory. If where
	 * is not defined, method will raise warning and return false. Field value must be rounded with
	 * ' marks! Example: $param[where]	= "field = 'value'";
	 * 
	 * @param array
	 * @return mixed
	 */	
	function getOne_DB( $params = array( "where"=>"" ) )
    {
		// Custom query is not supported in getOne. See getMany instead!
		$query = "SELECT *";
		$query .= " FROM `". $this->_target_table . "`";
		//
		if( isset( $params['where'] ) && $params['where'] != "" )
			$query .= " WHERE " . $params['where'];
		else
		{
			trigger_error( "Where parameter was not defined. GetOne operation could not be ended.", E_USER_WARNING );
			return false;
		}
		//
		$query .= " LIMIT 1";
		// run query
		//echo $query;
		$result = $this->_adodb->Execute( $query );
		//
        if( $result != "" )
        {
            if( $result->RecordCount() == 1 )
            {
				return $result->fields;
            }
            if( $result->RecordCount() > 1 )
            {
                return false;
            }
            if( $result->RecordCount() < 1 )
            {
                return false;
            }
        }
		else 
		{	
			// error case
			trigger_error( "Error on SQL query: ". $query, E_USER_ERROR );
			return false;
		}
    }// END OF METHOD getOne_DB

	/**
	 * Get several rows from the table
	 * 
	 * With optional parameter you can make detailed selects from the table.
	 * Parameter array must contain at least select, where, limit, order_by and order keys, 
	 * if array is given!
	 * 
	 * @param array optional
	 * @return mixed
	 */		
	function getMany_DB( $params = array( "select"=>"*", "where"=>"1", "limit"=>"-1", "order_by"=>"", "order"=>"ASC" ) )
    {
		//
		if( !isset( $params['custom_query'] ) )
		{
			if( isset( $params['select'] ) && $params['select'] != "" )
				$query = "SELECT " . $params['select'];
			else
				$query = "SELECT *";
			//
			$query .= " FROM `". $this->_target_table . "`";
			//
			if( isset( $params['where'] ) && $params['where'] != "" )
				$query .= " WHERE " . $params['where'];
			else
			{
				trigger_error( "Where parameter was not defined. GetOne operation could not be ended.", E_USER_WARNING );
				return false;
			}
			//
			if( isset( $params['order_by'] ) && $params['order_by'] != "" )
			{
				$query .= " ORDER BY '" . $params['order_by'] . "'";
				if( isset( $params['order'] ) && $params['order'] != "" )
					$query .= " " . $params['order'];
			}
				
			if( isset( $params['limit'] ) && $params['limit'] != "" )
				$query .= " LIMIT " . $params['limit'];
		}
		else
			$query = $params['custom_query'];
		// make query
		$result = $this->_adodb->Execute( $query );
		//
        if( $result != "" )
        {
            if( $result->RecordCount() > 0 )
            {
				while ( !$result->EOF ) 
				{
					$rows[] = $result->fields;
					$result->MoveNext();
				}
				return $rows;
            }
            if( $result->RecordCount() < 1 )
            {
                return false;
            }
        }
		else 
		{	
			// error case
			trigger_error( "Error on SQL query: ". $query, E_USER_ERROR );
			return false;
		}
    }// END OF METHOD getMany_DB

	/**
	 * SAVE & UPDATE
	 *
	 * Method saves an object to database. If object id = 0 method
	 * uses insert to make transaction. If id > 0, then update is used.
	 * On insert, last insert id is returned to the method caller.
	 * 
	 * Parameter array is used to make detailed update queries.
	 * 
	 * $params:
	 * [where]			field = value
	 * [limit]			-1 ... n
	 * [custom_query]	custom query string
	 * 
	 * @param array optional
	 * @param object PhpDtObject
	 * @return mixed Last insert id or true/false
     */ 
	function save( $pObj, $params = array( "where"=>"", "limit"=>"1" ) )
	{
		$insert_values = "";
		$update_values = "";

		foreach( $pObj->getIndex() as $key => $val )
		{
			$insert_values .= "'" . $val . "',";
			$update_values .= "`" . $key . "` = '" . $val . "',";
		}
		$insert_values = substr( $insert_values, 0, strlen( $insert_values )-1 );
		$update_values = substr( $update_values, 0, strlen( $update_values )-1 );
		//
		if( !isset( $params['custom_query'] ) )
		{
			if( $pObj->_index[$pObj->getPrimaryKey()] == 0 || $pObj->_index[$pObj->getPrimaryKey()] == NULL )
			{
				// insert option
				$query = "INSERT INTO `". $this->_target_table . "`";
				$query .= " VALUES ( $insert_values )";
			}
			else
			{
				// update option
				$query = "UPDATE `". $this->_target_table . "`";
				$query .= " SET $update_values";
				//
				if( isset( $params['where'] ) && $params['where'] != "" )
					$query .= " WHERE " . $params['where'];
				else
				{
					trigger_error( "Where parameter was not defined. Update operation could not be ended.", E_USER_WARNING );
					return false;
				}
				//
				if( isset( $params['limit'] ) && $params['limit'] != "" )
					$query .= " LIMIT " . $params['limit'];
			}
		}
		else
			$query = $params['custom_query'];
		// make query
		$result = $this->_adodb->Execute( $query );
		// return id or true/false
		if( $result != "" && ( $id == 0 || $id == NULL ) )
		{
			// insert case
			return $this->_adodb->Insert_Id();
		}
		else if( $result != "" ) 
		{	
			// update case
			return true;
		}
		else 
		{	
			// error case
			trigger_error( "Error on SQL query: ". $query, E_USER_ERROR );
			return false;
		}
		
	}// END OF METHOD save
	
	/**
	 * DELETE
	 * 
	 * Parameter array is used to make detailed delete queries.
	 * Where is a mandatory parameter. If $param['where'] is not defined, method
	 * will trigger user warning and return false. All other parameters are optional.
	 * 
	 * $params:
	 * [where]			id = 0
	 * [limit]			-1 ... n
	 * [custom_query]	custom query string
	 * 
	 * @param array
	 * @return boolean	
	 */ 
	function delete( $params )
	{
		if( !isset( $params['custom_query'] ) )
		{
			$query = "DELETE FROM `". $this->_target_table . "`";
			
			if( isset( $params['where'] ) && $params['where'] != "" )
				$query .= " WHERE " . $params['where'];
			else
			{
				trigger_error( "Where parameter was not defined. Delete operation could not be ended.", E_USER_WARNING );
				return false;
			}
			if( isset( $params['limit'] ) && $params['limit'] != "" )
				$query .= " LIMIT " . $params['limit'];
		}
		else
			$query = $params['custom_query'];
		// make query	
		$result = $this->_adodb->Execute( $query );
		//
		if ( $result != "" ) 
			return true;
		else
		{ 
			// error case
			trigger_error( "Error on SQL query: ". $query, E_USER_ERROR );
			return false;
		}
	}// END OF METHOD delete
	/**#@-*/
	
}// END OF CLASS User
?>
Return current item: PhpDtObject