<?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;
}
}
