Result

A \Doctrine\DBAL\Result object is returned by QueryBuilder->executeQuery() for ->select() and ->count() query types, and by Connection->select() and Connection->count() calls.

The object represents a query result set and has methods to fetch single rows with ->fetchAssociative() or to fetch all rows as an array with ->fetchAllAssociative().

Warning

The return type of single field values is not type safe! If you select a value from a field that is defined as INT, the Result result may very well return that value as a PHP string. This is also true for other database column types like FLOAT, DOUBLE and others. This is an issue with the database drivers used underneath. It may happen that MySQL returns an integer value for an INT field, while others may return a string. In general, the application itself must take care of an according type cast to achieve maximum DBMS compatibility.

fetchAssociative()

This method fetched the next row from the result. It is usually used in while() loops. This is the recommended way of accessing the result in most use cases.

Typical example:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php

// use TYPO3\CMS\Core\Database\Connection
// Fetch all records from tt_content on page 42
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
    ->select('uid', 'bodytext')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq(
            'pid',
            $queryBuilder->createNamedParameter(42, Connection::PARAM_INT)
        )
    )
  ->executeQuery();

while ($row = $result->fetchAssociative()) {
    // Do something useful with that single $row
}

Read how to correctly instantiate a query builder with the connection pool.

->fetchAssociative() returns an array reflecting one result row with field/value pairs in one call and retrieves the next row with the next call. It returns false when no more rows can be found.

fetchAllAssociative()

This method returns an array containing all rows of the result set by internally implementing the same while loop as above. Using that method saves some precious code characters, but is more memory intensive if the result set is large and contains many rows and data, since large arrays are carried around in PHP:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php

// use TYPO3\CMS\Core\Database\Connection;
// Fetch all records from tt_content on page 42
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$rows = $queryBuilder
    ->select('uid', 'bodytext')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq(
            'pid',
            $queryBuilder->createNamedParameter(42, Connection::PARAM_INT)
        )
    )
    ->executeQuery()
    ->fetchAllAssociative();

Read how to correctly instantiate a query builder with the connection pool.

fetchOne()

The method returns a single column from the next row of a result set, other columns from this result row are discarded. It is especially handy for QueryBuilder->count() queries:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php

// use TYPO3\CMS\Core\Database\Connection;
// Get the number of tt_content records on pid 42 into variable $numberOfRecords
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$numberOfRecords = $queryBuilder
    ->count('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq(
            'pid',
            $queryBuilder->createNamedParameter(42, Connection::PARAM_INT)
        )
    )
    ->executeQuery()
    ->fetchOne();

Read how to correctly instantiate a query builder with the connection pool.

Note

The Connection->count() implementation does exactly that to return the number of rows directly.

rowCount()

This method returns the number of rows affected by the last execution of this statement. Use this method instead of counting the number of records in a ->fetchAssociative() loop manually.

Warning

->rowCount() works well with DELETE, UPDATE and INSERT queries. However, it does not return a valid number for SELECT queries on some DBMSes. Never use ->rowCount() on SELECT queries. This may work with MySQL, but will fail with other databases like SQLite.

Reuse prepared statement

Doctrine DBAL usually prepares a statement first and then executes it with the given parameters. The implementation of prepared statements depends on the particular database driver. A driver that does not implement prepared statements properly falls back to a direct execution of a given query.

There is an API that allows to make real use of prepared statements. This is handy when the same query is executed over and over again with different arguments. The example below prepares a statement for the pages table and executes it twice with different arguments.

EXT:my_extension/Classes/Domain/Repository/MyRepository.php

// use TYPO3\CMS\Core\Database\Connection;
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
$statement = $queryBuilder
    ->select('uid')
    ->from('pages')
    ->where(
        $queryBuilder->expr()->eq(
            'uid',
            $queryBuilder->createPositionalParameter(0, Connection::PARAM_INT)
        )
    )
    ->prepare();

$pages = [];
foreach ([24, 25] as $pageId) {
    // Bind $pageId value to the first (and in this case only) positional parameter
 $statement->bindValue(1, $pageId, Connection::PARAM_INT);
 $result = $statement->executeQuery();
    $pages[] = $result->fetchAssociative();
    $result->free(); // free the resources for this result
}

Read how to correctly instantiate a query builder with the connection pool.

Looking at a MySQL debug log:

Prepare SELECT `uid` FROM `pages` WHERE `uid` = ?
Execute SELECT `uid` FROM `pages` WHERE `uid` = '24'
Execute SELECT `uid` FROM `pages` WHERE `uid` = '25'

The log shows one statement preparation with two executions.