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 stackoverflow.com/questions/883365/row-count-with-pdo which is very similar to how doctrine DBAL functions. — Will B.
– Will B., Commented Aug 20, 2015 at 20:55

DonkeyKong · Accepted Answer · 2015-02-04 15:56:27Z

21

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);

answered Feb 4, 2015 at 15:56

DonkeyKong

1,27212 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:54:34Z

20

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, 2017 at 11:54

CommunityBot

11 silver badge

answered Feb 4, 2013 at 17:53

penguin

8763 gold badges14 silver badges30 bronze badges

2 Comments

eReiche Over a year ago

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.

casivaagustin Over a year ago

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

John Linhart · Accepted Answer · 2019-11-30 13:17:04Z

5

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();

answered Nov 30, 2019 at 13:17

John Linhart

1,83621 silver badges23 bronze badges

Comments

DurandA · Accepted Answer · 2022-09-16 07:23:38Z

2

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();

answered Sep 16, 2022 at 7:23

DurandA

1,7183 gold badges29 silver badges58 bronze badges

Collectives™ on Stack Overflow

Using Doctrine DBAL to count number of rows from SELECT query

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related