/* 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 decision used search engines when displaying search results, forums, message boards, etc. This article describes how to implement page 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 string of shortcuts you need to know total number 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...

Request a selection of advertisements in in this case differs from the selection from the first option only in the presence of the SQL_CALC_FOUND_ROWS option. This option instructs MySQL, along with the data selection, to make and 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 thing that makes this script different from regular script without paging, this is because 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 last lines which display labels and a 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 UNION and the general UNION result generally. 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 appear 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.

The script will allow you to display not only arrows on " previous page" And " next page", as in the article, but also, if desired, will help to display a consistent list of links to all pages of the site. You can see what it looks like on my website - just go down to home page site.

Using phpmyadmin (or sql), create a table (the name is up to you, I called it opt) with two fields. Call the first field id, the second str. Both fields must have numeric type(int), assign auto_increment to the id field. This table will store numeric value(in the str field), which determines the number of posts displayed on one page.

Everything else is simple. We make a request and retrieve this numeric value, put it in a variable, for example, $num . We determine the total number of posts (messages) in the database and enter this value into the $posts variable. Find the total number of pages on the site ($total) and round down. We calculate from which post it is necessary to display messages (posts) on this page ($start). We make a request to the table with posts and limit it by a limit ($num), we also specify the parameter ($start) from which the output of records will begin. Now all that remains is to arrange the links to the relevant pages accordingly and, voila, you’re ready. More detailed description script can be seen directly in the code.

$result77 = mysql_query("SELECT str FROM opt", $db); $myrow77 = mysql_fetch_array($result77); // number of posts on one page $num = $myrow77["str"]; // Extract from URL current page@$page = $_GET["page"]; // Determine the total number of messages in the database $result00 = mysql_query("SELECT COUNT(*) FROM posti"); $temp = mysql_fetch_array($result00); $posts = $temp; // Find the total number of pages $total = (($posts - 1) / $num) + 1; $total = intval($total); // round the current page $page = intval($page); // If the $page variable is less than 0 or empty // assign $page 1 // And if the value of $page is beyond $total, // assign $page the value of the $total variable if(empty($page) or $page< 0) $page = 1; if($page >$total) $page = $total; // Calculate starting from which number // messages should be displayed $start = $page * $num - $num; // Select $num posts starting from the number $start // -----MINI POSTS----- $result = mysql_query("SELECT * FROM posti ORDER BY date DESC LIMIT $start, $num ",$db) ;

//Display all posts in a loop

// Check if back arrows are needed if ($page != 1) $pervpage = "First Previous"; // Check if forward arrows are needed if ($page != $total) $nextpage = "Next Last"; // Find the two nearest pages from both edges, if they exist if($page - 2 > 0) $page2left = " ". ($page - 2) " "; if($page - 1 > 0) $page1left = "". ($page - 1) " "; if($page + 2<=$total) $page2right = " ". ($page + 2) .""; if($page + 1 <=$total) $page1right = " ". ($page + 1) .""; // выводим ссылки echo $pervpage.$page2left.$page1left."".$page."".$page1right.$page2right.$nextpage;

The advantage of this navigation is that all pages of the site can be located within 3 clicks from the main page. This feature can be useful, for example, when working with the SAPE service.

The problem of implementing page navigation often faces novice PHP programmers. Dividing voluminous text into separate pages is used in many Web applications, from guest books and forums to various directories. Let's
Let's solve this problem.
So what do we need to implement page navigation? For example, let's take a guest book containing several hundred messages, in which you want to display X messages on the page.
Let's look at the problem more specifically. User messages are stored in a database post with the following structure:

• id – message number,
• text – message body,
• name – author’s name,
• time – creation time.

As a result, we need to get X messages output to the page, and also organize convenient navigation, for example, like this:

<< < ..2|3|4|5|6.. > >>

where 4 is the current page. In order to track the current page, we will use the parameter page, passed via URL. Eg:

www.myserver.com/index.php?page=X.

Here X is the page number (for example, let X be equal to 25).

Now, after this short introduction, we can begin the actual implementation.

// Establish a connection to the database
include "config.php" ;
// The variable stores the number of messages displayed on the page
$num = 25 ;
// Retrieve the current page from the URL
$page = $_GET ["page" ];
// Determine the total number of messages in the database
$result = mysql_query ("SELECT COUNT(*) FROM post" );
$posts = mysql_result($result, 0);
// Find the total number of pages
$total = intval (($posts - 1 ) / $num ) + 1 ;
// Determine the beginning of messages for the current page
$page = intval($page);
// If $page is less than one or negative
// go to the first page
// And if it’s too big, then go to the last one
if(empty($page ) or $page< 0 ) $page = 1 ;
if($page > $total ) $page = $total ;
// Calculate starting from which number
// messages should be displayed
$start = $page * $num - $num ;
// Select $num messages starting from number $start
$result = mysql_query ("SELECT * FROM post LIMIT $start , $num " );
// In a loop we transfer the query results to the $postrow array
while ($postrow = mysql_fetch_array ($result))
?>

This completes the first part. Two-dimensional array postrow stores all the fields of the post table necessary to display the page. Here is an example of how you can organize message output.

echo "

" ;
for($i = 0 ; $i< $num ; $i ++)
{
echo "


" ;
}
echo "

" . $postrow [ $i ][ "name" ]. "	" . $postrow [ $i ][ "time" ]. "
" . $postrow [ $i ][ "text" ]. "

" ;
?>

Now we need to organize navigation. We formulate the components of future navigation.
We will place the arrow codes “to the beginning” and “back” into one variable, and we will also do the same with the arrows “to the end” and “forward”.

// Check if back arrows are needed
if ($page != 1 ) $pervpage = "<<
. ($page - 1) . ">< " ;
// Check if forward arrows are needed
if ($page != $total ) $nextpage = " ">>
. $total. ">>>" ;

// Find the two nearest pages from both edges, if they exist
if($page - 2 > 0 ) $page2left = " " . ($page - 2 ) . " | " ;
if($page - 1 > 0 ) $page1left = " " . ($page - 1 ) . " | " ;
if($page + 2<= $total ) $page2right = " | " . ($page + 2 ) . "" ;
if($page + 1<= $total ) $page1right = " | " . ($page + 1 ) . "" ;

// Display menu
echo $pervpage . $page2left . $page1left . " " . $page . "" . $page1right . $page2right . $nextpage ;

Good evening users. You've probably often met page navigation on sites. In this lesson we will learn how to make such page-by-page navigation.

As usual, I am posting a demo version and source materials for this lesson. In the demo version you can see the page navigation that we will now do.

For this tutorial we will need a database, a running local server, a navigation script and your patience.

Step 1. Database

First of all, we need to create a database and a user. And also create a table and fill it with information. In my example the database is called navigation, and the table is called news.

See the screenshot below for the table structure:

The table consists of 3 fields − id, title, text.

Step 2. Connect to the database

Now we need to write a connection to the database. To do this I created a new file and called it cfg.php. In this file I wrote the following code:

There is nothing complicated in this code. We wrote a regular connection to the database and wrote down a condition, if suddenly something goes wrong, we will get an error. We also specified the database encoding - utf8.

Step 3. Outputting information from the database

Now let's create a file and call it index.php. This will be our main file. It will display records and here we will create page navigation. Don't forget to file index.php connect file cfg.php. To do this, write the following code somewhere at the beginning of the page:

In order to display information from the database, I wrote in the file index.php here is the code:

"; ) while($array = mysql_fetch_array($query)); ?>

There is nothing unusual in this code either. We have selected all the information from the table news and output it using a loop do/while.

Step 4. First part of the script

Now that we have all the information displayed, we can use the first part of the script. To do this in the file index.php Before sampling the information, insert the code:

$num = 5; $page = $_GET["page"]; $result00 = mysql_query("SELECT COUNT(*) FROM news"); $temp = mysql_fetch_array($result00); $posts = $temp; $total = (($posts - 1) / $num) + 1; $total = intval($total); $page = intval($page); if(empty($page) or $page $total) $page = $total; $start = $page * $num - $num;

This line $num = 5; is responsible for the number of articles on the page. Instead of the number 5, you can put your own value.
You also need to slightly change the selection information itself and add the following to it:

$query = mysql_query("SELECT * FROM news ORDER BY id LIMIT $start, $num");

Our code should now look like this:

"; echo $array["text"]; echo "

"; ) while($array = mysql_fetch_array($query)); ?>

Step 5. Display the navigation

In this step we need to display the navigation itself, which will look like 1 | 2 | Next | Last. To do this in the file index.php under the previous code open php tags and write the following code inside them:

0) $page5left = " ". ($page - 5) ." | "; if($page - 4 > 0) $page4left = " ". ($page - 4) ." | "; if($page - 3 > 0) $page3left = " ". ($page - 3) ." | "; if($page - 2 > 0) $page2left = " ". ($page - 2) ." | "; if($page - 1 > 0) $page1left = " ". ($page - 1) ." | "; if($page + 5 ". ($page + 5) .""; if($page + 4 ". ($page + 4) .""; if($page + 3 ". ($page + 3) .""; if($page + 2 ". ($page + 2) .""; if($page + 1 ". ($page + 1) .""; // Display the menu if there is more than one page if ($total > 1) ( Error_Reporting(E_ALL & ~E_NOTICE); echo "

"; echo $pervpage.$page5left.$page4left.$page3left.$page2left.$page1left." ".$page."".$page1right.$page2right.$page3right.$page4right.$page5right.$nextpage; echo "

Step 6. Result of the work done

That's all, this lesson is over. Thank you for your attention!

You can see the full code of all pages and the script code in the sources. You can download the source code from the link at the beginning of the lesson absolutely free and without advertising.

The lesson was prepared for you by the site team.