Location: PHPKode > scripts > XS PHP Library > xs-php-library/inc/dbmysql.inc.php
<?php
//
// +--------------------------------------------------------------------------+
// |                                                                          |
// |                   XS PHP Library  Generic Classes Library                |
// |                                                                          |
// |                   Copyright (c) 2001-2002 XSPHPLib Group.                |
// |                                                                          |
// +--------------------------------------------------------------------------+
// |                                                                          |
// | Distributed under the terms of the GNU Lesser General Public License as  |
// | published by the Free Software Foundation version 2.1                    |
// | See the GNU Lesser General Public License for more details. You should   |
// | have received a copy of the GNU Lesser General Public License along with |
// | this package; if not, write to the Free Software Foundation, Inc.,       |
// | 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.                 |
// |                                                                          |
// +--------------------------------------------------------------------------+
// |                                                                          |
// | Authors: Robert Bala <hide@address.com>                                   |
// |                                                                          |
// +--------------------------------------------------------------------------+
//
// $Id: dbmysql.inc.php,v 1.2 2002/11/28 09:50:30 rbala Exp $

/**
 * @package db
 * @version $Id: dbmysql.inc.php,v 1.2 2002/11/28 09:50:30 rbala Exp $
 */

/**
 * DBError implements a class for reporting database error messages.
 *
 * @author Robert Bala <hide@address.com>
 * @access public
 */
class DBError extends Error {

    /**
     * Error class constructor.
     *
     * The $reply array should has action and option keys eg.
     * $reply = array('action' => ERROR_ACTION_WRITE, 'option' => 'logfile.log');
     * Action key can be set with one of the following constants:
     * {@link ERROR_ACTION_ABORT}, {@link ERROR_ACTION_PRINT},
     * {@link ERROR_ACTION_LOGIN}, {@link ERROR_ACTION_EMAIL},
     * {@link ERROR_ACTION_DEBUG}, {@link ERROR_ACTION_WRITE},
     * {@link ERROR_ACTION_RAISE}.
     *
     * @access public
     * @param string $message error message, defaults to "Unexpected Error".
     * @param int $code error code (optional), defaults to 0.
     * @param array $reply error mode of operation.
     * @return void
     */
    function DBError($message='', $code=0, $reply=null) {
        Error::Error($message, $code, $reply);
    }
    
}

/**
 * Database broker class for MySQL database.
 *
 * Manages connection to database and provides database information.
 *
 * @author Robert Bala <hide@address.com>
 * @access public
 */
class DBBroker extends Object {

	/**
	 * Database connection handle.
     * @access private
	 * @var mixed
	 */
    var $_handle;
    
	/**
	 * Database connection params.
     * @access private
	 * @var array
	 */
    var $_params;

    /**
     * DBBroker class constructor.
     *
     * Creates the new instance of DBBroker class and sets up connection properties.
     *
     * @access public
     * @param array $params the database connection params, defaults to "".
     * @return void
     */
    function DBBroker($params='') {
        Object::Object();
        $this->_handle = null;
        $this->_params = array(
            'database' => '',
            'username' => '',
            'password' => '',
            'hostname' => 'localhost'
        );
        if (strlen($params)) {
            $params = explode(',', $params);
            if (isset($params[0])) {
                $this->_params['database'] = $params[0];
            }
            if (isset($params[1])) {
                $this->_params['username'] = $params[1];
            }
            if (isset($params[2])) {
                $this->_params['password'] = $params[2];
            }
            if (isset($params[3])) {
                $this->_params['hostname'] = $params[3];
            }
        }
    }
    
    /**
     * Gets the database connection param.
     *
     * Returns the database connection param, or empty string.
     *
     * @access public
     * @param string $param the database connection param name.
     * @return string
     */
    function getParam($param) {
        return $this->_params[$param];
    }
    
    /**
     * Gets database connection handle.
     *
     * Returns resource handle if the database connection is opened, null otherwise.
     *
     * @access public
     * @return mixed
     */
    function getHandle() {
        return $this->_handle;
    }

    /**
     * Sets the database connection param.
     *
     * Returns true on success or error object with an error message on any
     * kind of failure.
     *
     * @access public
     * @param string $handle the database connection param name.
     * @param string $value the database connection param value.
     * @return mixed
     */
    function setParam($param, $value) {
        if (!is_resource($this->_handle)) {
            if (strlen($param)) {
                $this->_params[$param] = $value;
            } else {
                return new DBError('setparam(): DBBroker param name is empty.');
            }
        } else {
            return new DBError('setparam(): DBBroker connection already established.');
        }
        return true;
    }
    
    /**
     * Opens database connection.
     *
     * If called when the databse is already connected, it returns error object
     * otherwise it returns true.
     *
     * @access public
     * @return mixed
     */
    function open() {
        if (!is_resource($this->_handle)) {
            $hostname = $this->_params['hostname'];
            $username = $this->_params['username'];
            $password = $this->_params['password'];
            if ($hostname && $username && $password) {
                $this->_handle = @mysql_pconnect($hostname, $username, $password);
            } elseif ($hostname && $username){
                $this->_handle = @mysql_pconnect($hostname, $username);
            } elseif ($hostname) {
                $this->_handle = @mysql_pconnect($hostname);
            }
            if (is_resource($this->_handle)) {
                if (!@mysql_select_db($this->_params['database'], $this->_handle)) {
                    if (@mysql_close($this->_handle)) {
                        $this->_handle = null;
                    }
                    return new DBError('open(): DBBroker could not select database.');
                }
            } else {
                return new DBError('open(): DBBroker ' . mysql_error(), mysql_errno());
            }
        } else {
            return new DBError('open(): DBBroker connection already established.');
        }
        return true;
    }
    
    /**
     * Closes database connection.
     *
     * If called when the database connection is closed or it couldn't be closed,
     * it returns error object, otherwise it returns true.
     *
     * @access public
     * @return mixed
     */
    function close() {
        if (is_resource($this->_handle)) {
            if (@mysql_close($this->_handle)) {
                $this->_handle = null;
            } else {
                return new DBError('close(): DBBroker ' . mysql_error(), mysql_errno());
            }
        } else {
            return new DBError('close(): DBBroker connection not established.');
        }
        return true;
    }

    /**
     * Retrieves the table count in the database.
     *
     * If called when the database connection is closed, it returns error object
     * otherwise it returns the table count in the database or a error object
     * with an error message on any kind of failure.
     *
     * @access public
     * @return mixed
     */
    function tableCount() {
        if (is_resource($this->_handle)) {
            $handle = @mysql_list_tables($this->_params['database'], $this->_handle);
            if (is_resource($handle)) {
                $result = @mysql_num_rows($handle);
                @mysql_free_result($handle);
            } else {
                return new DBError('tablecount(): DBBroker ' . mysql_error(), mysql_errno());
            }
        } else {
            return new DBError('tablecount(): DBBroker connection not established.');
        }
        return $result;
    }
    
    /**
     * Retrieves the table names in the database.
     *
     * If called when the database connection is closed, it returns error object
     * otherwise it returns the array containing names of the tables in the
     * database or a error object with an error message on any kind of failure.
     *
     * @access public
     * @return mixed
     */
    function tableNames() {
        if (is_resource($this->_handle)) {
            $handle = @mysql_list_tables($this->_params['database'], $this->_handle);
            if (is_resource($handle)) {
                $result = array();
                for ($i = 0; $i < @mysql_num_rows($handle); $i++) {
                    $result[] = @mysql_tablename($handle, $i);
                }
                @mysql_free_result($handle);
            } else {
                return new DBError('tablenames(): DBBroker ' . mysql_error(), mysql_errno());
            }
        } else {
            return new DBError('tablenames(): DBBroker connection not established.');
        }
        return $result;
    }

    /**
     * Performs database query.
     *
     * If called when the database connection is closed, it returns error object
     * otherwise it returns the {@link DBQuery} object containing results of the
     * last query performed on the database or a error object with an error
     * message on any kind of failure. If the query is not a cursor, the number
     * of affected rows is returned.
     *
     * @access public
     * @return mixed
     */
    function &query($query) {
        if (is_resource($this->_handle)) {
            if (strlen($query)) {
                $handle = @mysql_query($query, $this->_handle);
                if (is_resource($handle) || ($handle == true)) {
                    if (preg_match('/(SELECT|SHOW)/i', $query)) {
                        return new DBQuery($query, $handle);
                    } else {
                        return @mysql_affected_rows($this->_handle);
                    }
                } else {
                     return new DBError('query(): DBBroker ' . mysql_error(), mysql_errno());
                }
            } else {
                return new DBError('query(): DBBroker empty query string.');
            }
        } else {
            return new DBError('query(): DBBroker connection not established.');
        }
    }
    
    /**
     * Finds whether the database connection is opened.
     *
     * Returns true if the database connection is established, false otherwise.
     *
     * @access public
     * @return boolean
     */
    function isOpened() {
        return is_resource($this->_handle);
    }
    
}

/**
 * Advanced database query class.
 *
 * @author Robert Bala <hide@address.com>
 * @access public
 */
class DBQuery extends Object {

	/**
	 * The SQL statement.
     * @access private
	 * @var string
	 */
    var $_query;

	/**
	 * SQL statement param orginal values.
     * @access private
	 * @var array
	 */
    var $_params;
    
	/**
	 * SQL statement param prepared for parse.
     * @access private
	 * @var array
	 */
    var $_values;
    
	/**
	 * The cursor handle.
     * @access private
	 * @var mixed
	 */
    var $_handle;
    
	/**
	 * The current cursor position.
     * @access private
	 * @var int
	 */
    var $_number;

    /**
     * DBQuery class constructor.
     *
     * Creates the new instance of DBQuery class and sets up basic properties.
     *
     * @access public
     * @param string $query the SQL statement, defaults to "".
     * @param mixed $handle the query resource handle, defaults to null.
     * @return void
     */
    function DBQuery($query='', $handle=null) {
        Object::Object();
        $this->_query = $query;
        $this->_params = array();
        $this->_values = array();
        $this->_handle = $handle;
        $this->_number = 0;
    }
    
    /**
     * Gets the SQL statement.
     *
     * Returns the SQL statement or empty string.
     *
     * @access public
     * @return string
     */
    function getQuery() {
        return $this->_query;
    }
    
    /**
     * Gets the SQL statement variable.
     *
     * Returns the SQL statement variable or empty string.
     *
     * @access public
     * @param string $param the SQL statement variable name.
     * @return string
     */
    function getParam($param) {
        if (isset($this->_values[$param])) {
            return $this->_values[$param];
        }
        return '';
    }
    
    /**
     * Gets the cursor handle.
     *
     * Returns resource handle if the cursor is opened, null otherwise.
     *
     * @access public
     * @return mixed
     */
    function getHandle() {
        return $this->_handle;
    }
    
    /**
     * Sets the SQL statement.
     *
     * If called when the cursor is opened, it returns error object otherwise it
     * returns true.
     *
     * @access public
     * @param string $query the SQL statement.
     * @return string
     */
    function setQuery($query) {
        if (!is_resource($this->_handle)) {
            $this->_query = $query;
        } else {
            return new DBError("setquery(): DBQuery is already opened.");
        }
        return true;
    }

    /**
     * Sets the SQL statement variable.
     *
     * Returns true on success or error object with an error message on any
     * kind of failure.
     *
     * @access public
     * @param string $param the SQL statement variable name.
     * @param string $value the SQL statement variable value.
     * @return mixed
     */
    function setParam($param, $value) {
        if (!is_resource($this->_handle)) {
            if (strlen($param)) {
                $this->_values[$param] = $value;
                $this->_params[$param] = '/' . preg_quote('{' . $param . '}') . '/';
            } else {
                return new DBError("setparam(): DBQuery param name is empty.");
            }
        } else {
            return new DBError("setparam(): DBQuery is already opened.");
        }
        return true;
    }
    
    /**
     * Opens cursor.
     *
     * If called when the cursor is already opened, it returns error object
     * otherwise it returns true. If $offset is specified the internal row pointer
     * is moved to specified row number after successful opening the cursor.
     *
     * @access public
     * @param int $offset the internal row index, defaults null.
     * @return mixed
     */
    function open($offset=null) {
        if (!is_resource($this->_handle)) {
            $query = preg_replace($this->_params, $this->_values, $this->_query);
            if (strlen($query) && preg_match('/^(SELECT|SHOW)/i', $query)) {
                $handle = @mysql_query($query);
                if (is_resource($handle)) {
                    if (isset($offset) && ($offset < @mysql_num_rows($handle))) {
                        if (!@mysql_data_seek($handle, $offset)) {
                            return new DBError('open(): DBQuery ' . mysql_error(), mysql_errno());
                        }
                    }
                    $this->_handle = $handle;
                } else {
                    return new DBError('open(): DBQuery ' . mysql_error(), mysql_errno());
                }
            } else {
                return new DBError('open(): DBQuery coult not open empty or modify query.');
            }
        } else {
            return new DBError('open(): DBQuery is already opened.');
        }
        return true;
    }

    /**
     * Closes cursor.
     *
     * If called when the cursor is closed or it couldn't be closed, it returns
     * error object, otherwise it returns true.
     *
     * @access public
     * @return mixed
     */
    function close() {
        if (is_resource($this->_handle)) {
            if (@mysql_free_result($this->_handle)) {
                $this->_number = 0;
                $this->_handle = null;
            } else {
                return new DBError('close(): DBQuery ' . mysql_error(), mysql_errno());
            }
        } else {
            return new DBError('close(): DBQuery has not been opened.');
        }
        return true;
    }
    
    /**
     * Executes the query.
     *
     * If called when the cursor is opened, it returns error object otherwise
     * it returns the number of affected rows. If executed SQL statement is
     * selecting query the error object will be returned.
     *
     * @access public
     * @return mixed
     */
    function execute() {
        if (!is_resource($this->_handle)) {
            $query = preg_replace($this->_params, $this->_values, $this->_query);
            if (strlen($query) && !preg_match('/^(SELECT|SHOW)/i', $query)) {
                if (@mysql_query($query)) {
                    return @mysql_affected_rows();
                } else {
                    return new DBError('execute(): DBQuery ' . mysql_error(), mysql_errno());
                }
            } else {
                return new DBError('execute(): DBQuery coult not execute empty or select query.');
            }
        } else {
            return new DBError('execute(): DBQuery is already opened.');
        }
    }
    
    /**
     * Retrieves the cursor row count.
     *
     * If called when the query is closed, it returns error object otherwise it
     * returns the cursor row count or a error object with an error message on
     * any kind of failure. Note - this method works only with cursors.
     *
     * @access public
     * @return mixed
     */
    function rowCount() {
        if (is_resource($this->_handle)) {
            $result = @mysql_num_rows($this->_handle);
            if (empty($result) && mysql_error()) {
                return new DBError('rowCount(): DBQuery ' . mysql_error(), mysql_errno());
            }
        } else {
            return new DBError('rowCount(): DBQuery has not been opened.');
        }
        return $result;
    }
    
    /**
     * Retrieves the current cursor position.
     *
     * If called when the query is closed, it returns error object otherwise it
     * returns the current cursor position or a error object with an error
     * message on any kind of failure. Note - this method works only with cursors.
     *
     * @access public
     * @return mixed
     */
    function rowNumber() {
        if (is_resource($this->_handle)) {
            return $this->_number;
        } else {
            return new DBError('rowNumber(): DBQuery has not been opened.');
        }
    }

    /**
     * Retrieves the specified row from the cursor.
     *
     * If called when the query is closed, it returns error object otherwise it
     * returns the specified row from the cursor or a error object with an error
     * message on any kind of failure. Note - this method works only with cursors.
     * If $offset is specified the internal row pointer is moved to specified
     * row number before fetching the row.
     *
     * @access public
     * @param int $offset the internal row index, defaults null.
     * @return mixed
     */
    function fetchRow($offset=null) {
        if (is_resource($this->_handle)) {
            if (isset($offset)) {
                if (@mysql_data_seek($this->_handle, $offset)) {
                    $result = @mysql_fetch_array($this->_handle, MYSQL_BOTH);
                } else {
                    return new DBError('fetchRow(): DBQuery ' . mysql_error(), mysql_errno());
                }
            } else {
                $result = @mysql_fetch_array($this->_handle, MYSQL_BOTH);
            }
            if (empty($result)) {
                if (mysql_error()) {
                    return new DBError('fetchRow(): DBQuery ' . mysql_error(), mysql_errno());
                }
                return null;
            }
            if (empty($number)) {
                $this->_number += 1;
            } else {
                $this->_number = $number;
            }
        } else {
            return new DBError('fetchRow(): DBQuery has not been opened.');
        }
        return $result;
    }

    /**
     * Retrieves the cursor field count.
     *
     * If called when the query is closed, it returns error object otherwise it
     * returns the cursor field count or a error object with an error message
     * on any kind of failure. Note - this method works only with cursors.
     *
     * @access public
     * @return mixed
     */
    function fieldCount() {
        if (is_resource($this->_handle)) {
            $result = @mysql_num_fields($this->_handle);
            if (empty($result) && mysql_error()) {
                return new DBError('fieldcount(): DBQuery ' . mysql_error(), mysql_errno());
            }
        } else {
            return new DBError('fieldcount(): DBQuery has not been opened.');
        }
        return $result;
    }

    /**
     * Retrieves the cursor field names.
     *
     * If called when the query is closed, it returns error object otherwise it
     * returns the array containing cursor field names or a error object with an
     * error message on any kind of failure. Note - this method
     * works only with cursors.
     *
     * @access public
     * @return mixed
     */
    function fieldNames() {
        if (is_resource($this->_handle)) {
            $result = array();
            for ($i = 0; $i < @mysql_num_fields($this->_handle); $i++) {
                $result[] = @mysql_field_name($this->_handle, $i);
            }
            if ((count($result) == 0) && mysql_error()) {
                return new DBError('fieldcount(): DBQuery ' . mysql_error(), mysql_errno());
            }
        } else {
            return new DBError('fieldnames(): DBQuery invalid result handle.');
        }
        return $result;
    }

    /**
     * Finds whether the last query returned or affected any rows.
     *
     * Returns true if the last query returned or affected any rows, false otherwise.
     *
     * @access public
     * @return boolean
     */
    function isEmpty() {
        return (is_resource($this->_handle) && (@mysql_num_fields($this->_handle) == 0));
    }
    
    /**
     * Finds whether the cursor is opened.
     *
     * Returns true if the cursor is opened, false otherwise.
     *
     * @access public
     * @return boolean
     */
    function isOpened() {
        return is_resource($this->_handle);
    }

}

?>
Return current item: XS PHP Library