An example explains how to implement a paging and query function in PHP

In a web page, when we need to display more data, such as article lists, product lists, etc., we often need to paginate the data so that users can browse and view more conveniently. For web pages that have implemented the paging function, sometimes it is necessary to add some query conditions so that users can filter the required data more accurately. So, how to implement a paging and query function in PHP? Below, we will introduce the specific implementation method.

First, let’s review the implementation of paging.

When we want to implement a paging function, we usually need to use two keywords in the database: LIMIT and OFFSET. Among them, LIMIT indicates the maximum number of records to be fetched starting from a certain position, and OFFSET indicates the position to fetch from. Normally, we can calculate the values ​​of these two keywords, and the formula is: LIMIT ($page - 1) * $pagesize, $pagesize.

In practical applications, you also need to pay attention to the following points:

1. The total number of data needs to be obtained in order to calculate the total number of pages and the page numbers before and after the current page;
2. The number of pieces of data displayed on each page needs to be set in the code, usually 10, 20, 30, etc.;
3. To allow users to perform page jump operations, that is, to turn to the data page with a specified page number.

The above content is the basic knowledge of paging. If readers are not familiar with the implementation of paging, they can read some relevant information first.

Now, let’s take a look at how to implement the function of paging with query.

For the function of paging with query, two parts need to be considered respectively during implementation: paging and query. Below, we will introduce these two parts respectively.

1. Paging

The method of implementing paging is similar to the one introduced above, but with some fine-tuning. The specific implementation method is as follows:

1. Get the total number of query results.

The total number of query results is the basic data required for subsequent calculation of the total number of pages and the page numbers before and after. We can obtain it through the SELECT count(*) statement in SQL. An example is as follows:

SELECT count(*) as total FROM table WHERE condition;

Among them, total is the total number of query results.

2. Filter and paging based on the query conditions selected by the user.

This part is relatively complex and needs to be considered in conjunction with specific business scenarios. Taking the online mall as an example, if we want to filter product types, we need to add a drop-down menu to the front-end page from which users can select the desired type. Then, the query conditions sent from the front-end page are assembled into SQL statements, and then paging is performed through the paging algorithm, and finally the paging results are displayed.

Now, let’s take a look at the specific code implementation.

First is the code to get the total number:

$sql = "SELECT count(*) as total FROM table WHERE condition";
$result = $db->query($sql);
$row = $result->fetch_assoc();
$total = $row['total'];

Then is the code to assemble the SQL statement and perform paging:

$page = $_GET['page'] ? $_GET['page'] : 1;
$pagesize = 10;

$offset = ($page - 1) * $pagesize;

$sql = "SELECT * FROM table WHERE condition";

if (isset($_GET['type']) && !empty($_GET['type'])) {
    $type = $_GET['type'];
    $sql .= " AND type = '$type'";
}

$sql .= " ORDER BY id DESC LIMIT $offset, $pagesize";

$result = $db->query($sql);

Among them, $page represents the current page number, and $pagesize represents each page. The amount of data displayed on the page, $offset indicates the position from which to start fetching data. According to the query conditions sent from the front end, we assemble the SQL statement, add the LIMIT and OFFSET keywords for paging, and finally call the query method through the $db object to execute the SQL statement.

2. Query

When implementing the query function, we usually need to pass in the query conditions as parameters and filter based on the query conditions. For example, if we want to search by product name, we need to add a query box to the front-end page so that users can enter the product name they want to query. Then, the query conditions sent from the front-end page are assembled into SQL statements, and finally the query results are displayed in pages.

Now, let’s take a look at the specific code implementation.

First is the code for querying:

$name = $_GET['name'];
$sql = "SELECT count(*) as total FROM table WHERE name LIKE '%$name%'";
$result = $db->query($sql);
$row = $result->fetch_assoc();
$total = $row['total'];

Then is the code for assembling SQL statements and paging:

$page = $_GET['page'] ? $_GET['page'] : 1;
$pagesize = 10;

$offset = ($page - 1) * $pagesize;

$sql = "SELECT * FROM table WHERE name LIKE '%$name%' ORDER BY id DESC LIMIT $offset, $pagesize";

$result = $db->query($sql);

Among them, $name represents the keyword of the query, through LIKE Keywords are fuzzy matched. According to the query conditions sent from the front end, we assemble the SQL statement, add the LIMIT and OFFSET keywords for paging, and finally call the query method through the $db object to execute the SQL statement.

Summary

The above is the specific method of implementing paging with query in PHP. In practical applications, we need to optimize and improve based on specific business scenarios to achieve better user experience and performance. I hope the above content is helpful to all developers.

The above is the detailed content of An example explains how to implement a paging and query function in PHP. For more information, please follow other related articles on the PHP Chinese website!