Location: PHPKode > scripts > Just another SQL Wrapper > just-another-sql-wrapper/sql.class.php
<?php
/**
 * Just another MySQL wrapper, but with some "faster/easier" methods to create the query.
 * 
 * @package AARHOF
 * @author Martin Aarhof (martin at aarhof dot eu)
 * @link http://aarhof.eu/php/classes/jasw/
 * @name JASW
 * @version 1.0
 * 
 * * LICENSE
 * - GNU General Public License
 * - If you like to use this class for personal or commercial purposes, 
 * 	it's free as long as you have this notice in the file
 * - You may alter the source code. Any larger replacements or updates, please notice me :)
 * - The license is for all files included in this bundle.
 * http://www.gnu.org/licenses/gpl.html
 * 
 * * TODO
 * - Error handler with builtin mailscripts and so on
 * - PostgreeSQL handler
 * - MSSQL handler
 * - Maybe Access handler
 * 
 * KNOWN LIMITATIONS
 * - Joins, isnt possible yet with the b_ methods, but you can always fill the query in query method.
 */

/**
 * @desc Outputs all mysql codes needed for almost everything
 */
class SQL {
	/**
	* Database server name, defined outside the class!
	*
	* @var string
	* @access protected
	*/
	protected $db_server=DB_SERVER;
	
	/**
	* Database user name, defined outside the class!
	*
	* @var string
	* @access protected
	*/
	protected $db_user=DB_USER;
	
	/**
	* Database password name, defined outside the class!
	*
	* @var string
	* @access protected
	*/
	protected $db_passwd=DB_PASSWORD;
	
	/**
	* Database name, defined outside the class!
	*
	* @var string
	* @access protected
	*/
	protected $db_dbname=DB_DATABASE;
	
	/**
	* Table prefix, defined outside the class!
	* If all your table named fx. tbl_table1, tbl_table2 then insert tbl_ in the prefix,
	* then you only need to type table1, table2 in the b_****
	*
	* @var string
	* @access protected
	*/
	protected $db_prefix=DB_PREFIX;

	/**
	* Use of database engine.
	* Only MYSQL works at this moment
	*
	* @var string
	* @access protected
	*/
	protected $db_type=DB_TYPE;

	/**
	* Database link
	*
	* @var string
	* @access protected
	*/
	private $db_link;
	
	/**
	* Holder for the query
	*
	* @var string
	* @access protected
	*/
	private $db_query;
	
	/**
	* Used if you want to se the query, if true your query will NOT be executed
	*
	* @var bool
	* @access public
	*/
	public $showQuery = false;
	
	/**
	* Counter for how many selects/updates etc. you make on a page
	*
	* @var integer
	* @static integer
	* @access public
	*/
	public static $counter;

	/**
	 * Constructor, no parameters needed, if you dont want to define the database vars
	 * then put them in here.
	 * Connects to the database
	 * 
	 * @access public
	 * @param string $server
	 * @param string $user
	 * @param string $password
	 * @param string $database
	 * @param string $type
	 */
	public function __construct($server="",$user="",$password="",$database="",$type="MYSQL") {
		if($server) $this->db_server=$server;
		if($user) $this->db_user;
		if($password) $this->db_passwd;
		if($database) $this->db_dbname;
		
		switch (strtolower($type)) {
			case "mysql":
				$this->connect();
				break;
			case "mssql":
			case "pgsql":
			default:
				die($type." not implemented yet");
				break;
		}
	}
	
	/**
	* Connect to the database, and selects the database
	*
	* @access private
	*/
	private function connect() {
		if(!$this->db_link = @mysql_connect ($this->db_server,$this->db_user,$this->db_passwd)) $this->setError("mysql_connect(".$this->db_server.",".$this->db_user.",".$this->db_passwd.")");
		if(!@mysql_select_db($this->db_dbname, $this->db_link)) $this->setError("mysql_select_db(".$this->db_dbname.",".$this->db_link.")");
	}

	/**
	* Resets the static counter
	*
	* @access public
	*/
	public function resetCounter() {
		$this->counter=0;
	}

	/**
	* Return the static counter
	*
	* @access public
	* @return int
	*/
	public function getCounter() {
		return $this->counter;
	}
	/**
	* Close the database link
	* @return bool
	* @access public
	*/
	public function close() {
		return (mysql_close($this->db_link));
	}
	/**
	* Executes the query
	* @param string
	* @access public
	*/
	public function query($query) {
		if($this->showQuery) $this->setError($query);
		$this->db_query = @mysql_query ($query, $this->db_link);
		if (!$this->db_query) $this->setError($query);
		$this->countAdd();
	}

	/**
	* Fetches the query as array
	*
	* @access public
	* @return array
	*/
	public function fetch_array() {
		return mysql_fetch_array ($this->db_query, MYSQL_BOTH);
	}

	/**
	* Fetches the query as object
	*
	* @access public
	* @return object
	*/
	public function fetch_object() {
		return mysql_fetch_object($this->db_query);
	}

	/**
	* Fetches the query as row
	*
	* @access public
	* @return array
	*/
	public function fetch_row() {
		return mysql_fetch_row($this->db_query);
	}

	/**
	* Returns number of rows in the query
	*
	* @access public
	* @return integer
	*/
	public function num_rows() {
		return mysql_num_rows($this->db_query);
	}

	/**
	* Returns the number of affected rows in the query
	*
	* @access public
	* @return integer
	*/
	public function affected_rows() {
		return mysql_affected_rows();
	}

	/**
	* Returns the last added auto increment id
	*
	* @access public
	* @return integer
	*/
	public function insert_id() {
		return mysql_insert_id($this->db_link);
	}
	/**
	* Free the result from the memory
	*
	* @access public
	* @return bool
	*/
	public function free_result() {
		return mysql_free_result($this->db_query);
	}

	/**
	* Buildes a "get more" fx.
	* SELECT foo, bar, baz FROM table WHERE id = 1
	* // Returns array("foo"=>value,"bar"=>value,"baz"=>value)
	* 
	* @access public
	* @param string
	* @param string
	* @param string/array
	* @param string/array
	* @param string
	* @return string
	*/
	public function b_getMore($select,$table,$where="",$order="",$limit="") {
		if(!is_array($select)) return $this->b_getone($select,$table,$where,$order,$limit);
		else {
			$this->b_select($select,$table,$where,$order,$limit);
			return $this->fetch_row();
		}
	}

	/**
	* Buildes a "get one" fx.
	* SELECT name FROM table WHERE id = 1
	* // Returns string
	*
	* @access public
	* @param string
	* @param string
	* @param string/array
	* @param string/array
	* @param string
	* @return string
	*/
	public function b_getone($select,$table,$where="",$order="",$limit="") {
		$q = array();
		if(is_array($select)) die("b_getone does not accept an array - use b_select instead!");
		$this->b_select($select,$table,$where,$order,$limit);
		if($this->num_rows()==0) return false;
		list($out) = $this->fetch_row();
		return $out;
	}
	
	/**
	 * Only returns the "realname" used in b_fetchobject
	 *
	 * @access private
	 * @param string $name
	 * @return string
	 */
	
	private static function getAsName($name) {
		return (stripos($name," as ") ? substr($name,stripos($name," as ")+4) : $name);
	}
	
	/**
	 * Returns an array with like as
	 * select = array("id","test AS t");
	 * // Returns array("id"=>value,"t"=>value);
	 *
	 * @access public
	 * @param string/array $select
	 * @param string/array $table
	 * @param string/array $where
	 * @param string $order
	 * @param string $limit
	 * @param bool $oneRow
	 * @return array
	 */
	public function b_fetchobject($select,$table,$where="",$order="",$limit="",$oneRow=false) {
		if(!is_array($select)) $select=array($select);
		$out = array();
		$this->b_select($select,$table,$where,$order,$limit);
		if($this->num_rows()==0) return ;
		while ($r = $this->fetch_array()) {
			$arr = array();
			for($i=0;$i<count($select);$i++) {
				$arr[$this->getAsName($select[$i])]=$r[$this->getAsName($select[$i])];
			}
			$out[] = $arr;
		}
		return ($oneRow ? $out[0] : $out);
	}
	/**
	* Buildes a select
	*
	* @access public
	* @param string/array $select
	* @param string/array $table
	* @param string/array $where
	* @param string/array $order
	* @param string $limit
	*/
	public function b_select($select,$table,$where="",$order="",$limit="") {
		$q = array();
		
		$q[] = $this->_setSelect($select);
		$q[] = $this->setFrom($table);
		$q[] = $this->setWhere($where);
		$q[] = $this->setOrder($order);
		$q[] = $this->setLimit($limit);
		$this->query(implode(" ",$q));
	}
	
	/**
	 * Set the selects
	 *
	 * @access private
	 * @param array/string $select
	 * @return string
	 */
	private function _setSelect($select) {
		if(!is_array($select)) $select=array($select);
		return $this->_setStart("select").implode(",\n\t",$select);
	}
	
	/**
	* Buildes update query
	* UseQuote is used when the $update array should be passed through escapes
	*
	* @access public
	* @param array
	* @param string/array
	* @param string/array
	* @param string/array
	* @param string
	* @param boll
	*/
	public function b_update($update,$table,$where="",$order="",$limit="",$useQuote=true) {
		$set = array();
		$q = array();
		if(!is_array($update)) die("Update must be an array");
		foreach ($update AS $field => $value) $set[] = $field." = ".($useQuote?$this->setQuote($value):$value);
		$q[] = $this->_setStart("update");
		$q[] = $this->setFrom($table,false);
		$q[] = $this->_setSets($set);
		$q[] = $this->setWhere($where);
		$q[] = $this->setOrder($order);
		$q[] = $this->setLimit($limit);
		$this->query(implode(" ",$q));
	}
	
	/**
	 * Set the sets in insert query
	 *
	 * @access private
	 * @param array $set
	 * @return string
	 */
	
	private function _setSets($set) {
		return "SET\n\t".implode(",\n\t",$set);
	}

	/**
	* Buildes insert query
	* UseQuote is used when the $insert array should be passed through escapes
	*
	* @access public
	* @param array
	* @param string/array
	* @param boll
	*/
	public function b_insert($insert,$table,$useQuote=true) {
		$q = array();
		
		$q[]=$this->_setStart("insert into");
		$q[]=$this->setFrom($table,false);
		$q[]=$this->_setInsert($insert,$useQuote);
		
		$this->query(implode(" ",$q));
		return $this->insert_id();
	}
	
	/**
	 * Set the insert array
	 *
	 * @access private
	 * @param array $insert
	 * @return string
	 */
	
	private function _setInsert($insert,$quote) {
		if(!is_array($insert)) die("Insert must be an array");
		$fields = array();
		$values = array();
		foreach ($insert AS $field => $value) {
			$fields[] = $field;
			$values[] = ($quote?$this->setQuote($value):$value);
		}
		return "\n\t\t(".implode(",\n\t\t\t",$fields).")\n\tVALUES\n\t\t(".implode(",\n\t\t",$values).")";
	}
	
	/**
	* Buildes delete query
	*
	* @access public
	* @param string/array
	* @param string/array
	* @param string/array
	* @param string/array
	*/
	public function b_delete($table,$where="",$order="",$limit="") {
		$q = array();
		if(is_array($table)) $table=implode(",".$this->db_prefix,$table);
		$q[]=$this->_setStart("delete");
		$q[]= $this->setFrom($table);
		$q[]= $this->setWhere($where);
		$q[]= $this->setOrder($order);
		$q[]= $this->setLimit($limit);
		$this->query(implode(" ",$q));
	}

	/**
	* Returns count of rows
	*
	* @access public
	* @param string/array
	* @param string/array
	* @param string/array
	* @param string/array
	* @return integer
	*/
	public function b_count($table,$where="",$order="",$limit="") {
		return $this->b_getone("COUNT(*)",$table,$where,$order,$limit);
	}

	/**
	* Count the static counter up
	*
	* @access private
	*/
	private function countAdd() {
		SQL::$counter++;
	}
	
	/**
	* Creates the WHERE clausul to the b_***
	*
	* @access private
	* @param array
	* @param array/string
	*/
	private function setWhere($where) {
		if($where) return "\nWHERE\n\t".(is_array($where)?implode("\n\tAND\n\t",$where):$where);
	}

	/**
	* Creates the ORDER clausul to the b_***
	*
	* @access private
	* @param array
	* @param array/string
	*/
	private function setOrder($order) {
		if($order) return "\nORDER BY\n\t".(is_array($order)?implode(",",$order):$order);
	}

	/**
	* Creates the LIMIT clausul to the b_***
	*
	* @access private
	* @param array
	* @param array/string
	*/
	private function setLimit($limit) {
		if($limit) return "\nLIMIT\n\t".$limit;
	}

	/**
	* Set quotes on insert and updates querys
	*
	* @access private
	* @param string
	* @return string
	*/
	private function setQuote($value) {
		switch ($value) {
			case "NOW()":
				return $value;
				break;
			default:
				if($value{0}=="'" && $value{strlen($value)-1}=="'") $value = substr(substr($value,0,-1),1);
				if (get_magic_quotes_gpc()) $value = stripslashes($value);

				if(version_compare(phpversion(),"4.3.0")=="-1") $value = "'".mysql_escape_string($value)."'";
				else $value = "'".mysql_real_escape_string($value)."'";
				
				return $value;
				break;
		}
	}

	/**
	* Set quotes on insert and updates querys
	*
	* @access private
	* @param array
	* @param array/string
	* @param bool
	* @return array
	*/
	private function setFrom($table,$withFrom=true) {
		if(is_array($table)) $table=implode(",\n\t".$this->db_prefix,$table);
		return ($withFrom?"\nFROM\n\t":"").$this->db_prefix.$table;
	}
	
	/**
	 * Set start tag in query
	 *
	 * @access private
	 * @param string $type
	 * @return string
	 */
	private function _setStart($type) {
		return strtoupper($type)."\n\t";
	}
	
	/**
	* Error handler
	* Not implemented yet!
	*
	* @access private
	* @param integer
	*/
	private function setError($query) {
		echo "<b>Query:</b><br /><textarea rows='30' cols='120'>".$query."</textarea>";
		if(mysql_error()) echo "<br /><b>Error:</b><br />".mysql_error();
		if(mysql_errno()) echo "<br /><b>Error #:</b><br /><a href='http://dev.mysql.com/doc/refman/4.1/en/error-messages-server.html' target='_blanl'>".mysql_errno()."</a>";
		echo "<br /><b>Counter:</b><br />".$this->getCounter();
		
		$data[] = "<b>Client:</b><br />".mysql_get_client_info();
		$data[] = "<b>Link:</b><br />".mysql_get_host_info();
		$data[] = "<b>MySQL:</b><br />".mysql_get_server_info();
		$data[] = "<b>Protokol:</b><br />".mysql_get_proto_info();
		
		if($data) echo "<br />".implode("<br />",$data);
		exit;
	}
}
?>
Return current item: Just another SQL Wrapper