Php Mssql操作简单封装支持存储过程

Php Mssql操作简单封装支持存储过程_PHP教程

WBOY

Libérer： 2016-07-21 15:42:42

original

882 Les gens l'ont consulté

复制代码代码如下:

/* 
* class :Mssql 
* time :2009-12-10 
* author :Libaochang 
* version :1.0b 
* description :mssql database access class,it can execute the procedur or sql 
*/ 
class MssqlUtil 
{ 
var $user = null; //database user name 
var $keys = null; //database user password 
var $host = 'localhost'; //database host name/ip and port 
var $base = null; //database name 
var $link = null; //create link 
/** 
* construct function init all parmeters 
* @param  $host database host name/ip and port 
* @param  $user database user name 
* @param  $keys database user password 
* @param  $base database name 
*/ 
function __construct($host,$user,$keys,$base) 
{ 
$this->host = $host; 
$this->user = $user; 
$this->keys = $keys; 
$this->base = $base; 
} 
/** 
* create the connection 
*/ 
function connect() 
{ 
$this->link = mssql_connect($this->host,$this->user,$this->keys); 
if(!$this->link) 
{ 
die('connecting failed...check the module and setting...'); 
} 
$select = mssql_select_db($this->base,$this->link); 
if(!$select) 
{ 
die('data base is not exist...,please checke it ...'); 
} 
} 
/** 
* execute the procedur width the parameter 
* @param  $pName procedur name 
* @param  $parName parameters it's like this $par=array('@a'=>'a') 
* @param  $sqlTyle the procedur's parameter type, it's llike this $sqlType=array(SQLVARCHAR,SQLVARCHAR); and there is not the char single quote mark('). 
* @return  object array 
*/ 
function executeProcedur($pName,$parName,$sqlTyle) 
{ 
$this->connect(); 
$stmt = mssql_init($pName,$this->link); 
if(isset($parName)) 
{ 
$i = 0; 
foreach($parName as $par=>$value) 
{ 
mssql_bind($stmt,$par,$value,$sqlTyle[$i]); 
++$i; 
} 
$res = mssql_execute($stmt); 
$this->close(); 
while($row=mssql_fetch_assoc($res)) 
{ 
$r[] = $row; 
} 
unset($i); 
mssql_free_result($res); 
mssql_free_statement($stmt); 
return $r; 
} 
} 
/** 
* execute procedur without the parameter 
* @param  $pName Procedur Name 
* @return  object array 
*/ 
function executeProcedurNoPar($pName) 
{ 
$this->connect(); 
$stmt = mssql_init($pName,$this->link); 
$res = mssql_execute($stmt); 
$this->close(); 
while($row=mssql_fetch_assoc($res)) 
{ 
$r[] = $row; 
} 
mssql_free_result($res); 
mssql_free_statement($stmt); 
return $r; 
} 
/** 
* Get one row return Array 
* @param  $sql 
* @return  Array 
*/ 
function getRowArray($sql) 
{ 
$res = $this->query($sql); 
$r = mssql_fetch_row($res); 
mssql_free_result($res); 
return $r; 
} 
/** 
* Get one row return object 
* @param  $sql Sql 
* @return  Object 
*/ 
function getRowObject($sql) 
{ 
$res = $this->query($sql); 
$r = mssql_fetch_assoc($res); 
return $r; 
} 
/** 
* Execute one sql 
* @param  $sql Sql 
* @return  result 
*/ 
function query($sql) 
{ 
$this->connect(); 
$res = mssql_query($sql,$this->link); 
$this->close(); 
return $res; 
} 
/** 
* Get every row from result by Object, Return a Array with every element is Object 
* @param  $sql 
* @return  Object Array result 
*/ 
function getResult($sql) 
{ 
$res = $this->query($sql); 
while($row=mssql_fetch_assoc($res)) 
{ 
$r[] = $row; 
} 
unset($row); 
mssql_free_result($res); 
return $r; 
} 
/** 
* execute a sql 
* @param  $sql Sql 
*/ 
function executeSql($sql) 
{ 
return $this->query($sql); 
} 
/** 
* execute a sql statement 
* @param  $sql 
* @return  int $affected rows 
*/ 
function querySql($sql) 
{ 
$this->connect(); 
mssql_query($sql,$this->link); 
$affected = mssql_rows_affected($this->link); 
$this->close(); 
return $affected; 
} 
/** 
* close connection 
*/ 
function close() 
{ 
mssql_close(); 
} 
} 
?> 

下面说下调用

复制代码代码如下:

 
function __autoload($MssqlUtil) 
{ 
require $MssqlUtil.'.php'; 
} 
$db = new MssqlUtil($config['host'],$config['user'],$config['keys'],$config['base']); 

主要说下带参数的存储过程调用

复制代码代码如下:

 
$pName 存储过程名字 
$parName 参数，参数形式很重要，是数组类型，对应关系为 
array('@a'=>'a') @a 为存储过程里面的参数，a为要传递的值 
$sqlTyle 是存储过程参数的数据类型，是数组形式，也很重要 
array(SQLCHAR,SQLVARCHAR),注意不要加单引号等，因为SQLVARCHAR是SQL的一些常量 

带参数存储过程 
$db->executeProcedur($pName,$parName,$sqlTyle); 
无参数存储过程 
$db->executeProcedurNoPar($pName); 

select * from t2 where t2.id in(select max(t2.id) from t1 join t2 on t1.id = t2.pid group by t1.id);
取每个分类的最新一条数据。此处做个记录。
t1为类别表，t2为主表