1. Home
  2. Blog
  3. Contact
Blogitems
27Oct

Using the database API

Posted on October 27th 2010 at 09:00 inside Extension Development with 6 comments

Many times, when taking a short peek through new extensions in the TER, I find plenty of extensions that use PHP's native MySQL functions to access the database. In my previous post I already described why this something wrong, and why you should avoid it. In this article I will show you how it should be done, and how you can write less and cleaner code by doing so.

Accessing data with SELECT queries

The database API comes with several functions to do this. If we take a look in 't3lib/class.t3lib_db.php' we can see what is available.

  • exec_SELECTquery
  • exec_SELECT_mm_query
    Selecting data from with MM (more-more) relations
  • exec_SELECT_queryArray()
  • exec_SELECTgetRows
    Doing the full selection, and simply returning an array containing the results of the query. (continue reading to see an example).

Now let's see how the usage will be, this should give you a more clear image of how these wrapper functions work.

exec_SELECTquery 

  1. // The syntax
  2. exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '');
  3.  
  4. // A real-life example: The 10 pages that have the most subpages
  5. $result = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
  6.     'COUNT(0) AS total_pages, pid',
  7.     'pages',
  8.     'pid > 1',
  9.     'pid',
  10.     'total_pages DESC',
  11.     '10'
  12. );
// The syntax
exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '');

// A real-life example: The 10 pages that have the most subpages
$result = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
    'COUNT(0) AS total_pages, pid',
    'pages',
    'pid > 1',
    'pid',
    'total_pages DESC',
    '10'
);

As you see, the query is simply split up into small parts. Rather then writing a full SQL statement in a single string. In my opinion, if you write it down like I do it will be more easily readable than a complete SQL statement.

exec_SELECT_mm_query 

  1. // The syntax
  2. exec_SELECT_mm_query(
  3.     $select, 
  4.     $local_table, 
  5.     $mm_table, 
  6.     $foreign_table, 
  7.     $whereClause = '', 
  8.     $groupBy = '', 
  9.     $orderBy = '', 
  10.     $limit = ''
  11. );
  12.  
  13. // A real-life example: The 10 latest news articles from category 1
  14. $GLOBALS['TYPO3_DB']->exec_SELECT_mm_query(
  15.     '*',
  16.     'tt_news',
  17.     'tt_news_cat_mm',
  18.     'tt_news_cat',
  19.     'tt_news_cat.uid = 1',
  20.     '',
  21.     'tt_news.uid DESC',
  22.     '10'
  23. );
// The syntax
exec_SELECT_mm_query(
    $select,
    $local_table,
    $mm_table,
    $foreign_table,
    $whereClause = '',
    $groupBy = '',
    $orderBy = '',
    $limit = ''
);

// A real-life example: The 10 latest news articles from category 1
$GLOBALS['TYPO3_DB']->exec_SELECT_mm_query(
    '*',
    'tt_news',
    'tt_news_cat_mm',
    'tt_news_cat',
    'tt_news_cat.uid = 1',
    '',
    'tt_news.uid DESC',
    '10'
);

Same story as before, easy to construct, easy to read. If you prefer to see what you are writing, or you just don't want to remember the order in which you are writing it, you may want to have a look at the following.

exec_SELECT_queryArray 

  1. // The syntax
  2. exec_SELECT_queryArray($queryParts);
  3.  
  4. // The actual function
  5. function exec_SELECT_queryArray($queryParts) {
  6.         return $this->exec_SELECTquery(
  7.                 $queryParts['SELECT'],
  8.                 $queryParts['FROM'],
  9.                 $queryParts['WHERE'],
  10.                 $queryParts['GROUPBY'],
  11.                 $queryParts['ORDERBY'],
  12.                 $queryParts['LIMIT']
  13.         );
  14. }
  15.  
  16. // A real-life example: Selecting all SysFolders
  17. $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray(array(
  18.         'SELECT' => '*',
  19.         'FROM' => 'pages',
  20.         'WHERE' => 'doktype = 254'
  21. ));
// The syntax
exec_SELECT_queryArray($queryParts);

// The actual function
function exec_SELECT_queryArray($queryParts) {
	return $this->exec_SELECTquery(
		$queryParts['SELECT'],
		$queryParts['FROM'],
		$queryParts['WHERE'],
		$queryParts['GROUPBY'],
		$queryParts['ORDERBY'],
		$queryParts['LIMIT']
	);
}

// A real-life example: Selecting all SysFolders
$GLOBALS['TYPO3_DB']->exec_SELECT_queryArray(array(
	'SELECT' => '*',
	'FROM' => 'pages',
	'WHERE' => 'doktype = 254'
));

The reason that I show the actual function as well, is because it's nice to see what it actually does. This way selecting data is a little easier to read back in your code. Especially if you are not familiar to the database API wrappers. Unfortunately it can't be applied everywhere (not with MM for example).

exec_SELECTgetRows 

  1. // The syntax
  2. exec_SELECTgetRows(
  3.     $select_fields, 
  4.     $from_table, 
  5.     $where_clause, 
  6.     $groupBy = '', 
  7.     $orderBy = '', 
  8.     $limit = '', 
  9.     $uidIndexField = ''
  10. );
  11.  
  12. // A real-life example: Select all news records on page 11
  13. $rows = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
  14.     '*',
  15.     'tt_news',
  16.     'pid = 11'
  17. );
// The syntax
exec_SELECTgetRows(
    $select_fields,
    $from_table,
    $where_clause,
    $groupBy = '',
    $orderBy = '',
    $limit = '',
    $uidIndexField = ''
);

// A real-life example: Select all news records on page 11
$rows = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
    '*',
    'tt_news',
    'pid = 11'
);

This is my personal favorite. It combines the whole cycle of executing the query and fetching the data, and simply returns an array containing the results. This is ideal for most selection queries, since you won't have write the whole cycle of getting data to your array every time.

Inserting, updating and deleting

For this there aren't as many options as for selection. Each have one main function which you can use to do what you need to do. Let's have a look at them shall we?

exec_INSERTquery 

  1. // The syntax
  2. exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE)
  3.  
  4. // A real-life example: Inserting a front-end user
  5. $userData = array(
  6.     'username' => 'sebastiaan'
  7. );
  8. $GLOBALS['TYPO3_DB']->exec_INSERquery(
  9.     'fe_users',
  10.     $userData
  11. );
// The syntax
exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE)

// A real-life example: Inserting a front-end user
$userData = array(
    'username' => 'sebastiaan'
);
$GLOBALS['TYPO3_DB']->exec_INSERquery(
    'fe_users',
    $userData
);

As you see this is and easy way of inserting data into your database, without the fuss of writing and entire SQL statement and exploding your data array twice to get your pleased result.

Something nice about the INSERT and UPDATE functions is that the values that are inserted/updated are automatically quoted and escaped. You won't need to worry about these fields.

Remember, you do need to worry about anything that comes inside 'WHERE' clauses. No automatic escaping is done there.

Next to the easily understandable first 2 parameters, there is a third called 'no_quote_fields'. This can be used to prevent the API from escaping and quoting specified fields. This can be useful for example, when you are using a function to generate a field value.

exec_UPDATEquery 

  1. // The syntax
  2. exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE)
  3.  
  4. // A real-life example: Updating a front-end user
  5. $userData = array(
  6.     'email' => 'someone@somewhere.com'
  7. );
  8. $GLOBALS['TYPO3_DB']->exec_UPDATEquery(
  9.     'fe_users',
  10.     'uid = '.$GLOBALS['TSFE']->fe_user->user['uid'],
  11.     $userData
  12. );
// The syntax
exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE)

// A real-life example: Updating a front-end user
$userData = array(
    'email' => 'someone@somewhere.com'
);
$GLOBALS['TYPO3_DB']->exec_UPDATEquery(
    'fe_users',
    'uid = '.$GLOBALS['TSFE']->fe_user->user['uid'],
    $userData
);

Again a simple but logical syntax. It's pretty similar to the syntax of the INSERT, we just add an extra WHERE clause (like in a real SQL statement).

exec_DELETEquery 

  1. // The syntax
  2. exec_DELETEquery($table, $where)
  3.  
  4. // A real-life example: Deleting a news article
  5. $GLOBALS['TYPO3_DB']->exec_DELETEquery(
  6.     'tt_news',
  7.     'uid = 10'
  8. );
// The syntax
exec_DELETEquery($table, $where)

// A real-life example: Deleting a news article
$GLOBALS['TYPO3_DB']->exec_DELETEquery(
    'tt_news',
    'uid = 10'
);

Need I say more?

Please do be aware that this query will delete the actual record from the database! If you want the use the safe delete function which can be enabled for records you should update the record and set 'deleted=1' instead!

Security features

As I already showed with the UPDATE and INSERT wrappers, there is the possibility to automatically 'quote' your inputs. Unfortunately this only works for these data arrays. Since it's easy to distinguish between the fields and the value. And we aren't bothered by any additional SQL syntax code.

Of course we are all familiar with the mysql_real_escape_string() kind of functions (or at least I hope we are), but then again they aren't dynamic enough. Therefore the database API comes with some cool solutions for that.

Escaping strings and values

Escaping strings and values 

  1. // Fully quoting a string; Syntax:
  2. fullQuoteStr($str, $table);
  3. $string = $GLOBALS['TYPO3_DB']->fullQuoteStr("BLA 'BLA",'pages');
  4. // Result: 'BLA \'BLA'
  5.  
  6. // Performing the same function but over an array of values
  7. fullQuoteArray($arr, $table, $noQuote=FALSE)
  8.  
  9. // Just quoting (escaping) a string
  10. quoteStr($str, $table)
  11. $string = $GLOBALS['TYPO3_DB']->fullQuoteStr("BLA 'BLA",'pages');
  12. // Result: BLA \'BLA
  13.  
  14. // Escaping strings for use inside LIKE, unfortunately only replaces the % inside the strings, doesn't do
  15. // any form of quoting.
  16. function escapeStrForLike($str, $table)
  17.  
  18. // Intval()-ing over an arry
  19. function cleanIntArray($arr)
  20.  
  21. // Intval()-ing over a list (comma seperated values)
  22. function cleanIntList($list)
// Fully quoting a string; Syntax:
fullQuoteStr($str, $table);
$string = $GLOBALS['TYPO3_DB']->fullQuoteStr("BLA 'BLA",'pages');
// Result: 'BLA \'BLA'

// Performing the same function but over an array of values
fullQuoteArray($arr, $table, $noQuote=FALSE)

// Just quoting (escaping) a string
quoteStr($str, $table)
$string = $GLOBALS['TYPO3_DB']->fullQuoteStr("BLA 'BLA",'pages');
// Result: BLA \'BLA

// Escaping strings for use inside LIKE, unfortunately only replaces the % inside the strings, doesn't do
// any form of quoting.
function escapeStrForLike($str, $table)

// Intval()-ing over an arry
function cleanIntArray($arr)

// Intval()-ing over a list (comma seperated values)
function cleanIntList($list)

Other (big) helpers

Not only can this API help us make our code more secure, it can also help us to simplify and write less code. Take a look at the following 2 functions for example. They are really awesome, and many people will have never heard of them.

The list query 

  1. // Syntax
  2. listQuery($field, $value, $table)
  3.  
  4. // An example: Selecting users that are inside usergroup with uid 4
  5. $where = $GLOBALS['TYPO3_DB']->listQuery('usergroup',4,'fe_users');
  6. // The result will be:
  7. // (usergroup LIKE '%,4,%' OR  usergroup LIKE '4,%' OR usergroup LIKE '%,4' OR usergroup  = '4')
// Syntax
listQuery($field, $value, $table)

// An example: Selecting users that are inside usergroup with uid 4
$where = $GLOBALS['TYPO3_DB']->listQuery('usergroup',4,'fe_users');
// The result will be:
// (usergroup LIKE '%,4,%' OR  usergroup LIKE '4,%' OR usergroup LIKE '%,4' OR usergroup  = '4')

This one is used for finding matches inside comma-separated values, this is often used for storing relations to other records or multiple files. Imagine writing this yourself every time you need it?

Search Query 

  1. // Syntax
  2. searchQuery($searchWords, $fields, $table)
  3.  
  4. // An example
  5. $words = array('I','Like','TYPO3');
  6. $fields = array('title','bodytext');
  7. $where = $GLOBALS['TYPO3_DB']->searchQuery($words,$fields,'tt_news');
  8.  
  9. // Result:
  10. // (tt_news.title LIKE '%I%' OR tt_news.bodytext LIKE '%I%') AND
  11. // (tt_news.title LIKE '%Like%' OR tt_news.bodytext LIKE '%Like%') AND
  12. // (tt_news.title LIKE '%TYPO3%' OR tt_news.bodytext LIKE '%TYPO3%')
// Syntax
searchQuery($searchWords, $fields, $table)

// An example
$words = array('I','Like','TYPO3');
$fields = array('title','bodytext');
$where = $GLOBALS['TYPO3_DB']->searchQuery($words,$fields,'tt_news');

// Result:
// (tt_news.title LIKE '%I%' OR tt_news.bodytext LIKE '%I%') AND
// (tt_news.title LIKE '%Like%' OR tt_news.bodytext LIKE '%Like%') AND
// (tt_news.title LIKE '%TYPO3%' OR tt_news.bodytext LIKE '%TYPO3%')

Other common functionality

Of course it doesn't all end here. Most, if not all, of the basic features you may find in MySQL for example are also supported. Even if for some reason you might not be able to use one of the wrapper functions, you can still code the entire SQL statement yourself. Of course it's preferred to do it by one of the wrappers. This will reduce the chance of incompatibility with other systems. 

Here's a list of the basic functions supported by the database API.

Basic SQL functions 

  1. sql($db,$query); // Deprecated, removed from 4.5! Use sql_query() instead
  2. sql_query($query);
  3. sql_error();
  4. sql_num_rows($res);
  5. sql_fetch_assoc($res);
  6. sql_fetch_row($res);
  7. sql_free_result($res);
  8. sql_insert_id();
  9. sql_affected_rows();
  10. sql_data_seek($res,$seek);
  11. sql_field_type($res,$pointer);
  12. sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
  13. sql_select_db($TYPO3_db);
sql($db,$query); // Deprecated, removed from 4.5! Use sql_query() instead
sql_query($query);
sql_error();
sql_num_rows($res);
sql_fetch_assoc($res);
sql_fetch_row($res);
sql_free_result($res);
sql_insert_id();
sql_affected_rows();
sql_data_seek($res,$seek);
sql_field_type($res,$pointer);
sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
sql_select_db($TYPO3_db);

Conclusion

Using the database API is really a must in extension development. It provides the flexibility you will need, and make sure that your extensions have a much better chance of also being used on other systems. Since you are not completely bound a single database system like MySQL.

Not only compatibility is a plus, working with all the built in wrapper functions is also very pleasant. They allow you to write cleaner and less code, and what you do write is better readable.

If you have any questions or remarks, feel free to leave a comment.

Tags

typo3 extension development db database api

Comments (6)

Gravatar: Benjamin SerfhosBenjamin Serfhos2010-10-27 10:01

database layer

Good article!
Maybe its also good to mention the enableFields in this article? (I know you mentioned it in one post earlier).
 
I don't know why, but I'm always using the simple first one, while maybe the exec_SELECTgetRows will be faster.. I will try to work with this next time! =)

Gravatar: Sebastiaan de JongeSebastiaan de Jonge2010-10-27 10:06

RE: database layer

Thanks Ben! I didn't mention this function since it's not actually part of the database API, but of course in a matter of speeding up and writing less and cleaner code it's a good addition.

Gravatar: CERDAN YohannCERDAN Yohann2010-10-27 10:44

good article

Very good article sebastiaan. The only thing i have to say is about the "exec_SELECTgetRows" method. It is not a optimize function if you do a iteration on it (for example a foreach), because you browse the complete results 2 times (one to make the array and return it, one to browse it and display some informations). This is why i prefer the "exec_SELECTquery".
 
 

Gravatar: Ant ForshawAnt Forshaw2011-10-11 11:19

Thank you..

...for a great article - very informative. I have a quick question that I hope you may be able to answer. If an sql query via the API fails to commit its data to the database, for instance using exec_UPDATEquery, is there any way to detect that it has failed?
 
 

Gravatar: Sebastiaan de JongeSebastiaan de Jonge2011-10-11 12:34

RE: Thank you..

Hi Anthony,
 
I usually debug the query itself, I have described this in the following article: sebastiaandejonge.com/blog/articles/2009/december/16/typo3-debugging-sql-queries/
 
After taking a quick peek in the code, I discovered you can enable debugging in the database class as well. I would suggest turning it on before the query is executed and turning it off after it was. By looking at the code you should be able to enable it like this:
 
$GLOBALS['TYPO3_DB']->debugOutput = true;
 
Cheers,
Sebastiaan

Your comment