Efficiently getting number of rows returned of SELECT query with WHERE clause using PDO

Question

There are numerous discussions on SO regarding how to get the number of rows returned when running a SELECT query using PDO. While most (including the PHP manual) suggest using two queries, with the first running COUNT(), I haven't seen one that suggested how to easily do this using prepared statements with WHERE clauses.

How do I most-efficiently (both in processing and number of lines of code) run a COUNT() using the same WHERE clause? The prepared query already has the columns specified. fetchAll() won't work here because that won't scale; if I have to return millions of rows, processing it using fetchAll would be super slow.

For example, without the count:

$sql = "SELECT
            FirstName,
            LastName
        FROM
            People
        WHERE
            LastName = :lastName";
$query = $pdoLink->prepare($sql);
$query->bindValue(":lastName", '%Smith%');
$query->execute();      
while($row = $query->fetch(PDO::FETCH_ASSOC)) {
    echo $row['FirstName'] . " " . $row['LastName'];
}

I looked at just adding COUNT(ID) to the SELECT clause, and having it be just one query, but it looks like there is no real good way (or not database-specific way) of rewinding the fetch() once I get a row from it.

Another solution could be making the WHERE clause it's own variable that is built. But, that doesn't seem very efficient. It's preparing two queries, binding the values all over again, and executing it.

So something like:

$whereClause = " WHERE
                   LastName = :lastName";

$rowsSql = "SELECT
            COUNT(ID) As NumOfRows
        FROM
            People " . $whereClause;
$rowsQuery = $pdoLink->prepare($sql);
$rowsQuery->bindValue(":lastName", '%Smith%');
$rowsQuery->execute();
if ($rowsQuery->fetchColumn() >= 1)
    //Prepare the original query, bind it, and execute it.
    $sql = "SELECT
                FirstName,
                LastName
            FROM
                People " . $whereClause;
    $query = $pdoLink->prepare($sql);
    $query->bindValue(":lastName", '%Smith%');
    $query->execute();      
    while($row = $query->fetch(PDO::FETCH_ASSOC)) {
        echo $row['FirstName'] . " " . $row['LastName'];
    }
}
else
{
    //No rows found, display message
    echo "No people found with that name.";
}

possible duplicate of [Alternative for mysql\_num\_rows using PDO](http://stackoverflow.com/questions/11305230/alternative-for-mysql-num-rows-using-pdo) — Marc B, Apr 08 '13 at 19:52
@MarcB As I specified at the beginning of my question, I haven't found an example that uses a `WHERE` clause. My question is regarding how to efficiently do it using a `WHERE` clause. — Luke Shaheen, Apr 08 '13 at 19:53
@Miguelo This is a made up example--I want the number of rows returned by my `SELECT` statement, using the `WHERE` clause. What's in the `WHERE` clause should be irrelevant. — Luke Shaheen, Apr 08 '13 at 20:00
ok but based on your example you want to you want to use group by — Miguelo, Apr 08 '13 at 20:01
@Miguelo How would using `GROUP BY` allow my PHP code to know if rows are returned or not? The point here is to display a message if no rows are found, else, print the rows. — Luke Shaheen, Apr 08 '13 at 20:03
combine it with a `HAVING` clause so it will return no rows where the count is 0 — Miguelo, Apr 08 '13 at 20:07
@Miguelo Thanks, but that won't allow my PHP to determine if rows are returned or not. — Luke Shaheen, Apr 08 '13 at 20:11
The `fetchAll` method should be good for your example because your result set not seems to include many rows. You can use PHP's `count` to count the rows. — bitWorking, Apr 08 '13 at 20:13
possible duplicate of [Count number of rows in SELECT query with PDO](http://stackoverflow.com/questions/6041886/count-number-of-rows-in-select-query-with-pdo) — Shikiryu, Apr 08 '13 at 20:14
@redreggae That's not a great solution, because my People table (or another table I use this with) could have millions of rows, and using `fetchAll` would be very in-efficient in those cases. — Luke Shaheen, Apr 08 '13 at 20:15
@Shikiryu That question uses `SQLite`, I am asking regarding `MySQL`. That question does not have an accepted answer, and the highest-voted answer does not show the most effective way of running both queries; it just shows running the `COUNT()` query. — Luke Shaheen, Apr 08 '13 at 20:18

score 2 · Accepted Answer · answered Apr 08 '13 at 20:34

2

When using MySQL, PDOStatement::rowCount() returns the number of rows in the result set. It actually calls the underlying mysql_num_rows() C function to populate the value. No need for multiple queries or any other messing around.

This is true of MySQL, but this behaviour cannot be relied on for other drivers (others may support it but it's not guaranteed, I'm not familiar with others enough to say for sure either way). But since your question regards specifically MySQL, it should serve your purposes.

answered Apr 08 '13 at 20:34

DaveRandom

87,921
11
154
174

1

Perfect, thanks for linking to the underlying code. There seems to be lots of mis-conception out there regarding what drivers use `rowCount()` correctly! – Luke Shaheen Apr 08 '13 at 20:44
What? A place where the PHP documentation doesn't adequately describe the nuances of the language? I don't believe it. I *won't* believe it. :-P – DaveRandom Apr 08 '13 at 20:51

score 0 · Answer 2 · answered Sep 14 '17 at 12:40

0

Try this built-in PDO function; $query->rowCount();

answered Sep 14 '17 at 12:40

patrick nwakwoke

171
1
5

Efficiently getting number of rows returned of SELECT query with WHERE clause using PDO

2 Answers2

Linked