php7.0 连接sqlserver数据库封装

源码拿来即用

<?php
//php文件名称mssql.class.php
//sqlserver 数据库增删改查封装
$config = array(
  'host' => '',
  'username' => '',
  'password' => '',
  'database' => '',
);
class mssql
{
  private $sqlsrv_connect;
  private $result;
  /**
   * 数据库连接
   * @param $config 配置数组
   */
  public function connect($config)
  {
    $host = $config['host'];    //主机地址
    $username = $config['username'];//用户名
    $password = $config['password'];//密码
    $database = $config['database'];//数据库
    $port = $config['port'];    //端口号
    $connectionInfo = array("UID"=>$username, "PWD"=>$password, "Database"=>$database,"CharacterSet"=>"UTF-8"); 
    $this->mssql =   sqlsrv_connect($host,$connectionInfo);
  }
  /**
   * 数据查询
   * @param $table 数据表
   * @param null $field 字段
   * @param null $where 条件
   */
  public function select($table, $field = null, $where = null)
  {
    $sql = "SELECT  * FROM {$table}";
    if (!empty($field)) {
     // $field = '' . implode(',', $field) . '';
      $sql = str_replace('*', $field, $sql);
    }
    if (!empty($where)) {
      $sql = $sql . ' WHERE ' . $where;
    }
    // $this->sql =  $sql;
    $options =  array( "Scrollable" => SQLSRV_CURSOR_CLIENT_BUFFERED );
    $result =sqlsrv_query($this->mssql,$sql , $params,$options );
    return   $result;
   // return $this->result->num_rows;
    // return 11;

  }
  /**
   * @return mixed 只获取行数
   * 如果需要返回行数行数可以直接用sqlsrv_free_stmt($result);
   */
  public function select_row($table, $field = null, $where = null)
  {
    $sql = "SELECT  * FROM {$table}";
    if (!empty($field)) {
      $sql = str_replace('*', $field, $sql);
    }
    if (!empty($where)) {
      $sql = $sql . ' WHERE ' . $where;
    }
    $params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $result =sqlsrv_query($this->mssql,$sql, $params,$options );
    $rows_affected =  sqlsrv_num_rows($result);
    sqlsrv_free_stmt($result);
   //sqlsrv_close($this->mssql);
     return  $rows_affected;
  } 
  /**
   * 插入数据
   * @param $table 数据表
   * @param $data 数据数组
   */
  public function insert($table, $data)
  {
    
    $keys = implode(',', array_keys($data));
    $values = '\'' . implode("','", array_values($data)) . '\'';
    $sql = "INSERT INTO {$table}( {$keys} )VALUES( {$values} ) ";
    $result = sqlsrv_query($this->mssql,$sql);
    $rows_affected = sqlsrv_rows_affected( $result);
    sqlsrv_free_stmt( $result);
    sqlsrv_close($this->mssql);
     return  $rows_affected;
   
  //  return $sql;
  } 
  /**
   * 更新数据
   * @param $table 数据表
   * @param $data 数据数组
   * @param $where 过滤条件
   * @return mixed 受影响记录
   */
  public function update($table, $data, $where)
  {
    // foreach ($data as $key => $value) {
    //   $data[$key] = $this->mssql->real_escape_string($value);
    // }
    $sets = array();
    foreach ($data as $key => $value) {
      $kstr = '' . $key . '';
      $vstr = '\'' . $value . '\'';
      array_push($sets, $kstr . '=' . $vstr);
    }
    $kav = implode(',', $sets);
    $sql = "UPDATE {$table} SET {$kav} WHERE {$where} ";
    $result=sqlsrv_query($this->mssql,$sql);
    $rows_affected = sqlsrv_rows_affected($result);
    sqlsrv_free_stmt( $result);
    sqlsrv_close($this->mssql);
    return $rows_affected;
  }
  /**
   * 删除数据
   * @param $table 数据表
   * @param $where 过滤条件
   * @return mixed 受影响记录
   */
  public function delete($table, $where)
  {
    $sql = "DELETE FROM {$table} WHERE {$where}";
    $result=sqlsrv_query($this->mssql,$sql);
    $rows_affected = sqlsrv_rows_affected( $result);
    sqlsrv_free_stmt( $result);
    sqlsrv_close($this->mssql);
    return  $rows_affected;
  }
}

查询函数

<?php
//调用类
require_once 'mssql.class.php';
$employeecode = $_POST["employeecode"];
$mssql = new mssql ();
$mssql->connect($config);

//查询数据
$table = 'Qingxi_view';
$field = "top 30 * ";  
$where ="qx_employeecode = '".$employeecode."' order by 清洗日期 desc";
$result  = $mssql->select($table,$field,$where);

while($row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC))
    {
       $json .= json_encode($row).",";  //封装进json
    }
	$json = substr( $json  , 0, -1);  //json去掉最后的‘，’
	
//格式化输出
$data =  "{\"code\":0,\"msg\":\"\",\"count\":"."1".",\"data\":[".$json."]}"; 
	echo $data;
?>

官方文档

链接: 游标说明.
链接：PHPsqlserver函数手册