The same question is regularly asked on the forum: how to make a page-by-page conclusion. And every time the person is answered: “Easy! m lines, starting from the nth: Select request Limit $n,$m.” It's actually not that simple.

I have already written about the syntax of the LIMIT parameter, but to no avail. For a full page-by-page output of rows from the database, more is required. Required

• Process the page number (including checking if it is greater than the total number of pages)
• Draw a navigation bar (not just “back and forth”, but with links to several adjacent pages)

This is where the main problems begin.

I recently worked on a site that had these page-by-page stats in every list (and there were a lot of lists!). It was then that a solution emerged on how to reduce all these things to a simple and single solution. The result is four functions that I now use wherever page-by-page data output is needed, and I don’t waste my time worrying about the problem (how did I do it there, how can I get this code out of there?).

The first function is for internal use by the next two. Takes the page number, the total number of lines and the number of lines per page and returns the page number, already verified. The second takes the same thing, checks the page number and produces the LIMIT parameter, either full (LIMIT n,m), or short (LIMIT m), if this is the first page, or produces nothing. The third function makes a navigation bar from the same three parameters and the address for the link. Another function returns a number for a numbered list.

This is enough for normal operation With page-by-page output data. Let's see what happens in the program code:

// number of lines in pages

$in_page = 10 ;

// get the number of lines

$amount = @mysql_result(mysql_query( "SELECT count(id) as goods_total FROM goods"), 0 );

// draw a navigation line and write the beginning of the table

Print("

\n " );

// generate a request to the database

$goods_result = mysql_query ( "SELECT id, name, description, price FROM goods

ORDER BY name, price " .< b >get_limit ($page, $amount, $in_page));

// get the number for the numbered list

$count =< b >get_count_limit ($page, $amount, $in_page);

// print lines

While ($good_row = mysql_fetch_array ($goods_result)) (

$count++;

Print("

// background of every second line is gray

If ($count / 2 == intval ($count / 2 ))

Print(" bgcolor=#e1e1e1" );

Print ( ">

\n" );

// end of table and bottom navigation line

Print("

$count.

$(good_row) $(good_row)

$(good_row)

\n" );

That's ALL you need for page output! No need to stress anymore!

Just one explanation - the address of this script with all the parameters is specified as a parameter of the draw_bar function so that it only adds the page number there. If the sample is complex, you will have to manually form this address (after all, the simplification of life turned out to be relative: we simplify one thing and complicate another).

The navigation bar is made in the form of page numbers ("1 | 2 | 3"). But bringing it to the form "0-10 | 11-20 | 21-30" is not a problem.

Making page-by-page output using Php + Mysql

At the very beginning of my journey as a web programmer, I still had very little knowledge, but I had more than enough desire. And so I started writing my first script - a guest book. At that time, the Internet was just beginning to emerge and the guest book was popular. But after a few days, I ran into a problem that I couldn't solve at the time: how to display all messages 10 per page? This was easy, but how to make links to pages that would show the next 10 pages when you click on them???

This was a huge problem for me and to solve it I ran around a bunch of forums and sites. Page-based output is very necessary in our time, but there are still almost no articles on it. I decided to correct this injustice. Using my example, I will show you the page-by-page output of messages from the mysql database, because... it is very popular now and has many advantages over files. If you need, I can show you the page-by-page output of messages from the file.

First we need to share with the database in which our messages are stored. Let's not come up with huge databases with hundreds of tables. Let’s take, for example, a database in which there is only 1 table and 2 fields in it: the message number and the message itself. Below you can look at the structure of the table from this database.

id	text
1	text1
2	text2
3	text3
4	text4
5	text5

We’ve decided on the base, we can start the script. First, let's connect to our newly created database and calculate the number of rows in the posts table.
$link = mysql_connect("localhost","user","pass"); //Connect to the database server
mysql_ select _db("base",$link); //Select the base
$posts = mysql_query(" select* from posts");
$total_posts = mysql_num_rows($posts); //Count the number of rows in the table with messages (posts)

So, a start has been made. Now we will write a function to display links to other pages with messages, in fact, the page-by-page output itself. This is not a very complex function, but not the simplest either, because... It should not be less, but more at your discretion. We give the function 4 arguments (they are described in the comments). You can add a few bells and whistles to this function to suit your taste, I have given you only the basics, move on from there. For example, you can select a link to the current page.

function print_links($total,$page,$number,$links) //1 argument - total messages, 2 - current page number, 3 - number of messages on the page, 4 - number of displayed links
{
$return = null;
$pages = ceil($total/$number); //Calculate how many pages there should be

if($page+$links<= $pages)
{
$start = $page;
}
else
{
$start = $pages-($links-1);
$finish = $pages;
}

if($start< 0)
{
$start = 1;
}

for($i=$start;$i<=$finish;$i++) //Записываем в переменную ссылки
{
$return .= " ".$i." ";
}
return $return; )

So, our function is ready. In it you can specify how many links will be displayed and how many messages will be displayed. The easiest thing remains: you need to display messages in the browser, taking into account the page number the visitor is currently on.

First, let's add a little check. If you do not have a page number specified, the first page will be displayed to you by default. You can also add additional checks to prevent the script from working for requests like: $#$## or fav1324. This will increase the security of the script, and therefore the site.

if(!isset($page)) ( $page = "1"; ) //Here we do the check. If the page variable (page number) is not specified, then it is assigned the value 1
print print_links($total_posts,$page,"10","5"); //Display links to the browser
$query = mysql_query(" select* from posts limit ".(($page-1)*10).",10 "); //Select the required number of posts
while($row=mysql_fetch_array($guest)) ( print "
".$row["text"]."
"; //Display messages (do the design yourself) )
mysql_close($link);
?>

That's it now. I showed you all the functions: for generating links, for displaying messages, for connecting to the database, the rest is in your hands. If you are interested in how to make page-by-page output on files, then write to me and I will write an article so that everyone can understand.

If something is not clear, then visit my website or write to me by email.

/* 09.07.2008 */

Paged output (PHP and MySQL)

Quite often on a website there is a need to display a large amount of information of the same type, and, for ease of perception, it should be divided into parts, i.e. implement page-by-page viewing of this information. This solution is used by search engines when displaying search results, forums, message boards, etc. This article describes how to implement paged output using MySQL and PHP.

To begin with, I note that the article does not teach how to work with a database and PHP, but provides an explanation of the implementation and provides a ready-to-use one (page navigation).

Let's begin! Let's say there is a database (MySQL), for example, with advertisements. We need to implement their display on the site, in portions of 20 pieces per page. To move between portions, at the bottom of each page you need to create links with the numbers of “portions” (page labels):

Go...

Fetching data in chunks

To select ALL ads from the database, you need a query like:

SELECT * FROM table1

Of course, this is a simplified version, and in real tasks, most often, the request contains various conditions (WHERE, ORDER BY ... statements).

In order for this query to make selections in portions, you need to add the operator to it LIMIT:

LIMIT statement syntax: LIMIT row_count

Optional parameter offset tells you how many rows from the beginning of the sample you need to skip, and row_count indicates how many rows need to be selected, i.e. LIMIT 0, 20 (or just LIMIT 20 omitting zero offset) selects the first 20 rows (rows 0 to 19), and LIMIT 40, 20 specifies to skip 40 (rows 0 to 39) and select the next 20 (ie rows 40 to 59 will be selected).

Please note that the rows in the sample are numbered from zero, not from one.

So the queries for our ad example would be:

#query to select page 1: SELECT * FROM table1 LIMIT 0, 20 #query to select page 2: SELECT * FROM table1 LIMIT 20, 20 #query to select page 3: SELECT * FROM table1 LIMIT 40, 20

etc. offset increase by 20 for each subsequent page, and row_count always equals 20.

It should also be noted that the LIMIT operator in a query comes in order after WHERE , GROUP BY , HAVING , ORDER BY , but if you are new to MySQL, you can say that it comes at the end of the query line (followed by operators that are quite rarely used) .

The second part we need to deal with is the line with page labels...

Page shortcuts

For example, for a sample of the third twenty ads, the label could look like this:

page number 3

When you click on this link, the obyavleniya.php script is launched, which has access to the page_number parameter, indicating that 3 twenty advertisements are being requested - page 3. The script skips the first 40 ads, and selects the next 20.

To display this row of labels, you need to know the total number of pages (to know how many labels to "draw"). We can get it by dividing the total number of ads by the number of ads on the page, rounding the result to a higher integer. That is, if in our example, let’s say, there are only 107 ads, and we display 20 of them on each page, then the number of pages will be: 107 / 20 = 5.35, i.e. 5 full pages (20 advertisements each) + one incomplete (7 advertisements), in total, rounded up, we get 6 pages (accordingly, there will be 6 labels).

To count the total number of advertisements, there are two ways. The first way is to run a separate summarizing query almost similar to the query for selecting data, only without the limiting LIMIT operator and unnecessary sorting operations (ORDER BY), for example:

#query for selecting ads 3 pages SELECT * FROM table1 WHERE category_id="89" AND ... ORDER BY publish_date DESC LIMIT 40, 20 #query to count ALL ads in the database SELECT COUNT(*) FROM table1 WHERE category_id="89" AND ...

The first query selects advertisements, and the second one calculates their total number using the COUNT function. In practice, data retrieval queries can be quite cumbersome and heavy, so an additional heavy query for counting is not the most “necessary” operation. Also, this path is not as elegant as the second one...

MySQL 4.0.0 introduced great things like the function FOUND_ROWS and related to it SQL_CALC_FOUND_ROWS- option of the SELECT statement.

Let's consider the second option for calculating the total number of rows:

SELECT SQL_CALC_FOUND_ROWS* FROM table1 WHERE category_id="89" AND ... ORDER BY publish_date DESC LIMIT 40, 20 SELECT FOUND_ROWS()

Again, the first request makes a selection of ads, and the second gets the total number, but...

The ad selection query in this case differs from the selection from the first option only by the presence of the SQL_CALC_FOUND_ROWS option. This option instructs MySQL, along with the data selection, to also count all those rows that the query would return without the LIMIT operator. Those. in fact, this request includes, in a hidden form, the COUNT request from the first option. In this case, the calculated amount itself is not returned, but is remembered by the server. Now, in order to find out this number, you need to execute a request with the FOUND_ROWS function (in this case, the server does not perform any calculations, it simply returns what it remembered earlier).

The second option definitely looks more elegant and can also provide some speed gains.

Putting it all together

Now you know everything you need, and I can give an algorithm that describes the logic of the obyavleniya.php script for pagination, which is launched when the user enters the advertisement page...

1. First of all, when running the script, we look at what page the user is requesting (in our example, this is indicated by the page_number parameter);
2. Based on the number of the requested page, we calculate the offset parameter of the LIMIT operator;
3. we run a query for selecting ads with the operator LIMIT offset, 20 (where, 20 is the number of ads displayed on the page in our example);
4. we get the total number of advertisements in the database;
5. Based on point 4, we calculate the total number of ad pages and create a string of labels.

That's all. Now, I hope you can write your own script, or use mine, understanding the essence of how it works.

PHP Paging class for paging

Now I will give an example of how page navigation is organized using the PHP class Paging.

//connect the Paging class require("paging.inc.php "); //connect to the database$_DB = new mysqli($host,$user,$passwd,$db_name); //create an instance of the Paging class //as a parameter we pass it a pointer to the MySQL connection$_PAGING = new Paging($_DB); //perform a regular data request without worrying //about pagination via the get_page method of the Paging class object$r = $_PAGING->get_page("SELECT * FROM table1"); while($row = $r->fetch_assoc()) ( //process the data received from the database AS USUALLY and display it to the user } //display an information line like: "Shown from 1 to 20 of 107" echo $_PAGING->get_result_text()." advertisements"; //display shortcut links to the previous and next pages echo "Pages: ".$_PAGING->get_prev_page_link()." ".$_PAGING->get_next_page_link()."

"; //as well as a line with page numbers (main shortcuts) echo $_PAGING->get_page_links(); ?>

The only way this script differs from a regular script without paging is that the request for a sample of data that needs to be divided into parts is made not through mysqli->query() , but through the get_page() method implemented in the Paging class, as well as three the last lines that display the labels and the selection report line.

P.S

P.S.: I present this postscript more for the sake of completeness than as actual relevant information for most readers.

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() has some pitfalls when used in UNION queries, since LIMIT statements can be used in several places, and can affect both individual SELECT statements within the UNION and the overall result of the UNION as a whole. The purpose of SQL_CALC_FOUND_ROWS for UNION is to count the number of rows that will be returned without a global LIMIT . Therefore, the conditions for using SQL_CALC_FOUND_ROWS with UNION queries should be given:

• The SQL_CALC_FOUND_ROWS keyword must be specified in the first SELECT statement;
• The value of FOUND_ROWS() will only be accurate if UNION ALL is used. If UNION is specified without ALL , duplicate elimination occurs and the value of FOUND_ROWS() will only be approximate;
• If LIMIT is not present in UNION, then SQL_CALC_FOUND_ROWS is ignored and the number of rows in the temporary table that is created to execute UNION is returned.

Half past two at night, several days of lack of sleep... And suddenly an insight - I finally understood what kind of super-duper script I needed to promote my new website. No sooner said than done, I sit down... no, don’t write this super-duper script :) Programmers are lazy people, promotion specialists even more so (hence banner exchange networks, auto registrars in catalogs, and other lazy things). In general, I go to Google and begin to methodically scour the Runet in search of what I need. An hour or two passes... Vague suspicions creep into my soul, gradually developing into the certainty that the script I need either does not exist in nature, or is hidden on someone’s reserved localhost with access rights 000.

I break the task facing the script into several components and in about 15 minutes I write the implementation of most of its (script's) functions :) I happily take on the next feature... In general, all you need to do is provide page-by-page output of the content. That is, for example, there are 60 articles and you need to display these articles 10 per page and build a menu for this output in the form "1 2 3 ..."

It’s four in the morning, and nothing comes to mind other than the treasured “LIMIT”. And it’s not even that the database has nothing to do with my script. I realize with bitterness that my qualifications have been lost, the best years of my life have been wasted and... I go to Google ;)
After... in general, dawn had already arrived. By this time, I had become acquainted with a bunch of literature on this issue, but everywhere the situation was considered in one way or another in the context of working with SQL, or I constantly came across some widely replicated abstruse article about “separating the paging algorithm from the context or something -that-sort of" (IMHO, the following 7 lines of code solve this problem) in which I didn’t understand a single thing... :(And that’s when I already despaired and decided that the morning is wiser than the evening, on some dense, moss-overgrown forum (the address unfortunately has sunk into oblivion - at least I don’t have it), I came across a correspondence between a certain eternally apologetic Programmer and a crazy and evil Moderator - the topic of the correspondence was devoted specifically to building a menu for page-by-page display.And this was exactly what was needed!

In general, below in the text I correct the injustice: now there is an article on the Internet about how to ensure the construction of a menu for pagination! Simple working code in PHP, with detailed comments. No unnecessary functionality like "next page" links. This was done intentionally - I have never in my life clicked on “next” if I could have clicked on the page number - and this article was written to make it possible to understand the basic principle, i.e. the less code the better (even though the "next" button is added in three lines of code).

Code comment conventions:

• “Article” is a certain integral block of data (not necessarily text) which is displayed several pieces per page (this can be articles, news, pictures, etc.)
• "Page" is a page :) on which a certain number of "articles" are located.

".($i+1). ""; // otherwise we simply display the page number - this line is optional, // by skipping it you will simply get a link to the current page else echo $i+1; // if page_number is the current page - we do not display anything (we do not make a link) ) ? >

So on the browser screen we got something like "1 2 3 4 5 6 7 8", and the URL (on my computer, with the third "page" selected) looks like this: http://localhost/ep/ep.php?from=20. Please note that page number "3" corresponds to the value from=20 that is, the counting of the displayed articles will be carried out in this case from the 20th.

In general, as you already understood, the $from variable plays a key role here. It is involved in building links to each individual page (if, of course, you can put it that way:), and most importantly, it gives us a starting point for displaying articles. For example (and you will probably do exactly this), pointers to articles (or the articles themselves;) can be placed in an array, followed by selection and output to the browser of 10 articles, starting from the value of $from.

That is, once again in other words: the whole garden was actually built in order to give the script data regarding which “page” the visitor is currently on and starting from which “article” number the specified number of these articles should be displayed on the page - and this is the value of the $from variable. Those. with $from=20, pages 21 to 30 will be displayed (this also depends on the implementation of the output and the value of the $articles_per_page variable)