Attention

TYPO3 v9 has reached its end-of-life September 30th, 2021 and is not maintained by the community anymore. Looking for a stable version? Use the version switch on the top left.

You can order Extended Long Term Support (ELTS) here: TYPO3 ELTS.

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:

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;

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:

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;

$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:

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction

$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

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;

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'

Tip

You can also use QueryBuilder for generating more complex update queries. See examples in the QueryBuilder chapter.

DELETE a Row

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;

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

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