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

核心代码：

/*

* 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为主表