class MsSql{
private $Host; //连接数据库服务器
private $Database;//连接数据库主机名
private $UID; //连接数据库用户名
private $PWD; //连接数据库密码
/*析构函数完成数据成员的初始化*/
function __construct($Host='',$Database='',$UID='',$PWD=''){
$this->Host=$Host;
$this->Database=$Database;
$this->UID=$UID;
$this->PWD=$PWD;
}
/*连接数据库*/
public function conn(){
$Host=$this->Host;
$db_info=array("Database"=>$this->Database,"UID"=>$this->UID,"PWD"=>$this->PWD);
if ($link=sqlsrv_connect($Host,$db_info))
{
return $link;
}else {
//print_r($Host);
//print_r($db_info);exit;
$this->err('数据库连接错误');
}
}
/*执行SQL查询*/
public function fun_select($tables,$fields='*',$where=NULL,$group=NULL,$having=NULL,$order=NULL){
$sql='SELECT '.$fields.' FROM '.$tables
.$this->parseWhere($where)
.$this->parseGroup($group)
.$this->parseHaving($having)
.$this->parseOrder($order);
$query=sqlsrv_querey($sql);
$dataAll=sqlsrv_fetch_array($query);
//echo $dataAll[1];
return $dataAll;
}
/*添加记录操作*/
public function fun_add($tables,$dataArray){
if (is_array($dataArray)&&!empty($tables)){
$keys=array_keys($dataArray);//将$dataArray[]的索引取出组成一个数组
$keys=join(',', $keys);
$values=array_values($dataArray);//将$dataArray[]的数值取出组成一个数组
$values=join('','', $values);
$values="'".$values."'";
$sql="INSERT {$tables}({$keys}) VALUES({$values})";
var_dump($sql);exit;
return $this->execute($sql);
}else {
$this->err('没有要插入的数据');
return false;
}
}
/*更新记录操作*/
public function fun_update($tables,$data,$where,$order=null){
if (is_array($data)&&!empty($where)){
foreach ($data as $key=>$val){
$sets.=$key."='".$val."',";
}
$sets=rtrim($sets,',');
$sql="UPDATE {$tables} SET {$sets} ".$this->parseWhere($where).$this->parseOrder($order);
return $this->execute($sql);
}else {
return false;
}
}
/*删除记录操作*/
public function fun_delete($tables,$where,$order=NULL){
if (is_string($where)&&!empty($where)){
$sql="DELETE FROM {$tables}".$this->parseWhere($where).$this->parseOrder($order);
return $this->execute($sql);
}
return false;
}
/*解析WHERE条件*/
public function parseWhere($where){
$whereStr='';
if (is_string($where)&&!empty($where)){
$whereStr=$where;
}
return empty($whereStr)?'':' WHERE '.$whereStr;
}
/*解析GROUPBY条件*/
public function parseGroup($group) {
$groupStr = '';
if (is_array ( $group )) { //判断$group是否为数组，并进行解析
$groupStr .= ' GROUP BY ' . implode ( ',', $group );
}
elseif (is_string ( $group ) && ! empty ( $group )) { //判断$group是否为字符串，并进行解析
$groupStr .= ' GROUP BY ' . $group;
}
return empty( $groupStr ) ? '' : $groupStr;
}
/*解析Having by子句,对结果进行二次筛选*/
public function parseHaving($having){
$havingStr='';
if (is_string($having)&&!empty($having)){
$havingStr.=' HAVING '.$having;
}
return $havingStr;
}
/*解析order by 子名*/
public function parseOrder($order){
$orderStr='';
if (is_array($order)){
$orderStr.=' ORDER BY '.join(',', $order).'';
}elseif (is_string($order)&&!empty($order)){
$orderStr.=' ORDER BY '.$order.'';
}
return $orderStr;
}
/*将SQL语句进行预处理*/
public function execute($sql = NULL) {
$link=$this->conn();
if (!$link) {
return false;
}
if (!empty ($sql)){
$queryStr =sqlsrv_prepare($link,$sql);
$res =sqlsrv_execute ( $queryStr );
return $res;
}
}
/*错误信息*/
public function err($msg) {
if (!empty($msg)){
echo "<script>alert('".$msg."')</script>";
die ();
}
}
}
