Phpmysql database operation paging

Source: Internet
Author: User
If you have added new features or made improvements, please share with us. The code is as follows:


/*
* Mysql database paging
* @ Package pagelist
* @ Author yytcpt (shadow)
* @ Version 2008-03-27
* @ Copyrigth http://www.d5s.cn/
*/
/*
* Paging style
. Page {float: left; font: 11px Arial, Helvetica, sans-serif; padding: 6px 0; margin: 0px 10%; margin-top: 10px ;}
. Page a,. page strong {padding: 2px 6px; border: solid 1px # ddd; background: # fff; text-decoration: none ;}
. Page a: visited {padding: 2px 6px; border: solid 1px # ddd; background: # fff; text-decoration: none ;}
. Page. break {padding: 2px 6px; border: none; background: # fff; text-decoration: none ;}
. Page strong {padding: 2px 6px; border-color: #999; font-weight: bold; font-size: 13px; vertical-align: top; background: # fff ;}
. Page a: hover {color: # fff; background: # 0063DC; border-color: #036; text-decoration: none ;}
. Page a: hover p {color: # FFF; cursor: pointer! Important; cursor: hand ;}
*/
Class pagelist {
Var $ page; // page number
Var $ SQL; // paging SQL
Var $ img_path; // Icon path
Var $ img; // Icon name
Var $ img_btn; // Icon address
Var $ page_size; // you can specify the number of entries displayed on each page.
Var $ num_btn; // sets the number of displayed numeric pages.
Var $ total_pages; // total number of pages
Var $ total_records; // total number of records
Var $ url;
Var $ table;
Var $ new_ SQL; // The specified SQL statement
Var $ db;
Function _ construct (){
Global $ db;
$ This-> db = $ db;
$ Tmp_page = intval (trim ($ _ GET ["page"]);
$ This-> page = empty ($ tmp_page )? 1: $ tmp_page;
$ This-> set_table ();
$ This-> page_size = 20;
$ This-> num_btn = 9;
$ This-> img_path = '/images /';
$ This-> img = array ("ico_first.gif", "ico_front.gif", "ico_next.gif", "ico_last.gif ");
}
Function set_table (){
$ This-> table ["tablename"] = "";
$ This-> table ["id"] = "id ";
$ This-> table ["orderby"] = $ this-> table ["id"];
$ This-> table ["descasc"] = "DESC ";
$ This-> table ["fileds"] = "*";
$ This-> table ["where"] = "";
}
Function set_img (){
$ This-> img_btn [0] = "img_path. $ this-> img [0]. "'alt = 'homepage 'border = '0' align = 'absmiddle'/> ";
$ This-> img_btn [1] = "img_path. $ this-> img [1]. "'alt = 'previous page 'border = '0' align = 'absmiddle'/> ";
$ This-> img_btn [2] = "img_path. $ this-> img [2]. "'alt = 'next page' border = '0' align = 'absmiddle'/> ";
$ This-> img_btn [3] = "img_path. $ this-> img [3]. "'alt = 'Last page' border = '0' align = 'absmiddle'/> ";
}
Function set_show_page (){
$ This-> set_img (); // sets the page turning Image path.
$ This-> set_url ();
$ This-> set_total_records ();
If ($ this-> total_records <$ this-> page_size ){
$ This-> total_pages = 1;
} Else {
$ This-> total_pages = ceil ($ this-> total_records/$ this-> page_size );
}
If ($ this-> page> $ this-> total_pages ){
$ This-> page = $ this-> total_pages;
}
}
Function show_first_prv (){
If ($ this-> page = 1 ){
$ Str ="". $ This-> img_btn [0]." ". $ This-> img_btn [1]."";
} Else {
$ Str = "url." 1 "." '> ". $ this-> img_btn [0]." "; // here 1 is the homepage, and the page value is 1
$ Str. = "url. ($ this-> page-1)." '> ". $ this-> img_btn [1]." ";
}
Return $ str;
}
Function show_next_last (){
If ($ this-> page >=$ this-> total_pages ){
$ Str ="". $ This-> img_btn [2]." ". $ This-> img_btn [3]."";
} Else {
$ Str = "url. ($ this-> page + 1)." '> ". $ this-> img_btn [2]." ";
$ Str. = "url. $ this-> total_pages." '> ". $ this-> img_btn [3]." ";
}
Return $ str;
}
Function show_num_text (){
$ Str = "go toPage ";
$ Str. = "url. "'+ document. getElementById ('go _ num_text '). value; \ "style = 'font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: 14px; '> [Go]";
Return $ str;
}
Function show_num_select (){
If ($ this-> total_pages <50 ){
$ Str ="";For ($ I = 1; $ I <= $ this-> total_pages; $ I ++ ){$ Str. ="Page = $ I? "Selected = 'selected'": ""). ">". $ I ."";}$ Str. ="";
} Else {
$ Str = "";
}
Return $ str;
}
Function show_num_btn (){
If ($ this-> page >=1 and $ this-> page <= $ this-> total_pages ){
$ Tmp_p = ($ this-> num_btn-1)/2;
If ($ this-> page-$ tmp_p) <= 0 ){
$ Start_p = 1;
} Else {
If ($ this-> page-$ tmp_p)> $ this-> num_btn and ($ this-> page-$ tmp_p)> ($ this-> total_pages-$ this-> num_btn + 1 )){
$ Start_p = $ this-> total_pages-$ this-> num_btn + 1;
} Else {
$ Start_p = $ this-> page-$ tmp_p;
}
}
If ($ this-> page + $ tmp_p) <$ this-> total_pages ){
$ End_p = ($ this-> page + $ tmp_p) <$ this-> num_btn? $ This-> num_btn :( $ this-> page + $ tmp_p );
If ($ end_p> $ this-> total_pages ){
$ End_p = $ this-> total_pages;
}
} Else {
$ End_p = $ this-> total_pages;
}
}
$ Str = "";
For ($ I = $ start_p; $ I <= $ end_p; $ I ++ ){
If ($ I ==$ this-> page ){
$ Str. ="". $ I ."";
} Else {
$ Str. = "url. $ I." '> ". $ I ."";
}
}
Return $ str;
}
Function show_page_info (){
$ Str = "total". $ this-> total_records. "/". $ this-> total_pages. "page ";
Return $ str;
}
Function show_page (){
If ($ this-> total_records <1 ){
$ This-> set_show_page ();
}
$ Str = $ this-> total_pages> 1? $ This-> show_first_prv (). $ this-> show_num_btn (). $ this-> show_next_last (). $ this-> show_page_info (). $ this-> show_num_text ():"";
Return $ str;
}
// Total number of pages
Function set_total_pages (){
$ This-> total_pages = ceil ($ this-> total_records/$ this-> page_size );
}
// Total number of records
Function set_total_records (){
If ($ this-> total_records = 0 or! Isset ($ this-> total_records )){
If (empty ($ this-> count_ SQL) and! Empty ($ this-> table ["tablename"]) {
$ SQL = "SELECT count (". $ this-> table ["id"]. ") as count_id FROM '". $ this-> table ["tablename"]. "'". ($ this-> table ["where"]! = ""? "WHERE". $ this-> table ["where"]: "");
} Else {
$ SQL = preg_replace ("/SELECT (.*?) FROM (.*?) /I "," SELECT count (id) AS count_id FROM \ 2 ", $ this-> SQL );
}
$ Arr = $ this-> db-> row_query_one ($ SQL );
$ This-> total_records = $ arr ["count_id"];
}
}
/*
* Return query data based on SQL
* When specifying $ SQL, you do not need to specify limit.
*/
Function get_rows_by_ SQL ($ SQL ){
$ This-> SQL = $ SQL. "LIMIT ". $ this-> page_size * ($ this-> page-1 ). ",". $ this-> page_size; // The specified SQL statement;
Return $ this-> db-> row_query ($ this-> SQL );
}
/*
* The most common paging method requires only three parameters.
* $ Tablename table name, $ where query condition, $ orderby sorting field (IDs are sorted in reverse order by default)
*/
Function get_rows ($ tablename, $ where = "", $ orderby = ""){
$ This-> table ["tablename"] = $ tablename;
$ This-> table ["where"] = $ where;
$ Orderby? $ This-> table ["orderby"] = $ orderby :"";
$ Arr = array (
"Page" => $ this-> show_page (), // paging code
"Rows" => $ this-> get_rows_by_ SQL (), // number of records
"Sum" => $ this-> total_records, // The total number of records
);
Return $ arr;
}
/*
* Special query: $ SQL _query: SQL statement, $ row_count: total number of statistics
*/
Function get_rows_ SQL ($ SQL _query, $ row_count = 0 ){
$ This-> total_records = $ row_count;
$ Arr ["rows"] = $ this-> get_rows_by_ SQL ($ SQL _query );
$ Arr ["page"] = $ this-> show_page ();
$ Arr ["sum"] = $ this-> total_records;
Return $ arr;
}
Function get_ SQL (){
If ($ this-& gt; total_records & gt; 10000 ){
$ This-> SQL = "SELECT ". $ this-> table ["fileds"]. "FROM '". $ this-> table ["tablename"]. "'". ($ this-> table ["where"]! = ""? "WHERE ". $ this-> table ["where"]. AND '. $ this-> table ["id"]. '> =': 'Where '. $ this-> table ["id"]. '> = '). '(SELECT '. $ this-> table ["id"]. 'from ''. $ this-> table ["tablename"]. ''Order '. $ this-> table ["id"]. 'limit '. $ this-> page_size * ($ this-> page-1 ). ', 1 )'. "order ". $ this-> table ["orderby"]. "". $ this-> table ["descasc"]. "LIMIT ". $ this-> page_size;
} Else {
$ This-> SQL = "SELECT ". $ this-> table ["fileds"]. "FROM '". $ this-> table ["tablename"]. "'". ($ this-> table ["where"]! = ""? "WHERE ". $ this-> table ["where"]: ""). "order ". $ this-> table ["orderby"]. "". $ this-> table ["descasc"]. "LIMIT ". $ this-> page_size * ($ this-> page-1 ). ",". $ this-> page_size;
}
// SELECT * FROM articles order by id desc limit 0, 20
// SELECT * FROM articles WHERE category_id = 123 AND id> = (SELECT id FROM articles order by id LIMIT 10000, 1) LIMIT 10
Return $ this-> SQL; // SQL statement
}
Function set_url (){
$ Arr_url = array ();
Parse_str ($ _ SERVER ["QUERY_STRING"], $ arr_url );
Unset ($ arr_url ["page"]);
If (empty ($ arr_url )){
$ Str = "page = ";
} Else {
$ Str = http_build_query ($ arr_url). "& page = ";
}
$ This-> url = "http: //". $ _ SERVER ["HTTP_HOST"]. $ _ SERVER ["PHP_SELF"]. "? ". $ Str;
}
}
?>


The code is as follows:


$ Db_config ["hostname"] = "127.0.0.1"; // server address
$ Db_config ["username"] = "root"; // database username
$ Db_config ["password"] = "root"; // database password
$ Db_config ["database"] = "wap_blueidea_com"; // database name
$ Db_config ["charset"] = "utf8 ";
$ Config ["charset"] = "UTF-8"; // website code

Include ('Db. php ');
Include ('pagelist. php ');
$ Db = new db ();
$ Db-> connect ($ db_config );
Header ("content-type: text/html; charset =". $ config ["charset"]); // sets page encoding
$ Pl = new pagelist ();
$ Arr = $ pl-> get_rows ('Table _ name ');
Unset ($ pl );
Echo'

';  
print_r($arr);
echo '
';
// When special SQL statements are specified
$ Pl = new pagelist ();
$ SQL = 'select * FROM 'wap _ article' AS a, 'wap _ article_info 'AS B WHERE a. id = B. articleid ';
$ Arr = $ pl-> get_rows_ SQL ($ SQL );
Unset ($ pl );
Echo'
';  
print_r($arr);
echo '
';
?>

When the total number of records in a table is more than 10000, subquery pages are used, which is more efficient. when the data volume is small, direct query is faster.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.