mysql paging query how to realize the

Combining MySQL database, how to realize the paging function

First step: we can use the $_GET method to get the value of a parameter, and get different dynamic parameters when the user clicks on the previous page, the next page, the first page, or the last page

Second step: according to MySQL’s LIMIT keyword, the dynamic parameters of the SQL statement stitching

We will define the number of data items to be displayed on each page, in the position of the first parameter of LIMIT, and according to the dynamic change of the address bar parameter to specify the data from which to start displaying data. p>The number of data items to be displayed on each page is defined in the position of the first parameter of the limit, and the dynamic change of the address bar parameter is used to specify the data to be displayed starting from the first data item

How to Generate Stored Procedures to Operate MySQL Paging Queries

The following article describes how to Generate Stored Procedures to Operate MySQL Paging Queries correctly, and if you are interested in operating MySQL Paging Queries correctly, you can click on the following article to view it. We hope you will find it useful after browsing. The following article describes a generic stored procedure to perform the correct operation of a MySQL paging query, if you are interested in performing the correct operation of a MySQL paging query you will be able to If you are interested in the correct operation of MySQL paging query, you can click on the following article to view it. We hope you will find it useful after browsing.

Some time ago, there was no SQLServer to MySQL (and PHP with the best combination) of common stored procedures, in the spirit of sharing, for you to dedicate this MySQL paging query common stored procedures, assuming that the database used for guestbook:

useguestbook;delimiter $$dropprocereifexistsprc_page_result$$createprocereprc_page_result(incurrpageint,incolumnsvarchar(500),intablenamevarchar(500), insConditionvarchar(500),inorder_fieldvarchar(100),inasc_fieldint,inPrima(the most complete web hosting management system)ry_fieldvarchar(100),inpagesizeint) order_field,’desc’);setsTemp='(selectmax’;endif;ifcurrpage=1thenifsCondition”thensetsSql=concat(‘select’,columns,’from’,tablename ,’where’);setsSql=concat(sSql,sCondition,sOrder,’limit?’);elsesetsSql=concat(‘select’,columns,’from’,tablename,sOrder,’limit?’); endif;elseifsCondition”thenetsSql=concat(‘select’,columns,’from’,tablename);setsSql=concat(sSql,’where’,sCondition,’and’,Prima(most perfect web hosting management system)ry_field,sTemp);setsSql=concat(sSql,'(‘,Prima(perfect web hosting management system)ry_field,’)’,’from(select’);setsSql=concat(sSql,”,Prima(most perfect web hosting management system)ry_field,’from’,tablename,sOrder);setsSql=concat(sSql,’limit’,(currpage-1)*pagesize,’)astabtemp)’,sOrder);setsSql= concat(sSql,’limit?’);elsesetsSql=concat(‘select’,columns,’from’,tablename);setsSql=concat(sSql,’where’,Prima(the most complete web hosting management system)ry_ field,sTemp);setsSql=concat(setsSql,'(‘,Prima(most complete web hosting management system)ry_field,’)’,’from(select’);setsSql=concat(setsSql,”,Prima(most complete web hosting management system)ry_ field,’from’,tablename,sOrder);setsSql=concat(sSql,’limit’,(currpage-1)*pagesize,’)astabtemp)’,sOrder);setsSql=concat(sSql,’limit? ‘);endif;endif;set@iPageSize=pagesize;set@sQuery=sSql;preparemtfrom@sQuery;executestmtusing@iPageSize;end;$$delimiter;

Can be store it as a database script and then import it with the command:

MySQL (best combination with PHP)-uroot-p<pageResult.sql;

call:

mysql split table paging query solution ideas

When the business in accordance with the month table, but the front-end h5 need to page display, partners do not know if there is no encounter this this demand finally how to complete.

I think of a solution idea here, may not be perfect, I hope to be able to turn to attract jade.

1, into the parameter pageNo for the page number, if 1 then it is the first page. pageSize can be into the parameter can also be fixed dead, here fixed dead 10. Limit is the data offset mark, according to the entry of the parameter pageNo calculated, Limit = (pageNo-1)*pageSize. assuming that the A table 41 data conformity, B table 51 data conformity. data matches, and table B has 51 data matches, as shown below.

There are several kinds of situation 1. If limit<A table 41 then need to take data from A table, (1) if Limit+pageSize<Count directly get data can be (2) if Limit+pageSize>Count, then you need to take some of the data from the A table from the B table to take part of the data. table to get part of the data from B table.

1. If Limit>A table 41 Then you need to take the data from the B table, if the B data is still insufficient, then repeat the above steps. As shown below