Location: PHPKode > scripts > Easy Query > EasyQuery.php
<?php
/**
* define user credentials
*/
define('SERVER',	'server');
define('USER',		'mysql_user');
define('PASSWORD',	'user_password');
define('DATABASE',	'database');
/**
*
* @package: 	EasyQuery
* @author: 		a77icu5
* @link			hide@address.com
*/
class EasyQuery
{	
	/**
	 * handler of the conection
	 * 
	 * @access	private
	 * @var		integer	$_con;
	 */
	private $_con		= NULL;
	
	/**
	 * sql statenment
	 * 
	 * @access	private
	 * @var		string	$_query
	 */
	private $_query		= NULL;
	
	/**
	 * string of the last query ejecuted
	 * 
	 * @access	private
	 * @var		string	$_myQquery
	 */
	private $_myQuery	= NULL;
	
	/**
	 * values to return 
	 * 
	 * @access	private
	 * @var		string	$_values;
	 */
	private $_values	= '*';
	
	/**
	 * sql where condition
	 * 
	 * @access	private	
	 * @var		string	$_where;
	 */
	private $_where		= NULL;
	
	/**
	 * sql order by option
	 * 
	 * @access	private
	 * @var		string	$_order;
	 */
	private $_order		= NULL;
	
	/**
	 * sql group by option
	 * 
	 * @access	private
	 * @var		string	$_group
	 */
	private $_group		= NULL;
	
	/**
	 * sql limit option
	 * 
	 * @access	private
	 * @var		string $_limit;
	 */
	private $_limit		= NULL;
	
	/**
	 * sql inner join option
	 * 
	 * @access	private
	 * @var		string	$_join;
	 */
	private $_tableJoin	= NULL;
	
	/**
	 * result of the sql statenment 
	 * 
	 * @access	private
	 * @var		string	$_result
	 */
	private $_result	= NULL;
	
	/**
	 * rows fetched returned by the query
	 * 
	 * @access	private
	 * @var		array	$_fetched;
	 */
	private $_fetched	= array();
	 

	/**
	 * Start the conection with the credentials
	 * defined in the user config.php file
	 * 
	 * @access	public
	 * @return	void
	 */
	public function  __construct() {
		$this->_con	= $this->connect();
	}
	
	/**
	 * connect with mysql and select the databse to use
	 *
	 * @access	public
	 * @return	object
	 */
	public function connect() {
		if (!$this->_con = mysql_connect(SERVER, USER, PASSWORD)) {
			exit('We can\'t connect using provided credentials.');
		}
		if (!mysql_select_db(DATABASE, $this->_con)) {
			exit('We can\'t select the database.');
		}
		return $this->_con;
	}
	
	/**
	 * initialize custom query
	 * 
	 * @access	public
	 * @param	string	$query
	 * @return	object
	 */
	public function setQuery($query) {
		$this->_query = trim($query);
		return $this;
	}
	
	/**
	 * insert new rows in the database
	 * 
	 * @access	public
	 * @param	string	$table
	 * @param	array	$values
	 * @see		method::scape
	 * @return	void
	 */
	public function insert($table, $values) {
		$this->_query = 'INSERT INTO ' . $table . ' ('
						. implode(', ', array_keys($values))
						.') VALUES(';
		foreach ($values as $key => $value) {
			$value = $value;
			if(is_numeric($value)) {
				$this->_query .= ', ' . $this->scape($value);
			} else {
				$this->_query .= ', \''. $this->scape($value) . '\'';
			}
		}
		$this->_query = str_replace('(,', '(', $this->_query);
		$this->_query .=  ')';
		$this->execute();	
	}
	
	/**
	 * update records in the database
	 * 
	 * @access	public
	 * @param	string	$table
	 * @param	array	$values
	 * @see		method::getWhere()
	 * @return	void 
	 */ 
	public function update($table, $values) {
		$this->_query = 'UPDATE '. $table . ' SET ';
		foreach ($values as $key => $value) {
			$data .= $key . ' = ';
			if(!is_numeric($value)) {
				$data .= '\'' . $this->scape($value) . '\', '; 
			} else {
				$data .= $this->scape($value) . ', ';
			}
		}
		$this->_query .= $data;
		$this->_query = substr($this->_query, 0, strlen($this->_query)-2) . $this->getWhere() . ' ' . $this->getLimit();
		$this->execute();
	}
	
	/**
	 * delete records in the database
	 * 
	 * @access	public
	 * @param	string	$table
	 * @see		method::getWhere()
	 * @return	void
	 */
	public function delete($table) {
		$this->_query = 'DELETE FROM ' . $table . ' ' . $this->getWhere() . ' ' . $this->getLimit();
		$this->execute();
	}
	
	/**
	 * define the values to return
	 * 
	 * @access	public
	 * @param	string	$values
	 * @return	void
	 */
	public function values($values = '*'){
		$this->_values = $values;
		return $this;
	}
	
	/**
	 * define the where condition, if the condition
	 * is already defined then create the 'and' option
	 * to define a second condition
	 * 
	 * @access	public
	 * @param	string	$key
	 * @param	mixed	$value
	 * @return	void
	 */
	public function where($key, $value) {
		if($this->_where != ''){
			$this->_where .= ' AND ' . $key . ' = ';
			if(!is_numeric($value)) {
				$this->_where .= '\'' . $this->scape($value) .'\'';
			} else {
				$this->_where .= $this->scape($value);
			}
		} else {
			$this->_where = ' WHERE ' . $key . ' = ';
			if(!is_numeric($value)) {
				$this->_where .= '\'' . $this->scape($value) . '\'';
			} else {
				$this->_where .= $this->scape($value);
			}
		}
		return $this;
	}
	
	/**
	 * define the order by option
	 * 
	 * @access	public
	 * @param	string	$values
	 * @param	string	$type[optional]
	 * @return	void
	 */
	public function order($values, $type = 'desc'){
		$this->_order = ' ORDER BY ' . $values . ' ' . $type;
		return $this;
	}
	/**
	 * define the group by option
	 * 
	 * @access	public
	 * @param	string	$values
	 * @return	void
	 */
	public function group($values){
		$this->_group = ' GROUP BY ' . $values;
		return $this;
	}
	
	/**
	 * define the join option, if join  is already defined
	 * then create another join in the query
	 * 
	 * @access	public
	 * @param	string $table
	 * @param	string $condition
	 * @param	string $type
	 * @return	void
	 */
	public function tableJoin($table, $condition, $type) {
		if($this->_tableJoin != '') {
			$this->_tableJoin .= ' ' . strtoupper($type) . ' JOIN ' . $table . ' ON ' . $condition;
		} else {
			$this->_tableJoin = ' ' . strtoupper($type) . ' JOIN ' . $table . ' ON ' . $condition;
		}
		return $this;
	}
	
	/**
	 * define the limit of rows returned
	 * 
	 * @access	public
	 * @param	string $offset
	 * @param	string $numrows
	 * @return 
	 */
	public function limit($offset = 0, $numrows = 0) {
		$this->_limit =  ' LIMIT ' . $offset;
		if($numrows != 0) {
			$this->_limit .= ', ' . $numrows;
		}
		return $this;
	}
	
	/**
	 * return the values defined
	 * 
	 * @access	public
	 * @see		method::values()
	 * @return	string;
	 */
	public function getValues(){
		return $this->_values;
	}
	
	/**
	 * return 'where' contidion of the query
	 * 
	 * @access	public
	 * @see		method::where()
	 * @return	string 
	 */
	public function getWhere(){
		return $this->_where;
	}
	
	/**
	 * return 'order by' option of the query
	 * 
	 * @access	public
	 * @see		method::order()
	 * @return	string
	 */
	public function getOrder() {
		return	$this->_order;
	}
	
	/**
	 * return 'group by' option of the query
	 * 
	 * @access	public
	 * @see		method::group()
	 * @return	string 
	 */
	public function getGroup(){
		return $this->_group;
	}
	
	/**
	 * return all 'the inner join' options of the query
	 * 
	 * @access	public
	 * @see		method::getTableJoin()
	 * @return	string 
	 */
	public function getTableJoin(){
		return $this->_tableJoin;
	}
	
	/**
	 * return 'limit' option of the query
	 * 
	 * @access	public
	 * @see		method::limit
	 * @return	string 
	 */
	public function getLimit(){
		return $this->_limit;
	}
	
	/**
	 * return all the rows fetched in the query
	 * 
	 * @access	public
	 * @param	string $table
	 * @see		method::getValues()
	 * @see		method::getTableJoin()
	 * @see		method::getWhere()
	 * @see		method::getGroup()
	 * @see		method::getOrder()
	 * @see		method::getLimit()
	 * @return	array 
	 */
	public function get($table, $singleRow = FALSE){
		$this->_query = 'SELECT ' . $this->getValues() . ' FROM ' . $table  
				. $this->getTableJoin()
				. $this->getWhere() 
				. $this->getGroup() 
				. $this->getOrder()
				. $this->getLimit();
		$this->execute();
		return $this->fetchQuery($singleRow);
	}
	
	/**
	 * set to empty all the atributes used
	 * after the query has been executed
	 * 
	 * @access	private
	 * @return	void
	 */
	private function _reset() { 
		$this->_query		= NULL;
		$this->_values		= '*';
		$this->_where		= NULL;
		$this->_group		= NULL;
		$this->_order		= NULL;
		$this->_tableJoin	= NULL;
		$this->_limit		= NULL;
		$this->_fetched		= array();
	}
	
	/**
	 * check if exist rows on specific table
	 * 
	 * @access	public
	 * @param	string	$table
	 * @see		method::numRows()
	 * @see		method::_execute()
	 * @return	boolean
	 */
	public function haveRows($table) {
		$this->_query = 'SELECT * FROM ' . $table;
		$this->execute();
		if($this->numRows() > 0) {
			return TRUE;
		}
		return FALSE;
	}
	
	/**
	 * count number rows in the current
	 * query executed
	 * 
	 * @access	public
	 * @return	integer 
	 */
	public function numRows() {
		return mysql_num_rows($this->_result);
	}
	
	/**
	 * return the last ID generated by
	 * the current query executed
	 * 
	 * @access	public
	 * @return	integer 
	 */
	public function insertId() {
		return mysql_insert_id($this->_con);
	}
	
	/**
	 * return number of rows afected by
	 * the last sql statenment
	 * 
	 * @access	public
	 * @return	integer 
	 */
	public function rowsAfected() {
		return mysql_affected_rows($this->_con);
	}
	
	/**
	 * free mysql memory used in the current
	 * select sql statenment 
	 * 
	 * @access	public
	 * @return	void 
	 */
	public function freeResult() {
		return mysql_free_result($this->_result);
	}
	
	/**
	 * close the current conection
	 * 
	 * @access	public
	 * @return	void 
	 */
	public function disconnect() {
		mysql_close($this->_con);
	}
	
	/**
	 * return the current sql statenment
	 * generated by the user
	 * 
	 * @access	public
	 * @return	string 
	 */
	public function myQuery() {
		return $this->_myQuery;
	}
	
	/**
	 * execute sql statenments
	 * 
	 * @access	public
	 * @return	boolean 
	 */
	public function execute() {
		$this->_myQuery = $this->_query;
		$this->_result = mysql_query($this->_query, $this->_con) or die(mysql_error($this->_con));
		$this->_reset();
		return $this->_result;
	}
	
	/**
	 * create an array with the fetched rows
	 * produced by the query
	 * 
	 * @access	public
	 * @return	array
	 */
	public function fetchQuery($singleRow){
		$this->_fetched = array();
		if($singleRow == TRUE) {
			$this->_fetched = mysql_fetch_object($this->_result);
		} else {
			while($row = mysql_fetch_object($this->_result)) {
				$this->_fetched[] = $row;
			}
		}
		return $this->_fetched;
	}
	
	/**
	 * scape variables before insert or select 
	 * in the database
	 * 
	 * @access	public
	 * @param	mixed	$value
	 * @return	mixed
	 */
	public function scape($value){
		if(is_numeric($value)) {
			return stripslashes($value);
		}
		return mysql_real_escape_string($value);
	}
}
Return current item: Easy Query