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'