Drupal Query Functions
As with the Schema API, whenever you are adding, retrieving, or manipulating data in your database you should always use Drupal's specific query functions. Amongst other reasons the use of the queries helps with scalability. For example when your site becomes very popular you could employ multiple database servers to handle the increased load. You can only do this if you use Drupal's functions.
Compare the following two sets of code that enter the same data into the same table. One using MySQL specific PHP + SQL code and the other using Drupal's database agnostic code.
mysql_query("INSERT INTO bd_filter (uid, count) VALUES (1, 4)"); DRUPAL7
$fields = array('uid' => 1, 'count' => 4); db_insert('bd_filter')->fields($fields)->execute();
The Drupal method may be a bit strange but there are many reasons why it is preferable. You already read that this helps to create a scalable website. It also helps to keep your site secure. Drupal watches each query for adherence to the database schema (for example, you can't enter a string into an integer field) and for common database attacks such as the infamous SQL injection. By using the Drupal specific functions you not only create a scalable website but a secure one as well.
Installing a Custom Database Table
In this exercise you will create a custom database table to store a count of the attempts a user made to post a comment containing the keywords.
2. Type the following code into the newly created bd_filter.install file. This code will create both a method to install your database table and to uninstall it.
function bd_filter_install() { // Create tables.
drupal_install_schema('bd_filter');
* Implementation of hook_uninstall().
function bd_filter_uninstall() { // Remove tables.
drupal_uninstall_schema('bd_filter');
* Implementation of hook_schema().
function bd_filter_schema() { $schema['bd_filter'] = array(
'description' => 'Tracks users attempting to use restricted words', 'fields' => array( 'uid' => array( 'type' => 'int', 'not null' => TRUE, 'default' => 0,
'description' => 'The user id of the user attempting to use restricted words',
'count' => array( 'type' => 'int', 'not null' => TRUE, 'default' => 0,
'description' => 'The number of times the user has made an attempt',
1. Within your module's directory create a new file named bd_filter.install.
* Implementation of hook_install().
Available for download on lownloadon Wroi.com */
return $schema;
3. Save your module, then navigate to the modules administration page, then:
a. Disable your module.
b. Click Uninstall at the top of the modules page and choose to uninstall your module. C. Enable your module.
How It Works
When a module is first enabled Drupal runs the hook_install function and hook_uninstall when the module is disabled. Each of these functions passes a Schema API array to Drupal's database functions allowing you to define your tables and let Drupal do all the work.
NOTE Drupal will only run hook_install the first time that a module is enabled. You will need to disable, uninstall, and re-enable a module to trigger Drupal to run hook_install again. Fortunately the devel module has a one-click method to make this easy at http://drupal.org/project/devel.
Recording the Count
At this point you have the table necessary to record a count of the number of attempts a user makes. Each user will have a single record in the database with the count field updated after each attempt. Drupal's db_merge function makes this easy because it's designed to handle either an INSERT or an UPDATE query depending on if the user has an existing record. The function also allows for expressions to quickly increment the attempt count.
In your module you will use db_merge with this code:
global $user; db_merge('bd_filter')
->key(array('uid' => $user->uid)) ->fields(array('count' => 1)) ->expression('count', 'count + 1') ->execute();
To help you understand what's going on in this code break down its components:
1. ->global is a PHP keyword that pulls in an object or variable that is global in scope. In this case global is pulling in the Drupal-created $user object, which contains information about the currently logged in user.
2. ->db_merge is set to use the bd_filter database table.
3. ->key sets how the database record will be found, db_merge will search for records where the uid is equal to the user's id.
4. ->fields tell Drupal which field (column) of the database you are updating. If the column is empty a 1 will be placed into it, otherwise the expression will be used.
5. ->expression runs a mathematical expression on a given field. In this example the field count will be equal to count's previous value plus 1.
6. ->execute() tells Drupal to execute the query and update the database.
With a general understanding on how to use db_merge continue onto to the next exercise to put it into action in your module.
TRY IT OUT
Update Your Custom Table
In this exercise you will utilize the db_merge function to update your custom table with a count of the user's attempts to post a comment containing restricted keywords.
1. Modify the bd_filter_validate function within the bd_filter.module file with the following code:
* Implementation of hook_comment_filter
Available for */ download on
Wrox.com function bd_filter_comment_validate(&$form, &$form_state) {
$words_to_filter = explode("\n", variable_get('bd_filter_word1,11)); foreach ($words_to_filter as $key => $value) { $word_to_filter = trim($value);
if (preg_match('/\b' . $word_to_filter . '\b/i', $form_values['comment'])) {
form_set_error('comment', t('Your post contains an offending word, please remove it and resubmit'));
//Record this attempt global $user; if ($user->uid > 0) { db_merge('bd_filter')
->key(array('uid' => $user->uid)) ->fields(array('count' => 1)) ->expression('count', 'count + 1') ->execute(); } //end if ($user->uid } //end if (preg_match } //end foreach } //end function code snippet Chapter 17 Comment filter
2. Save the file then attempt to post a comment containing a restricted keyword.
How It Works
When a user attempts to post a comment containing a restricted keyword, Drupal will prevent the posting, set an error on the form and then using db_merge to record the attempt. Drupal's unique db_merge will automatically add or update a database record allowing you to write only a tiny bit of code.
TRY IT OUT
Average user rating: 5 stars out of 1 votes
Post a comment