API: class.t3lib

class.t3lib_db.php

TYPO3 "database wrapper" class (new in 3.6.0)
This class contains
- abstraction functions for executing INSERT/UPDATE/DELETE/SELECT queries ("Query execution"; These are REQUIRED for all future connectivity to the database, thus ensuring DBAL compliance!)
- functions for building SQL queries (INSERT/UPDATE/DELETE/SELECT) ("Query building"); These are transitional functions for building SQL queries in a more automated way. Use these to build queries instead of doing it manually in your code!
- mysql() wrapper functions; These are transitional functions. By a simple search/replace you should be able to substitute all mysql*() calls with $GLOBALS['TYPO3_DB']->sql*() and your application will work out of the box. YOU CANNOT (legally) use any mysql functions not found as wrapper functions in this class!
See the Project Coding Guidelines (doc_core_cgl) for more instructions on best-practise

This class is not in itself a complete database abstraction layer but can be extended to be a DBAL (by extensions, see "dbal" for example)
ALL connectivity to the database in TYPO3 must be done through this class!
The points of this class are:
- To direct all database calls through this class so it becomes possible to implement DBAL with extensions.
- To keep it very easy to use for developers used to MySQL in PHP - and preserve as much performance as possible when TYPO3 is used with MySQL directly...
- To create an interface for DBAL implemented by extensions; (Eg. making possible escaping characters, clob/blob handling, reserved words handling)
- Benchmarking the DB bottleneck queries will become much easier; Will make it easier to find optimization possibilities.

USE:
In all TYPO3 scripts the global variable $TYPO3_DB is an instance of this class. Use that.
Eg. $GLOBALS['TYPO3_DB']->sql_fetch_assoc()

Files: 1
Total filesizes: 38 Kbytes
Functions and classes: 43

class.t3lib_db.php

class t3lib_DB

@package: TYPO3

@subpackage: t3lib

Query execution
These functions are the RECOMMENDED DBAL functions for use in your applications
Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
They compile a query AND execute it immediately and then return the result
This principle heightens our ability to create various forms of DBAL of the functions.
Generally: We want to return a result pointer/object, never queries.
Also, having the table name together with the actual query execution allows us to direct the request to other databases.

function exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE)

function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)

function exec_DELETEquery($table,$where)

function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')

function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')

function exec_SELECT_queryArray($queryParts)

function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='')

Query building

function INSERTquery($table,$fields_values,$no_quote_fields=FALSE)

function UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)

function DELETEquery($table,$where)

function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')

function listQuery($field, $value, $table)

function searchQuery($searchWords,$fields,$table)

Various helper functions
Functions recommended to be used for
- escaping values,
- cleaning lists of values,
- stripping of excess ORDER BY/GROUP BY keywords

function fullQuoteStr($str, $table)

function fullQuoteArray($arr, $table, $noQuote=FALSE)

function quoteStr($str, $table)

function escapeStrForLike($str, $table)

function cleanIntArray($arr)

function cleanIntList($list)

function stripOrderBy($str)

function stripGroupBy($str)

function splitGroupOrderLimit($str)

MySQL wrapper functions
(For use in your applications)

function sql($db,$query)

function sql_query($query)

function sql_error()

function sql_num_rows($res)

function sql_fetch_assoc($res)

function sql_fetch_row($res)

function sql_free_result($res)

function sql_insert_id()

function sql_affected_rows()

function sql_data_seek($res,$seek)

function sql_field_type($res,$pointer)

function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)

function sql_select_db($TYPO3_db)

SQL admin functions
(For use in the Install Tool and Extension Manager)

function admin_get_dbs()

function admin_get_tables()

function admin_get_fields($tableName)

function admin_get_keys($tableName)

function admin_query($query)

Connecting service

function connectDB()

Debugging

function debug($func)

Detailed descriptions:

class.t3lib_db.php

Filesize:	38 K
Func/Classes:	43
Tags:	@author Kasper Skaarhoj <kasperYYYY@typo3.com> @package TYPO3 @subpackage t3lib

class t3lib_DB

Returns:	[type]	...
	@author Kasper Skaarhoj <kasperYYYY@typo3.com>
	@package TYPO3
	@subpackage t3lib

function exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE)

Creates and executes an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
Using this function specifically allows us to handle BLOB and CLOB fields depending on DB
Usage count/core: 47

$table	string	Table name
$fields_values	array	Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$insertFields" with 'fieldname'=>'value' and pass it to this function as argument.
$no_quote_fields=FALSE	string/array	See fullQuoteArray()
Returns:	pointer	MySQL result pointer / DBAL object

function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)

Creates and executes an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
Using this function specifically allow us to handle BLOB and CLOB fields depending on DB
Usage count/core: 50

$table	string	Database tablename
$where	string	WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
$fields_values	array	Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$updateFields" with 'fieldname'=>'value' and pass it to this function as argument.
$no_quote_fields=FALSE	string/array	See fullQuoteArray()
Returns:	pointer	MySQL result pointer / DBAL object

function exec_DELETEquery($table,$where)

Creates and executes a DELETE SQL-statement for $table where $where-clause
Usage count/core: 40

$table	string	Database tablename
$where	string	WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
Returns:	pointer	MySQL result pointer / DBAL object

function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')

Creates and executes a SELECT SQL-statement
Using this function specifically allow us to handle the LIMIT feature independently of DB.
Usage count/core: 340

$select_fields	string	List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
$from_table	string	Table(s) from which to select. This is what comes right after "FROM ...". Required value.
$where_clause	string	Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
$groupBy=''	string	Optional GROUP BY field(s), if none, supply blank string.
$orderBy=''	string	Optional ORDER BY field(s), if none, supply blank string.
$limit=''	string	Optional LIMIT value ([begin,]max), if none, supply blank string.
Returns:	pointer	MySQL result pointer / DBAL object

function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')

Creates and executes a SELECT query, selecting fields ($select) from two/three tables joined
Use $mm_table together with $local_table or $foreign_table to select over two tables. Or use all three tables to select the full MM-relation.
The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local / [$mm_table].uid_foreign <--> [$foreign_table].uid
The function is very useful for selecting MM-relations between tables adhering to the MM-format used by TCE (TYPO3 Core Engine). See the section on $TCA in Inside TYPO3 for more details.

Usage: 12 (spec. ext. sys_action, sys_messages, sys_todos)

$select	string	Field list for SELECT
$local_table	string	Tablename, local table
$mm_table	string	Tablename, relation table
$foreign_table	string	Tablename, foreign table
$whereClause=''	string	Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT! You have to prepend 'AND ' to this parameter yourself!
$groupBy=''	string	Optional GROUP BY field(s), if none, supply blank string.
$orderBy=''	string	Optional ORDER BY field(s), if none, supply blank string.
$limit=''	string	Optional LIMIT value ([begin,]max), if none, supply blank string.
Returns:	pointer	MySQL result pointer / DBAL object
	@see exec_SELECTquery()

function exec_SELECT_queryArray($queryParts)

Executes a select based on input query parts array

Usage: 9

$queryParts	array	Query parts array
Returns:	pointer	MySQL select result pointer / DBAL object
	@see exec_SELECTquery()

function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='')

Creates and executes a SELECT SQL-statement AND traverse result set and returns array with records in.

$select_fields	string	See exec_SELECTquery()
$from_table	string	See exec_SELECTquery()
$where_clause	string	See exec_SELECTquery()
$groupBy=''	string	See exec_SELECTquery()
$orderBy=''	string	See exec_SELECTquery()
$limit=''	string	See exec_SELECTquery()
$uidIndexField=''	string	If set, the result array will carry this field names value as index. Requires that field to be selected of course!
Returns:	array	Array of rows.

function INSERTquery($table,$fields_values,$no_quote_fields=FALSE)

Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
Usage count/core: 4

$table	string	See exec_INSERTquery()
$fields_values	array	See exec_INSERTquery()
$no_quote_fields=FALSE	string/array	See fullQuoteArray()
Returns:	string	Full SQL query for INSERT (unless $fields_values does not contain any elements in which case it will be false)
	@deprecated use exec_INSERTquery() instead if possible!

function UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)

Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
Usage count/core: 6

$table	string	See exec_UPDATEquery()
$where	string	See exec_UPDATEquery()
$fields_values	array	See exec_UPDATEquery()
$no_quote_fields=FALSE	array	See fullQuoteArray()
Returns:	string	Full SQL query for UPDATE (unless $fields_values does not contain any elements in which case it will be false)
	@deprecated use exec_UPDATEquery() instead if possible!

function DELETEquery($table,$where)

Creates a DELETE SQL-statement for $table where $where-clause
Usage count/core: 3

$table	string	See exec_DELETEquery()
$where	string	See exec_DELETEquery()
Returns:	string	Full SQL query for DELETE
	@deprecated use exec_DELETEquery() instead if possible!

function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')

Creates a SELECT SQL-statement
Usage count/core: 11

$select_fields	string	See exec_SELECTquery()
$from_table	string	See exec_SELECTquery()
$where_clause	string	See exec_SELECTquery()
$groupBy=''	string	See exec_SELECTquery()
$orderBy=''	string	See exec_SELECTquery()
$limit=''	string	See exec_SELECTquery()
Returns:	string	Full SQL query for SELECT
	@deprecated use exec_SELECTquery() instead if possible!

function listQuery($field, $value, $table)

Returns a WHERE clause that can find a value ($value) in a list field ($field)
For instance a record in the database might contain a list of numbers, "34,234,5" (with no spaces between). This query would be able to select that record based on the value "34", "234" or "5" regardless of their positioni in the list (left, middle or right).
Is nice to look up list-relations to records or files in TYPO3 database tables.

$field	string	Field name
$value	string	Value to find in list
$table	string	Table in which we are searching (for DBAL detection of quoteStr() method)
Returns:	string	WHERE clause for a query

function searchQuery($searchWords,$fields,$table)

Returns a WHERE clause which will make an AND search for the words in the $searchWords array in any of the fields in array $fields.

$searchWords	array	Array of search words
$fields	array	Array of fields
$table	string	Table in which we are searching (for DBAL detection of quoteStr() method)
Returns:	string	WHERE clause for search

function fullQuoteStr($str, $table)

Escaping and quoting values for SQL statements.
Usage count/core: 100

$str	string	Input string
$table	string	Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
Returns:	string	Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
	@see quoteStr()

function fullQuoteArray($arr, $table, $noQuote=FALSE)

Will fullquote all values in the one-dimensional array so they are ready to "implode" for an sql query.

$arr	array	Array with values (either associative or non-associative array)
$table	string	Table name for which to quote
$noQuote=FALSE	string/array	List/array of keys NOT to quote (eg. SQL functions) - ONLY for associative arrays
Returns:	array	The input array with the values quoted
	@see cleanIntArray()

function quoteStr($str, $table)

Substitution for PHP function "addslashes()"
Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
NOTICE: You must wrap the output of this function in SINGLE QUOTES to be DBAL compatible. Unless you have to apply the single quotes yourself you should rather use ->fullQuoteStr()!

Usage count/core: 20

$str	string	Input string
$table	string	Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
Returns:	string	Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
	@see quoteStr()

function escapeStrForLike($str, $table)

Escaping values for SQL LIKE statements.

$str	string	Input string
$table	string	Table name for which to escape string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
Returns:	string	Output string; % and _ will be escaped with \ (or otherwise based on DBAL handler)
	@see quoteStr()

function cleanIntArray($arr)

Will convert all values in the one-dimensional array to integers.
Useful when you want to make sure an array contains only integers before imploding them in a select-list.
Usage count/core: 7

$arr	array	Array with values
Returns:	array	The input array with all values passed through intval()
	@see cleanIntList()

function cleanIntList($list)

Will force all entries in the input comma list to integers
Useful when you want to make sure a commalist of supposed integers really contain only integers; You want to know that when you don't trust content that could go into an SQL statement.
Usage count/core: 6

$list	string	List of comma-separated values which should be integers
Returns:	string	The input list but with every value passed through intval()
	@see cleanIntArray()

function stripOrderBy($str)

Removes the prefix "ORDER BY" from the input string.
This function is used when you call the exec_SELECTquery() function and want to pass the ORDER BY parameter by can't guarantee that "ORDER BY" is not prefixed.
Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
Usage count/core: 11

$str	string	eg. "ORDER BY title, uid"
Returns:	string	eg. "title, uid"
	@see exec_SELECTquery(), stripGroupBy()

function stripGroupBy($str)

Removes the prefix "GROUP BY" from the input string.
This function is used when you call the SELECTquery() function and want to pass the GROUP BY parameter by can't guarantee that "GROUP BY" is not prefixed.
Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
Usage count/core: 1

$str	string	eg. "GROUP BY title, uid"
Returns:	string	eg. "title, uid"
	@see exec_SELECTquery(), stripOrderBy()

function splitGroupOrderLimit($str)

Takes the last part of a query, eg. "... uid=123 GROUP BY title ORDER BY title LIMIT 5,2" and splits each part into a table (WHERE, GROUPBY, ORDERBY, LIMIT)
Work-around function for use where you know some userdefined end to an SQL clause is supplied and you need to separate these factors.
Usage count/core: 13

$str	string	Input string
Returns:	array

function sql($db,$query)

Executes query
mysql() wrapper function
DEPRECATED - use exec_* functions from this class instead!
Usage count/core: 9

$db	string	Database name
$query	string	Query to execute
Returns:	pointer	Result pointer / DBAL object

function sql_query($query)

Executes query
mysql_query() wrapper function
Usage count/core: 1

$query	string	Query to execute
Returns:	pointer	Result pointer / DBAL object

function sql_error()

Returns the error status on the last sql() execution
mysql_error() wrapper function
Usage count/core: 32

Returns:

string

MySQL error string.

function sql_num_rows($res)

Returns the number of selected rows.
mysql_num_rows() wrapper function
Usage count/core: 85

$res	pointer	MySQL result pointer (of SELECT query) / DBAL object
Returns:	integer	Number of resulting rows.

function sql_fetch_assoc($res)

Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
mysql_fetch_assoc() wrapper function
Usage count/core: 307

$res	pointer	MySQL result pointer (of SELECT query) / DBAL object
Returns:	array	Associative array of result row.

function sql_fetch_row($res)

Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
The array contains the values in numerical indices.
mysql_fetch_row() wrapper function
Usage count/core: 56

$res	pointer	MySQL result pointer (of SELECT query) / DBAL object
Returns:	array	Array with result rows.

function sql_free_result($res)

Free result memory
mysql_free_result() wrapper function
Usage count/core: 3

$res	pointer	MySQL result pointer to free / DBAL object
Returns:	boolean	Returns TRUE on success or FALSE on failure.

function sql_insert_id()

Get the ID generated from the previous INSERT operation
mysql_insert_id() wrapper function
Usage count/core: 13

Returns:

integer

The uid of the last inserted record.

function sql_affected_rows()

Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
mysql_affected_rows() wrapper function
Usage count/core: 1

Returns:

integer

Number of rows affected by last query

function sql_data_seek($res,$seek)

Move internal result pointer
mysql_data_seek() wrapper function
Usage count/core: 3

$res	pointer	MySQL result pointer (of SELECT query) / DBAL object
$seek	integer	Seek result number.
Returns:	boolean	Returns TRUE on success or FALSE on failure.

function sql_field_type($res,$pointer)

Get the type of the specified field in a result
mysql_field_type() wrapper function
Usage count/core: 2

$res	pointer	MySQL result pointer (of SELECT query) / DBAL object
$pointer	integer	Field index.
Returns:	string	Returns the name of the specified field index

function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)

Open a (persistent) connection to a MySQL server
mysql_pconnect() wrapper function
Usage count/core: 12

$TYPO3_db_host	string	Database host IP/domain
$TYPO3_db_username	string	Username to connect with.
$TYPO3_db_password	string	Password to connect with.
Returns:	pointer	Returns a positive MySQL persistent link identifier on success, or FALSE on error.

function sql_select_db($TYPO3_db)

Select a MySQL database
mysql_select_db() wrapper function
Usage count/core: 8

$TYPO3_db	string	Database to connect to.
Returns:	boolean	Returns TRUE on success or FALSE on failure.

function admin_get_dbs()

Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
This is only used as a service function in the (1-2-3 process) of the Install Tool. In any case a lookup should be done in the _DEFAULT handler DBMS then.
Use in Install Tool only!
Usage count/core: 1

Returns:

array

Each entry represents a database name

function admin_get_tables()

Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
In a DBAL this method should 1) look up all tables from the DBMS of the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
Usage count/core: 2

Returns:

array

Tables in an array (tablename is in both key and value)

function admin_get_fields($tableName)

Returns information about each field in the $table (quering the DBMS)
In a DBAL this should look up the right handler for the table and return compatible information
This function is important not only for the Install Tool but probably for DBALs as well since they might need to look up table specific information in order to construct correct queries. In such cases this information should probably be cached for quick delivery.

$tableName	string	Table name
Returns:	array	Field information in an associative array with fieldname => field row

function admin_get_keys($tableName)

Returns information about each index key in the $table (quering the DBMS)
In a DBAL this should look up the right handler for the table and return compatible information

$tableName	string	Table name
Returns:	array	Key information in a numeric array

function admin_query($query)

mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
Usage count/core: 10

$query	string	Query to execute
Returns:	pointer	Result pointer

function connectDB()

Connects to database for TYPO3 sites:

Returns:

void

function debug($func)

Debug function: Outputs error if any

$func	string	Function calling debug()
Returns:	void

class.t3lib_db.php

Query building

Various helper functions Functions recommended to be used for - escaping values, - cleaning lists of values, - stripping of excess ORDER BY/GROUP BY keywords

MySQL wrapper functions (For use in your applications)

SQL admin functions (For use in the Install Tool and Extension Manager)

Connecting service

Debugging

Detailed descriptions:

Various helper functions
Functions recommended to be used for
- escaping values,
- cleaning lists of values,
- stripping of excess ORDER BY/GROUP BY keywords

MySQL wrapper functions
(For use in your applications)

SQL admin functions
(For use in the Install Tool and Extension Manager)