mysql資料庫封裝

標籤：

<?php

/**
 * name: sql操作封裝，可擴充 .
 * User: 張雲山
 * Date: 2016/9/4
 * Time: 22:02
 */

//php檔案編碼設定
header(‘Content-type: text/html; charset=utf-8‘);
//資料庫類
class sqled {
    public function addTabase($sql_url,$sql_name,$sql_pss,$sql_DB){//建立資料庫
        $conn = new mysqli($sql_url,$sql_name,$sql_pss);
        if(!$conn){
            echo "串連失敗<br>";
        };
        $sql = "CREATE DATABASE ".$sql_DB;
        if($conn->query($sql) == TRUE){
            return true;
        }else{
            echo $conn->error."<br>";
        };
        $conn -> close();
    }
    //樣本（建立資料庫）************///  addTabase($sql_url,$sql_name,$sql_pss,$sql_DB);
    ///*************************************************************************///


    public function addTable($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//建立表以及欄位
        $conn = new mysqli($sql_url,$sql_name,$sql_pss,$sql_DB);
        // 檢測串連
        if (!$conn) {
            echo "串連失敗";
        };
        $sql = "CREATE TABLE {$data[‘name‘]} (
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            {$data[‘data‘]}
        )";
        /*
          id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        firstname VARCHAR(30) NOT NULL,
        lastname VARCHAR(30) NOT NULL,
        email VARCHAR(50),
        reg_date TIMESTAMP
       */
        if($conn->query($sql) == TRUE){
            return true;
        }else{
            echo $conn->error."<br>";
        };
        $conn -> close();
    }
    //樣本（建立表以及欄位）************///  addTable($sql_url,$sql_name,$sql_pss,$sql_DB,array("name"=>"ceshi_biao","data"=>"
    //                              test VARCHAR(30) NOT NULL
    //                         "));
    //***************************************************************************************///



    public function addDB($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//添加欄位資料
        $conn = new mysqli($sql_url,$sql_name,$sql_pss,$sql_DB);
        if(!$conn){
            echo "串連失敗";
        };
        $sql = "INSERT INTO {$data[‘name‘]} (
          {$data["key"]}
        )
        VALUES (
          {$data[‘value‘]}
        )";
        if($conn->query($sql) == TRUE){
            return true;
        }else{
            echo $conn->error;
        };
        $conn -> close();
    }
    //樣本（添加欄位資料）************///  addDB($sql_url,$sql_name,$sql_pss,$sql_DB,array(
    //                         "name"=>"ceshi_biao",
    //                         "key"=>"zanIndex",
    //                         "value"=>"0",
    //));
    ///***********************************************************************************///



    public function queryDB($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//讀取資料
        // 建立串連
        $conn = new mysqli($sql_url,$sql_name,$sql_pss,$sql_DB);
        // 檢測串連
        if ($conn->connect_error) {
            die("串連失敗: " . $conn->connect_error);
        };
        $sql = "SELECT {$data[‘data‘]} FROM {$data[‘name‘]}";
        $result = $conn->query($sql);
        if ($result->num_rows > 0) {
            // 輸出每行資料
            $data_data = explode(",",$data[‘data‘]);
            $key = ‘{‘;
            $fh = ‘,‘;
            for($i = 0 ; $i < count($data_data) ; $i++){
                if($i == count($data_data)-1){
                    $fh = ‘‘;
                };
                $key = $key.‘"‘.$data_data[$i].‘":"$row[\‘‘.$data_data[$i].‘\‘]"‘.$fh;
            };
            $key = $key.‘}‘;
            $returnData = array();
            while($row = $result->fetch_assoc()) {
                $key1 = json_decode($key,true);
                foreach($key1 as $keyName=>$value){
                    $value =  "{".$value."}";
                    eval("\$value = \"$value\";");
                    $key1[$keyName] = $value;
                };
                array_push($returnData,$key1);
            };
//            echo $returnData;//執行回調，並返回查詢結果
        } else {
            echo "0 個結果";
        };
        return $returnData;
        $conn->close();
    }
    //樣本（資料讀取或資料查詢）************///  queryDB($sql_url,$sql_name,$sql_pss,$sql_DB,array(
    //                         "name" => "ceshi_biao",
    //                         "data" => "id,zanIndex",
    //                     ));
    //******************************************************************///



    public function updateDB($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//資料更新
        $con=mysqli_connect($sql_url,$sql_name,$sql_pss,$sql_DB);
        // 檢測串連
        if (mysqli_connect_errno())
        {
            echo "串連失敗: " . mysqli_connect_error();
        };
        $sqlUpdate_set = null;
        $sqlUpdate_WHERE = null;
        //無條件設定，全部更新
        $index = 0;
        $fh = ",";
        foreach ($data[‘newdata‘] as $key => $value){
            $index += 1;
            if($index == count($data[‘newdata‘])){
                $fh = null;
            };
            $sqlUpdate_set = $sqlUpdate_set.$key."=‘".$value."‘".$fh;
        };
        //有條件限制s
        if(count($data["data"]) > 0 ){
            $sqlUpdate_WHERE = "WHERE";
            $fh1 = "AND";
            $index1 = 0;
            foreach ($data["data"] as $key=>$value){
                $index1 += 1;
                if($index1 == count($data[‘data‘])){
                    $fh1 = null;
                };
                $sqlUpdate_WHERE = $sqlUpdate_WHERE." ".$key."=‘".$value."‘".$fh1;
            };
        };
        //有條件限制e
        mysqli_query($con,"UPDATE {$data[‘name‘]} SET ".$sqlUpdate_set.$sqlUpdate_WHERE);
        mysqli_close($con);
        return true;
    }
    //樣本(資料更新)************///  updateDB($sql_url,$sql_name,$sql_pss,$sql_DB,array(
    //                         "name"=>"ceshi_biao",
    //                         "newdata" => array(
    //                             "zanIndex"=>"豆腐乾發給豆腐",
    //                             "id"=>"1",
    //                         ),
    //                         "data"=> array()//條件
    //                     ));
    //*****************************************************************///



    public function deleteDB($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//資料刪除
        $con=mysqli_connect($sql_url,$sql_name,$sql_pss,$sql_DB);
        // 檢測串連
        if (mysqli_connect_errno())
        {
            echo "串連失敗: " . mysqli_connect_error();
        };
        $result = mysqli_query($con,"DELETE FROM {$data[‘name‘]} WHERE {$data[‘data‘]}");
        echo $result;
        mysqli_close($con);
        return true;
    }
    //樣本(資料刪除)************///  deleteDB($sql_url,$sql_name,$sql_pss,$sql_DB,array(
    //                         "name"=>"ceshi_biao",
    //                     ));
    //*********************************************************************************//


    //資料初始值賦值
    public function aab($a,$b){
        if($a){
            $a = $a;
        }else{
            $a = $b;
        };
        return $a;
    }
    //*********************************************************************************//


    //資料庫操作類型
    public static $sql_type = array(
        "addTabase"=>"addTabase",//建立資料庫
        "addTable"=>"addTable",//建立表以及欄位
        "addDB"=>"addDB",//添加欄位資料
        "queryDB"=>"queryDB",//讀取資料
        "updateDB"=>"updateDB",//資料更新
        "deleteDB"=>"deleteDB"//資料刪除
    );
    //*********************************************************************************//

    //sql綜合調用方法；
    public function fn($sql){
        $sql[‘type‘] = $this->aab($sql[‘type‘],sqled::$sql_type[‘queryDB‘]);//預設資料庫操作設定為（資料查詢）
        $sql[‘url‘] = $this->aab($sql[‘url‘],"127.0.0.1");
        $sql[‘name‘] = $this->aab($sql[‘name‘],"root");
        $sql[‘pss‘] = $this->aab($sql[‘pss‘],"admin");
        $sql[‘db‘] = $this->aab($sql[‘db‘],"");
        $sql[‘table‘] = $this->aab($sql[‘table‘],"");
        $sql[‘array‘] = $this->aab($sql[‘array‘],array());
        switch ($sql[‘type‘]){
            case "addTabase";//建立資料庫
                return $this->addTabase($sql[‘url‘],$sql[‘name‘],$sql[‘pss‘],$sql[‘db‘]);
                break;
            case "addTable";//建立表以及欄位
                return $this->addTable($sql[‘url‘],$sql[‘name‘],$sql[‘pss‘],$sql[‘db‘],$sql[‘array‘]);
                break;
            case "addDB";//添加欄位資料
                return $this->addDB($sql[‘url‘],$sql[‘name‘],$sql[‘pss‘],$sql[‘db‘],$sql[‘array‘]);
                break;
            case "queryDB";//讀取資料
                return $this->queryDB($sql[‘url‘],$sql[‘name‘],$sql[‘pss‘],$sql[‘db‘],$sql[‘array‘]);
                break;
            case "updateDB";//資料更新
                return $this->updateDB($sql[‘url‘],$sql[‘name‘],$sql[‘pss‘],$sql[‘db‘],$sql[‘array‘]);
                break;
            case "deleteDB";//資料刪除
                return $this->deleteDB($sql[‘url‘],$sql[‘name‘],$sql[‘pss‘],$sql[‘db‘],$sql[‘array‘]);
                break;
        }
    }
    //樣本(sql操作)*****************///
        //$sql = new sqled();
        //$q =  $sql->fn(array(
        //    "db"=>sql_db,
        //    ‘type‘=>sqled::$sql_type[‘deleteDB‘],
        //    "array"=>array(
        //        "name"=>"user",
        //        "data"=>"id",
        //        "newdata"=>array(
        //            "id"=>"5",
        //            "user" => "new-d"
        //        )
        //    )
        //));
    ///******************************************************************************//


};

mysql資料庫封裝