How to write SQL statements for paging queries using PHP-PHP Problem-php.cn

How to write SQL statements for paging queries using PHP

PHPz

Release： 2023-04-03 18:36:01

Original

925 people have browsed it

With the popularity of Web applications, many websites require paging query functions. PHP is a very popular web development language, and its built-in database access functions are also very powerful. This article will introduce how to use PHP to write SQL statements for paging queries.

1. What is paging query

Paging query refers to displaying a query result in pages, with only a fixed number of records displayed on each page. Generally speaking, paging query requires the following parameters:

Number of records displayed on each page
Page number to be displayed
Total number of records

Through these parameters, we can calculate the starting position and ending position of the records that need to be displayed on the current page, and construct the corresponding SQL statement.

2. PHP paging query example

Assume there is a table named "students" with the following structure:

CREATE TABLE students (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL,
  gender ENUM('male', 'female') NOT NULL,
  score INT NOT NULL
);

Copy after login

Now we need to query the information of all girls, and The page is displayed in paging format with 10 records per page. The following is a simple PHP paging query example:

<?php
// 连接数据库
$conn = mysqli_connect(&#39;localhost&#39;, &#39;username&#39;, &#39;password&#39;, &#39;database&#39;);
if (!$conn) {
  die(&#39;数据库连接失败：&#39; . mysqli_error());
}

// 计算总记录数
$sql = "SELECT COUNT(*) FROM students WHERE gender=&#39;female&#39;";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($result);
$total = $row[0];

// 计算起始位置
$pagesize = 10;
$page = isset($_GET[&#39;page&#39;]) ? $_GET[&#39;page&#39;] : 1;
$start = ($page - 1) * $pagesize;

// 构造SQL语句
$sql = "SELECT * FROM students WHERE gender=&#39;female&#39; LIMIT $start, $pagesize";
$result = mysqli_query($conn, $sql);
?>
<html>
<head>
<title>分页查询示例</title>
</head>
<body>
<h1>分页查询示例</h1>
<table>
<tr>
  <th>ID</th>
  <th>姓名</th>
  <th>年龄</th>
  <th>性别</th>
  <th>成绩</th>
</tr>
<?php while ($row = mysqli_fetch_array($result)): ?>
<tr>
  <td><?php echo $row[&#39;id&#39;]; ?></td>
  <td><?php echo $row[&#39;name&#39;]; ?></td>
  <td><?php echo $row[&#39;age&#39;]; ?></td>
  <td><?php echo $row[&#39;gender&#39;]; ?></td>
  <td><?php echo $row[&#39;score&#39;]; ?></td>
</tr>
<?php endwhile; ?>
</table>
<p>
<?php
// 输出分页链接
$pages = ceil($total / $pagesize);
for ($i = 1; $i <= $pages; $i++) {
  if ($i == $page) {
    echo "<strong>$i</strong> ";
  } else {
    echo "<a href=&#39;?page=$i&#39;>$i</a> ";
  }
}
?>
</p>
</body>
</html>

Copy after login

The above code will output an HTML page for paging query. First, use the "mysqli_query" function to query the total number of records that meet the conditions, and then use the "ceil" function to calculate the total number of pages. The following code snippet shows how to output pagination links, where the "$page" variable represents the current page number.

$pages = ceil($total / $pagesize);
for ($i = 1; $i <= $pages; $i++) {
  if ($i == $page) {
    echo "<strong>$i</strong> ";
  } else {
    echo "<a href=&#39;?page=$i&#39;>$i</a> ";
  }
}

Copy after login

3. SQL statement construction method

In the above example, we use the LIMIT clause to limit the range of returned results. Its usage is:

SELECT * FROM table_name LIMIT start, size

Copy after login

where "start " represents the starting position of the returned result, and "size" represents the size of the returned result. Under normal circumstances, we need to calculate the appropriate starting position and the size of the returned results to implement paging queries. The calculation formula is:

$start = ($page - 1) * $pagesize;
$size = $pagesize;

Copy after login

Another way to write it is to use the OFFSET clause, and its usage is:

SELECT * FROM table_name LIMIT size OFFSET start

Copy after login

Where "OFFSET start" represents the starting position of the returned result, and "LIMIT size" represents the size of the returned result. Compared with using the LIMIT clause, there is no functional difference between using the OFFSET clause. However, some database implementations may have different performance optimization directions for the two writing methods. The specific situation requires flexible selection based on the actual situation.

4. Summary

This article introduces how to write SQL statements for paging queries in PHP, and provides a simple example. In actual development, it is necessary to choose an appropriate SQL statement construction method according to the actual situation, and handle possible exceptions in paging queries to improve the availability and stability of Web applications.

The above is the detailed content of How to write SQL statements for paging queries using PHP. For more information, please follow other related articles on the PHP Chinese website!