Phpmssql paging SQL statement optimization continues to affect. Copy the code as follows :? Php *** @ Filename: page. SQL. class. php * @ CreatTime: 2009-01-06 * @ Descrition: This class is the SQL statement processing class. * @ UpdateTime-1: null * @ Version: jswweb1
The code is as follows:
/**
* @ Filename: page. SQL. class. php
* @ CreatTime: 2009-01-06
* @ Descrition: This class is an SQL statement processing class.
* @ UpdateTime-1: null
* @ Version: jswweb1.0.0
* @ Author: fkedwgwy
* @ Dome:
$ SQL // SQL statement
$ Allcount // total number of records
$ Pagesize // number of records displayed on the page
$ Page // current page
$ Sqlc = new sqlpage ($ SQL, $ allcount, $ pagesize, $ page );
$ SQL = $ sqlc-> getsql ();
Optimized statement:
SELECT * FROM (select top 10 * FROM (select top 270 Lsh, Ztm, Dyzrsm, Dyzzfs, Cbsm, Cbny, Ssh, Fbsl, jcsl from ts_gcb where Ssh like 'c % 'order by Lsh asc) AS inner_tbl order by Lsh DESC) AS outer_tbl order by Lsh asc
*/
Class sqlpage {
Function sqlpage ($ SQL, $ allcount, $ pagesize, $ page ){
$ This-> SQL = $ SQL; // query language name
$ This-> allcount = intval ($ allcount); // The total number of records
$ This-> pagesize = intval ($ pagesize); // page size (number of records displayed)
$ This-> page = intval ($ page); // The current page
$ This-> getpage ();
$ This-> gettop ();
}
Function getpage () {// get the current page
$ This-> allpage = ceil ($ this-> allcount/$ this-> pagesize); // returns the maximum integer of the current decimal number.
If ($ this-> page = "" or $ this-> page> $ this-> allpage or $ this-> page <0 or $ this-> page = 0) {
$ This-> page2 = 1;
} Else {
$ This-> page2 = intval ($ this-> page); // Convert the page number to a number.
}
}
Function gettop () {// Obtain the TOP size of subquery 2
If ($ this-> page2 <$ this-> allpage ){
$ This-> top2 = $ this-> pagesize;
} Else {
$ This-> top2 = $ this-> allcount-$ this-> pagesize * ($ this-> allpage-1 );
}
}
/* Function getsql () {// Obtain the SQL statement
$ This-> s = preg_replace ("/select/I", "", $ this-> SQL );
$ This-> top1 = $ this-> pagesize * $ this-> page2;
$ This-> sql1 = "select top $ this-> top1 $ this-> s ";
If (strpos ($ this-> SQL, "asc") {// ascending
$ This-> SQL _e = "select * from (select TOP $ this-> top2 * FROM ($ this-> sql1) as aSysTable ORDER BY $ this-> order DESC) as bSysTable order by $ this-> order ASC ";
} Else
// $ This-> SQL _e = "select * from (select TOP $ this-> top2 * FROM ($ this-> sql1) as aSysTable ORDER BY $ this-> order DESC) as bSysTable order by $ this-> order ASC ";
If (strpos ($ this-> SQL, "desc") {// descending order
$ This-> SQL _e = "select * from (select TOP $ this-> top2 * FROM ($ this-> sql1) as aSysTable ORDER BY $ this-> order asc) as bSysTable order by $ this-> order desc ";
} Else {// not sorting
$ This-> SQL _e = "select * from (select TOP $ this-> top2 * FROM ($ this-> sql1) as aSysTable ORDER BY $ this-> order DESC) as bSysTable order by $ this-> order ASC ";
}
// Echo $ this-> SQL _e;
Return $ this-> SQL _e;
}*/
Function getsql ()
{
$ SQL = $ this-> SQL;
$ This-> top1 = $ this-> pagesize * $ this-> page2;
$ Orderby = stristr ($ SQL, 'Order ');
If ($ orderby! = False ){
$ Sort = (stripos ($ orderby, 'desc ')! = False )? 'Desc': 'asc ';
$ Order = str_ireplace ('Order by', '', $ orderby );
$ Order = trim (preg_replace ('/\ bASC \ B | \ bDESC \ B/I', '', $ order ));
}
$ SQL = preg_replace ('/^ SELECT \ s/I', 'Select top'. ($ this-> top1). '', $ SQL );
$ SQL = 'select * FROM (select top '. $ this-> top2.' * FROM ('. $ SQL.') AS inner_tbl ';
If ($ orderby! = False ){
$ SQL. = 'Order by'. $ ORDER .'';
$ SQL. = (stripos ($ sort, 'asc ')! = False )? 'Desc': 'asc ';
}
$ SQL. = ') AS outer_tbl ';
If ($ orderby! = False ){
$ SQL. = 'Order by'. $ ORDER. ''. $ sort;
}
Echo $ SQL;
Return $ SQL;
}
}
?>
The http://www.bkjia.com/PHPjc/319975.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/319975.htmlTechArticle code is as follows :? Php/*** @ Filename: page. SQL. class. php * @ CreatTime: 2009-01-06 * @ Descrition: This class is the SQL statement processing class. * @ UpdateTime-1: null * @ Version: jswweb1...