Mssql + php database operations

Mssql + operation class classDbQueryForMssql {& nbsp; ** & nbsp; * Maximum number of records returned by the select Method & nbsp; * & nbsp; constMAX_ROW_NUM100000; & nbsp; ** & nbsp; * Data Query Result set object & nbsp; * @ varobject $ dataSet & nbsp; * & nbsp; p mssql + operation class

Class DbQueryForMssql {
/**
* Maximum number of records returned by the select Method
*/
Const max_row_num= 100000;

/**
* Data Query Result set object
* @ Var object $ dataSet
*/
Public $ dataSet = NULL;

/**
* Data source object
* @ Var object $ ds
*/
Public $ ds = NULL;

/**
* The queried SQL statement
* @ Var string $ SQL
*/
Public $ SQL = '';

Public $ transCnt = 0;

/**
* Query mode. The value is OCI_COMMIT_ON_SUCCESS or OCI_DEFAULT.
* @ Var string $ excuteMode
*/
Public $ executeMode = OCI_COMMIT_ON_SUCCESS;

/**
* Constructor
* @ Param object $ ds Database
* @ Param string $ SQL the SQL statement to be initialized and queried
*/
Function _ construct ($ ds = NULL, $ SQL = NULL ){
If (! $ Ds ){
$ This-> error (DbException: DB_UNCONNECTED, 'database also

Not connected. ');
} Else {
$ This-> ds = $ ds;
If ($ SQL ){
$ This-> open ($ SQL );
}
}
}

/**
* Release the memory occupied
* @ Param object $ dataSet: result set of the resource to be released
* @ Access public
*/
Public function close ($ dataSet = NULL ){
If ($ dataSet ){
@ Mssql_free_statement ($ dataSet );
} Else {
@ Mssql_free_statement ($ this-> dataSet );
$ This-> eof = false;
$ This-> recordCount = 0;
$ This-> recNo =-1;
}
}
Function _ destruct ()
{
@ Mssql_free_result ($ this-> dataSet );
@ Mssql_free_statement ($ this-> dataSet );
@ Mssql_close ($ this-> ds-> connect );
}
/**
* Encrypt the $ pass database and return the encrypted value.
* @ Param string $ pass the string to be encrypted
* @ Return string
* @ Access public
*/
Public function encodePassword ($ pass ){
Return md5 ($ pass );
}

/**
* Get the error message and error code
* @ Param integer $ queryResult Query Result
* @ Return array
* @ Access protected
*/
Protected function errorInfo ($ queryResult = NULL ){
$ Result ['message'] = mssql_get_last_message ();
@ Mssql_select_db ($ this-> ds-> name, $ this-> ds-> connect );
/* If (_ select_db ($ this-> ds-> name ">! @ Mysql_select_db ($ this-> ds-> name )){
Throw new DbException ('database does not exist ',

DbException: DB_OPEN_FAILED );
}*/
$ Id = @ mssql_query ("select @ ERROR", $ this-> ds-> connect );
If (! $ Id ){
Return false;
}
$ Arr = mssql_fetch_array ($ id );
@ Mssql_free_result ($ id );
If (is_array ($ arr )){
$ Result ['code'] = $ arr [0];
} Else {
Return $ result ['code'] =-1;
}
Return $ result;
}

/**
* Handle error messages
* @ Param string $ errorId error ID
* @ Param string $ error message
* @ Access protected
*/
Protected function error ($ errorId, $ errorMessage ){
Throw new DbException ($ errorMessage, $ errorId );
}

/**
* Execute SQL statements
* @ Param string $ SQL SQL statement
* @ Return object
* @ Param int $ rowFrom start row number, which starts from 1
* @ Param int $ rowTo end row number. The value 0 indicates
* @ Access public
* @ See DbQuery: open
*/
Public function execute ($ SQL = '', $ rowFrom = 0, $ rowTo =

Self: MAX_ROW_NUM, $ error = true ){
// Echo $ this-> ds-> name;
If ($ rowTo! = Self: MAX_ROW_NUM ){
$ Nrows = $ rowTo-$ rowFrom + 1;
}
$ Offset = $ rowFrom;
If ($ nrows> 0 ){
$ Nn = $ nrows + $ offset-1;
$ SQL = preg_replace ('/(^ s * selects +

(Distinctrow | distinct )?) /I ',' \ 1 top '. $ nn. '', $ SQL );
}

@ Mssql_select_db ($ this-> ds-> name, $ this-> ds-> connect );
/* If ()){
Throw new DbException ('database does not exist ',

DbException: DB_OPEN_FAILED );
}*/
$ DataSet = @ mssql_query ($ SQL, $ this-> ds-> connect );
// Echo $ SQL .'



';
If (! $ DataSet & $ error ){
$ SqlError = $ this-> errorInfo ();
$ ErrorMessage = 'Run ['.

$ SQL
.'] Error!

Color = # FF0000> ['
. $ SqlError ['code']. ']:'
. $ SqlError ['message']. '';
$ This-> error (DbException: DB_QUERY_ERROR,

$ ErrorMessage );
}

If ($ offset ){
$ Offset = $ offset-1; // var_dump ($ dataSet); echo 'abc ';
$ ResultNum = mssql_num_rows ($ dataSet );
If ($ resultNum <$ offset ){
@ Mssql_data_seek ($ dataSet, $ resultNum-1 );
} Else {
@ Mssql_data_seek ($ dataSet, $ offset );
}
}
Return $ dataSet;
}

/**
* Execute the SQL statement and save the result set to the attribute $ dataSet.
* @ Param string $ SQL SQL statement
* @ Param int $ rowFrom start row number, which starts from 1
* @ Param int $ rowTo end row number. The value 0 indicates
* @ Return object
* @ Access public
* @ See DbQuery: execute
*/
Public function open ($ SQL = '', $ rowFrom = 0, $ rowTo =

Self: MAX_ROW_NUM ){
$ This-> dataSet = $ this-> execute ($ SQL, $ rowFrom, $ rowTo );
$ This-> SQL = $ SQL;
Return $ this-> dataSet;
}

/**
* Split the field values of a row into an array.
* @ Param object $ dataSet result set
* @ Param integer $ resultType: return type, OCI_ASSOC, OCI_NUM, or

OCI_BOTH
* @ Return array
*/
Public function fetchRecord ($ dataSet = NULL, $ resultType = MSSQL_BOTH ){
$ Result = @ mssql_fetch_array ($ dataSet )? $ DataSet: $ this-

> DataSet, $ resultType );
If (is_array ($ result )){
Foreach ($ result as $ key => $ value ){
If (! Is_numeric ($ key )){
$ Result [strtolower ($ key)] = $ value;
}
}
}
Return $ result;
}

/**
* Obtain the number of fields
* @ Param object $ dataSet result set
* @ Return integer
*/
Public function getFieldCount ($ dataSet = NULL ){

Return mssql_num_fields ($ dataSet )? $ DataSet: $ this-

> DataSet );
}

/**
* Obtain the next record. Returns the record number. If it reaches the end of the record, FALSE is returned.
* @ Return integer
* @ Access public
* @ See getPrior ()
*/
Public function next (){
Return $ this-> fetchRecord ();
}

/**
* Obtain the current database time in the format of yyyy-mm-dd hh: mm: ss.
* @ Return string
* @ Access public
*/
Public function getNow (){
Return $ this-> getValue ('select TO_CHAR (SYSDATE, 'yyyy-MM-DD

HH24: MI: ss') dateOfNow FROM dual ');
}

/**
* Retrieve data from the data table according to the SQL statement. Only the value of the first record is obtained,
* If the record contains only one field, only the field value is returned.
* If not found, FALSE is returned.
*
* @ Param string $ SQL SQL statement
* @ Return array
* @ Access public
*/
Public function getValue ($ SQL = '', $ dataFormat = MSSQL_BOTH ){
$ DataSet = $ this-> execute ($ SQL, 1, 1 );

If ($ result = $ this-> fetchRecord ($ dataSet, $ dataFormat )){
$ FieldCount = $ this-> getFieldCount ($ dataSet );
$ Idx = 0;
If ($ dataFormat = MSSQL_ASSOC) {// if you use

When MSSQL_ASSOC has only one column, you need to know the column name of the first column.
$ FirstColumnInfo = mssql_fetch_field

($ DataSet, 0 );
$ Idx = $ firstColumnInfo-> name; // column name
}
$ This-> close ($ dataSet); // print_r ($ result );
Return ($ fieldCount <= 1 )? $ Result [$ idx]: $ result;
} Else {
Return false;
}
}

/**
* Auto-increment value of ID
*
* @ Return int
* @ Access public
*/
Public function getInsertId (){
Return $ this-> getValue ('select @ identity ');
}

/**
* Sequence Extraction
* @ Param $ seq sequence name
* @ Return int
* @ Access public
*/
Public function getSeq ($ seq = ''){
$ This-> execute ('in in TRANSACTION adodbseq ');
$ OK = $ this-> execute ("update $ seq with (tablock, holdlock)

Set id = id + 1 ", 0, self: MAX_ROW_NUM, false );
If (! $ OK ){
$ This-> execute ("create table $ seq (id float (53 ))");
$ OK = $ this-> execute ("insert into $ seq

(Tablock, holdlock) values (1) ", 0, self: MAX_ROW_NUM, false );
If (! $ OK ){
$ This-> execute ('rollback TRANSACTION

Adodbseq ');
Return false;
}
$ This-> execute ('commit TRANSACTION adodbseq ');
Return 1;
}
$ Num = $ this-> getValue ("select id from $ seq ");
$ This-> execute ('commit TRANSACTION adodbseq ');
Return $ num;
}
/**
* Returns true if the table exists.
* @ Param string $ tableName name of the table to be queried
* @ Return bool
* @ Access public
*/
Public function tableIsExists ($ tableName ){
Return false;
}

/**
* Start transaction
* @ Access public
*/
Public function begin (){
$ This-> transCnt + = 1;
$ This-> execute ('in in TRAN ');
Return true;
}

/**
* Submit a transaction.
* @ Access public
*/
Public function commit (){
If ($ this-> transCnt ){
$ This-> transCnt-= 1;
}
$ This-> execute ('commit TRAN ');
Return true;
}

/**
* Roll back a transaction
* @ Access public
*/
Public function rollback (){
If ($ this-> transCnt ){
$ This-> transCnt-= 1;
}
$ This-> execute ('rollback TRAN ');
Return true;
}

/**
* Insert a record
* @ Param string $ tableName table name
* @ Param array $ fieldArray field array
* @ Param string $ whereForUnique uniqueness Condition
* @ Return int
* @ Access public
*/
Public function insert ($ tableName, $ fieldArray, $ whereForUnique =

NULL ){
If (! $ TableName |! $ FieldArray |! Is_array ($ fieldArray )){
Throw new Exception ('parameter $ tableName or $ fieldArray

The value of is invalid! ');
}
If ($ whereForUnique ){
$ Where = 'where'. $ whereForUnique;
$ IsExisted = $ this-> getValue ('select COUNT (*) from'

. $ TableName. $ where );
If ($ isExisted ){
Throw new DbException ('record already exists! ',

DbException: DB_RECORD_IS_EXISTED );
}
}
$ FieldNameList = array ();
$ FieldValueList = array ();
Foreach ($ fieldArray as $ fieldName => $ fieldValue ){
If (! Is_int ($ fieldName )){
$ FieldNameList [] = $ fieldName;
$ FieldValueList [] = '''. $ fieldValue.

''';
}
}
$ FieldName = implode (',', $ fieldNameList );
$ FieldValue = implode (',', $ fieldValueList );
$ SQL = 'insert'. $ tableName .'('
. $ FieldName. ') VALUES ('.

$ FieldValue .')';
// Return $ SQL;
Return $ this-> execute ($ SQL );
}

/**
* Update a record
* @ Param string $ tableName table name
* @ Param array $ fieldArray field array
* @ Param string $ whereForUpdate query Condition
* @ Param string $ whereForUnique uniqueness Condition
* @ Return int
* @ Access public
*/
Public function update ($ tableName, $ fieldArray,

$ WhereForUpdate = NULL, $ whereForUnique = NULL ){
If (! $ TableName |! $ FieldArray |! Is_array ($ fieldArray )){
Throw new Exception ('parameter $ tableName or $ fieldArray

The value of is invalid! ');
}
If ($ whereForUnique ){
$ Where = 'where'. $ whereForUnique;
$ IsExisted = $ this-> getValue ('select COUNT (*) from'

. $ TableName. $ where );
If ($ isExisted ){
Throw new DbException ('record already exists! ',

DbException: DB_RECORD_IS_EXISTED );
}
}
$ FieldNameValueList = array ();
Foreach ($ fieldArray as $ fieldName => $ fieldValue ){
If (! Is_int ($ fieldName )){
$ FieldNameValueList [] = $ fieldName. '= ''.

$ FieldValue .''';
}
}
$ FieldNameValue = implode (',', $ fieldNameValueList );
If ($ whereForUpdate ){
$ WhereForUpdate = 'where'. $ whereForUpdate;
}
$ SQL = 'update'. $ tableName
. 'Set'. $ fieldNameValue.

$ WhereForUpdate;
Return $ this-> execute ($ SQL );
// Return $ SQL;
}

/**
* Select a record
* @ Param string $ SQL statement
* @ Param string $ dataFormat returns the data format, Value

"Array", "hashmap", "hashmap_str", "dataset"
* @ Param int $ rowFrom start row number, which starts from 1
* @ Param int $ rowTo end row number. The value 0 indicates
* @ Result array
* @ Access public
*/
Public function select ($ SQL, $ dataFormat = 'array', $ rowFrom = 0,

$ RowTo = self: MAX_ROW_NUM ){
$ DataSet = $ this-> execute ($ SQL, $ rowFrom, $ rowTo );
Switch ($ dataFormat ){
Case 'array': // array
$ Result = array ();
$ IsMultiField = ($ this-> getFieldCount ($ dataSet)>

1 );
$ I = 0;
While ($ data = $ this-> fetchRecord ($ dataSet )){
$ Result [$ I] = ($ isMultiField )? $ Data:

$ Data [0];
$ I ++;
}
$ This-> close ($ dataSet );
Break;

Case 'arrayassoc ': // array, with a BUG. The column name must be indexed! If only

There is a column sometimes
$ Result = array ();
$ IsMultiField = ($ this-> getFieldCount ($ dataSet)>

1 );
$ I = 0;
While ($ data = $ this-> fetchRecord

($ DataSet, MSSQL_ASSOC )){
$ Idx = 0;
If (! $ IsMultiField) {// if there is only one column
If ($ dataFormat = MSSQL_ASSOC) {// use

When MSSQL_ASSOC has only one column, you need to know the column name of the first column.
$ FirstColumnInfo = mssql_fetch_field

($ DataSet, 0 );
$ Idx = $ firstColumnInfo-

> Name; // column name
}
}
$ Result [$ I] = ($ isMultiField )? $ Data:

$ Data [$ idx];
$ I ++;
}
$ This-> close ($ dataSet );
Break;

Case 'hashmap': // hash
$ Result = array ();
While ($ data = $ this-> fetchRecord ($ dataSet )){
$ Result [$ data [0] = $ data [1];
}
$ This-> close ($ dataSet );
Break;

Case 'hashmap _ str': // hash string
$ Result = array ();
While ($ data = $ this-> fetchRecord ($ dataSet,

OCI_NUM )){
$ Result [] = $ data [0]. '='. $ data [1];
}
$ Result = implode ('|', $ result );
$ This-> close ($ dataSet );
Break;

Default: // dataset. When the returned data format is dataset, select

The function of the method is the same as that of the execute method.
$ Result = $ dataSet;
}
Return $ result;
}

/**
* Returns the maximum value.
* @ Param string $ tableName table name
* @ Param string $ idField field name
* @ Param string $ where query Condition
* @ Return int
* @ Access public
*/
Public function getMax ($ tableName, $ idField, $ where = NULL ){
$ Where = ($ where )? ('Where'. $ WHERE ):'';
Return $ this-> getValue ('select MAX ('. $ idField.') from'

. $ TableName. $ where );
}
}