时间戳

首页 > 解决方案 > 带分页的 PHP PDO 搜索

php - 带分页的 PHP PDO 搜索

问题描述

我知道以前确实有人问过这个问题，尽管即使阅读了很多材料，我仍然遇到了麻烦。我的问题是我不能真正同时处理搜索和分页，因为它们自己完美地工作。这部分脚本是主要逻辑所在：

// Quantity of results per page
$limit = 5;

// Check if page has been clicked
if (!isset($_GET['page'])) {
    $page = 1;
} else{
    $page = $_GET['page'];
}

// Initial offset, if page number wasn't specified than start from the very beginning
$starting_limit = ($page-1)*$limit;

// Check if search form has been submitted
if (isset($_GET['search'])) {
    $searchTerm = $_GET['search'];
    $sql = "SELECT company.id, company.name, inn, ceo, city, phone
              FROM company
              LEFT JOIN address ON company.id = address.company_id
              LEFT JOIN contact ON company.id = contact.company_id
              WHERE
                MATCH (company.name, inn, ceo) AGAINST (:searchTerm)
                OR MATCH (city, street) AGAINST (:searchTerm)
                OR MATCH(contact.name, phone, email) AGAINST (:searchTerm)
                ORDER BY id DESC LIMIT $starting_limit, $limit";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(':searchTerm' => $searchTerm));
    // Count number of rows to make proper number of pages
    $total_results = $stmt->rowCount();
    $total_pages = ceil($total_results/$limit);
} else { // Basically else clause is similar to the search block except no search is being made
    $sql = "SELECT * FROM company";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    // Again count number of rows
    $total_results = $stmt->rowCount();
    $total_pages = ceil($total_results/$limit);
    // And then make a query
    $stmt = $pdo->prepare("
      SELECT company.id, company.name, company.inn,
            company.ceo, address.city, contact.phone
          FROM company
          LEFT JOIN address
          ON company.id = address.company_id
          LEFT JOIN contact
          ON company.id = contact.company_id
          ORDER BY id ASC LIMIT $starting_limit, $limit");
    $stmt->execute();
}
?>

这几乎是不言自明的（用返回的数据填充表）：

<?php
       // Filling the result table with results
       while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
           echo "<tr><td scope='row'>" . $row['inn'] . "</td>";
           echo "<td><a href='company.php?id=" . $row["id"] . "'>" . $row['name'] . "</a>" . "</td>";
           echo "<td>" . $row['ceo'] . "</td>";
           echo "<td>" . $row['city'] . "</td>";
           echo "<td>" . $row['phone'] . "</td></tr>";
       }
       ?>

这是分页面板：

<?php
    // Debug information - test how many results and pages there actually were
    echo $total_results."\n";
    echo $total_pages;

    // Paginating part itself
    for ($page=1; $page <= $total_pages ; $page++):?>

    <a href='<?php
        if (isset($searchTerm)) {
            echo "pagination_test.php?search=$searchTerm&page=$page";
        } else {
            echo "pagination_test.php?page=$page";
        } ?>' class="links"><?php  echo $page; ?>
    </a>

    <?php endfor; ?>

这里的问题是，虽然分页本身和搜索一样完美，但是当我结合搜索和页面参数时，对于任何搜索查询，我只返回 5 条记录，并且分页只有 1 页，但我仍然能够手动转到一个页面，结果在那里并且正确！

请帮我找出问题所在。不仅如此，我开始注意到代码变得非常草率和不可维护，我欢迎对此提出任何批评和代码组织/架构建议，因为我知道这个有问题:)

标签： phppdopagination

解决方案

您需要编写单独的查询以按搜索条件获取结果的总数，没有限制

$sql = "SELECT company.id, company.name, inn, ceo, city, phone
          FROM company
          LEFT JOIN address ON company.id = address.company_id
          LEFT JOIN contact ON company.id = contact.company_id
          WHERE
            MATCH (company.name, inn, ceo) AGAINST (:searchTerm)
            OR MATCH (city, street) AGAINST (:searchTerm)
            OR MATCH(contact.name, phone, email) AGAINST (:searchTerm)";
$stmt = $pdo->prepare($sql);
$stmt->execute(array(':searchTerm' => $searchTerm));

$total_results_without_limit = $stmt->rowCount();
$total_pages = ceil($total_results_without_limit/$limit);

推荐阅读