Db¶
\nn\t3::Db()¶
Simplify access to the most commonly used database operations for write, read, delete .
Overview of Methods¶
\nn\t3::Db()->debug($query = NULL, $return = false);¶
Debug MySQL Query
\nn\t3::Db()->debug( $query );
echo \nn\t3::Db()->debug( $query, true );
@return string\nn\t3::Db()->delete($table = '', $constraint = [], $reallyDelete = false);¶
Database entry löschen. Small and fine.
\nn\t3::Db()->delete('table', $uid);
\nn\t3::Db()->delete('table', ['uid_local'=>$uid]);
@return boolean\nn\t3::Db()->filterDataForTable($data = [], $table = '');¶
Keep in key/val array only elements whose keys also
exist in TCA for particular table.
| @return array
\nn\t3::Db()->findAll($table = '', $ignoreEnableFields = false);¶
action findAll Finds ALL entry
\nn\t3::Db()->findAll('fe_users');
\nn\t3::Db()->findAll('fe_users', true);
@return array\nn\t3::Db()->findByUid($table = '', $uid = NULL, $ignoreEnableFields = false);¶
Finds an entry based on the UID. Works even if frontends have not been initialized yet, e.g. while AuthentificationService is running or in the scheduler.
\nn\t3::Db()->findByUid('fe_user', 12);
\nn\t3::Db()->findByUid('fe_user', 12, true);
@param int $uid@return array\nn\t3::Db()->findByValues($table = NULL, $whereArr = [], $useLogicalOr = false, $ignoreEnableFields = false);¶
action findByCustomField. Finds ALL entries based on a desired field value. Works even if frontends have not been initialized yet,
\nn\t3::Db()->findByValues('fe_users', ['email'=>'david@99grad.de']);
@param string $table@param array $whereArr@param boolean $useLogicalOr@param boolean $ignoreEnableFields@return array\nn\t3::Db()->findOneByValues($table = NULL, $whereArr = [], $useLogicalOr = false, $ignoreEnableFields = false);¶
Finds ONE entry based on desired field values.
\nn\t3::Db()->findOneByValues('fe_users', ['email'=>'david@99grad.de']);
@param string $table@param array $whereArr@param boolean $useLogicalOr@param boolean $ignoreEnableFields@return array\nn\t3::Db()->getColumn($table = '', $colName = '', $useSchemaManager = false);¶
Get a table column (TCA) for specific table
\nn\t3::Db()->getColumn( 'tablename', 'fieldname' );
@return array\nn\t3::Db()->getColumnLabel($column = '', $table = '');¶
Get localized label of a specific TCA field.
| @return string
\nn\t3::Db()->getColumns($table = '', $useSchemaManager = false);¶
Get all table columns (TCA) for specific table
\nn\t3::Db()->getColumns( 'tablename' );
@return array\nn\t3::Db()->getColumnsByType($table = '', $colType = '', $useSchemaManager = false);¶
Get fields of a table by a specific type
\nn\t3::Db()->getColumnsByType( 'tx_news_domain_model_news', 'slug' );
@return array\nn\t3::Db()->getConnection();¶
Get a “raw” connection to the database. Only useful in real emergencies.
$connection = \nn\t3::Db()->getConnection();
$connection->fetchAll( 'SELECT FROM tt_news WHERE 1;' );
@return \TYPO3\CMS\Core\Database\Connection\nn\t3::Db()->getDeleteColumn($table = '');¶
Get delete column for specific table. This column is used as a flag for deleted records. Normally: deleted = 1
@return string\nn\t3::Db()->getQueryBuilder($table = '');¶
GetQueryBuilder for a table
$queryBuilder = \nn\t3::Db()->getQueryBuilder( 'fe_users' );
Example:
$queryBuilder = \nn\t3::Db()->getQueryBuilder( 'fe_users' );
$queryBuilder->select('name')->from( 'fe_users' );
$queryBuilder->andWhere( $queryBuilder->expr()->eq( 'uid', $queryBuilder->createNamedParameter(12) ));
$rows = $queryBuilder->execute()->fetchAll();
@param string $table@return QueryBuilder\nn\t3::Db()->getRepositoryForModel($className = NULL);¶
Get repository for a model.
\nn\t3::Db()->getRepositoryForModel( \My\Domain\Model\Name );
@return string\nn\t3::Db()->getTableNameForModel($className = NULL);¶
Get table name for a model.
Alias to \nn\t3::Obj()->getTableName()
\nn\t3::Db()->getTableNameForModel( \My\Domain\Model\Name );
@return string\nn\t3::Db()->insert($table = '', $data = []);¶
Insert database entry. Simple and foolproof. Returns array of inserted data including insertUid of entry
$insertArr = \nn\t3::Db()->insert('table', ['bodytext'=>'...']);
@return int\nn\t3::Db()->orderBy($queryOrRepository, $ordering = []);¶
Set sorting for a repository or query.
$ordering = ['title' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING];
\nn\t3::Db()->orderBy( $queryOrRepository, $ordering );
@return $queryOrRepository\nn\t3::Db()->quote($str = '');¶
A replacement for the mysqli_real_escape_string() method.
Should only be used in an emergency for low-level queries. Better to use,
preparedStatements.
Will only work with SQL, not DQL.
$sword = \nn\t3::Db()->quote($sword);
$sword = \nn\t3::Db()->quote('"; DROP TABLE fe_user;#');
@return string\nn\t3::Db()->setFalConstraint($queryBuilder = NULL, $tableName = '', $falFieldName = '', $numFal = true, $operator = false);¶
Add constraint for sys_file_reference to a QueryBuilder. Constrains the results to see if there is a FAL relation.
$queryBuilder = \nn\t3::Db()->getQueryBuilder( $table );
// Only get records that have at least one SysFileReference for falfield
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield' );
// ... that do NOT have a SysFileReference für falfield
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield', false );
// ... which have EXACTLY 2 SysFileReferences
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield', 2 );
// ... which have 2 or less (less than or equal) SysFileReferences
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield', 2, 'lte' );
@return $queryBuilder\nn\t3::Db()->setNotInSysCategoryConstraint($queryBuilder = NULL, $sysCategoryUids = [], $tableName = '', $categoryFieldName = 'categories');¶
Constrain contraint to records that are NOT in one of the specified categories.
Opposite and alias to \nn\t3::Db()->setSysCategoryConstraint()
$queryBuilder = \nn\t3::Db()->getQueryBuilder( $table );
\nn\t3::Db()->setNotInSysCategoryConstraint( $queryBuilder, [1,3,4], 'tx_myext_tablename', 'categories' );
@return $queryBuilder\nn\t3::Db()->setSysCategoryConstraint($queryBuilder = NULL, $sysCategoryUids = [], $tableName = '', $categoryFieldName = 'categories', $useNotIn = false);¶
Add constraint for sys_category / sys_category_record_mm to a QueryBuilder. Constrains the results to the specified sys-categories UIDs.
$queryBuilder = \nn\t3::Db()->getQueryBuilder( $table );
\nn\t3::Db()->setSysCategoryConstraint( $queryBuilder, [1,3,4], 'tx_myext_tablename', 'categories' );
@return $queryBuilder\nn\t3::Db()->sortBy($objectArray, $fieldName = 'uid', $uidList = []);¶
Sorts results of a query by an array and specific field.
Solves the problem that a ->in() query does not return the results
in the order of the IDs passed. Example:
| $query->matching($query->in('uid', [3,1,2])); does not necessarily come up
in the order [3,1,2] comes back.
$insertArr = \nn\t3::Db()->sortBy( $storageOrArray, 'uid', [2,1,5]);
@return array\nn\t3::Db()->statement($statement = '', $params = []);¶
Submit a “raw” query to the database. You can’t get any closer to the database. You are responsible for everything yourself. Injections are only opposed by your (hopefully sufficient :) intelligence.
Helps e.g. with queries of tables, which are not part of the Typo3 installation and are therefore therefore could not be reached via the normal QueryBuilder.
// ALWAYS escape variables über!
$keyword = \nn\t3::Db()->quote('search term');
$rows = \nn\t3::Db()->statement( "SELECT FROM tt_news WHERE bodytext LIKE '%${keyword}%'");
// or better use prepared statements:
$rows = \nn\t3::Db()->statement( 'SELECT FROM tt_news WHERE bodytext LIKE :str', ['str'=>"%${keyword}%"] );
For a SELECT statement, the rows are returned from the database as an array.
For all other statements (e.g., UPDATE or DELETE), the number of rows involved is returned.
@return mixed\nn\t3::Db()->tableExists($table = '');¶
Exists a specific DB table?
$exists = \nn\t3::Db()->tableExists('table');
@return boolean\nn\t3::Db()->truncate($table = '');¶
Empty database;
\nn\t3::Db()->truncate('table');
@return boolean\nn\t3::Db()->undelete($table = '', $constraint = []);¶
Restore database entry
nnt3::Db()->undelete(‘table’, $uid); nnt3::Db()->undelete(‘table’, [‘uid_local’=>$uid]);
@return boolean\nn\t3::Db()->update($table = '', $data = [], $uid = NULL);¶
Update database entry. Quick and easy.
\nn\t3::Db()->update('table', [], 1);
\nn\t3::Db()->update('table', [], ['email'=>'david@99grad.de', 'pid'=>12, ...]);
Using true instead of a $uid will update ALL records in the table.
\nn\t3::Db()->update('table', ['test'=>1], true);
@return boolean