2018年2月22日 星期四

ConnectDB

<?php

interface ConnectDB {

    public function connect($database); // 連線

    public function selectDB($database);

    public function query($sql);

    public function fetchArray(); // 返回陣列

    public function fetchObjArray(); // 返回物件

    public function fetchOneArray($column = FALSE);

    public function fetchKeyArray($keyWord = null);

    public function Insert2DB($sql); // 專門用於插入資料用(會返回 insert_id)

    public function getInsertId();

    public function freeResult(); // 釋放結果的記憶體

    public function closeConnect();

    public function autoCommit($option);

    public function getConnect(); // 返回連線

    public function getResult(); // 返回查詢結果

    public function getColumn(); // 返回所有查詢後的欄位

    public function numOfRows();
}

?>
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of ConnectDBMyli
 *
 * @author test
 */
class ConnectDBMyli implements ConnectDB {

    protected $insert_id;
    protected $user;
    protected $psw;
    protected $url;
    protected $dataBase;
    protected $conn;
    protected $result;
    protected $errorMsg;

    /* ---------------------------------------------------------------------- */

    public function __construct() {
       
    }

    /* ---------------------------------------------------------------------- */

    public function setUser($user) {
        $this->user = $user;
    }

    /* ====================================================================== */

    public function setPsw($psw) {
        $this->psw = $psw;
    }

    /* ====================================================================== */

    public function setUrl($url) {
        $this->url = $url;
    }

    /* ====================================================================== */

    public function connect($database, $url = NULL, $user = NULL, $psw = NULL) {
        if ($url) {
            $this->setUrl($url);
        }

        if ($user) {
            $this->setUser($user);
        }

        if ($psw) {
            $this->setPsw($psw);
        }
        /* ------------------------------------------ */
        $this->dataBase = $database;

        $this->conn = @new mysqli($this->url, $this->user, $this->psw, $this->dataBase);

        if (mysqli_connect_errno() != 0) {

            $this->errorMsg = sprintf("connect error: %s", mysqli_connect_error());

            throw new Exception($this->errorMsg);
        }
        /* ------------------------------------------ */

        if (!($this->result = @$this->conn->query("SET NAMES 'utf8'"))) {

            $this->errorMsg = sprintf("set names error: %s", $this->conn->error);

            // throw new Exception($this->errorMsg);
        }
    }

    /* ====================================================================== */

    /**
     * 在建立連結之後,選擇 dataBase
     *
     * {@inheritDoc}
     * @see ConnectDB::selectDB()
     */
    public function selectDB($database) {
        $conn = $this->conn;

        $conn->select_db($database);
    }

    /* ====================================================================== */

    /**
     * 查詢
     *
     * {@inheritDoc}
     * @see ConnectDB::query()
     */
    public function query($sql) {
        $conn = $this->conn;


        if (!($this->result = $conn->query($sql))) {

            $this->errorMsg = sprintf("query error: %s", $this->conn->error);

            throw new Exception($this->errorMsg);
        }
    }

    /* ====================================================================== */

    public function fetchArray() {
        $data_arry = array();

        $result = $this->result;

        while (($row_data = $result->fetch_array()) !== null) {
           
           
            $data_arry[] = $row_data;
        }
    
        return $data_arry;
    }

    /* ====================================================================== */

    public function fetchObjArray() {
        $data_arry = array();

        $result = $this->result;

        while (($obj = $result->fetch_object()) !== null) {
            $data_arry[] = $obj;
        }

        return $data_arry;
    }

    /* ====================================================================== */

    /**
     * 只取出一個欄位的值
     */
    public function fetchOneColumnArray($column = FALSE) {
        $data_arry = array();
        $result = $this->result;
        $column = strtolower($column);


        while (($row_data = $result->fetch_array()) !== null) {

            foreach ($row_data as $k => $v) {
                if ($column === strtolower($k)) {

                    $data_arry[] = $v;
                }
            }
        }

        $this->freeResult();

        return $data_arry;
    }

    /* ====================================================================== */

    public function Insert2DB($sql) {
        $conn = $this->conn;

        if (!($this->result = $conn->query($sql))) {
            $this->errorMsg = sprintf("query error: %s", $this->conn->error);
            throw new Exception($this->errorMsg);
        }

        $insert_id = $conn->insert_id;

        $this->freeResult();

        return $insert_id;
    }

    /* ====================================================================== */

    public function autoCommit($option) {
       
    }

    /* ====================================================================== */

    public function closeConnect() {
        $this->conn->close();
    }

    /* ---------------------------------------------------------------------- */

    public function fetchKeyArray($keyWord = null) {
        $result_array = array();
        $result = $this->result;

        if ($keyWord === null) {
            // 若沒有給 key,則用預設的查詢回應
            $result_array = fetchArray();
        } else {
            while (($row_data = $result->fetch_array()) !== null) {

                if (isset($row_data[$keyWord])) {
                    $result_array[$row_data[$keyWord]] = $row_data;
                }
            }
        }

        return $result_array;
    }

    /* ---------------------------------------------------------------------- */

    public function fetchOneArray($column = FALSE) {
        $result = $this->result;

        $row_data = $result->fetch_array();

        if ($column) {
            return $row_data[$column];
        }

        $this->freeResult();

        return $row_data;
    }

    /* ---------------------------------------------------------------------- */

    public function freeResult() {
        $this->result->close();
    }

    /* ---------------------------------------------------------------------- */

    public function getInsertId() {
        $conn = $this->conn;

        return $conn->insert_id;
    }

    /* ---------------------------------------------------------------------- */

    public function getConnect() {
       
    }

    /* ---------------------------------------------------------------------- */

    public function getResult() {
       
    }

    /* ---------------------------------------------------------------------- */

    public function getColumn() {
        $column_array = array();
        $result = $this->result;

        $column_array = $result->fetch_field();

        return $column_array;
    }

    public function numOfRows() {
        $result = $this->result;
        return $result->num_rows;
    }

}