Implement paging and search functions using PHP and SQLite

Use PHP and SQLite to implement paging and search functions

In web development, paging and search functions are very common and practical functions. This article will introduce how to use PHP and SQLite to implement these two functions, and provide corresponding code examples.

First, we need to create a SQLite database to store our data. Suppose we have a table containing user information, which contains fields such as id, name, age, etc. We can use the following SQL command to create the table:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);

Next, we need to write PHP code to connect to the database and query the data. Assume that we have connected to the database and have obtained the page number and search keyword entered by the user. We can use the following code to query and paginate:

// 获取页码和搜索关键字
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$keyword = isset($_GET['keyword']) ? $_GET['keyword'] : '';

// 计算分页偏移量
$offset = ($page - 1) * 10;

// 构建查询SQL语句
$sql = "SELECT * FROM users";
if ($keyword) {
    $sql .= " WHERE name LIKE '%$keyword%'";
}
$sql .= " LIMIT 10 OFFSET $offset";

// 执行查询
$result = $conn->query($sql);

// 输出查询结果
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Age: " . $row['age'];
        echo "<br>";
    }
} else {
    echo "没有找到匹配的结果";
}

// 计算总页数
$total = $conn->query("SELECT COUNT(*) FROM users")->fetch_row()[0];
$totalPages = ceil($total / 10);

// 输出分页链接
for ($i = 1; $i <= $totalPages; $i++) {
    echo "<a href='?page=$i'>$i</a> ";
}

The above code first obtains the page number and search keyword entered by the user. Then, the query SQL statement is constructed based on the user input. Among them, LIMIT and OFFSET are used to implement paging. Finally, the query is executed and the query results are output. At the same time, the total number of pages is also calculated and pagination links are output.

In addition, in order to implement the search function, we have added keyword conditional judgment to the query SQL statement. In this way, when the user enters a search keyword, only results containing that keyword will be queried.

Finally, we can add a search form on the front-end page to allow users to enter keywords and submit. The code is as follows:

<form action="" method="GET">
    <input type="text" name="keyword">
    <input type="submit" value="搜索">
</form>

Through the above code examples, we can implement simple and practical paging and search functions. Of course, according to actual needs, we can also optimize and expand the code, such as adding sorting functions, using AJAX asynchronous loading, etc. Hope this article is helpful to you!

The above is the detailed content of Implement paging and search functions using PHP and SQLite. For more information, please follow other related articles on the PHP Chinese website!