<?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;
}
}
?>
|
Download
