PHP与MySQL分页数据实现高效服务端搜索教程

<?php
// 数据库连接 $conn 假设已存在

if (isset($_GET['page']) && $_GET['page'] != "") {
    $page = $_GET['page'];
} else {
    $page = 1;
}

$search_query = "";
if (isset($_GET['search']) && $_GET['search'] != "") {
    // 对搜索关键词进行安全处理，防止SQL注入
    // 推荐使用预处理语句，此处为简化示例，实际生产环境务必使用 mysqli_prepare 或 PDO
    $search_keyword = mysqli_real_escape_string($conn, $_GET['search']);
    $search_query = " WHERE `column_name` LIKE '%" . $search_keyword . "%'"; // 替换为你的搜索列名
    // 如果需要搜索多个列，可以使用 OR 连接
    // $search_query = " WHERE `column1` LIKE '%" . $search_keyword . "%' OR `column2` LIKE '%" . $search_keyword . "%'";
}

$total_records_per_page = 10;
$offset = ($page - 1) * $total_records_per_page;
$previous_page = $page - 1;
$next_page = $page + 1;
$adjacents = "2";

// 统计总记录数时，也需要应用搜索条件
$result_count = mysqli_query(
    $conn,
    "SELECT COUNT(*) As total_records FROM `snapchat`" . $search_query
);
$total_records = mysqli_fetch_array($result_count);
$total_records = $total_records['total_records'];
$total_no_of_pages = ceil($total_records / $total_records_per_page);
$second_last = $total_no_of_pages - 1;

// 获取当前页数据时，同样应用搜索条件和分页限制
$sql = "SELECT * FROM `snapchat`" . $search_query . " LIMIT " . $offset . ", " . $total_records_per_page;
$result = mysqli_query($conn, $sql);

// ... 之后是数据渲染到表格的代码
?>

<?php
// ... (之前的分页参数定义)

$search_keyword = "";
$search_param = ""; // 用于预处理绑定的参数
if (isset($_GET['search']) && $_GET['search'] != "") {
    $search_keyword = $_GET['search'];
    $search_param = "%" . $search_keyword . "%"; // 绑定参数时需要包含通配符
}

// 统计总记录数
$count_sql = "SELECT COUNT(*) As total_records FROM `snapchat`";
if (!empty($search_keyword)) {
    $count_sql .= " WHERE `column_name` LIKE ?";
}
$stmt_count = mysqli_prepare($conn, $count_sql);
if (!empty($search_keyword)) {
    mysqli_stmt_bind_param($stmt_count, "s", $search_param);
}
mysqli_stmt_execute($stmt_count);
$result_count = mysqli_stmt_get_result($stmt_count);
$total_records = mysqli_fetch_array($result_count)['total_records'];
mysqli_stmt_close($stmt_count);

$total_no_of_pages = ceil($total_records / $total_records_per_page);
$second_last = $total_no_of_pages - 1;

// 获取当前页数据
$data_sql = "SELECT * FROM `snapchat`";
if (!empty($search_keyword)) {
    $data_sql .= " WHERE `column_name` LIKE ?";
}
$data_sql .= " LIMIT ?, ?";

$stmt_data = mysqli_prepare($conn, $data_sql);
if (!empty($search_keyword)) {
    mysqli_stmt_bind_param($stmt_data, "sii", $search_param, $offset, $total_records_per_page);
} else {
    mysqli_stmt_bind_param($stmt_data, "ii", $offset, $total_records_per_page);
}
mysqli_stmt_execute($stmt_data);
$result = mysqli_stmt_get_result($stmt_data);
mysqli_stmt_close($stmt_data);

// ... 之后是数据渲染到表格的代码
?>

<!-- 假设这是你的搜索输入框 -->
<input type="text" id="myInput" placeholder="搜索..." value="<?php echo isset($_GET['search']) ? htmlspecialchars($_GET['search']) : ''; ?>">
<button onclick="performSearch()">搜索</button>

<script>
function performSearch() {
  var input = document.getElementById("myInput");
  var searchTerm = input.value;
  var currentUrl = new URL(window.location.href);

  // 移除旧的 'search' 参数，避免重复
  currentUrl.searchParams.delete('search');
  // 移除旧的 'page' 参数，搜索时通常从第一页开始
  currentUrl.searchParams.delete('page');

  if (searchTerm) {
    currentUrl.searchParams.append('search', searchTerm);
  }

  window.location.href = currentUrl.toString();
}

// 也可以监听 Enter 键
document.getElementById("myInput").addEventListener("keypress", function(event) {
  if (event.key === "Enter") {
    performSearch();
  }
});
</script>

<nav aria-label="Page navigation example">
  <ul class="pagination justify-content-center">
    <?php 
    // 获取当前搜索关键词，用于拼接分页链接
    $current_search_param = isset($_GET['search']) ? '&search=' . urlencode($_GET['search']) : '';
    ?>

    <li <?php if($page <= 1){ echo "class='page-item page-link disabled' style='color:black;'"; } ?>>
    <a <?php if($page > 1){ echo "class='page-link' href='?page=$previous_page" . $current_search_param . "'"; } ?>>Poprzednia</a>
    </li>

    <?php 
    if ($total_no_of_pages <= 10){       
        for ($counter = 1; $counter <= $total_no_of_pages; $counter++){
            if ($counter == $page) {
           echo "<li class='page-item active'><a class='page-link'>$counter</a></li>";  
                }else{
           echo "<li class='page-item'><a class='page-link' href='?page=$counter" . $current_search_param . "'>$counter</a></li>";
                }
        }
    }
    elseif($total_no_of_pages > 10){
        // ... (省略了中间页码逻辑，但原理相同：所有链接都要加上 $current_search_param)
        // 示例：
        if($page <= 4) {            
            for ($counter = 1; $counter < 8; $counter++){       
                if ($counter == $page) {
                    echo "<li class='page-item active'><a class='page-link'>$counter</a></li>";  
                }else{
                    echo "<li class='page-item'><a class='page-link' href='?page=$counter" . $current_search_param . "'>$counter</a></li>";
                }
            }
            echo "<li class='page-item'><a class='page-link'>...</a></li>";
            echo "<li class='page-item'><a class='page-link' href='?page=$second_last" . $current_search_param . "'>$second_last</a></li>";
            echo "<li class='page-item'><a class='page-link' href='?page=$total_no_of_pages" . $current_search_param . "'>$total_no_of_pages</a></li>";
        }
        // ... 其他分页逻辑也需如此处理
    }
    ?>

    <li <?php if($page >= $total_no_of_pages){ echo "class='page-item page-link disabled' style='color:black;'"; } ?>>
    <a <?php if($page < $total_no_of_pages) { echo "class='page-link' href='?page=$next_page" . $current_search_param . "'"; } ?>>Następna</a>
    </li>
    <?php if($page < $total_no_of_pages){
        echo "<li class='page-item'><a class='page-link' href='?page=$total_no_of_pages" . $current_search_param . "'>Ostatnia &rsaquo;&rsaquo;</a></li>";
        } ?>
</ul>
</nav>