PHP+MySQL OOP 筆記

爬一下PHP+MySQL OOP雖然有人寫教學文,但似乎沒有 mysqli 函數的寫法,所以就手動寫了個。

自己先習慣寫一個登入物件(DBLogin),在串接到資料庫登入(DBMySQL),比較方便做安全性檢查。

<?
define('DB_MYSQL_HOST', 'YOUR_DB_HOST_HERE');
define('DB_MYSQL_USER', 'YOUR_USERNAME');
define('DB_MYSQL_PW', 'YOUR_PASSWORD');
define('DB_MYSQL_DB', 'YOUR_DB_TABLE');
?>
<?php
/**************************
 * 資料庫登入格式化
 **************************/
class DBLogin{
  var $user;
  var $password;
  var $host;
  var $db;
  function __construct($host, $user, $password, $dbname)
  {
    $this->host     = $host;
    $this->user     = $user;
    $this->password = $password;
    $this->db       = $dbname;
  }
  public function the_user()
  {
    return StringSafe($this->user,0,255); //例如你可以寫個函數檢查
  }
  public function the_pw()
  {
    return $this->password;
  }
  public function the_host()
  {
    return $this->host;
  }
  public function the_db()
  {
    return $this->db;
  }
}
/**************************
 * MySQL連線
 **************************/
class DBMySQL{
  //設定變數
  var $_dbConn; //連線資料
  var $_qryRes; //儲存資料
  //連線MySQL
  function __construct($hostname, $user, $password, $dbname){
    //建立連線
    $_mysql_conn = mysqli_connect($hostname,
                                  $user,
                                  $password,
                                  $dbname)
                                  or die(TXT_ERR_DB.' '.mysqli_connect_error());
    //設定UTF-8
    mysqli_set_charset($_mysql_conn, "utf8");
    $this -> _dbConn = $_mysql_conn;
    return true;
  }
  //關閉連線
  function __destruct()
  {
    mysqli_close($this->_dbConn);
  }
  //單一SQL查詢資料
  public function query($sql){
    //逃逸字元
    //$_sql = mysqli_real_escape_string($this->_dbConn, $sql);
    $_sql=$sql;
    //撈資料
    $qryData = mysqli_query($this->_dbConn, $_sql)
                or die(TXT_ERR_DB.' '.mysqli_errno($this->_dbConn));
    //儲存資料後回傳
    $this->_qryRes = $qryData;
    return $qryData;
  }
  //複數SQL查詢
  public function queryMulti($sql)
  {
    //逃逸字元
    //$_sql = mysqli_real_escape_string($this->_dbConn, $sql);
    $_sql=$sql;
    //撈資料
    if(mysqli_multi_query($this->_dbConn, $_sql)){
      do {
        if($_result = mysqli_store_result($this->_dbConn)){
          while ($_row = mysqli_fetch_all($_result, MYSQLI_ASSOC)) {
            $this->_qryRes[] = $_row;
          }
          mysqli_free_result($_result);
        }
      } while (mysqli_next_result($this->_dbConn));
    }
    return $this->_qryRes;
  }
  //回傳查詢值(全部)
  public function getAllData($type=null)
  {
    $_term  = $type;
    switch ($_term) {
      case 'MYSQLI_NUM':
        return mysqli_fetch_all($this->_qryRes, MYSQLI_NUM);
        break;
      case 'MYSQLI_ASSOC':
        return mysqli_fetch_all($this->_qryRes, MYSQLI_ASSOC);
        break;
      case 'MYSQLI_BOTH':
        return mysqli_fetch_all($this->_qryRes, MYSQLI_BOTH);
        break;
      default:
        return mysqli_fetch_all($this->_qryRes, MYSQLI_ASSOC);
        break;
    }
  }
  //回傳查詢值(逐筆)
  public function getData($type=null)
  {
    $_term  = $type;
    switch ($_term) {
      case 'MYSQLI_NUM':
        return mysqli_fetch_array($this->_qryRes, MYSQLI_NUM);
        break;
      case 'MYSQLI_ASSOC':
        return mysqli_fetch_array($this->_qryRes, MYSQLI_ASSOC);
        break;
      case 'MYSQLI_BOTH':
        return mysqli_fetch_array($this->_qryRes, MYSQLI_BOTH);
        break;
      default:
        return mysqli_fetch_array($this->_qryRes, MYSQLI_NUM);
        break;
    }
  }
  //回傳查詢值總數
  public function getRowsNum()
  {
    return mysqli_num_rows($this->_qryRes);
  }
}
?>

接著就可以用下面這樣方式串接:

<?php
/* ########################################
 * 載入資料庫
######################################## */
//呼叫你的設定檔
require_once('defines.php');
require_once('mysql.php');
//設定連線
$_login = new DBLogin(DB_MYSQL_HOST, 
                      DB_MYSQL_USER, 
                      DB_MYSQL_PW, 
                      DB_MYSQL_DB);
//建立連線
$jsondb = new DBMySQL($_login -> the_host(),
                      $_login -> the_user(),
                      $_login -> the_pw(),
                      $_login -> the_db());
//成功連線
$jsondbSQL = 'SELECT * from table'; //你的SQL
$jsondb -> query($jsondbSQL);       //呼叫連線
//輸出資料
$jsonRows = $jsondb->getAllData();  //全部資料
$jsonCount = $jsondb->getRowsNum(); //傳回總查詢值
//執行你的資料處理
?>