PHP MySQL技术咨询：批量取数、分页查询、结果计数优化

1. Fetch All Results in One Step (No Manual Loop) #

Instead of using a while loop to iterate through rows, you can use mysqli_fetch_all()—a built-in function that retrieves all rows from the result set into an associative array in a single call. This simplifies your code and eliminates the need for manual iteration. Here's how to update your function:

function search($stmt, $age) {
    $sql = "SELECT * FROM Users WHERE age = ? LIMIT 10";
    if(!mysqli_stmt_prepare($stmt, $sql)) {
        return false;
    } 
    mysqli_stmt_bind_param($stmt, "i", $age);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    // Fetch all rows at once as an associative array
    return mysqli_fetch_all($result, MYSQLI_ASSOC);
}

A quick note: While this removes the loop from your code, the underlying operation still processes each row (so it's technically O(n) under the hood). But from your code's perspective, it's a single, clean step to get the full result set.

2. Using OFFSET to Retrieve a Range of Results #

To get rows 40 to 50 (inclusive) from a total of 50 results, use the LIMIT clause with two parameters: LIMIT offset, row_count. Remember MySQL uses 0-based indexing, so the 40th row starts at offset 39, and you need 11 rows to cover 40–50.

Your SQL query can be written in two equivalent ways:

-- Syntax 1: LIMIT [offset], [row count]
SELECT * FROM Users WHERE age = ? LIMIT 39, 11;

-- Syntax 2: More explicit with OFFSET keyword
SELECT * FROM Users WHERE age = ? LIMIT 11 OFFSET 39;

Both will return exactly the range you need.

3. Count Results Without Storing All Rows or Looping #

There are two efficient approaches here, depending on your use case:

Option 1: Use SQL's COUNT() Function (Best for Just Counting)

If you only need the number of matching rows (not the actual data), run a COUNT(*) query. This is far more efficient because the database calculates the count without returning all rows:

function countUsersByAge($stmt, $age) {
    $sql = "SELECT COUNT(*) AS user_count FROM Users WHERE age = ?";
    if(!mysqli_stmt_prepare($stmt, $sql)) {
        return false;
    }
    mysqli_stmt_bind_param($stmt, "i", $age);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_assoc($result);
    return $row['user_count'];
}

Option 2: Use mysqli_num_rows() (If You Already Have the Result Set)

If you've already executed a SELECT query and have the result object (but don't want to store all rows or loop), use mysqli_num_rows() to get the count directly:

// After executing your SELECT query and getting $result
$totalRows = mysqli_num_rows($result);

This gives you the row count instantly without iterating through each entry.

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