使用PDO预处理语句实现带表单筛选的David Carr分页类问题

Hey there! Let's tackle this PDO prepared statement + David Carr's pagination class issue for filtered queries — I've been down this road before, so here's how I got it working smoothly:

First, you need to construct your SQL query dynamically without hardcoding user input. Using WHERE 1=1 lets you easily append AND conditions without worrying about breaking the query structure:

// Start with base query
$sql = "SELECT * FROM your_table WHERE 1=1";
$params = [];

// Add filter conditions based on form input
if (!empty($_POST['username'])) {
    $sql .= " AND username LIKE ?";
    $params[] = "%{$_POST['username']}%";
}
if (!empty($_POST['account_status'])) {
    $sql .= " AND account_status = ?";
    $params[] = $_POST['account_status'];
}

This keeps your query safe from SQL injection while adapting to whatever filters the user submits.

David Carr's class needs two key things: the total number of filtered records, and the paginated result set. Here's how to tie it all together with PDO:

// Get total count of filtered records (reuse your filter conditions)
$count_sql = "SELECT COUNT(*) FROM your_table WHERE 1=1" . substr($sql, strpos($sql, "AND"));
$count_stmt = $pdo->prepare($count_sql);
$count_stmt->execute($params);
$total_records = $count_stmt->fetchColumn();

// Initialize pagination
$pagination = new Pagination();
$pagination->total = $total_records;
$pagination->page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$pagination->limit = 15; // Your preferred per-page limit
$offset = ($pagination->page - 1) * $pagination->limit;

// Add pagination to your main query
$sql .= " LIMIT ? OFFSET ?";
$params[] = $pagination->limit;
$params[] = $offset;

// Execute and fetch results
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$filtered_results = $stmt->fetchAll(PDO::FETCH_ASSOC);

This is the most common gotcha! When users click pagination links, you need to keep their filter selections intact. Here's how to pass those parameters along:

// Collect filter parameters to append to links
$filter_params = [];
if (!empty($_POST['username'])) $filter_params['username'] = urlencode($_POST['username']);
if (!empty($_POST['account_status'])) $filter_params['account_status'] = urlencode($_POST['account_status']);
$query_string = http_build_query($filter_params);

// If the pagination class lets you set a custom query string, use that
// For example, if there's a set_query_string() method:
$pagination->set_query_string($query_string);

// Or manually build links if needed:
$next_page_link = "results.php?page=" . ($pagination->page + 1) . "&{$query_string}";

When users first submit the form, you'll want to switch from POST to GET to avoid accidental form resubmissions on refresh. Add this at the top of your results page:

session_start();
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Store filters in session as a fallback
    $_SESSION['filters'] = $_POST;
    // Redirect to a GET request to lock in the filters
    header("Location: results.php?" . http_build_query($_POST));
    exit;
}
// Use filters from GET or session if available
$active_filters = isset($_GET['username']) ? $_GET : $_SESSION['filters'] ?? [];

• Stick to positional placeholders (?) instead of named ones — they're easier to manage for dynamic queries.
• Always validate user input before adding it to your parameters (even with prepared statements, this prevents wonky filter behavior).
• If no filters are submitted, your query will default to WHERE 1=1 which acts like a full table query — perfect for reusing your existing working logic!

内容的提问来源于stack exchange，提问作者Tim Gallant