使用PDO调用存储过程遇2014错误，求fetchAll()使用方案

Hey there! Let's break down why you're running into this error and how to fix it using PDOStatement::fetchAll() exactly as you asked.

Why the Error Happens #

When you use fetch() in a loop with your initial GetAllProducts() call, PDO keeps the database connection tied up with an unbuffered result set—it's still fetching rows one at a time from the server. MySQL doesn't let you run new queries (like your GetProductImages() call) while this unbuffered set is active, hence the "Cannot execute queries while other unbuffered queries are active" message.

The fetchAll() Solution #

Instead of keeping the result set open and fetching rows one by one, we'll load the entire product list into a PHP array first with fetchAll(). This closes the initial result set and frees up the connection to run your other stored procedures.

Here's how to adjust your code step by step:

1. Modify the Product List Query to Use fetchAll()

Instead of returning the PDOStatement object, fetch all results into an array immediately:

// Original code:
// $query = "CALL GetAllProducts()";
// $statement = $this->connection->prepare($query);
// $statement->execute();
// return $statement;

// Updated code:
$query = "CALL GetAllProducts()";
$statement = $this->connection->prepare($query);
$statement->execute();

// Fetch all products into a PHP array (frees the database connection)
$products = $statement->fetchAll(PDO::FETCH_ASSOC);

// Optional but good practice: Close the cursor to ensure cleanup
$statement->closeCursor();

return $products;

2. Loop Through the Array Instead of the Statement

Now you can iterate over the in-memory array, and safely call your other stored procedures inside the loop:

// Get the pre-fetched products array
$products = $yourProductRepository->getAllProducts();

foreach ($products as $row) {
    extract($row);
    
    // Example: Get product images (with parameter binding to avoid SQL injection!)
    $query = "CALL GetProductImages(:productId)";
    $imageStmt = $this->connection->prepare($query);
    // Bind the ID securely instead of concatenating
    $imageStmt->bindParam(':productId', $id, PDO::PARAM_INT);
    $imageStmt->execute();
    
    // Fetch images (you can use fetchAll here too for simplicity)
    $productImages = $imageStmt->fetchAll(PDO::FETCH_ASSOC);
    // Clean up the cursor for this statement
    $imageStmt->closeCursor();
    
    // Process your images here...
    
    // Repeat the same pattern for your other stored procedures (variants, etc.)
    $variantStmt = $this->connection->prepare("CALL GetProductVariants(:productId)");
    $variantStmt->bindParam(':productId', $id, PDO::PARAM_INT);
    $variantStmt->execute();
    $productVariants = $variantStmt->fetchAll(PDO::FETCH_ASSOC);
    $variantStmt->closeCursor();
    
    // Process variants...
}

Key Notes #

• Avoid SQL Injection: I swapped your concatenated $id for a parameter binding (bindParam)—never directly insert user input (or any dynamic value) into SQL strings!
• closeCursor(): Calling this after executing stored procedures ensures any leftover result set data is cleared, preventing unexpected errors with future queries.
• Memory Considerations: If your product list is extremely large (thousands+ rows), fetchAll() will load all rows into PHP memory. For most use cases, this is manageable, but if you hit memory limits, you could look into buffered queries via the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute (but fetchAll() is cleaner for your scenario).

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