MySQL统计B>0行数问题：PDO rowCount()返回1的解决方法

Ah, I get where you're stuck here—this is a super common pitfall when working with aggregate SQL functions and PDO's rowCount()!

Why rowCount() Returns 1 Every Time #

When you run SELECT COUNT(*) FROM table WHERE B > 0, your database will always return exactly one row, even if there are zero matching records. That single row contains the count value (0, 1, 500, etc.).

rowCount() doesn't look at the value inside that row—it just counts how many rows the query returned. Since the COUNT query always gives you one row, rowCount() will always be 1, no matter what the actual count is.

The Correct Approach: Fetch the Count Value #

Instead of relying on rowCount(), you need to pull the actual count number from the result set. Here's the cleanest way to do it:

// Prepare and execute your COUNT query
$stmt = $pdo->prepare("SELECT COUNT(*) FROM table WHERE B > 0");
$stmt->execute();

// Fetch the first (and only) column from the result
$matchingRows = $stmt->fetchColumn();

// Now check the count value
if ($matchingRows === 0) {
    echo "No rows found where B > 0";
} else {
    // Run your subsequent code here—you know there are matching rows!
}

fetchColumn() is perfect here because it grabs the first column of the first row directly, which is exactly the count number you need.

Alternative: Query Rows Directly (Less Efficient for Large Datasets) #

If you actually need the matching rows later, you could skip the COUNT query entirely and use rowCount() on a SELECT * query:

$stmt = $pdo->prepare("SELECT * FROM table WHERE B > 0");
$stmt->execute();

$rowCount = $stmt->rowCount();

if ($rowCount === 0) {
    echo "No rows found where B > 0";
} else {
    // You can fetch the rows with $stmt->fetchAll() or similar, then proceed
}

⚠️ Note: This is less efficient for large tables because the database has to retrieve all matching rows instead of just returning a single count number. Stick with the COUNT query + fetchColumn() approach unless you need the actual row data.

Key Takeaway #

rowCount() is for counting how many rows a query returned, not for extracting aggregate values like COUNT(*). Always fetch the count value directly from the result set when using aggregate functions.

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