Usefull functions to work with MySQL under PHP5


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



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.