欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-06 22:24:39
核心代码:

核心代码:

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