Using Doctrine DBAL to count number of rows from SELECT query

Question

OK so I am looking for a neat and short way to count the number of rows from a SELECT query using Doctrine DBAL.

I know that I could SELECT COUNT(*) but then I need to sort through the array when I fetch results. Alternatively, it's been suggested to look in to getScalarResult(). But I can't seem to find any documentation about this, other than in DQL (which is a different project).

So what is the neatest way to do this? I guess it's because I'm used to the great MySQLI attribute num_rows!

Suggest reading http://stackoverflow.com/questions/883365/row-count-with-pdo which is very similar to how doctrine DBAL functions. — Will B., Aug 20 '15 at 20:55

score 21 · Answer 1 · answered Feb 04 '15 at 15:56

Another way to do this with Doctrine DBAL is to get the count as a field and return the column

    $sql = "SELECT count(*) AS Total FROM myTable WHERE myId = :myId";
    $stmt = $conn->prepare($sql);
    $stmt->bindValue('myId', $myId, PDO::PARAM_INT);
    $stmt->execute();
    $count = $stmt->fetchColumn(0);

score 18 · Accepted Answer · edited May 23 '17 at 11:54

18

Actually I thought I had looked really hard, but I just came across this Count Records Returned MySQL Doctrine

So the way to do it is via the rowCount() method.

Example:

$num_rows = $conn->executeQuery("SELECT * FROM users")->rowCount();

edited May 23 '17 at 11:54

Community

1
1

answered Feb 04 '13 at 17:53

penguin

856
3
14
30

7

That only makes sense if you anyway need to fetch the records. Otherwise fetching all records just to count them in PHP is a bad idea performance wise. – eReiche Jun 17 '14 at 15:33
9

Be aware that rowCount is not 100% trustable for all databases, the behavior may change depending on the database that you are using it. This comment is copy pasted from the source code of the method. ***If the last SQL statement executed by the associated Statement object was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications. [link](http://www.doctrine-project.org/api/dbal/2.4/source-class-Doctrine.DBAL.Driver.Statement.html#111-123)*** – casivaagustin Feb 04 '15 at 14:58

score 4 · Answer 3 · answered Nov 30 '19 at 13:17

I enjoy to use the query builder. An example:

    $queryBuilder = $connection->createQueryBuilder();
    $queryBuilder->select('COUNT(*)');
    $queryBuilder->from("the_table");
    $queryBuilder->where('some_column = :theValue');
    $queryBuilder->setParameter('theValue', $someValue);

    return (int) $queryBuilder->execute()->fetchColumn();

score 1 · Answer 4 · answered Sep 16 '22 at 07:23

Here is an updated version of @DonkeyKong answer for Doctrine DBAL >= 2.13:

$sql = "SELECT count(*) AS Total FROM myTable WHERE myId = :myId";
$stmt = $conn->prepare($sql);
$stmt->bindValue('myId', $myId, PDO::PARAM_INT);
$result = $stmt->executeQuery();
$count = $result->fetchOne();

Using Doctrine DBAL to count number of rows from SELECT query

4 Answers4