php - sql - 确定第一组记录

Question

我想做一个分页:

< page >

哪里<表示前 10 个帖子和 >表示接下来的 10 条记录。

我知道我们可以使用:

对于前 10 条记录:

SELECT some_column FROM Table WHERE some_column ORDER BY some_column LIMIT 10 

接下来的 10 条记录:

SELECT some_column FROM Table WHERE some_column ORDER BY some_column LIMIT 10 OFFSET 10

最近 10 条记录:

SELECT some_column FROM Table WHERE some_column ORDER BY some_column DESC LIMIT 10

问题在于:假设我正在查看 11 - 20 条记录，我单击以预览 1 - 10 条记录。

我要<在我查看时消失 1 - 10 records这样用户就不会滥用 <不停地向数据库发送查询。

附言我正在使用托管的 mysql 服务器。

Answer 1

基本上这个想法是:

• 如果当前页是第一页(page 1) , 隐藏 <
• 如果当前页是最后一页，隐藏>

您可以使用 $_GET['page']并将其用作当前页面的标记:

http://youdomain.php?page=300

所以首先，你可以初始化它:

$page = isset($_GET['page']) ? $_GET['page'] : 1;

然后判断最后一页:ceil(total number of rows / how many rows per page)

示例:PDO

$check = $db->query('SELECT COUNT(id) as total FROM TABLE_NAME'); // get total rows
$total_items = $check->fetch(PDO::FETCH_ASSOC)['total']; // 5.4 above (dereference)
$total_pages = ceil($total_items / $limit); // calculate how many pages
// of course this is the last page

所以现在，只需在两种情况下使用它:

<div class="paging">
    <!-- previous -->
    <?php if($page != 1): ?>
        <a href="?page=<?php echo $page-1; ?>" title="Previous Page">&laquo;</a>
    <?php endif; ?>

    <span><?php echo $page; ?></span> <!-- current page -->

    <!-- next -->
    <?php if($page != $total_pages): ?>
        <a href="?page=<?php echo $page+1; ?>" title="Next Page">&raquo;</a>
    <?php endif; ?>
</div>

示例逻辑:

$db = new PDO('mysql:host=localhost;dbname=DATABASE_NAME;charset=utf8', 'USERNAME', 'PASSWORD');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// paginate using url get `?page=x` where x is a number
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$limit = 10; // default entries

$check = $db->query('SELECT COUNT(id) as total FROM TABLE_NAME'); // get total rows
$total_items = $check->fetch(PDO::FETCH_ASSOC)['total']; // 5.4 above
$total_pages = ceil($total_items / $limit); // calculate how many pages
$offset = ($page - 1) * $limit; // offset

// using limit offset
$query = $db->query("SELECT * FROM TABLE_NAME LIMIT $offset, $limit");
$results = $query->fetchAll(PDO::FETCH_ASSOC);


?>

<table border="1" cellpadding="10">
    <?php foreach($results as $row): ?>
        <tr>
            <!-- just add other info -->
            <td><?php echo $row['id']; ?></td>
        </tr>
    <?php endforeach; ?>
</table>
<div class="paging">
    <?php if($page != 1): ?>
        <a href="?page=<?php echo $page-1; ?>" title="Previous Page">&laquo;</a>
    <?php endif; ?>
    <span><?php echo $page; ?></span>
    <?php if($page != $total_pages): ?>
        <a href="?page=<?php echo $page+1; ?>" title="Next Page">&raquo;</a>
    <?php endif; ?>
</div>