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.

ノート

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

ノート

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'

ちなみに

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'