Build Efficient Pagination and Search with PHP and SQLite: A Complete Example

Introduction to Pagination and Search Using PHP and SQLite

Pagination and search are essential features in web development, especially when displaying large sets of data. By combining the lightweight SQLite database with PHP, you can build a fast and efficient system for these tasks. This article walks you through the process using clear code examples.

Creating a SQLite Database and User Table

First, you need to create a SQLite database and a table to store user data. Here's an example SQL command to create a basic users table:

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

Implementing Pagination and Search with PHP

Now, let’s dive into the PHP code that handles user input, constructs the SQL query, and displays results:

// Get current page number and search keyword
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$keyword = isset($_GET['keyword']) ? $_GET['keyword'] : '';

// Calculate the offset for pagination
$offset = ($page - 1) * 10;

// Build SQL query
$sql = "SELECT * FROM users";
if ($keyword) {
    $sql .= " WHERE name LIKE '%$keyword%'";
}
$sql .= " LIMIT 10 OFFSET $offset";

// Execute the query
$result = $conn->query($sql);

// Display query results
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Age: " . $row['age'];
        echo "<br>";
    }
} else {
    echo "No matching results found.";
}

// Calculate total pages
$total = $conn->query("SELECT COUNT(*) FROM users")->fetch_row()[0];
$totalPages = ceil($total / 10);

// Output pagination links
for ($i = 1; $i <= $totalPages; $i++) {
    echo "<a href='?page=$i'>$i</a> ";
}

Creating the Search Form

To allow users to input keywords and search, add the following simple HTML form to your frontend:

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

Summary and Optimization Tips

This PHP and SQLite solution provides a basic yet functional framework for pagination and search. For more advanced applications, consider implementing the following improvements:

• Add sorting capabilities to the query
• Use AJAX for seamless, real-time search
• Sanitize and validate input to prevent SQL injection

This guide is ideal for small-scale projects, prototypes, or learning environments. It serves as a solid foundation that can be expanded with more complex logic as needed.