Jump to [1] [2] [Full screen preview]

<?php

/**
* SqlServer操作類(sqlsrv)
* Class SQLSrv
*/
class SQLSrv
{
   private $dbhost;
   private $dbuser;
   private $dbpw;
   private $dbname;
   private $port;
   private $result;
   private $connid = 0;
   private $insertid = 0;
   private $cursor = 0;
   public static $instance = null;

   public function __construct($db)
   {
       function_exists("sqlsrv_connect") or die("<pre class="brush:php;toolbar:false">請先安裝 sqlsrv 擴展。");

       $this->dbhost = !empty($db[&#39;hostname&#39;]) ? $db[&#39;hostname&#39;] : &#39;localhost&#39;;
       $this->dbuser = $db[&#39;username&#39;];
       $this->dbpw = $db[&#39;password&#39;];
       $this->dbname = $db[&#39;dbname&#39;];
       $this->port = !empty($db[&#39;port&#39;]) ? $db[&#39;port&#39;] : 1433;
       $this->connect();
   }

   public static function getdatabase($db){
       if(empty(self::$instance)){
           self::$instance = new SQLSrv($db);
       }
       return self::$instance;
   }

   /**
    * 連接數(shù)據(jù)庫
    * @return int
    */
   private function connect()
   {
       $serverName = "{$this->dbhost}, {$this->port}";
       $connectionInfo = array( "Database"=>$this->dbname, "UID"=>$this->dbuser, "PWD"=>$this->dbpw);
       if(!$this->connid = @sqlsrv_connect($serverName, $connectionInfo)){
           $this->halt(print_r( sqlsrv_errors(), true));
       }

       return $this->connid;
   }

   /**
    * 執(zhí)行sql
    * @param $sql
    * @return mixed
    */
   public function query($sql)
{
if(empty($sql)){
           $this->halt(&#39;SQL IS NULL!&#39;);
}

$result = sqlsrv_query($this->connid, $sql);

if(!$result){  //調(diào)試用，sql語句出錯時會自動打印出來
           $this->halt(&#39;MsSQL Query Error&#39;, $sql);
}

       $this->result = $result;

return $this->result;
}

   /**
    * 獲取一條數(shù)據(jù)（一維數(shù)組）
    * @param $sql
    * @return array|bool
    */
   public function find($sql)
   {
       $this->result = $this->query($sql);
       $args = $this->fetch_array($this->result);
       return $args ;
   }

   /**
    * 獲取多條（二維數(shù)組）
    * @param $sql
    * @param string $keyfield
    * @return array
    */
   public function findAll($sql, $keyfield = &#39;&#39;)
   {
       $array = array();
       $this->result = $this->query($sql);
       while($r = $this->fetch_array($this->result)){
           if($keyfield){
               $key = $r[$keyfield];
               $array[$key] = $r;
           }else{
               $array[] = $this->objectToArray($r);
           }
       }
       return $array;
   }

   /**
    * 對象轉(zhuǎn)數(shù)組
    * @param $obj
    * @return array
    */
   private function objectToArray($obj){
       $ret = array();
       foreach ($obj as $key => $value) {
           if (gettype($value) == "array" || gettype($value) == "object"){
               $ret[$key] =  $this->objectToArray($value);
           }else{
               $ret[$key] = $value;
           }
       }
       return $ret;
   }

   public function fetch_array($query, $type = SQLSRV_FETCH_ASSOC)
   {
       if(is_resource($query)) return sqlsrv_fetch_array($query, $type);
       if($this->cursor < count($query)){
           return $query[$this->cursor++]; 
       }
       return FALSE; 
   }

   public function affected_rows()
   {
       return sqlsrv_rows_affected($this->connid);
   }

   public function num_rows($query)
   {
       return is_array($query) ? count($query) : sqlsrv_num_rows($query);
   }

   public function num_fields($query)
   {
       return sqlsrv_num_fields($query);
   }

   /**
    * 釋放連接資源
    * @param $query
    */
   public function free_result($query)
   {
       if(is_resource($query)) @sqlsrv_free_stmt($query);
   }

   public function insert_id()
   {
       return $this->insertid;
   }

   public function fetch_row($query)
   {
       return sqlsrv_num_rows($query);
   }

   /**
    * 關(guān)閉數(shù)據(jù)庫連接
    * @return bool
    */
   public function close()
   {
       return sqlsrv_close($this->connid);
   }

   /**
    * 拋出錯誤
    * @param string $message
    * @param string $sql
    */
   public function halt($message = &#39;&#39;, $sql = &#39;&#39;)
   {
       $_sql = !empty($sql) ? "MsSQL Query:$sql <br>" : &#39;&#39;;
       exit("<pre class="brush:php;toolbar:false">{$_sql}Message:$message");
   }

   /**
    * 開始一個事務(wù).
    */
   public function begin()
   {
       return sqlsrv_begin_transaction($this->connid);
   }

   /**
    * 提交一個事務(wù).
    */
   public function commit()
   {
       return sqlsrv_commit($this->connid);
   }

   /**
    * 回滾一個事務(wù).
    */
   public function rollback()
   {
       return sqlsrv_rollback($this->connid);
   }

   /**
    * 返回服務(wù)器信息
    * @return array
    */
   public static function serverInfo(){
       return sqlsrv_server_info($this->connid);
   }

   /**
    * 返回客戶端信息
    * @return array|null
    */
   public static function clientInfo(){
       return sqlsrv_client_info($this->connid);
   }

   /**
    * 析構(gòu)函數(shù),關(guān)閉數(shù)據(jù)庫,垃圾回收
    */
   public function __destruct()
   {
       if(!is_resource($this->connid)){
           return;
       }

       $this->free_result($this->result);
       $this->close();
   }
}

2. [Code] How to use Jump to [1] [2] [Full screen preview]

<?php
require "database/sqlsrv_driver.php";     //導(dǎo)入數(shù)據(jù)庫操作類

$_db = array( //數(shù)據(jù)庫連接信息
   &#39;hostname&#39; => &#39;localhost&#39;, //主機地址
   &#39;username&#39; => &#39;sa&#39;, //用戶名
   &#39;password&#39; => &#39;123&#39;, //密碼
   &#39;dbname&#39; => &#39;test_db&#39;, //數(shù)據(jù)庫名
   &#39;port&#39; => 1433, //端口  默認(rèn)1433
);
$db = SQLSrv::getdatabase($_db); //數(shù)據(jù)庫連接

#最終 $db 就是你的數(shù)據(jù)庫實例

#查詢示例
$sql = "select * from table1";

#查詢單條：
$result = $db->find($sql);

#查詢多條：
$result = $db->findAll($sql);

#執(zhí)行增刪改：
$result = $db->query($sql);

#...