Home >Database >Mysql Tutorial >How to Implement Simple Database Pagination in PHP?

How to Implement Simple Database Pagination in PHP?

Barbara StreisandOriginal: 2025-01-21 19:07:10203browse

<code class="language-php"><?php
// Database connection (replace with your credentials)
$host = 'localhost';
$dbname = 'your_database';
$user = 'your_user';
$password = 'your_password';

try {
    $dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}


// Get total number of records
$stmt = $dbh->query('SELECT COUNT(*) FROM your_table');
$totalRecords = $stmt->fetchColumn();


// Pagination settings
$recordsPerPage = 20;
$totalPages = ceil($totalRecords / $recordsPerPage);
$currentPage = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$currentPage = max(1, min($currentPage, $totalPages)); // Ensure page number is within valid range

$offset = ($currentPage - 1) * $recordsPerPage;


// Fetch data for current page
$stmt = $dbh->prepare("SELECT * FROM your_table LIMIT :limit OFFSET :offset");
$stmt->bindParam(':limit', $recordsPerPage, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);


// Display data
echo "<h2>Data (Page " . $currentPage . " of " . $totalPages . ")</h2>";
if ($data) {
    echo "<ul>";
    foreach ($data as $row) {
        echo "<li>" . implode(", ", $row) . "</li>"; // Adjust as needed for your table structure
    }
    echo "</ul>";
} else {
    echo "<p>No data found for this page.</p>";
}



// Display pagination links
echo "<div class='pagination'>";
for ($i = 1; $i <= $totalPages; $i++) {
    $activeClass = ($i == $currentPage) ? 'active' : '';
    echo "<a href='?page=" . $i . "' class='" . $activeClass . "'>" . $i . "</a>";
}
echo "</div>";


?>

<!DOCTYPE html>
<html>
<head>
<title>Simple Pagination</title>
<style>
.pagination a {
    display: inline-block;
    padding: 8px 12px;
    margin: 0 4px;
    text-decoration: none;
    border: 1px solid #ccc;
}

.pagination a.active {
    background-color: #4CAF50;
    color: white;
}
</style>
</head>
<body>
</body>
</html>
</code>

This improved example includes:

Error Handling: A try-catch block handles potential database connection errors.
Prepared Statements: Uses prepared statements to prevent SQL injection vulnerabilities. This is crucial for security.
Input Validation: While the original example used filter_input, it lacked comprehensive validation. This version ensures the page number is a positive integer and within the valid range.
Clearer Data Display: The data is displayed in a more user-friendly format (an unordered list). You'll need to adapt the implode part to match your database table's column names.
Complete HTML: Provides a complete HTML structure for better rendering.
CSS Styling: Basic CSS is included to style the pagination links.
Placeholder Values: Remember to replace "your_database", "your_user", "your_password", and "your_table" with your actual database credentials and table name.

Remember to create the necessary database and table before running this code. This enhanced example provides a more robust and secure solution for pagination in PHP.

The above is the detailed content of How to Implement Simple Database Pagination in PHP?. For more information, please follow other related articles on the PHP Chinese website!

php sql css html Integer for while format try catch Error number this display column input table database

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：How to Implement Simple PHP Pagination for Database Results?Next article：How to Implement Simple PHP Pagination for Database Results?

See more

How to Implement Simple Database Pagination in PHP?

Related articles