/**
* 数据库管理
*
* @author wangaibo168@163.com
* @charset utf-8
* 不支持sqlserver2005(包括)以下的版本
*/
class Db {
/**
* @var 数据库连接配置
*/
private static $DbLink;
/**
* @var 数据库连接配置
*/
private static $DbConfig;
/**
* @var 需要执行的SQL语句
*/
private static $SqlStr;
/**
* @var 最后错误信息
*/
private static $ErrorMsg;
/**
* 默认构造函数
*/
public function __construct(){
self::set(‘host‘,‘localhost‘
);
self::set(‘port‘,‘33333‘
);
self::set(‘username‘,‘lpinfoe‘
);
self::set(‘password‘,‘LOPALinfo8e‘
);
self::set(‘database‘,‘lopal2015e‘
);
self::set(‘charset‘,‘utf-8‘
);
}
/**
* 配置连接参数
* @param $opt
*/
public static function configure(
$opt){
if(!
is_array(
$opt))
return;
self::
$DbConfig =
$opt;
}
/**
* 设置连接参数
* @param $key
* @param $value
*/
public static function set(
$key,
$value){
if(
empty(
$key))
return;
if(!
is_array(self::
$DbConfig)) self::
$DbConfig =
array();
self::
$DbConfig[
$key] =
$value;
}
/**
* 读取连接参数
* @param $key
* @return null
*/
public static function get(
$key){
if(!
is_array(self::
$DbConfig) ||
empty(
$key) || !
array_key_exists(
$key,self::
$DbConfig))
return null;
return self::
$DbConfig[
$key];
}
/**
* 数据库连接参数检查
* @return bool
*/
public static function check(){
$host = self::get(‘host‘
);
if(
empty(
$host))
return false;
$port = self::get(‘port‘
);
if(!
is_numeric(
$port) ||
$port<0 ||
$port>65535)
return false;
$username = self::get(‘username‘
);
if(
empty(
$username))
return false;
$database = self::get(‘database‘
);
if(
empty(
$database))
return false;
$charset = self::get(‘charset‘
);
if(
empty(
$charset))
return false;
return true;
}
/**
* 连接初始化
* @param bool $reconnect
*/
public static function connect(
$reconnect=
false){
if(!
$reconnect &&
is_resource(self::
$DbLink))
return;
self::
close();
if(!self::check())
die(‘Database Configuration Error‘
);
$opt =
array(‘Database‘=>self::get(‘database‘),‘CharacterSet‘=>self::get(‘charset‘),‘UID‘=>self::get(‘username‘),‘ReturnDatesAsStrings‘=>
true,‘PWD‘=>self::get(‘password‘
));
self::
$DbLink = sqlsrv_connect(self::get(‘host‘).‘,‘.self::get(‘port‘),
$opt);
if(!
is_resource(self::
$DbLink)){
$err =
sqlsrv_errors();
die(‘Database Connection Error(‘.
$err[0][‘message‘].‘)‘
);
}
// 测试连接是否可用
/* $stmt = sqlsrv_query(self::$DbLink,‘select 1 num‘);
if(!is_resource($stmt)){
$err = sqlsrv_errors();
die(‘Database Query Error(‘.$err[0][‘message‘].‘)‘);
}
sqlsrv_free_stmt($stmt);*/
}
/**
* 查询SQL语句
* @param $sql
* @param array $params
* @return array|bool
*/
public static function executeQuery(
$sql,
$params=
array()){
self::
$SqlStr =
$sql;
self::
$ErrorMsg = ‘‘
;
if(
empty(
$sql))
return false;
self::
connect();
if(!
is_array(
$params)){
$params =
array();
}
$stmt = sqlsrv_query(self::
$DbLink,self::
$SqlStr,
$params);
if(!
is_resource(
$stmt)){
$err =
sqlsrv_errors();
self::
$ErrorMsg =
$err[0][‘message‘
];
return false;
}
$arr =
explode(‘;‘,
$sql);
if(
count(
$arr)>1
){
for(
$i=
count(
$arr);
$i>1;
$i--
){
sqlsrv_next_result($stmt);
}
}
$rows =
array();
while(
$row = sqlsrv_fetch_array(
$stmt,
SQLSRV_FETCH_ASSOC)){
$rows[] =
$row;
}
sqlsrv_free_stmt($stmt);
return $rows;
}
/**
* 查询SQL语句
* @param $sql
* @param array $params
* @return bool|int
*/
public static function executeCount(
$sql,
$params=
array()){
self::
$SqlStr =
$sql;
self::
$ErrorMsg = ‘‘
;
if(
empty(
$sql))
return false;
self::
connect();
if(!
is_array(
$params)){
$params =
array();
}
$stmt = sqlsrv_query(self::
$DbLink,self::
$SqlStr,
$params);
if(!
is_resource(
$stmt)){
$err =
sqlsrv_errors();
self::
$ErrorMsg =
$err[0][‘message‘
];
return false;
}
$row = sqlsrv_fetch_array(
$stmt,
SQLSRV_FETCH_NUMERIC);
$count = 0
;
if(
is_array(
$row) &&
count(
$row)==1
){
$count =
intval(
$row[0
]);
}
sqlsrv_free_stmt($stmt);
return $count;
}
/**
* 执行SQL语句
* @param $sql
* @param array $params
* @return bool|int
*/
public static function executeUpdate(
$sql,
$params=
array()){
self::
connect();
self::
$SqlStr =
$sql;
self::
$ErrorMsg = ‘‘
;
if(
empty(
$sql))
return false;
self::
connect();
if(!
is_array(
$params)){
$params =
array();
}
$stmt = sqlsrv_query(self::
$DbLink,self::
$SqlStr,
$params);
if(!
is_resource(
$stmt)){
$err =
sqlsrv_errors();
self::
$ErrorMsg =
$err[0][‘message‘
];
return false;
}
$num = sqlsrv_rows_affected(
$stmt);
sqlsrv_free_stmt($stmt);
return $num;
}
/**
* 返回服务器信息
* @return array
*/
public static function serverInfo(){
self::
connect();
return sqlsrv_server_info(self::
$DbLink);
}
/**
* 返回客户端信息
* @return array|null
*/
public static function clientInfo(){
self::
connect();
return sqlsrv_client_info(self::
$DbLink);
}
/**
* 构建表SQL
* @param $table
* @return string
*/
private static function tableSql(
$table){
if(
empty(
$table))
return ‘‘
;
if(
is_array(
$table)){
$arr =
array();
foreach(
$table as $k=>
$v){
$arr[] = ‘[‘.
$v.‘] ‘.
$k;
}
$tableSql =
implode(‘,‘,
$arr);
}else{
$tableSql = ‘[‘.
$table.‘]‘
;
}
return $tableSql;
}
/**
* 构建字段SQL
* @param $field
* @return string
*/
private static function fieldSql(
$field){
if(
empty(
$field))
return ‘*‘
;
if(
is_array(
$field)){
$fieldSql = ‘[‘.
implode(‘],[‘,
$field).‘]‘
;
}else{
$fieldSql =
$field;
}
return $fieldSql;
}
/**
* 构建条件SQL
* @param $where
* @return string
*/
private static function whereSql(
$where){
if(
empty(
$where))
return ‘‘
;
$whereSql = ‘ where ‘
;
if(
is_array(
$where)){
$whereSql .=
implode(‘ and ‘,
$where);
}else{
$whereSql .=
$where;
}
return $whereSql;
}
/**
* 构建排序SQL
* @param $order
* @return string
*/
private static function orderSql(
$order){
if(
empty(
$order))
return ‘‘
;
$orderSql = ‘ order by ‘
;
if(
is_array(
$order)){
$orderSql .=
implode(‘,‘,
$order);
}else{
$orderSql .=
$order;
}
return $orderSql;
}
/**
* 统计数据量
* @param $table
* @param $where
* @param null $values
* @return bool|int
*/
public static function count(
$table,
$where,
$values=
null){
if(
empty(
$table))
return false;
$tableSql = self::tableSql(
$table);
$whereSql = self::whereSql(
$where);
self::
$SqlStr = ‘select count(*) from ‘.
$tableSql.
$whereSql;
return self::executeCount(self::
$SqlStr,
$values);
}
/**
* 添加数据
* @param $table
* @param $data
* @return bool|int|string
*/
public static function add(
$table,
$data){
if(
empty(
$table) || !
is_array(
$data) ||
count(
$data)==0)
return false;
self::
connect();
$fields =
array();
$values =
array();
$places =
array();
foreach(
$data as $key=>
$value){
// 忽略以下划线开头的键
if(
stripos(
$key,‘_‘)===0)
continue;
$fields[] = ‘[‘.
$key.‘]‘
;
$values[] =
$value;
$places[] = ‘?‘
;
}
self::
$SqlStr = ‘insert into [‘.
$table.‘](‘.
implode(‘,‘,
$fields).‘)values(‘.
implode(‘,‘,
$places).‘)‘
;
$num = self::executeUpdate(self::
$SqlStr,
$values);
return $num>0
;
}
/**
* 插入数据并获取最后插入数据的ID(自增长数据)
* @param $table
* @param $data
* @return bool|int|string
*/
public static function autoIdAdd(
$table,
$data){
if(
empty(
$table) || !
is_array(
$data) ||
count(
$data)==0)
return false;
self::
connect();
$fields =
array();
$values =
array();
$places =
array();
foreach(
$data as $key=>
$value){
// 忽略以下划线开头的键
if(
stripos(
$key,‘_‘)===0)
continue;
$fields[] = ‘[‘.
$key.‘]‘
;
$values[] =
$value;
$places[] = ‘?‘
;
}
self::
$SqlStr = ‘insert into [‘.
$table.‘](‘.
implode(‘,‘,
$fields).‘)values(‘.
implode(‘,‘,
$places).‘);select top 1 SCOPE_IDENTITY() id‘
;
$rows = self::executeQuery(self::
$SqlStr,
$values);
if(!
is_array(
$rows) ||
count(
$rows)==0)
return false;
$id =
$rows[0][‘id‘
];
if(!
is_numeric(
$id))
return false;
return $id;
}
/**
* 更新数据
* @param $table
* @param $data
* @param $where
* @param null $value
* @return bool
*/
public static function update(
$table,
$data,
$where,
$value=
null){
if(
empty(
$table) ||
empty(
$where))
return false;
self::
connect();
$whereSql = self::whereSql(
$where);
$values =
array();
$places =
array();
foreach(
$data as $key=>
$v){
if(
stripos(
$key,‘_‘)===0)
continue;
$values[] =
$v;
$places[] = ‘[‘.
$key.‘]=?‘
;
}
foreach(
$value as $v){
$values[] =
$v;
}
self::
$SqlStr = ‘update [‘.
$table.‘] set ‘.
implode(‘,‘,
$places).
$whereSql;
$num = self::executeUpdate(self::
$SqlStr,
$values);
return $num>0
;
}
/**
* 删除数据
* @param $table
* @param $where
* @param null $values
* @return bool
*/
public static function delete(
$table,
$where,
$values=
null){
if(
empty(
$table) ||
empty(
$where))
return false;
self::
connect();
$tableSql = self::tableSql(
$table);
$whereSql = self::whereSql(
$where);
self::
$SqlStr = ‘delete from ‘.
$tableSql.
$whereSql;
$num = self::executeUpdate(self::
$SqlStr,
$values);
return $num>0
;
}
/**
* 查询数据(全部)
* @param $table
* @param $field
* @param $where
* @param $order
* @param null $values
* @return array|bool
*/
public static function fetchAll(
$table,
$field,
$where,
$order,
$values=
null){
if(
empty(
$table))
return false;
$tableSql = self::tableSql(
$table);
$fieldSql = self::fieldSql(
$field);
$whereSql = self::whereSql(
$where);
$orderSql = self::orderSql(
$order);
self::
$SqlStr = ‘select ‘.
$fieldSql.‘ from ‘.
$tableSql.
$whereSql.
$orderSql;
$rows = self::executeQuery(self::
$SqlStr,
$values);
return $rows;
}
/**
* 查询数据(分页)
* @param $table
* @param $page
* @param $size
* @param $field
* @param $where
* @param $order
* @param null $values
* @return array|bool
*/
public static function fetchPage(
$table,
$page,
$size,
$field,
$where,
$order,
$values=
null){
if(
empty(
$table))
return false;
$tableSql = self::tableSql(
$table);
$fieldSql = self::fieldSql(
$field);
$whereSql = self::whereSql(
$where);
$orderSql = self::orderSql(
$order);
$size =
intval(
$size);
if(
$size<=0)
$size = 10
;
$start = (
$page-1)*
$size;
if(
$start<0)
$start = 0
;
$end =
$start+
$size;
self::
$SqlStr = ‘select * from (select ROW_NUMBER()over(order by tempcolumn) temprownumber,* from (select top ‘.
$end.‘ tempcolumn=0,‘.
$fieldSql.‘ from ‘.
$tableSql.
$whereSql.
$orderSql.‘) temptable1) temptable2 where temprownumber>‘.
$start;
$rows = self::executeQuery(self::
$SqlStr,
$values);
return $rows;
}
/**
* 查询数据(单条)
* @param $table
* @param $field
* @param $where
* @param $order
* @param null $values
* @return bool
*/
public static function fetchOne(
$table,
$field,
$where,
$order,
$values=
null){
if(
empty(
$table))
return false;
$tableSql = self::tableSql(
$table);
$fieldSql = self::fieldSql(
$field);
$whereSql = self::whereSql(
$where);
$orderSql = self::orderSql(
$order);
self::
$SqlStr = ‘select top 1 ‘.
$fieldSql.‘ from ‘.
$tableSql.
$whereSql.
$orderSql;
$rows = self::executeQuery(self::
$SqlStr,
$values);
if(!
is_array(
$rows) ||
count(
$rows)!=1)
return false;
return $rows[0
];
}
/**
* 获取单个字段数据
* @param $table
* @param $name
* @param string $def
* @param null $where
* @param null $order
* @param null $values
* @return string
*/
public static function fetchField(
$table,
$name,
$def=‘‘,
$where=
null,
$order=
null,
$values=
null){
if(
empty(
$name))
return $def;
$data = self::fetchOne(
$table,
$name,
$where,
$order,
$values);
if(!
is_array(
$data))
return $def;
return $data[
$name];
}
/**
* 获取最后插入的数据ID
* @param $table
* @return int|string
*/
public static function lastId(
$table){
$sql = ‘select top 1 IDENT_CURRENT(\‘[‘.
$table.‘]\‘) id‘
;
$rows = self::executeQuery(
$sql);
if(!
is_array(
$rows) ||
count(
$rows)!=1)
return -1
;
$id =
$rows[0][‘id‘
];
if(!
is_numeric(
$id))
return -1
;
return $id;
}
/**
* 获取最后执行的SQL语句
* @return mixed
*/
public static function lastSql(){
return self::
$SqlStr;
}
/**
* 获取错误
* @return mixed
*/
public static function lastError(){
return self::
$ErrorMsg;
}
/**
* 获取连接字串
* @return mixed
*/
public static function getDb(){
return self::
$DbLink;
}
/**
* 关闭连接
*/
public static function close(){
if(
is_resource(self::
$DbLink)) sqlsrv_close(self::
$DbLink);
self::
$DbLink =
null;
}
}
?>
php 链接 sqlserver 2005以上版本数据库
标签：
