Php Mssql simple encapsulation supports stored procedures
- 2020-03-31 20:14:08
- OfStack
Core code:
<?php
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;
}
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 ...');
}
}
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;
}
}
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;
}
function getRowArray($sql) {
$res = $this->query($sql);
$r = mssql_fetch_row($res);
mssql_free_result($res);
return $r;
}
function getRowObject($sql) {
$res = $this->query($sql);
$r = mssql_fetch_assoc($res);
return $r;
}
function query($sql) {
$this->connect();
$res = mssql_query($sql, $this->link);
$this->close();
return $res;
}
function getResult($sql) {
$res = $this->query($sql);
while ($row = mssql_fetch_assoc($res)) {
$r[] = $row;
}
unset($row);
mssql_free_result($res);
return $r;
}
function executeSql($sql) {
return $this->query($sql);
}
function querySql($sql) {
$this->connect();
mssql_query($sql, $this->link);
$affected = mssql_rows_affected($this->link);
$this->close();
return $affected;
}
function close() {
mssql_close();
}
}
?>
Now let's talk about downscaling
function __autoload($MssqlUtil) {
require $MssqlUtil.'.php';
}
$db = new MssqlUtil($config['host'],$config['user'],$config['keys'],$config['base']);
The following is mainly about stored procedure calls with parameters
$pName Stored procedure name
$parName Parameter, parameter form is very important, is the array type, the corresponding relationship is
array('@a'=>'a') @a Is the parameter in the stored procedure, a Is the value to be passed
$sqlTyle Is the data type of the stored procedure parameter, is the array form, is also important
array(SQLCHAR,SQLVARCHAR), Be careful not to put single quotes and so on, because SQLVARCHAR is SQL Some of the constants of
Stored procedure with parameters
$db->executeProcedur($pName,$parName,$sqlTyle);
Parameterless stored procedures
$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);
Take the latest data for each category. Make a note here.
T1 is the category table and t2 is the master table