Attention

TYPO3 v8 has reached its end-of-life March 31st, 2020 and is not maintained by the community anymore. Looking for a stable version? Use the version switch on the top left.

There is no further ELTS support. It is recommended that you upgrade your project and use a supported version of TYPO3.

Basic CRUD

A list of basic usage examples of the query API. This is just a kickstart. Details on the single methods are found in the following chapters, especially QueryBuilder and Connection.

Note

The examples use the shorthand syntax for class names. Please refer to Class overview for the full namespace.

INSERT a row

A straight insert to a table:

GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tt_content')
    ->insert(
        'tt_content',
        [
            'pid' => (int)42,
            'bodytext' => 'bernd',
        ]
    );

INSERT INTO `tt_content` (`pid`, `bodytext`) VALUES ('42', 'bernd')

SELECT a single row

Straight fetch of a single row from tt_content table:

$uid = 4;
$row = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getConnectionForTable('tt_content')
    ->select(
        ['uid', 'pid', 'bodytext'], // fields to select
        'tt_content', // from
        [ 'uid' => (int)$uid ] // where
    )
    ->fetch();

Result in $row:

array(3 items)
   uid => 4 (integer)
   pid => 35 (integer)
   bodytext => 'some content' (12 chars)

The engine quotes field names, adds default TCA restrictions like "deleted=0", and prepares a query executed with this final statement:

SELECT `uid`, `pid`, `bodytext`
    FROM `tt_content`
    WHERE (`uid` = '4')
        AND ((`tt_content`.`deleted` = 0)
        AND (`tt_content`.`hidden` = 0)
        AND (`tt_content`.`starttime` <= 1473447660)
        AND ((`tt_content`.`endtime` = 0) OR (`tt_content`.`endtime` > 1473447660)))

Note

Default restrictions deleted, hidden, startime and endtime based on TCA setting of a table are only applied to select() calls, they are not added for delete() or other query types.

SELECT multiple rows with some WHERE magic

Advanced query using the QueryBuilder and manipulating the default restrictions:

$uid = 4;
// Get a query builder for a query on table "tt_content"
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
// Remove all default restrictions (delete, hidden, starttime, stoptime), but add DeletedRestriction again
$queryBuilder->getRestrictions()
    ->removeAll()
    ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
// Execute a query with "bodytext=klaus OR uid=4" and proper quoting
$rows = $queryBuilder
    ->select('uid', 'pid', 'bodytext')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->orX(
            $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus')),
            $queryBuilder->expr()->eq('uid', $queryBuilder->createNamedParameter($uid, \PDO::PARAM_INT))
        )
    )
    ->execute()
    ->fetchAll();

Result in $rows:

array(2 items)
   0 => array(3 items)
      uid => 4 (integer)
      pid => 35 (integer)
      bodytext => 'bernd' (5 chars)
   1 => array(3 items)
      uid => 366 (integer)
      pid => 13 (integer)
      bodytext => 'klaus' (5 chars)

The executed query looks like:

SELECT `uid`, `pid`, `bodytext`
    FROM `tt_content`
    WHERE ((`bodytext` = 'klaus') OR (`uid` = 4))
        AND (`tt_content`.`deleted` = 0)

UPDATE multiple rows

GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tt_content')
    ->update(
        'tt_content',
        [ 'bodytext' => 'bernd' ], // set
        [ 'bodytext' => 'klaus' ] // where
    );

UPDATE `tt_content` SET `bodytext` = 'bernd' WHERE `bodytext` = 'klaus'

DELETE a row

GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tt_content')
    ->delete(
        'tt_content', // from
        [ 'uid' => (int)4711 ] // where
    );

DELETE FROM `tt_content` WHERE `uid` = '4711'