当前位置:Gxlcms > PHP教程 > Php Mssql操作简单封装支持存储过程

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

时间:2021-07-01 10:21:17 帮助过:31人阅读

核心代码:

<?php
/*
 * 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 <type> $host database host name/ip and port
   * @param <type> $user database user name
   * @param <type> $keys database user password
   * @param <type> $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 <type> $pName procedur name
   * @param <type> $parName parameters it's like this $par=array('@a'=>'a')
   * @param <type> $sqlTyle the procedur's parameter type, it's llike this $sqlType=array(SQLVARCHAR,SQLVARCHAR); and there is not the char single quote mark(').
   * @return <type> 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 <type> $pName Procedur Name
   * @return <type> 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 <type> $sql
   * @return <type> 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 <type> $sql Sql
   * @return <type> Object
   */
  function getRowObject($sql) {
    $res = $this->query($sql);
    $r = mssql_fetch_assoc($res);
    return $r;
  }
  /** 
   * Execute one sql
   * @param <type> $sql Sql
   * @return <type> 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 <type> $sql
   * @return <type> 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 <type> $sql Sql
   */
  function executeSql($sql) {
    return $this->query($sql);
  }
  /** 
   * execute a sql statement
   * @param <type> $sql
   * @return <type> 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为主表

人气教程排行