<?php
/**
* 数据库管理
*
* @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;
} }
?>