自己封装的php Mysql操作类

自己封装的mysql操作类,可以快捷的操作mysql的增删改查,参考了android的xutils对数据库的操作,欢迎拍砖

fn_insert("library","id,title,current,max","null,'nihao3',3,120"); $db->fn_insert("library","null,'nihao3',3,120"); //delete $db->fn_delete("library",array('current'=>3)); $db->fn_delete("library",array(array('current','>','3'),array('max','!=','150'))); //update $db->fn_update("library",array('name'=>"niha1314"), array(array('current','=','3'),array('max','=','150'))); //select $selector=new SelectOr(); $res=$db->fn_select("library", $selector->where(array('max'=>100))->order("id",true)->limit(5),MYSQL_ASSOC); $res=mysqlHelper::getInstance()->fn_select("library", SelectOr::getInstance()->where(array('max'=>100))->order("id",true)->limit(5) ,MYSQLI_ASSOC); print_r($res); * * //Operate other DB (eg. certhr) mysqlHelper::getInstance("certhr")->fn_insert("demo","'pengwei','20112110010521'"); * */ class MysqlHelper{ const HOST="localhost"; const DATABASE="demo"; const ENCODING="UTF8"; private $uid; private $password; private static $_instance; private function __construct($db=null){ $this->uid="root"; $this->pwd="wei8888go"; $dbnull?$this->connect($this->uid,$this->pwd): $this->connect( $this->uid,$this->pwd,$db); } private function connect($uid,$pwd,$db=null){ $link=mysql_connect(self::HOST,$uid,$pwd) or die($this->error()); if($dbnull){ mysql_select_db(self::DATABASE,$link) or die("Error:没有检索到".self::DATABASE."数据库!"); }else{ mysql_select_db($db,$link) or die("Error:没有检索到".self::DATABASE."数据库!"); } $this->query("SET NAMES ".self::ENCODING) or die('字符集设置错误'); } //单例模式(让类只有一个静态对象) public static function getInstance($db=null){ if(!(self::$_instance instanceof self)){ self::$_instance=$dbnull?new self():new self($db); } return self::$_instance; } //防止对象被克隆 function __clone(){ trigger_error("Clone is not allow",E_USER_ERROR); } /***************Mysql基本操作方法*****************/ function query($sql){ return mysql_query($sql); } function fetch_array($query,$result_type=MYSQL_ASSOC){ return mysql_fetch_array($query,$result_type); } //获取一条记录(MYSQL_ASSOC,MYSQL_NUM,MYSQL_BOTH) function get_One($sql,$result_type = MYSQL_ASSOC){ $query = $this->query($sql); $rt =$this->fetch_array($query,$result_type); return $rt; } //获取全部记录 public function get_all($sql,$result_type = MYSQL_ASSOC) { $query = $this->query($sql); $rt = array(); while($row =$this->fetch_array($query,$result_type)) { array_push($rt,$row); } return $rt; } /** * 返回前一次 MySQL 操作所影响的记录行数 * mysql_result * @return int */ function affected_rows() { return mysql_affected_rows(); } function result($query,$row) { return mysql_result($query,$row); } function error(){ return mysql_error(); } function num_rows($query) { return @mysql_num_rows($query); } function num_fields($query) { return mysql_num_fields($query); } function free_result($query) { return mysql_free_result($query); } function insert_id() { return mysql_insert_id(); } /** * 从结果集中取得一行作为数字数组 * @param $query * @return array */ function fetch_row($query) { return mysql_fetch_row($query); } function version() { return mysql_get_server_info(); } function close() { return mysql_close(); } /***************自定义方法:增删改查*****************/ /** * @param $table 数据表名 * @param null $name 数据表字段名 * @param $val 数据表字段名对应的值 */ function fn_insert($table,$name,$val=null){ if($valnull){ $this->query("insert into $table values ($name) "); }else{ $this->query("insert into $table ($name) values ($val) "); } } /** * @param $table 数据表名 * @param null $where 查找条件的参数和值 */ function fn_delete($table,$where=null){ //删除表中所有数据 if($wherenull){ $this->query("delete from ".$table); //自定义where参数 }else{ $whereParam=builtWhere($where); $this->query("delete from $table where ".$whereParam); } } /** * @param $table 数据表名 * @param $setVal 更新的参数和值 * @param null $where 查找条件的参数和值 */ function fn_update($table,$setVal,$where=null){ $valParam=builtWhere($setVal,","); if($wherenull){ $this->query("update $table set ".$valParam); }else{ $whereParam=builtWhere($where); $this->query("update $table set ".$valParam." where ".$whereParam); } } /** * @param $table 数据表名 * @param null $SelectOr 查找条件设置操作对象 * @param int $result_type * @return array */ function fn_select($table,SelectOr $SelectOr=null,$selectName=null,$result_type=MYSQLI_ASSOC){ if($SelectOrnull&&$selectNamenull){ return $this->get_all("select * from ".$table,$result_type); }else if($selectNamenull){ $sql="select * from $table ".$SelectOr->getRes(); return $this->get_all($sql,$result_type); }else{ $sql="select $selectName from $table ".$SelectOr->getRes(); return $this->get_all($sql,$result_type); } } } /** * Mysql选择SELECT操作类 * Class SelectOperate */ class SelectOr{ private $sql; private static $obj; function __construct(){} public static function getInstance(){ if(!(self::$obj instanceof self)){ self::$obj=new self; } self::$obj->sql=""; return self::$obj; } function where($where){ $this->sql.=" where ".builtWhere($where); return $this; } function order($orderBy,$desc=true){ if($desc){ $this->sql.=" order by $orderBy desc "; }else{ $this->sql.=" order by $orderBy asc "; } return $this; } function limit($num){ $this->sql.=" limit $num"; return $this; } function getRes(){ return $this->sql; } } /** * 封装Sql语句Where * @param $where * @param string $connector 连接符[,|and] * @return string */ function builtWhere($where,$connector="and"){ if(is_string($where)){ return $where; }else if(is_array($where)){ $whereParam=" "; $i=1; switch(arrLevel($where)){ //参数形式:array('id'=>'5','uid'=>'wei8888go') case 1: foreach($where as $k=>$v){ if(is_string($v)){ $v="'".$v."'"; } if($icount($where)){ $whereParam.=" $k=$v "; }else{ $whereParam.=" $k=$v $connector "; } $i++; } break; //参数形式:array(array('id','>','5'),array('uid','!=','wei8888go')) case 2: for($k=0;$k3){ $key=$where[$k][0]; $operate=$where[$k][1]; $param=$where[$k][2]; if(is_string($param)){ $param="'".$param."'"; } if($icount($where)){ $whereParam.=" $key $operate $param "; }else{ $whereParam.=" $key $operate $param $connector "; } $i++; } } break; default: break; } return $whereParam; } } /** * 判断数组维度(只能判断二维) * @param $array */ function arrLevel($array){ $n=0; if(is_array($array)){ $n=1; foreach($array as $val){ if(is_array($val)){ $n++; break; } } } return $n; } ?>

操作方法演示

fn_insert("library","null,'nihao3',3,120"); mysqlHelper::getInstance()->fn_delete("library",array('current'=>3)); mysqlHelper::getInstance()->fn_update("library",array('name'=>"niha1314"),array(array('current','=','3'))); mysqlHelper::getInstance("certhr")->fn_insert("demo","'pengwei','20112110010521'"); $res=mysqlHelper::getInstance()->fn_select("library", SelectOr::getInstance()->where(array('max'=>100))->order("id",true)->limit(20),"max,id,current"); print_r($res); $res=MysqlHelper::getInstance("schoolknow")->fn_select("schoolfellow_comment", SelectOr::getInstance()->where(array(array("sfid","=",11),array("uid","!=",'wei8888go@qq.com'), array("id",">",5)))); print_r($res); ?>