Advanced Search

Usefull functions to work with MySQL under PHP5

/ Published in: PHP
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. <?php
  2. class database{
  3.  
  4. 	private $conn_id=null;
  5. 	private $_debug=0;
  6. 	private $sql_res=null;
  7. 	private $last_error=null;
  8. 	private $total_queryes=0;
  9. 	public $version=1;
  10. 	public $db_version=null;
  11.  
  12. 	//private $auth_info=array('host'=>'localhost','user'=>'poimenno','passwd'=>'mdjwg58h','database'=>'poimenno','prefix'=>'futbolka');
  13. 	private $auth_info=array('host'=>'localhost','user'=>'root','passwd'=>'','database'=>'futbolka','prefix'=>'futbolka');
  14.  
  15. 	function  __construct(){
  16. 		if($this->_debug){
  17. 			set_error_handler("system::_errorMsg");
  18. 		}
  19. 	}
  20.  
  21. 	private function makeFieldsString($data)
  22. 	{
  23. 		$i=0;
  24. 		if(is_array($data)){
  25. 	        foreach($data as $k=>$v)
  26. 			{
  27. 			    $i++;
  28. 				$fld.=$v;
  29. 				if($i<(count($data)))
  30. 				{
  31. 					$fld.=',';
  32. 				}
  33. 			}
  34. 		}else{
  35. 			$fld="*";
  36. 		}
  37. 		return $fld;
  38. 	}
  39.  
  40. 	private function makeLimitString($data)
  41. 	{
  42. 		return (($data)?(is_array($data)?$data[0].','.$data[1]:$data):'');
  43. 	}
  44.  
  45. 	private function makeWhereString($data,$type="AND")
  46. 	{
  47. 			$where=null;
  48. 			$i=0;
  49. 			if(is_array($data) && count($data)>0)
  50. 			{
  51. 				$where='WHERE ';
  52. 				foreach($data as $k=>$v)
  53. 				{
  54. 					$i++;
  55. 					$where.=$k."='".$v."' ";
  56. 					if($i<(count($data)))
  57. 					{
  58. 						$where.=$type." ";
  59. 					}
  60. 				}
  61. 			}
  62. 			return $where;
  63. 	}
  64.  
  65. 	public function deleteRow($table,$where_clause,$where_type="AND")
  66. 	{
  67. 		if(!is_array($where_clause))
  68. 		{
  69. 			$result=DATABASE_WRONG_PARAM;
  70. 		}else{
  71. 			$query="DELETE FROM `#prefix#_".$table."`".$this->makeWhereString($where_clause,$where_type);
  72. 			$q=$this->proceedQuery($query);
  73. 			$result=!$this->isError();
  74. 		}
  75. 		return $result;
  76. 	}
  77.  
  78. 	/**
  79. 	 * 
  80. 	 * @return 
  81. 	 * @param $table String  New of the source datatable
  82. 	 * @param $fields Array  Fields list
  83. 	 * @param $where Array[optional] Where-clause string
  84. 	 * @param $order String[optional]	Sorting order
  85. 	 * @param $limitation Boolean[optional]	Limitation of resulted corteges
  86. 	 */
  87. 	public function getRows($table,$fields,$where=1,$order=false,$limitation=false){
  88. 		 if(trim($table)=='' || ($fields!='*' && !is_array($fields)) || ($where!=1 && !is_array($where))){
  89. 			 $result=DATABASE_WRONG_PARAM;
  90. 		 }else{
  91. 				$ord=(trim($order)!='')?'ORDER BY `'.$order.'`':'';
  92. 				$query=sprintf("SELECT %s FROM `#prefix#_%s` %s %s %s",
  93. 														$this->makeFieldsString($fields),
  94. 														$table,
  95. 														$this->makeWhereString($where),
  96. 														$ord,
  97. 														$this->makeLimitString($limit)
  98. 								);
  99. 				#if($table=="clients")die($query);
  100. 				$q=$this->proceedQuery($query);
  101. 				if($this->isError()){
  102. 					$result=DATABASE_PROCEED_ERROR;
  103. 				}else{
  104. 				 $result=$q;
  105. 				}
  106. 		 }
  107. 		 return $result;
  108. 	}
  109.  
  110. 	public function updateRow($table,$updates,$where_clause,$where_type="AND"){
  111. 		if(!is_array($where_clause) || !is_array($updates)){
  112. 			$result=DATABASE_WRONG_PARAM;
  113. 		}else{
  114. 			$upd='';
  115. 			$i=0;
  116. 			foreach($updates as $k=>$v){
  117.         		$i++;
  118. 				$upd.=$k."=".((is_numeric($v) || preg_match('/(\+|\-|\*|\!)/',$v))?$v:'\''.$v.'\'');
  119. 				if($i<(count($updates))){
  120. 					$upd.=',';
  121. 				}
  122. 			}
  123. 			$query="UPDATE `#prefix#_".$table."` SET ".$upd." ".$this->makeWhereString($where_clause);
  124. 			$q=$this->proceedQuery($query);
  125. 			$result=!$this->isError();
  126. 		}
  127. 		return $result;
  128. 	}
  129.  
  130. 	public function fetchQuery($q){
  131. 		$result=($q && is_resource($q))?mysql_fetch_array($q): NULL;
  132. 		return $result;
  133. 	}
  134.  
  135. 	public function setConnection(){
  136. 		if($this->checkConnection()!=DATABASE_CONNECTION_ESTABILISHED){
  137. 			$auth=$this->getProperty(array('auth_info'=>array('host','user','passwd','database')));
  138. 			$this->propertySet("conn_id",mysql_connect($auth['host'],$auth['user'],$auth['passwd']));
  139. 			if($this->isError()){
  140. 				$this->sqlErrorExpect(DATABASE_CONNECTION_ERROR,__LINE__,__FILE__);
  141. 			}else{
  142. 				$this->propertySet('sql_res',mysql_select_db($auth["database"],$this->getProperty('conn_id')));
  143. 				$this->proceedQuery("SET NAMES utf8");
  144. 				if($this->isError()) $this->sqlErrorExpect(DATABASE_ACCESS_ERROR,__LINE__,__FILE__);
  145. 			}
  146. 		}
  147. 	}
  148.  
  149. 	public function getErrorsList($count=2){
  150. 		$this->temp['_errors']=$this->getProperty('_errors');
  151. 		$this->temp['_result']=array();
  152. 		if(count($this->temp['_errors'])!=0){
  153.         	for($i=0;$i<$count;$i++){
  154.            		$this->temp['_result'][]=$this->temp['_errors'][$i];
  155.         	}
  156. 		}else{
  157. 			return array();
  158. 		}
  159. 		return $this->temp['_result'];
  160. 	}
  161.  
  162. 	public function sqlErrorString(){
  163. 				 return $this->getLastError();
  164. 	}
  165.  
  166. 	public function getTableFields($table)
  167. 	{
  168. 			$result=array();
  169. 			 $scheme=$this->getRows($table,"*",1);
  170. 			 #die(print_r($scheme));
  171. 			 $i=0;
  172. 			 while($i<mysql_num_fields($scheme))
  173. 			 {
  174. 			 	$meta=mysql_fetch_field($scheme,$i);
  175. 				$result[]=array('name'=>$meta->name,'type'=>$meta->type);
  176. 				$i++;
  177. 			 }
  178. 			 return array('data'=>$result,'count'=>mysql_num_fields($scheme));
  179. 	}
  180.  
  181. 	public function insertRow($table,$data){
  182. 			 $query="INSERT into `#prefix#_".$table."` ";
  183. 			 $fs=$this->getTableFields($table);
  184. 			 $fields=$fs['data'];
  185. 			 $count=$fs['count'];
  186. 			 $i=0;
  187. 			 $fscheme='(';
  188. 			 $scheme='';
  189. 			 #die(print_r($data));
  190. 			 foreach($fields as $k=>$v)
  191. 			 {
  192. 					$scheme.=($v['type']=='blob' || $v['type']=='string')?'\'':'';
  193. 					$scheme.=$data[$i];
  194. 					$scheme.=($v['type']=='blob' || $v['type']=='string')?'\'':'';
  195. 					$scheme.=($i<($fs['count']-1))?',':'';
  196. 					$fscheme.='`'.$v['name'].'`';
  197. 					$fscheme.=($i<($fs['count']-1))?',':'';
  198. 					$i++;
  199. 			 }
  200. 			 $query.=$fscheme.') VALUES('.$scheme.')';
  201. 			 $q=$this->proceedQuery($query);
  202. 			 if($q)
  203. 			 {
  204. 			 	return mysql_insert_id($this->conn_id);
  205. 			 }else{
  206. 			 	return false;
  207. 			 }
  208. 	}
  209.  
  210.     public function checkRowExists($table,$rows,$where_type="AND",$limitation=true){
  211.         $this->propertySet('sql_res',null);
  212.         $query=sprintf("SELECT * FROM `#prefix#_%s` %s LIMIT %s",
  213. 																$table,
  214. 																$this->makeWhereString($rows),
  215. 																($limitation)?1:'');
  216.  
  217. 		$q=$this->proceedQuery($query);
  218.         if(!$this->isError()){
  219.             return($this->getNumrows($q)!=0) ;
  220.         }else{
  221.             return DATABASE_PROCEED_ERROR;
  222.         }
  223.         return 0;
  224.     }
  225.  
  226.  
  227. 	public function getProperty($property,$class=null){
  228. 		if(!is_array($property)){
  229. 			if(!$class)
  230. 				$result=(in_array($property,get_class_vars(get_class($this))))?$this->$property:DATABASE_PROPERTY_NOT_EXISTS;
  231. 			else
  232. 				$result=(in_array($property,get_class_vars(get_class($class))))?$$class->${$property[0]}[$property[1]]:DATABASE_PROPERTY_NOT_EXISTS;
  233. 		}else{
  234. 			$result=array();
  235. 			$class=($class && $class!=null)?$class:'this';
  236. 			foreach($property as $k=>$v){
  237. 				$property_exists=($class!='this')?in_array($k,get_class_vars(get_class($class))):isset($this->$k);
  238. 				if($property_exists){
  239. 					$result=array();
  240. 					foreach($property[$k] as $c=>$d){
  241. 						$result[$d]=$$class->{$k}[$d];
  242. 					}
  243. 				}else{
  244. 					$result[$k]=DATABASE_PROPERTY_NOT_EXISTS;
  245. 				}
  246. 			}
  247. 		}
  248. 		return $result;
  249. 	}
  250.  
  251.  
  252. 	public function isError(){
  253. 		return (mysql_error()==true);
  254. 	}
  255.  
  256.  
  257. 	public function closeConnection(){
  258. 		return (($this->getConnId()!==false)?(@mysql_close($this->getConnId()) && $this->propertySet('conn_id',null)):DATABASE_CONNECTION_NOT_SET);
  259. 	}
  260.  
  261.  
  262. 	public function proceedQuery($query){
  263. 		$this->setConnection();
  264. 		$this->propertySet("sql_res",null);
  265. 		$auth=$this->getProperty(array('auth_info'=>array('prefix')));
  266. 		$query=str_replace('#prefix#',$auth['prefix'],$query);
  267. 		if(trim($query)!=''){
  268. 			$this->propertySet("sql_res",@mysql_query($query,$this->getConnId()));
  269. 			if($this->isError()) $this->sqlErrorExpect(DATABASE_ACCESS_ERROR,__LINE__,__FILE__);
  270. 		}else{
  271. 			$this->sqlErrorExpect(DATABASE_INPUT_ERROR,__LINE__,__FILE__);
  272. 		}
  273. 		return $this->getProperty("sql_res");
  274. 	}
  275.  
  276. 	public function checkConnection(){
  277. 		return(($this->getProperty("conn_id")==true)?DATABASE_CONNECTION_ESTABILISHED:DATABASE_CONNECTON_NOT_SET);
  278. 	}
  279.  
  280. 	public function getNumrows($query_id){
  281. 		$this->propertySet("sql_res",null);
  282. 		if($query_id){
  283. 			$this->propertySet("sql_res",@mysql_num_rows($query_id));
  284. 			if($this->isError())
  285. 				$this->sqlErrorExpect(DATABASE_PROCEED_ERROR,__LINE__,__FILE__);
  286. 		}else{
  287. 				$this->sqlErrorExpect(DATABASE_INPUT_ERROR,__LINE__,__FILE__);
  288. 		}
  289. 		return $this->getProperty("sql_res");
  290. 	}
  291.  
  292. 	public function getSQLParameter($table,$col,$where){
  293. 		if(is_array($where) && count($where)!=0){
  294. 			$qStamp='';
  295. 			if(is_array($col)){
  296. 				$i=0;
  297. 				foreach($col as $k=>$v){
  298. 					$i++;
  299. 					$qStamp.=$v;
  300. 					if($i>1)	$qStamp.=',';
  301. 				}
  302. 			}else{
  303. 				$qStamp=$col;
  304. 			}
  305. 			$q=$this->proceedQuery(sprintf('SELECT %s FROM `#prefix#_%s` WHERE %s',$this->makeFieldsString($col),$table,$this->makeWhereString($where)));
  306. 			if(!$this->isError()){
  307. 				if($this->getNumrows($q)!=0){
  308. 					$row=$this->fetchQuery($q);
  309. 					$this->_result=$row[$col];
  310. 				}else{
  311. 					$this->_result=-1;
  312. 				}
  313. 			}else{
  314. 				$this->_result=-1;
  315. 			}
  316. 		}
  317. 							#die();
  318. 	return $this->_result;
  319. 	}
  320.  
  321. 	public function getConnId(){
  322. 		return $this->getProperty('conn_id');
  323. 	}
  324.  
  325. 	public function sqlErrorExpect($msg,$err_line,$err_file){
  326. 				 if($this->_debug)trigger_error($this->getErrorText($msg)."(<pre>Line:".$err_line.";<br/>File:".$err_file."<br/>MySQL Response:".$this->sqlErrorString()."<br/>)</pre>");
  327. 	}
  328. 	public function getNumcols($query_id){}
  329.  
  330. 	public function getInstance(){
  331. 	}
  332.  
  333. 	public function propertySet($var,$value,$class=null){
  334. 		if(!$class){
  335.             	$this->$var=$value;
  336. 		}else{
  337. 			if(class_exists($class)){
  338. 					$class->$var=$value;
  339. 			}else{
  340. 				return false;
  341. 			}
  342. 		}
  343. 		return true;
  344. 	}
  345.  
  346.  
  347. }
  348. ?>

URL: http://e-code.tnt43.com

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.