How to use PHP for data query and page turning

In website development, it is often necessary to query data and display it in pages. When the amount of data is very large, paging is required to ensure query efficiency and user experience. This article will introduce how to use PHP to query data, turn pages, and display pictures on each page.

1. Data query First, we need to get the data we want to query and store it in the database. Here we take a simple student information table as an example, including fields such as name, gender, age, grades, etc. We use MySQL database for demonstration.

 CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `age` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  `picture` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Then, we insert some data into the table, as well as the students' photos. Here we use pictures such as student1.jpg and student2.jpg.

 INSERT INTO `students` (`name`, `gender`, `age`, `score`, `picture`) VALUES ('Tom', 'M', 18, 80, 'student1.jpg');
INSERT INTO `students` (`name`, `gender`, `age`, `score`, `picture`) VALUES ('Jerry', 'F', 17, 90, 'student2.jpg');
INSERT INTO `students` (`name`, `gender`, `age`, `score`, `picture`) VALUES ('Mike', 'M', 19, 75, 'student3.jpg');
INSERT INTO `students` (`name`, `gender`, `age`, `score`, `picture`) VALUES ('Mary', 'F', 18, 85, 'student4.jpg');
INSERT INTO `students` (`name`, `gender`, `age`, `score`, `picture`) VALUES ('John', 'M', 20, 70, 'student5.jpg');
INSERT INTO `students` (`name`, `gender`, `age`, `score`, `picture`) VALUES ('Jane', 'F', 19, 95, 'student6.jpg');

2. Data page turning Next, we need to perform data query and paging in PHP. We define that each page displays 5 pieces of data, and then calculates the total number of pages and the current page number. The code example is as follows:

<?php
// 连接数据库
$conn = mysqli_connect("localhost", "username", "password", "database");

// 每页显示的记录数
$pageSize = 5;

// 当前页码
$pageNum = isset($_GET[&#39;pageNum&#39;]) ? intval($_GET[&#39;pageNum&#39;]) : 1;

// 计算总记录数和总页数
$sql = "SELECT count(*) FROM students";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_row($result);
$totalRecords = $row[0];
$totalPages = ceil($totalRecords / $pageSize);

// 计算查询起始位置
$startIndex = ($pageNum - 1) * $pageSize;

// 查询数据
$sql = "SELECT * FROM students LIMIT $startIndex, $pageSize";
$result = mysqli_query($conn, $sql);
?>

In the above code, we first connect to the database and define the number of records displayed on each page and the current page number. Then, calculate the total number of records and total pages, and calculate the query starting position based on the current page number. Finally, perform data query through LIMIT keyword and return the results.

3. Show pictures Next, we need to display the data along with the photo of each student. Here we use the HTML tag to display images.

<?php while ($row = mysqli_fetch_assoc($result)): ?>
  <div>
    <h3><?php echo $row[&#39;name&#39;]; ?></h3>
    <p>性别：<?php echo $row[&#39;gender&#39;]; ?></p>
    <p>年龄：<?php echo $row[&#39;age&#39;]; ?></p>
    <p>成绩：<?php echo $row[&#39;score&#39;]; ?></p>
    <img src="<?php echo $row[&#39;picture&#39;]; ?>" width="100"/>
  </div>
<?php endwhile; ?>

In the above code, we use a while loop to iterate through the query results and display the photo of each student using the tag.

It should be noted that the src attribute of the img tag needs to specify the correct image path. 4. Pagination navigation Finally, we need to display paging navigation at the bottom of the page to facilitate users to turn pages.

The code example is as follows:

 <div>
  <?php if ($pageNum > 1): ?>
    <a href="?pageNum=<?php echo $pageNum - 1; ?>">上一页</a>
  <?php endif; ?>
  <?php for ($i = 1; $i <= $totalPages; $i++): ?>
    <?php if ($i == $pageNum): ?>
      <span><?php echo $i; ?></span>
    <?php else: ?>
      <a href="?pageNum=<?php echo $i; ?>"><?php echo $i; ?></a>
    <?php endif; ?>
  <?php endfor; ?>
  <?php if ($pageNum < $totalPages): ?>
    <a href="?pageNum=<?php echo $pageNum + 1; ?>">下一页</a>
  <?php endif; ?>
</div>

In the above code, we first determine whether the current page number is greater than 1, and if so, display the "Previous Page" button. Then use a for loop to iterate through all page numbers and generate navigation links. If the current page number is equal to the loop variable $i, the current page number is displayed, otherwise the link is displayed.

Finally, determine whether the current page number is less than the total number of pages. If so, display the "Next Page" button. Summarize This article introduces how to use PHP to query data, turn pages, and display pictures on each page. It should be noted that in order to ensure page performance, it is recommended to use thumbnails or limit the image size when displaying images. In addition, in actual development, issues such as data query conditions and data sorting also need to be considered.

The above is the detailed content of How to use PHP for data query and page turning. For more information, please follow other related articles on the PHP Chinese website!