php 的mysql操作類

標籤：

親自測試，網上其他版本沒法用，有很多錯誤，這是本人親自測試用的，絕對增刪改查都可以。

<?php/** * Created by Netbeans. * User: Lugo * Date: 16-7-14 * Version: 1.0.0 * Time: 上午10:50 */    class MysqlHelper {                const HOST="localhost";        const DATABASE = "demo";        const ENCODING = "UTF8";        private $uname = 'root';        private $pwd = '';                private static $_instance;                private function __construct($db=NULL) {            $this->uname='root';            $this->pwd='';            $db == NULL ? $this->connect($this->uname,  $this->pwd) : $this->connect($this->uname,  $this->pwd,$db);        }                        private function connect($uname,$pwd,$db=NULL) {            $link = mysqli_connect(self::HOST, $uname, $pwd) or die($this->error());            if($db == null) {                mysqli_select_db( $link,self::DATABASE) or die('Error:沒有監測到'.self::DATABASE.'資料庫!');            }  else {                mysqli_select_db($link, $db) or die ('Error:沒有監測到'.self::DATABASE.'資料庫');            }            //$this->query("SET NAMES".self::ENCODING) or die ('字元集設定錯誤');            mysqli_set_charset($link, "utf8");            return $link;        }                        //單例模式(讓類只有一個靜態對象)        public static function getInstance($db=null){            if(!(self::$_instance instanceof self)) {                self::$_instance = $db == null ? new self() : new self($db);            }            return self::$_instance;        }                                //防止對象被複製        function __clone() {            trigger_error('不允許複製',E_USER_ERROR);        }                        /**************MySQL基本操作方法**************/                function query($sql) {            $conn = $this->connect("root", "","demo");            return  mysqli_query($conn, $sql) ;        }                        function fetch_array($query,$result_type=MYSQL_ASSOC) {                      return mysqli_fetch_array($query,$result_type) ;        }                //擷取一條記錄(MYSQL_ASSOC,MYSQL_NUM,MYSQL_BOTH)        function get_one($sql,$result_type = MYSQLI_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操作所影響的記錄行數        function affected_rows(){            return mysqli_affected_rows();        }                        function result($query,$row){            return mysqli_result($query, $row);        }                function error(){            return mysqli_error();        }                        function num_rows($query){            return mysqli_num_rows($query);        }                                function num_fields($query){            return mysqli_num_fields($query);        }                        function free_result($query){           return mysqli_free_result($query);         }                function insert_id(){            return mysqli_insert_id();        }                                //從結果集中取得一行作為數字數組        function  fetct_row($query){            return mysqli_fetch_row($query);        }                function version(){            return mysql_get_server_info();        }                        function close(){            return mysqli_close();        }                        /**************自訂方法:增刪改查 *************/        /**         * @param $table 資料表名         * @param null $name 資料表欄位名         * @param  $var 資料表欄位名對應的值         */                function fn_insert($table,$name,$val=NULL) {            if($val == NULL) {                $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($where == NULL ){                //刪除表中所有資料,別亂來啊                $this->query("DELETE FROM ",$table);            }  else {                //刪除自訂資料                $whereParam = SelectOr::builtWhere($where);                $this->query("DELETE FROM $table WHERE ". $whereParam) ;            }        }                 /**        * @param $table 資料表名        * @param $setVal 更新的參數和值        * @param null $where 尋找條件的參數和值          *UPDATE `tableName` SET `description` = '中國館是短髮' WHERE `user`.`id` = 6;        */                function fn_update($table,  $setVal , $where ) {                        //此處必須用selector::來訪問下,作為另一個類,必須要調用            $valParam = SelectOr::builtWhere($setVal,",");            if($where == NULL) {                $this->query("UPDATE $table SET ".$valParam);            }else {                $whereParam = SelectOr::builtWhere($where);                $this->query(("UPDATE $table SET ".$valParam."WHERE".$whereParam));            }        }                        /**         * @param  $name $selectOr 尋找條件設定作業對象,where部分內容         * @param int $selectName 所要選的欄位,比如 select id,name from ……  id和name就是本部分內容         * @param int $result_type 排序的種類,倒序或正序         */        function fn_select($table,  SelectOr $selectOr = NULL,$selectName=NULL,$result_type=MYSQLI_ASSOC) {            if($selectOr == NULL && $selectName==NULL) {                return $this->get_all("SELECT * FROM ".$table,$result_type);            }elseif ($selectName==NULL) {                $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 選擇操作類    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".  $this->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 update($where){            $this->sql.= $this->builtWhere($where);            return $this;        }         *          */                function limit($num) {            $this->sql.=" LIMIT $num";            return $this;        }                        function getRes() {            //$sql就是上面的一個定義的變數,你需要在調用的頁面寫SelectOr::getInstance()->where(array("uid"=>1))時            //就會在where,order,limit中有$this->sql . = xxxx            return $this->sql;        }                             /**         * 封裝SQL語句Where         * @param $where         * @param string $connector 連結符號[,|and]         * @param string          */                function builtWhere($where,$connector="AND") {            if(is_string($where)) {                return $where;            }  elseif (is_array($where)) {                                $whereParam = " ";                $i = 1;                                //下面這個之前用$this來調用的,後來在update時報未定義,所以改成self:: 未分析之間區別                switch (self::arrLevel($where)) {                                        //如果是一緯數組,即參數形勢:array('id'=>'5','uname'=>'lugo')                                       case 1:                        foreach($where as $k=>$v) {                            //如果$v是字串,則用單引號引起來;                            if(is_string($v)) {                                $v = "'".$v."'";                            }                                                                                    //如果where語句長度是1則不加 and 操作                            if($i==count($where)) {                                $whereParam .="$k=$v";                            } else {                                $whereParam .= "$k=$v $connector";                            }                            $i++;                        }                        break;                                            //參數形式:array(array('id','>','5'),array('uname','!=','lugo'))                    case 2:                        for($k=0; $k<count($where) ; $k++) {                            if(count($where[$k]) == 3 ) {                                $key = $where[$k][0];                                $operate = $where[$k][1];                                $param = $where[$k][2];                                if(is_string($param)) {                                    $param = "'".$param."'";                                }                                                                //計算添加 "and" 次數                                if($i == count($where)) {                                    $whereParam .= " $key $operate $param";                                }else{                                    $whereParam.=" $key $operate $param $connector";                                }                                $i++;                            }                       }                       break;                    default :                    break;                }                return $whereParam;            }        }            //判斷數組緯度(只能判斷二維)        function arrLevel($array) {            $n = 1;            if(is_array($array)) {                foreach ($array as $val) {                    if(is_array($val)) {                        $n++;                        break;                    }                }            }            return $n;        }    }                                    ?> 

操作指南

<?php    //error_reporting(E_ALL^E_NOTICE^E_WARNING);    require_once("databases.php");            /*操作說明:    //選擇其他資料庫連接    MysqlHelper::getInstance()->connect("root","","demo");        //選擇語句    $res = MysqlHelper::getInstance()->fn_select("blog" ,  SelectOr::getInstance()->where(array('uid'=>2))->order("uid",TRUE),"uid,title,content,time");    $res = MysqlHelper::getInstance()->fn_select("user",  SelectOr::getInstance()->where(array('username'=>'hello3')));    $res = SelectOr::getInstance()->fn_select("library", SelectOr::getInstance()->where(array('max'=>100))->order("id",true)->limit(5),MYSQLI_ASSOC);        //插入的語句格式    $res= MysqlHelper::getInstance()->fn_insert("user","username,description","'hello的55','領導slslslsllslsl'");        //UPDATE `user` SET `description` = '中國館是短髮' WHERE `user`.`id` = 6;    $res = MysqlHelper::getInstance()->fn_update('user',array('description'=>'zzz'),array(array('username','=','hello3'),array('id','=','7')));    $res = MysqlHelper::getInstance()->fn_update('user', SelectOr::getInstance()->update(array('description'=>'這個之前亂碼了')),  SelectOr::getInstance()->update(array(array('username','=','hello3') ,array('id','=','7'))));        //刪除語句    $res = MysqlHelper::getInstance()->fn_delete("user",array(array('username','=','hello3'),array('id','=','8')));    $db->fn_delete("library",array(array('current','>','3'),array('max','!=','150')));    print_r($res);    */?> 

php 的mysql操作類