Home >Backend Development >PHP Tutorial >Discussion on php+mysql paging processing_PHP tutorial
Discussion on php+mysql paging processing_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:27:501250browse
Discussion on php+mysql paging processing The common paging processing process is: 1. Use select count(*) from tbl_name to obtain the total number of records to be paging 2. Calculate the total number of pages based on the number of records on each page: Total number of pages = ceil (Total number of records/number of records per page) 3. Calculate the starting position based on the current page number: starting position = (current page number-1)*number of records per page 4. Use select * from tbl_name limit starting position, Number of records per page Obtain the records to be displayed 5. List output related information In this process, the database needs to traverse the table twice to obtain the required data. Although limit will terminate the traversal after getting the specified number of records, the previous retrieval up to the "starting position" is wasted. A new algorithm is proposed here to discuss with everyone: 1. Use the user variables of mysql to split and extract the starting ID number of each page. 2. The number of records in the query result is the total number of pages. 3. Get the starting id of the current page based on the current page number. 4. Use select * from tbl_name where id>= starting id limit to get the records to be displayed. 5. List output related information can be seen in the latter query. Due to the use of id as the primary key, the database can directly locate the required record. This reduces query time. This query algorithm has a by-product: it can generate a duplicate record that connects the previous and next pages, that is, there is an overlapping record between each page. Of course, it's easy to get rid of him. The following is the test code: ="; } if($mode) $pagesize++; mysql_query("set @v:=-1"); // Define mysql user variable $rs = mysql_query("select @v:= (@v+1) as xh, id from data HAVING mod(xh,$pagesize)=0 order by id $order"); mysql_result_all($rs); // Check the distribution of each page echo $pages = mysql_num_rows($rs ); // Get the total number of pages if($mode) $pagesize--; // Test the paging results, $i represents the displayed page for($i=0;$i
[$i] $xh -- $id"; $rs1 = mysql_query("select * from data where id$expr$id order by id $order limit $pagesize"); mysql_result_all( $rs1); // Display related results} ?> mysql_result_all.inc I have posted this function many times, it is very useful for debugging programs function mysql_result_all($result,$format="") { echo ""; for($i=0;$i