How to execute two mysql queries as one in PHP/MYSQL?

How to execute two mysql queries as one in PHP/MYSQL?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Combining SQL_CALC_FOUND_ROWS and FOUND_ROWS() in a Single PHP Execution

You are looking to execute a paginated search query followed by a call to FOUND_ROWS() in one atomic step, much like how ADO.NET's DataSet allows you to collect multiple tables into a single object before iterating over them. The core challenge here is that the MySQL protocol does not natively support returning two distinct result sets from a single mysql_query() call when using the legacy extension, and even with modern extensions, handling multiple result sets requires specific iteration patterns that are often overlooked.

The pattern you are familiar with in ASP.NET involves executing SQL statements that return structured data into an in-memory collection object. In PHP, if you are using the deprecated mysql_* functions (which is likely what your source code implies given the syntax), a single call to mysql_query() strictly returns one result set. If you attempt to run both the paginated select and the FOUND_ROWS() command sequentially within a single string passed to mysql_query, the function will typically only process the first statement and ignore the rest, or it may treat them as a single logical query block which fails because they are independent statements.

To achieve the behavior of accessing two separate data sets from one "attempt" at execution, you must leverage the fact that MySQL supports multiple statements within a single connection handle. However, the legacy mysql_query() function does not provide an easy way to distinguish between the first result set and the second. You would need to manually iterate through the results using mysql_fetch_assoc(), which often returns false after the first row is fetched if the underlying implementation doesn't support multi-result parsing in that specific context, or you must switch to mysql_multi_query() (if available in your specific PHP build) and then loop through the returned resources.

A more robust approach involves using the PDO extension or mysqli, which are the standards for modern PHP development. With PDO, you can execute a multi-statement query block wrapped in a transaction if necessary, though for read-only operations like this, a transaction is not strictly required. The syntax changes significantly:

try {
    $pdo = new PDO($dsn, $user, $pass);
    // Enable multiple statements support explicitly if using PDO::ATTR_EMULATE_PREPARES context
    $stmt = $pdo->query("SELECT SQL_CALC_FOUND_ROWS Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10; SELECT FOUND_ROWS();");
    
    // The first result set contains your paginated data
    if ($stmt instanceof PDOStatement) {
        while ($row = $stmt->fetch()) {
            // Process paginated results here
        }
        
        // Move to the second result set using next() method
        $stmt->next();
        
        // Fetch the total count from the second statement
        $totalRows = $stmt->fetchColumn();
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

The critical technical detail here is the use of the next() method on the PDOStatement object. This allows you to skip over the first result set cursor and position the driver to fetch data from the subsequent statement in the batch. Without this, your application logic would fail to retrieve the count associated with the original SQL_CALC_FOUND_ROWS query.

When migrating away from legacy code, consider that SQL_CALC_FOUND_ROWS is deprecated as of MySQL 8.0 and removed in later versions due to performance implications on large datasets. It forces an index scan and a full table read to calculate the total count before returning the limited rows. If you are working with modern MySQL versions, it is often better to use window functions like ROW_NUMBER() OVER (ORDER BY ...) combined with subqueries or CTEs to achieve similar pagination logic without relying on deprecated features.

If you are maintaining a legacy codebase where SQL_CALC_FOUND_ROWS remains necessary, ensure you are using an extension that fully supports multiple result sets. The standard mysqli extension provides this capability through its next_result() method, offering a familiar API pattern closer to what you might expect from ADO.NET:

$result = mysqli_query($connection, $searchQuery);
if (!$result) { /* handle error */ }

// Process first result set (paginated data)
while ($row = mysqli_fetch_assoc($result)) {
    // Handle row
}

// Move to next result set
mysqli_next_result($connection);

// Fetch the count from the second statement
$total = mysqli_fetch_array(mysqli_query($connection, "SELECT FOUND_ROWS()"));

In this scenario, the logic separates the execution into two distinct calls but treats them as part of a cohesive workflow within a single script execution. This mimics the DataSet behavior where you access tables sequentially rather than trying to force a single function call to return a complex array structure that PHP lacks native support for in its older extensions.

Our network