/* 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 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...

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 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 it is organized page navigation using the PHP Paging class.

//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 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 overall result UNION in general. 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.

In Lessons 1 and 2, you've already learned a little about PHP and set up (or accessed) a server. Now we are ready to create our first PHP page. It will be easy and simple, but after this lesson you will already know a lot more about PHP.

Typically a PHP file is text file with extension .php, consisting of:

• Text
• HTML tags
• PHP scripts

You are already familiar with text and HTML tags. Now let's focus on PHP scripts.

PHP scripts

The PHP Documentation Group has released detailed documentation. There are many links to this documentation throughout this tutorial. The goal is to get you into the habit of looking for answers to your questions. PHP is so vast that it cannot be covered in this tutorial. But PHP is quite simple! Moreover, PHP language often quite similar to ordinary English.

Let's start building your first PHP page.

Example: Hello World!

Let's start creating a regular HTML document, but let's name the file page.php and place it in the root directory of the site. If you are using XAMPP (see Lesson 2), the path to the file on your computer (which is now the server) will be "c:\xampp\htdocs\page.php".

The HTML code should look something like this:

As you probably remember from the first lesson, PHP gives commands to the server. So let's write a command to the server.

First we have to tell the server where the PHP code is begins And ends. In PHP tags And ?> are used to mark the beginning and end of blocks of PHP code that the server must execute (on most servers it is sufficient to use as a start tag, but will be more correct when using PHP for the first time.)

Let's now try adding this block of code to your HTML code:

Hello World!"; ?>

If you view this PHP document in a browser, it will look something like this:

But it will be interesting to view the HTML code in the browser (selecting "view source"):

PHP codes are gone! From the first lesson, you remember that only the server can see PHP codes - the client (browser program) sees only the result!

Let's see what happens. We ask the server to write

Hello World!

. In more technical terms, we use the echo string function to write a specialized string to the client, with semicolons indicating the end of the command. But don't worry! In this tutorial we will try to use technical terminology to a minimum.

Our first example is obviously not very impressive. But don't rush! Now things will get more interesting. Let's look at another example.

Example: Now!

Let's force the server to write something else. We can, for example, ask it to output the current date and time:

In the browser we will see:

Relevant HTML code:

It's getting a little more interesting, hasn't it?

The server displays the date and time when rendering the PHP page. Please note that when you refresh the page in your browser, the new time will be displayed. The server displays the current date and time whenever the page is sent to the client.

Note also that HTML contains only date and time, but not PHP codes. Therefore, this example is independent of which browser is used. In reality, all functionality is performed server technology and always works in all browsers!

In this article, I talked about how to get a set of data from a database table corresponding to the page selected by the user. In this article we will look at Pagination output in PHP. Pagination is a list of the pages themselves. There are various options on the Internet, but they all suffer from one thing - a huge amount of code to create an infinite number of options. But do you really need infinity? You only need 1 option, which will be on the site. In this article we are with you let's create a relatively complex pagination, but there won’t be much code at all.

To create Pagination in PHP, we need to have several parameters at the input:

• count_pages- number of pages.
• active- current active page.
• count_show_pages- number of pages displayed. For example, if there is 1000 pages, then displaying them all is very problematic. Therefore, only the specified quantity will be displayed in this parameter.
• url- address of the page for which and Pagination is created.
• url_page- address of the page with the parameter page without a value at the end. For example, " /abc.php?func=create&page=".

Now we can create pagination, this is what it looks like PHP:

/* Input parameters */
$count_pages = 50;
$active = 15;
$count_show_pages = 10;
$url = "/index.php";
$url_page = "/index.php?page=";
if ($count_pages > 1) ( // All this only if the number of pages is greater than 1
/* Next is the calculation of the first page to be displayed and the last (so that the current page is somewhere in the middle, if possible, and so that the total amount of pages displayed is equal to count_show_pages, or less if the number of pages is not enough) */
$left = $active - 1;
$right = $count_pages - $active;
if ($left< floor($count_show_pages / 2)) $start = 1;
else $start = $active - floor($count_show_pages / 2);
$end = $start + $count_show_pages - 1;
if ($end > $count_pages) (
$start -= ($end - $count_pages);
$end = $count_pages;
if ($start< 1) $start = 1;
}
?>

Pages:

!}<<<
!}<





>!}
>>>!}

I’ll say right away that although the code is small, it is quite complex for beginners. 3 years ago I spent a lot of time doing something like this and then the code turned out 2 times more.

And then you can do this Pagination via CSS decorate as you wish. You can also change the layout if desired (for example, instead of angle brackets " Previous page"and similar ones, put beautiful pictures), the main thing is not to touch PHP code.

This one is very convenient Pagination implemented on this site, as well as on some of my other sites.

Create a file named hello.php and put it in your web server"s root directory (DOCUMENT_ROOT) with the following content:

Example #1 Our first PHP script: hello.php

Hello World

" ; ?>

Use your browser to access the file with your web server"s URL, ending with the /hello.php file reference. When developing locally this URL will be something like http://localhost/hello.php or http://127.0.0.1/hello.php but this depends on the web server"s configuration. If everything is configured correctly, this file will be parsed by PHP and the following output will be sent to your browser:

Hello World

This program is extremely simple and you really did not need to use PHP to create a page like this. All it does is display: Hello World using the PHP echo statement. Note that the file does not need to be executable or special in any way. The server finds out that this file needs to be interpreted by PHP because you used the ".php" extension, which the server is configured to pass on to PHP. Think of this as a normal HTML file which happens to have a set of special tags available to you that do a lot of interesting things.

If you tried this example and it did not output anything, it prompted for download, or you see the whole file as text, chances are that the server you are on does not have PHP enabled, or is not configured properly. Ask your administrator to enable it for you using the Installation chapter of the manual. If you are developing locally, also read the installation chapter to make sure everything is configured properly. Make sure that you access the file via http with the server providing you the output. If you just call up the file from your file system, then it will not be parsed by PHP. If the problems persist anyway, do not hesitate to use one of the many options.

The point of the example is to show the special PHP tag format. In this example we used to indicate the start of a PHP tag. Then we put the PHP statement and left PHP mode by adding the closing tag, ?> . You may jump in and out of PHP mode in an HTML file like this anywhere you want. For more details, read the manual section on the basic PHP syntax.

Note: A Note on Line Feeds

Line feeds have little meaning in HTML, however it is still a good idea to make your HTML look nice and clean by putting line feeds in. A linefeed that follows immediately after a closing ?> will be removed by PHP. This can be extremely useful when you are putting in many blocks of PHP or include files containing PHP that aren't supposed to output anything. At the same time it can be a bit confusing. You can put a space after the closing ?> to force a space and a line feed to be output, or you can put an explicit line feed in the last echo/print from within your PHP block.

Note: A Note on Text Editors

There are many text editors and Integrated Development Environments (IDEs) that you can use to create, edit and manage PHP files. A partial list of these tools is maintained at » PHP Editors List. If you wish to recommend an editor, please visit the above page and ask the page maintainer to add the editor to the list. Having an editor with syntax highlighting can be helpful.

Note: A Note on Word Processors

Word processors such as StarOffice Writer, Microsoft Word and Abiword are not optimal for editing PHP files. If you wish to use one for this test script, you must ensure that you save the file as plain text or PHP will not be able to read and execute the script.

Note: A Note on Windows Notepad

If you are writing your PHP scripts using Windows Notepad, you will need to ensure that your files are saved with the .php extension. (Notepad adds a .txt extension to files automatically unless you take one of the following steps to prevent it.) When you save the file and are prompted to provide a name for the file, place the filename in quotes (i.e. " hello.php "). Alternatively, you can click on the "Text Documents" drop-down menu in the "Save" dialog box and change the setting to "All Files". You can then enter your filename without quotes.

Now that you have successfully created a working PHP script, it is time to create the most famous PHP script! Make a call to the phpinfo() function and you will see a lot of useful information about your system and setup such as available predefined variables , loaded PHP modules, and configuration settings. Take some time and review this important information.

Example #2 Get system information from PHP