There are not many functions, such as simple encapsulation and no caching. ezSQL is usually used, but ezSQL does not support stored procedures. Therefore, stored procedures are encapsulated here because of your own needs, just make a tag here.
The code is as follows:
/*
* 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 ('ing ING 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
* @ Return Object
*/
Function getRowObject ($ SQL)
{
$ Res = $ this-> query ($ SQL );
$ R = mssql_fetch_assoc ($ res );
Return $ r;
}
/**
* Execute one SQL
* @ Param $ 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
*/
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 ();
}
}
?>
Call
The code is as follows:
Function _ autoload ($ MssqlUtil)
{
Require $ MssqlUtil. '. php ';
}
$ Db = new MssqlUtil ($ config ['host'], $ config ['user'], $ config ['Keys '], $ config ['base']);
It mainly refers to the stored procedure call with parameters
The code is as follows:
$ PName stored procedure name
$ ParName parameter, which is of the array type and has the corresponding relationship
Array ('@ a' => 'A') @ a is the parameter in the stored procedure, and a is the value to be passed.
$ SqlTyle is the data type of stored procedure parameters. it is also important to use arrays.
Array (SQLCHAR, SQLVARCHAR). do not add single quotation marks, because SQLVARCHAR is a constant of SQL.
Stored procedure with parameters
$ Db-> executeProcedur ($ pName, $ parName, $ sqlTyle );
No parameter stored procedure
$ 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 );
Obtain the latest data of each category. Make a record here.
T1 is a category table, and t2 is the main table.