php如何实现分页功能_php分页查询代码示例

LIMIT

<?php

// 数据库连接配置
$host = 'localhost';
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 1. 定义每页显示的记录数
$recordsPerPage = 10;

// 2. 获取当前页码
// 检查URL中是否有'page'参数，如果没有，默认为第一页
$currentPage = isset($_GET['page']) && is_numeric($_GET['page']) ? (int)$_GET['page'] : 1;
// 确保页码不小于1
if ($currentPage < 1) {
    $currentPage = 1;
}

// 3. 查询总记录数
$stmt = $pdo->query("SELECT COUNT(*) FROM your_table");
$totalRecords = $stmt->fetchColumn();

// 4. 计算总页数
$totalPages = ceil($totalRecords / $recordsPerPage);

// 确保当前页码不超过总页数
if ($currentPage > $totalPages && $totalPages > 0) {
    $currentPage = $totalPages;
} elseif ($totalPages == 0) { // 如果没有记录，当前页就是0
    $currentPage = 0;
}


// 5. 计算SQL查询的偏移量 (OFFSET)
// OFFSET = (当前页码 - 1) * 每页记录数
$offset = ($currentPage - 1) * $recordsPerPage;
if ($offset < 0) $offset = 0; // 防止负数偏移量

// 6. 查询当前页的数据
$stmt = $pdo->prepare("SELECT * FROM your_table ORDER BY id DESC LIMIT :offset, :recordsPerPage");
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':recordsPerPage', $recordsPerPage, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll();

// 7. 生成分页导航链接
echo "<h1>数据列表</h1>";
echo "<pre>";
print_r($data); // 假设这里是你的数据展示
echo "</pre>";

echo "<div class='pagination'>";
// 上一页链接
if ($currentPage > 1) {
    echo "<a href='?page=" . ($currentPage - 1) . "'>上一页</a> ";
}

// 页码链接
$startPage = max(1, $currentPage - 2);
$endPage = min($totalPages, $currentPage + 2);

if ($startPage > 1) {
    echo "<a href='?page=1'>1</a> ";
    if ($startPage > 2) {
        echo "... ";
    }
}

for ($i = $startPage; $i <= $endPage; $i++) {
    if ($i == $currentPage) {
        echo "<span class='current-page'>$i</span> ";
    } else {
        echo "<a href='?page=$i'>$i</a> ";
    }
}

if ($endPage < $totalPages) {
    if ($endPage < $totalPages - 1) {
        echo "... ";
    }
    echo "<a href='?page=$totalPages'>$totalPages</a> ";
}

// 下一页链接
if ($currentPage < $totalPages) {
    echo "<a href='?page=" . ($currentPage + 1) . "'>下一页</a> ";
}
echo "</div>";

?>

LIMIT

LIMIT offset, count

offset

LIMIT offset, count

count

offset

offset

LIMIT

offset

paginate()

// app/Http/Controllers/PostController.php

use App\Models\Post;
use Illuminate\Http\Request;

class PostController extends Controller
{
    public function index()
    {
        // 每页显示15条记录
        $posts = Post::paginate(15);

        // 也可以这样，从请求中获取每页数量
        // $posts = Post::paginate(request('per_page', 15));

        return view('posts.index', ['posts' => $posts]);
    }
}

links()

{{-- resources/views/posts/index.blade.php --}}

@foreach ($posts as $post)
    {{ $post->title }} <br>
@endforeach

<div class="mt-4">
    {{ $posts->links() }}
</div>

?page=X

LIMIT

offset

composer require knplabs/knp-paginator-bundle

// src/Controller/ArticleController.php

namespace App\Controller;

use App\Entity\Article;
use Doctrine\ORM\EntityManagerInterface;
use Knp\Component\Pager\PaginatorInterface;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

class ArticleController extends AbstractController
{
    #[Route('/articles', name: 'app_articles')]
    public function index(
        Request $request,
        EntityManagerInterface $entityManager,
        PaginatorInterface $paginator
    ): Response {
        $queryBuilder = $entityManager->getRepository(Article::class)->createQueryBuilder('a');

        $pagination = $paginator->paginate(
            $queryBuilder->getQuery(), /* query NOT result */
            $request->query->getInt('page', 1), /* current page number */
            10 /* limit per page */
        );

        return $this->render('articles/index.html.twig', [
            'pagination' => $pagination,
        ]);
    }
}

{# templates/articles/index.html.twig #}

{% for article in pagination %}
    {{ article.title }} <br>
{% endfor %}

<div class="navigation">
    {{ knp_pagination_render(pagination) }}
</div>