【PHP】SQL Server 数据库类（适用于win mssql扩展及freetds扩展）

<?php
if(!defined('IN_WOGU')) {
exit('Access Denied');
}

/**
 * SQL Server 数据库操作类,基于MSSQL扩展（或FreeTDS）
 *
 * Mssql类的默认配置
 * $default = array(
 *'servername'    => '',//服务器名
 *'username'      => '',//用户名
 *'password'      => '',//密码
 *'database'      => '',//数据库名
 *'table_pre'     => '',//表前辍
 *'new_link'      => false,//是否创建新的数据库连接
 *'autoconnect'   => true,//是否自动连接，为false时为lazy load模式
 *'debug'         => false,//是否启用debug
 *//其它数据库的(使用selectDB切换的)的表前辍映射表
 *'map'           => array(
 *//'dbname' => 'this db table pre'
 *)
 *)
 *
 * 使用Mssql
 * $db = new Mssql();
 * $db->open(array(
 *'servername' => 'KALLESPC\SQLEXPRESS',
 *'username'   => 'sa',
 *'password'   => 'phpfi',
 *'database'   => 'php',
 *'table_pre'  => 'table_pre',
 *'map'        => array(
 *'otherdb' => 'otherdbpre_'
 *)
 * ));
 * //注意：这里wogu_在内部将自动被处理成$this->_tablePre
 * $db->query('SELECT * FROM wogu_tablename WHERE id=1');
 * $r = $db->fetchArray();
 * print_r($r);
 *
 * @author wogu
 * @lastmodify 2011-11-9
 */

class Mssql
{
protected $_link;
protected $_config = array();
protected $_lastError = '';
protected $_lastqueryid = null;
protected $_querycount = 0;
protected $_tablePre;
protected $_database;

public function open(array $config) {
$default = array(
'servername'    => '',
'username'      => '',
'password'      => '',
'database'      => '',
'table_pre'     => '',
'new_link'      => false,
'autoconnect'   => true,
'debug'         => false,
'map'           => array()
);

$this->_config = array_merge($default, $config);
if($this->_config['autoconnect'] == true) {
return $this->_connect();
}
}

private function _connect() {
$this->_link = mssql_connect($this->_config['servername'], $this->_config['username'], $this->_config['password'], $this->_config['new_link']);

if(!$this->_link) {
return $this->_halt('Can\'t connect to SqlServer');
}

if(!empty($this->_config['database'])) {
$this->selectDB($this->_config['database']);
}

return $this->_link;
}

public function selectDB($database_name) {
$this->_database = $database_name;
$this->_tablePre = empty($this->_config['map'][$database_name]) ? $this->_config['table_pre'] : $this->_config['map'][$database_name];
return mssql_select_db($database_name, $this->_link);
}

private function _execute($sql) {
if(!is_resource($this->_link)) {
$this->_connect();
}

$sql = str_replace('wogu_', $this->_tablePre, $sql);
$this->_lastqueryid = mssql_query($sql, $this->_link);

if(!$this->_lastqueryid) {
return $this->_halt('SqlServer query error', $sql);
}

$this->_querycount++;
return $this->_lastqueryid;
}

public function query($query) {
return $this->_execute($query);
}

public function fetchArray($result_type = MSSQL_ASSOC) {
$res = mssql_fetch_array($this->_lastqueryid, $result_type);

if(!$res) {
$this->freeResult();
}

return $res;
}

public function freeResult() {
if(is_resource($this->_lastqueryid)) {
mssql_free_result($this->_lastqueryid);
$this->_lastqueryid = null;
}
}

public function affectedRows() {
return mssql_rows_affected($this->_lastqueryid);
}

/**
 * 默认返回一行，当$col>=0时返回该行的某一列
 * @param string $sql 查询语句
 * @param int $col 列号
 * @return mixed
 */
public function fetchOne($sql, $col = -1) {
$this->_execute($sql);
$res = $this->fetchArray();
$this->freeResult();

return $col >= 0 ? $res[$col] : $res;
}

public function insert($table, $data, $return_insert_id = false) {
if(!is_array( $data ) || $table == '' || count($data) == 0) {
return false;
}

$valuedata = array_values($data);
array_walk($valuedata, array($this, 'escape'));

$field = implode (',', array_keys($data));
$value = implode (',', $valuedata);

$sql = 'INSERT INTO ' . $this->config['database'] . '.' . $table . ' (' . $field . ') VALUES (' . $value . ')';
$return = $this->execute($sql);
return $return_insert_id ? $this->lastInsertId() : $return;
}

public function update($data, $table, $where = '') {
if($table == '' or $where == '') {
return false;
}

$where = ' WHERE '.$where;
$field = '';
if(is_string($data) && $data != '') {
$field = $data;
} elseif (is_array($data) && count($data) > 0) {
$fields = array();
foreach($data as $k=>$v) {
switch (substr($v, 0, 2)) {
case '+=':
$v = substr($v,2);
if (is_numeric($v)) {
$fields[] = $k.'='.$k.'+'.$v;
} else {
continue;
}

break;
case '-=':
$v = substr($v,2);
if (is_numeric($v)) {
$fields[] = $k.'='.$k.'-'.$v;
} else {
continue;
}
break;
default:
$fields[] = $k.'='.$this->escape($v);
}
}
$field = implode(',', $fields);
} else {
return false;
}

$sql = 'UPDATE wogu_'.$table.' SET '.$field.$where;
return $this->_execute($sql);
}

/**
 * 只考虑数字和字符串的情形，bool,object等类型本身就是错误的
 * @param mix $value 待处理变量
 * @return mix
 */
public function escape(&$value, $key = '') {
if(is_string($value)) {
$value = '\'' . $value . '\'';
}

return $value;
}

public function lastInsertId() {
    $sql = 'SELECT SCOPE_IDENTITY()';
    return (int)$this->fetchOne($sql, 0);
}

public function set_debug($flag) {
$this->_config['debug'] = $flag;
}

protected function _halt($message = '', $sql = '') {
$this->_lastError = mssql_get_last_message();

if($this->_config['debug']) {
$errormsg = "<b>Message : </b> $message<br><b>Mssql Query : </b> $sql <br /><b> Mssql Error : </b>" . $this->_lastError;
echo '<div style="font-size:12px;text-align:left; border:1px solid #9cc9e0; padding:1px 4px;color:#000000;font-family:Arial, Helvetica,sans-serif;"><span>' . $errormsg . '</span></div>';
exit;
}

return false;
}

public function getLastMessage() {
return $this->_lastError;
}

public function getLink() {
return $this->_link;
}

public function getQueryCount() {
return $this->_querycount;
}
}