In my former post [1] I focused mainly on the issue to migrate existing DB calls from TYPO3-DB to Doctrine DBAL. This one is about writing tiny vanilla DB statements without any „special“ clauses. Only for these „Gimme everything from one table“ or „Just delete that one row“ situations.
Getting the connection
First you have to get your connection like you did it in the good ol‘ days by declaring $databaseConnection = $GLOBALS['TYPO3_DB'] .
|
1 2 |
$databaseConnection = GeneralUtility::makeInstance(ConnectionPool::class) ->getConnectionForTable('tx_mytable'); |
Select
The Select statement is described in a little bit more detailed. The other examples will become shorter, but I am sure, you’ll able to adapt, if you know a bit about database statements.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
$result = $databaseConnection->select( // The columns of the table which to select. // Simply ['*'] would also be possible here of course. [ 'column1', 'column2', 'column3' ], // The name of the table on which to select. 'tx_mytable', // The selection criteria. An associative array containing column-value pairs (a.k.a. "where") // All statements will connected later in the query with "AND". // (optional) [ 'uid' => $selectUid, 'title' => 'someRandomTitle' ], // The columns to group the results by. // (optional) [ 'column23' ], // Associative array of column name/sort directions pairs. // (optional) [ 'title' => 'ASC' ], // The maximum number of rows to return. // (optional) 23, // The first result row to select (when used with limit). // (optional) 42 ); |
So „Gimme all pages“ will simply be
|
1 2 3 4 |
$result = $databaseConnection->select( ['*'], ['pages'] ); |
If you are using this short syntax style to create your result sets, you have not much influence to the restrictions. So everything that is deleted or hidden (e.g. in the frontend) will not be selected. The only thing left you have to do is to iterate over the result set with e.g. foreach ($result->fetchAll() as $row) …
Insert
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$result = $databaseConnection->insert( // The name of the table to insert data into. 'tx_mytable' // An associative array containing column-value pairs. [ 'title' => 'Some nice title', 'body' => 'Lorem ipsum dolor sit amed' ] // Types of the inserted data. // (optional) [] ); |
Update
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$result = $databaseConnection->update( // The name of the table to update. 'tx_mytable' // An associative array containing column-value pairs. [ 'title' => 'Some nice new title', 'body' => 'More lorem ipsum dolor sit amed' ] // The update criteria. An associative array containing column-value pairs (a.k.a "where"). [ 'uid' => $selectUid, 'title' => 'someRandomTitle' ], // Types of the merged $data and $identifier arrays [] ); |
Delete
|
1 2 3 4 5 6 7 8 9 10 |
$result = $databaseConnection->update( // The name of the table on which to delete. 'tx_mytable' // The deletion criteria. An associative array containing column-value pairs. [ 'uid' => $selectUid ], // The types of identifiers. [] ); |
Conclusion
There are a couple of other functions, that I didn’t want to discuss here, either because they are more or less self explaining (e.g. ->count()) or in my opinion a kind of risk (e.g. -> truncate()). If you want to have more and detailed informations about this, check out the file typo3/sysext/core/Classes/Database/Connection.php which is responsible for all of the former stuff.
To be honest, I am a little bit unsure about this toolset. On the one hand, it could be a nice addition, if you need small statements. But on the other hand you’ll have to learn the complex way nevertheless as described in [1], because you have to walk that road latest when you need more control over your statements („more control“ as in „restrictions“ and „joins“).
Links
[1] Migrate from the TYPO3 database wrapper to the Doctrine DBAL syntax
Bei „Delete“ steht „->update“ im Codebeispiel