How to determine the "number of pages" most effectively?

Question

I hope I do not write a duplicate, but I did not find anything that could answer my question. (Although it seems to me that this is a fairly common problem.)

The problem arises in almost every web project: you have a table with many entries and want them to appear on separate pages.

Now I am wondering what is the best way to calculate the number of pages needed for a particular set of table rows.

Here are some approaches I've been thinking about. I would like to receive a response to how effective they are. I will give examples specific to PHP, but I am sure that similar methods exist in other languages.

• Probably the best way is to keep the number of pages statically and change the value each time you add a new record. (However ... I'm looking for a dynamic solution :-))

• Draw SELECT COUNT(*)on the lines of interest and calculate the page number each time the page is displayed.

• Make the usual selection to get a result set for all rows. Now do not load the lines, calling mysql_fetch_rowor so, but get the number of lines with mysql_num_rows. (Since I have no idea how this is implemented, I cannot say whether it is effective or not. Who knows?) Then I could conveniently move the pointer to the result set. (For mysqliexists mysql_data_seek, but the native MySQL extension does not have a similar function. Therefore, I assume that this is just some buffering behavior mysqli)

, ( )?

Answer 1

2

select count(*) from [Table] where [Expressions]

select [Columns] from [Table] where [Expressions] limit [Pagesize] offset [Pagenum*Pagesize-Pagesize]

, .

CMS , , ..

Answer 2

- WTF, , , /.

, , ;).

SELECT COUNT, . , , .

, , . . , . ?

Answer 3

, , , . "page N of M" - , .

, ( ), " " (fetchsize, top ..), . , , , .

.

Answer 4

, SQL-, LIMIT. , SELECT * FROM myTable LIMIT 0,20 20 . PHP script 2 SQL- SELECT * FROM myTable LIMIT 20,40, 20 40. , ?

PHP script, COUNT (*)/rowsPerPage , . LIMIT lastLimit, TotalRowCount.

: , , , 1. TotalRows/MaxPerPage = howManyPagesNeeded

Answer 5

2 :

• , ?
• ?

.

Answer 6

, , , .

Answer 7

- SQL_CALC_FOUND_ROWS

:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

, 10 , , , .

, FOUND_ROWS() MySQL

Answer 8

In fact, if you populate a webpage with PHP, it’s easier to just get the MySQL table, put the data into an array and use a for-loop to loop through data like this

for($i=0;$i<count($arrayvar);$i++){}

With even a simple template engine, you can declare a block inside another block and parse all the data in one pipeline line.

Hope the type of answer you are looking for. I do not quite understand what you mean by your question, but I think it is.