源码拿来即用
<?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函数手册