需要php_serv.dll支持,需要到微软官网下载 支持sqlserver2008(包括)以上版本 sqlserver2005以下就不需要尝试了,分页函数用法不支持的. 类比较简单,就不做演示了 http://www.du52.com/text.php?id=582 无 ?php/** * 数据库管理 * * @author wangaibo168@163.co
需要php_serv.dll支持,需要到微软官网下载
支持sqlserver2008(包括)以上版本
sqlserver2005以下就不需要尝试了,分页函数用法不支持的.
类比较简单,就不做演示了
http://www.du52.com/text.php?id=582 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