如何使用 PHP PDO和 MySQL 创建分页

小鱼儿 2023-09-27 09:58 98阅读 0赞

对于本教程，我们将使用 PHP 和 MySQL 创建分页，分页是用户如何浏览包含大量数据的网站的某些部分，我们将在本教程中使用 MySQL，我们将从数据库中选择记录并显示它们在使用 HTML 和 PHP 分页的表格中。

我们将在此分页中显示下一个和上一个按钮，以及第一页和最后一页，以及突出显示当前页面之间的页面。

我们需要做的不仅仅是显示下一个和上一个按钮，但这是因为我们可以更改代码，将其更改为我们想要的方式，如果我们想删除分页页面，我们可以这样做。

### Pagination.php ###

让我们继续创建分页文件，我们称之为*pagination.php*.

我们现在需要连接到我们的数据库，我们可以使用以下代码行：

$dsn = "mysql:host=localhost;dbname=pagination;charset=utf8mb4";
    $options = [
        PDO::ATTR_EMULATE_PREPARES => false, // turn off emulation mode for "real" prepared statements
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //turn on errors in the form of exceptions
            //PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //make the default fetch be an associative array
    ];
    try {
        $conn = new PDO($dsn, "root", "root", $options);
    } catch (Exception $e) {
        error_log($e->getMessage());
        exit('Something weird happened'); //something a user can understand
    }

*您可以更改连接信息，将文本替换为您的 MySQL 详细信息*

出于本教程的目的，我创建了数据库*分页*, 下面是该数据库的 SQL 语句，使用[phpMyAdmin][]或您首选的数据库管理工具运行 SQL 语句。

CREATE DATABASE IF NOT EXISTS `pagination` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    USE `pagination`;
    
    CREATE TABLE IF NOT EXISTS `students` (
      `id` int(11) NOT NULL,
      `name` varchar(255) NOT NULL,
      `age` tinyint(1) NOT NULL,
      `joined` varchar(10) NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;
    
    INSERT INTO `students` (`id`, `name`, `age`, `joined`) VALUES
    (1, 'David Deacon', 23, '17/08/2018'),
    (2, 'Sheri Warner', 19, '03/05/2018'),
    (3, 'Sean Glover', 24, '24/07/2018'),
    (4, 'John West', 17, '13/08/2018'),
    (5, 'Rufus Clarke', 20, '28/07/2018'),
    (6, 'Roosevelt Myers', 20, '25/07/2018'),
    (7, 'Elvira Andrews', 22, '02/07/2018'),
    (8, 'Richard	Cook', 26, '19/07/2018'),
    (9, 'Lorenzo	Harris', 23, '01/07/2018'),
    (10, 'Eduardo	Hoffman', 17, '03/07/2018'),
    (11, 'Jeanne Fisher', 20, '13/08/2018'),
    (12, 'Tracy Bowers', 30, '07/07/2018'),
    (13, 'Heidi Lawrence', 18, '04/06/2018'),
    (14, 'Tara Holland', 25, '01/07/1991'),
    (15, 'Grant Edwards', 22, '22/06/2018'),
    (16, 'Bradford Green', 29, '02/05/2018'),
    (17, 'Gwen Schultz', 20, '02/05/2018'),
    (18, 'Hope Dawson', 28, '21/08/2018'),
    (19, 'Florence Osborne', 19, '17/05/2018'),
    (20, 'Rickey Poole', 26, '28/06/2018'),
    (21, 'Casey Sutton', 28, '06/07/2018'),
    (22, 'Willie Lowe', 23, '11/05/2018'),
    (23, 'Stephen	Schultz', 28, '15/07/2018'),
    (24, 'Eileen Lynch', 18, '12/06/2018'),
    (25, 'Aaron Ruiz', 29, '02/05/2018'),
    (26, 'Mae Murray', 30, '24/06/2018'),
    (27, 'Regina Hanson', 21, '26/07/2018'),
    (28, 'Cameron	Mclaughlin', 20, '29/07/2018'),
    (29, 'Earl Hale', 17, '30/06/2018'),
    (30, 'Marta Blair', 24, '10/06/2018'),
    (31, 'Alberta	Silva', 22, '05/06/2018'),
    (32, 'Joanna Holmes', 20, '20/05/2018'),
    (33, 'Alex Brock', 30, '12/05/2018'),
    (34, 'Colin Wright', 19, '28/05/2018'),
    (35, 'Peter Schmidt', 25, '10/07/2018'),
    (36, 'Joshua Price', 27, '13/07/2018'),
    (37, 'Elias Chandler', 22, '19/07/2018'),
    (38, 'Stanley	Ross', 21, '02/06/2018'),
    (39, 'Vera Cole', 26, '02/05/2018'),
    (40, 'Johnny Daniels', 29, '19/07/2018'),
    (41, 'Yvonne Hopkins', 21, '16/07/2018');
    
    ALTER TABLE `students` ADD PRIMARY KEY (`id`);
    
    ALTER TABLE `students` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=42;

让我们回到分页 PHP 文件，我们现在需要做的是创建一些我们将用于分页的重要变量。

// Get the total number of records from our table "students".
    //$total_pages = $conn->query('SELECT * FROM students')->rowCount();
    //The COUNT SQL statement that we will use.
    $sql = "SELECT COUNT(*) AS num FROM students";
    
    //Prepare the COUNT SQL statement.
    $stmt = $conn->prepare($sql);
    
    //Execute the COUNT statement.
    $stmt->execute();
    
    //Fetch the row that MySQL returned.
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    //The $row array will contain "num". Print it out.
    $total_pages = $row['num'];
    
    // Check if the page number is specified and check if it's a number, if not return the default page number which is 1.
    $page = isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 1;
    
    // Number of results to show on each page.
    $num_results_on_page = 5;

使用第一个变量，我们从*学生*我们数据库中的表，这将用于计算将有多少页。第二个变量用于确定用户在哪个页面上，例如，如果 URL 是*pagination.php?page=3*那么页码将为 3，如果 URL 中不存在该页面，则默认为 1。第三个变量确定每个页面上将显示多少个结果，例如，如果我们想显示 10 个结果（列表10 名学生）我们将数字更改为 10，默认设置为 5。

所以现在我们需要从数据库中获取当前页面的记录，下面的代码就是这样做的。

if ($stmt = $conn->prepare('SELECT * FROM students ORDER BY name LIMIT ?,?')) {
        // Calculate the page to get the results we need from our table.
        $calc_page = ($page - 1) * $num_results_on_page;
        $stmt->bindParam(1, $calc_page);
        $stmt->bindParam(2, $num_results_on_page);
        $stmt->execute();
    
    }

上面的代码中发生了一些事情，我们要做的第一件事是准备语句，这可以防止 SQL 注入，如果你不这样做，你可能会面临被黑客攻击的风险，*$calc\_page*变量基本上决定了我们表中的起始索引，之后，我们绑定变量（i是整数），然后执行SQL语句。结果将存储在*$结果*多变的。

现在我们需要显示这些结果，结果将显示在表格中，您可以按照自己的方式显示结果，但对于本教程，我们将使用表格。

<table>
    	<tr>
    		<th>Name</th>
    		<th>Age</th>
    		<th>Join Date</th>
    	</tr>
    	<?php while ($row = $stmt->fetch()): ?>
    	<tr>
    		<td><?php echo $row['name']; ?></td>
    		<td><?php echo $row['age']; ?></td>
    		<td><?php echo $row['joined']; ?></td>
    	</tr>
    	<?php endwhile; ?>
    </table>

上面的代码基本上会在表格中显示每个学生及其*姓名*, *年龄*， 和*加入*日期。

现在我们需要显示分页。

<?php if (ceil($total_pages / $num_results_on_page) > 0): ?>
    <ul class="pagination">
    	<?php if ($page > 1): ?>
    	<li class="prev"><a href="pagination.php?page=<?php echo $page-1 ?>">Prev</a></li>
    	<?php endif; ?>
    
    	<?php if ($page > 3): ?>
    	<li class="start"><a href="pagination.php?page=1">1</a></li>
    	<li class="dots">...</li>
    	<?php endif; ?>
    
    	<?php if ($page-2 > 0): ?><li class="page"><a href="pagination.php?page=<?php echo $page-2 ?>"><?php echo $page-2 ?></a></li><?php endif; ?>
    	<?php if ($page-1 > 0): ?><li class="page"><a href="pagination.php?page=<?php echo $page-1 ?>"><?php echo $page-1 ?></a></li><?php endif; ?>
    
    	<li class="currentpage"><a href="pagination.php?page=<?php echo $page ?>"><?php echo $page ?></a></li>
    
    	<?php if ($page+1 < ceil($total_pages / $num_results_on_page)+1): ?><li class="page"><a href="pagination.php?page=<?php echo $page+1 ?>"><?php echo $page+1 ?></a></li><?php endif; ?>
    	<?php if ($page+2 < ceil($total_pages / $num_results_on_page)+1): ?><li class="page"><a href="pagination.php?page=<?php echo $page+2 ?>"><?php echo $page+2 ?></a></li><?php endif; ?>
    
    	<?php if ($page < ceil($total_pages / $num_results_on_page)-2): ?>
    	<li class="dots">...</li>
    	<li class="end"><a href="pagination.php?page=<?php echo ceil($total_pages / $num_results_on_page) ?>"><?php echo ceil($total_pages / $num_results_on_page) ?></a></li>
    	<?php endif; ?>
    
    	<?php if ($page < ceil($total_pages / $num_results_on_page)): ?>
    	<li class="next"><a href="pagination.php?page=<?php echo $page+1 ?>">Next</a></li>
    	<?php endif; ?>
    </ul>
    <?php endif; ?>

我们在这里做的第一件事是检查是否有超过 1 页，如果只有 1 页则不需要显示分页。然后我们检查当前页面是否大于1，如果是则显示上一个按钮，然后如果页面大于3我们可以显示第一页，也就是1。

之后我们显示当前页前后的2页，所以如果第5页会这样显示*1 ... 3 4 5 6 7 ... 14*，这与我们之后所做的基本相同，但相反。

现在我们需要为我们的分页和表格添加一些样式，下面的 CSS 代码将做到这一点。

table {
    	border-collapse: collapse;
    	width: 500px;
    }
    td, th {
    	padding: 10px;
    }
    th {
    	background-color: #54585d;
    	color: #ffffff;
    	font-weight: bold;
    	font-size: 13px;
    	border: 1px solid #54585d;
    }
    td {
    	color: #636363;
    	border: 1px solid #dddfe1;
    }
    tr {
    	background-color: #f9fafb;
    }
    tr:nth-child(odd) {
    	background-color: #ffffff;
    }
    .pagination {
    	list-style-type: none;
    	padding: 10px 0;
    	display: inline-flex;
    	justify-content: space-between;
    	box-sizing: border-box;
    }
    .pagination li {
    	box-sizing: border-box;
    	padding-right: 10px;
    }
    .pagination li a {
    	box-sizing: border-box;
    	background-color: #e2e6e6;
    	padding: 8px;
    	text-decoration: none;
    	font-size: 12px;
    	font-weight: bold;
    	color: #616872;
    	border-radius: 4px;
    }
    .pagination li a:hover {
    	background-color: #d4dada;
    }
    .pagination .next a, .pagination .prev a {
    	text-transform: uppercase;
    	font-size: 12px;
    }
    .pagination .currentpage a {
    	background-color: #518acb;
    	color: #fff;
    }
    .pagination .currentpage a:hover {
    	background-color: #518acb;
    }

你现在应该有一些看起来像这样的东西：

![44a19911a24db47318098fc5f3d3bd16.png][]

### 源代码 ###

完整的*pagination.php源代码*：

<?php
    // Below is optional, remove if you have already connected to your database.
    $dsn = "mysql:host=localhost;dbname=pagination;charset=utf8mb4";
    $options = [
        PDO::ATTR_EMULATE_PREPARES => false, // turn off emulation mode for "real" prepared statements
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //turn on errors in the form of exceptions
            //PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //make the default fetch be an associative array
    ];
    try {
        $conn = new PDO($dsn, "root", "root", $options);
    } catch (Exception $e) {
        error_log($e->getMessage());
        exit('Something weird happened'); //something a user can understand
    }
    
    
    // Get the total number of records from our table "students".
    //$total_pages = $conn->query('SELECT * FROM students')->rowCount();
    //The COUNT SQL statement that we will use.
    $sql = "SELECT COUNT(*) AS num FROM students";
    
    //Prepare the COUNT SQL statement.
    $stmt = $conn->prepare($sql);
    
    //Execute the COUNT statement.
    $stmt->execute();
    
    //Fetch the row that MySQL returned.
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    //The $row array will contain "num". Print it out.
    $total_pages = $row['num'];
    
    // Check if the page number is specified and check if it's a number, if not return the default page number which is 1.
    $page = isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 1;
    
    // Number of results to show on each page.
    $num_results_on_page = 5;
    
    if ($stmt = $conn->prepare('SELECT * FROM students ORDER BY name LIMIT ?,?')) {
        // Calculate the page to get the results we need from our table.
        $calc_page = ($page - 1) * $num_results_on_page;
        $stmt->bindParam(1, $calc_page);
        $stmt->bindParam(2, $num_results_on_page);
        $stmt->execute();
        ?>
        <!DOCTYPE html>
        <html>
            <head>
                <title>PHP & MySQL Pagination by CodeShack</title>
                <meta charset="utf-8">
                <style>
                    html {
                        font-family: Tahoma, Geneva, sans-serif;
                        padding: 20px;
                        background-color: #F8F9F9;
                    }
                    table {
                        border-collapse: collapse;
                        width: 500px;
                    }
                    td, th {
                        padding: 10px;
                    }
                    th {
                        background-color: #54585d;
                        color: #ffffff;
                        font-weight: bold;
                        font-size: 13px;
                        border: 1px solid #54585d;
                    }
                    td {
                        color: #636363;
                        border: 1px solid #dddfe1;
                    }
                    tr {
                        background-color: #f9fafb;
                    }
                    tr:nth-child(odd) {
                        background-color: #ffffff;
                    }
                    .pagination {
                        list-style-type: none;
                        padding: 10px 0;
                        display: inline-flex;
                        justify-content: space-between;
                        box-sizing: border-box;
                    }
                    .pagination li {
                        box-sizing: border-box;
                        padding-right: 10px;
                    }
                    .pagination li a {
                        box-sizing: border-box;
                        background-color: #e2e6e6;
                        padding: 8px;
                        text-decoration: none;
                        font-size: 12px;
                        font-weight: bold;
                        color: #616872;
                        border-radius: 4px;
                    }
                    .pagination li a:hover {
                        background-color: #d4dada;
                    }
                    .pagination .next a, .pagination .prev a {
                        text-transform: uppercase;
                        font-size: 12px;
                    }
                    .pagination .currentpage a {
                        background-color: #518acb;
                        color: #fff;
                    }
                    .pagination .currentpage a:hover {
                        background-color: #518acb;
                    }
                </style>
            </head>
            <body>
                <table>
                    <tr>
                        <th>Name</th>
                        <th>Age</th>
                        <th>Join Date</th>
                    </tr>
        <?php while ($row = $stmt->fetch()): ?>
                        <tr>
                            <td><?php echo $row['name']; ?></td>
                            <td><?php echo $row['age']; ?></td>
                            <td><?php echo $row['joined']; ?></td>
                        </tr>
        <?php endwhile; ?>
                </table>
                    <?php if (ceil($total_pages / $num_results_on_page) > 0): ?>
                    <ul class="pagination">
                    <?php if ($page > 1): ?>
                            <li class="prev"><a href="pagination.php?page=<?php echo $page - 1 ?>">Prev</a></li>
                        <?php endif; ?>
    
                        <?php if ($page > 3): ?>
                            <li class="start"><a href="pagination.php?page=1">1</a></li>
                            <li class="dots">...</li>
            <?php endif; ?>
    
                        <?php if ($page - 2 > 0): ?><li class="page"><a href="pagination.php?page=<?php echo $page - 2 ?>"><?php echo $page - 2 ?></a></li><?php endif; ?>
                        <?php if ($page - 1 > 0): ?><li class="page"><a href="pagination.php?page=<?php echo $page - 1 ?>"><?php echo $page - 1 ?></a></li><?php endif; ?>
    
                        <li class="currentpage"><a href="pagination.php?page=<?php echo $page ?>"><?php echo $page ?></a></li>
    
            <?php if ($page + 1 < ceil($total_pages / $num_results_on_page) + 1): ?><li class="page"><a href="pagination.php?page=<?php echo $page + 1 ?>"><?php echo $page + 1 ?></a></li><?php endif; ?>
                        <?php if ($page + 2 < ceil($total_pages / $num_results_on_page) + 1): ?><li class="page"><a href="pagination.php?page=<?php echo $page + 2 ?>"><?php echo $page + 2 ?></a></li><?php endif; ?>
    
                        <?php if ($page < ceil($total_pages / $num_results_on_page) - 2): ?>
                            <li class="dots">...</li>
                            <li class="end"><a href="pagination.php?page=<?php echo ceil($total_pages / $num_results_on_page) ?>"><?php echo ceil($total_pages / $num_results_on_page) ?></a></li>
            <?php endif; ?>
    
                        <?php if ($page < ceil($total_pages / $num_results_on_page)): ?>
                            <li class="next"><a href="pagination.php?page=<?php echo $page + 1 ?>">Next</a></li>
                        <?php endif; ?>
                    </ul>
                        <?php endif; ?>
            </body>
        </html>
        <?php
    }
    ?>

### 结论 ###

恭喜，您已经成功地为您的网站创建了一个完全工作的分页，您可以通过多种不同的方式创建分页，这种方式可能是最好的方式之一，而且它是安全的！如果您喜欢本教程，请记得订阅和分享，关注我们的社交媒体帐户，帮助我们的网站发展，我们将提供更多有用的教程。

[phpMyAdmin]: https://www.phpmyadmin.net/
[44a19911a24db47318098fc5f3d3bd16.png]: https://img-blog.csdnimg.cn/img_convert/44a19911a24db47318098fc5f3d3bd16.png