PHP双搜索函数（SKU/日期）疑问：SQL正常PHP分页遇问题

Hey there! It’s super common to hit snags when translating a working raw SQL query into a PHP pagination system—let’s walk through the most likely issues that could be tripping you up:

1. Incorrect LIMIT/OFFSET Calculation #

Pagination lives or dies by getting LIMIT and OFFSET right, and it’s easy to mess up the math:

• If your pagination starts counting pages from 1 (the usual approach), your offset should be ($currentPage - 1) * $itemsPerPage. For example, page 3 with 10 items per page would use LIMIT 10 OFFSET 20.
• Remember: OFFSET applies to the grouped, filtered results from your query, not raw table rows. If you calculate offset based on ungrouped row counts, your page results will be inconsistent.

2. Lost Search Parameters Between Pages #

When users click to page 2 (or beyond), do your search filters (sku LIKE 'ISCE%' and settlement_id LIKE '7072432852') get carried over?

• If using URL parameters for pagination, append the sku and settlement_id filters to every pagination link. Example: ?page=2&sku=ISCE%&settlement_id=7072432852
• If using POST, include these values in hidden form fields so they’re sent with every page request. Without them, your query will fall back to unfiltered results (or no results at all).

3. Wrong Query Execution Order #

Double-check that your query follows MySQL’s correct execution sequence:
WHERE → GROUP BY → HAVING → ORDER BY → LIMIT/OFFSET

• If you wrap your grouped query in a subquery and apply pagination outside of it, you’ll paginate raw rows before grouping—breaking your intended results. Always apply LIMIT/OFFSET after all filtering, grouping, and sorting.

4. Miscalculating Total Page Count #

To show accurate pagination controls (like "Page 1 of 5"), you need an accurate count of matching records. Since your query groups by sku, a simple COUNT(*) won’t work—you need to count distinct skus:

SELECT COUNT(DISTINCT sku) 
FROM settlements 
WHERE sku LIKE 'ISCE%' AND settlement_id LIKE '7072432852' 
HAVING sku IS NOT NULL AND LENGTH(sku) > 0

• Using raw row counts instead of distinct skus will make your total page number way off (multiple rows might map to one sku).

5. SQL Injection Risks & Variable Parsing Errors #

Even if your query works in Workbench, directly concatenating variables into your PHP SQL string can cause issues:

• Special characters (like % in your sku filter) might get escaped incorrectly, breaking your LIKE condition.
• Always use prepared statements (with PDO or mysqli) to pass variables into your query. This avoids injection risks and ensures parameters are parsed correctly.

Example PDO Implementation #

Here’s a quick snippet that ties all this together:

// Set up pagination parameters
$currentPage = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$itemsPerPage = 10;
$offset = ($currentPage - 1) * $itemsPerPage;

// Get search filters (sanitize these if they come from user input!)
$skuFilter = 'ISCE%';
$settlementIdFilter = '7072432852';

try {
    $pdo = new PDO('mysql:host=localhost;dbname=your_database', 'your_user', 'your_password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Get total number of matching skus
    $countQuery = $pdo->prepare("
        SELECT COUNT(DISTINCT sku) 
        FROM settlements 
        WHERE sku LIKE ? AND settlement_id LIKE ? 
        HAVING sku IS NOT NULL AND LENGTH(sku) > 0
    ");
    $countQuery->execute([$skuFilter, $settlementIdFilter]);
    $totalItems = $countQuery->fetchColumn();
    $totalPages = ceil($totalItems / $itemsPerPage);

    // Fetch current page results
    $dataQuery = $pdo->prepare("
        SELECT settlement_id, sku 
        FROM settlements 
        WHERE sku LIKE ? AND settlement_id LIKE ? 
        GROUP BY sku 
        HAVING sku IS NOT NULL AND LENGTH(sku) > 0 
        ORDER BY sku 
        LIMIT ? OFFSET ?
    ");
    $dataQuery->execute([$skuFilter, $settlementIdFilter, $itemsPerPage, $offset]);
    $results = $dataQuery->fetchAll(PDO::FETCH_ASSOC);

    // Now you can loop through $results and build your pagination links!
} catch(PDOException $e) {
    echo "Oops, something went wrong: " . $e->getMessage();
}

If you’re still stuck, sharing your actual PHP pagination code would help pinpoint exactly where things are going sideways.

内容的提问来源于stack exchange，提问作者Steven Smith