feelin’ good

Drupal: Working With Databases

This is the first in my series of short notes on the book Pro Drupal Development by John K. Vandyk and Matt Westgate. It is for those of you interested in writing your own Drupal modules. This notes corresponds to “Chapter 5: Working With Databases” of the book.

Code samples are taken from the book; some with slight modifications for brevity. I’m sorry about the lack of indentations, as WordPress sucks in formatting <code> tags.

Drupal’s database abstraction layer works as the middle man between our code and database. By using the database abstraction layer, we can avoid database-specific function calls and queries, and sanitize user-submitted input to prevent SQL injection attacks.

Defining Database Parameter

Settings for connecting to the database are kept in settings.php, which is located in sites/example.com or sites/default, in the form of

$db_url = 'mysql://username:password@localhost/databasename';

Understanding the Database Abstraction Layer

The type of database is determined by the value of $db_url in settings.php. Depending on the type of database, the corresponding includes/database.dbtype.inc is included. For example, it is includes/database.mysql.inc for MySQL.

Connecting to the Database

No explicit command is required. Connection is automatically carried out in the bootstrap process.

To connect to the database from outside Drupal, call

include_once('includes/bootstrap.inc');
drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE); //generates active connection
//proceed with db_query() ...

Performing Simple Queries

db_query(query string [, value(s) of placeholders(s), ...]);

E.g.

db_query("INSERT INTO {joke} (nid, vid, punchline) VALUES (%d, %d, '%s')", $node->nid, $node->vid, $node->punchline);

  • Placeholders of string values should be in single quotes.
  • Table names must be enclosed by { }, so that they can be automatically prefixed to avoid table name collisions.
  • Always use placeholders for user-submitted input, with the actual values following as parameters. The values will be sanitized to prevent SQL injection attacks.
  • Placeholders are %s for string, %d for integer, %f for float, %b for binary data (don’t enclose in ''), %% for literal %
  • NULL, TRUE, FALSE are casted to decimal equivalents.

Retrieving Query Results

Getting Multiple Rows

$sql = "SELECT * FROM {node} WHERE type='blog' AND status=1"; //status=1 published, status=0 unpublished

$result = db_query(db_rewrite_sql($sql));

while ($data = db_fetch_object($result)) {
$node = node_load( $data->nid );
print node_view( $node, TRUE );
}

Getting a Single Value

  • Use db_result(), which is equivalent to db_query() + db_fetch_object().

$sql = "SELECT COUNT(*) FROM {node} WHERE type='blog' AND status=1";

$total = db_result(db_query($sql));

Getting a Limited Range of Results

  • Use db_query_range(), which is equivalent to db_query() + LIMIT clause, because not all database types use the same LIMIT syntax.
  • Placeholder parameters come before limit parameters.

$sql = "SELECT * FROM {node} WHERE type='%s' AND status=%d ORDER BY n.created DESC";

$result = db_query_range(db_rewrite_sql($sql), $type, $status, 0, 10);

Getting Results for Paged Display

A paged display is a page of formatted results with links to more results and the “first and last” links.

$sql = "SELECT * FROM {node} n WHERE type='blog' AND status=1 ORDER BY n.created DESC";

$result = pager_query(db_rewrite_sql($sql), 0, 10);

while ($data = db_fetch_object($result)) {
$node = node_load($data->nid);
print node_view($node, TRUE);
}

print theme('pager', NULL, 10); //add links to remaining pages of results

theme('pager') knows the number of results from pager_query().

Using a Temporary Table

When a lot of processing is needed

$result = db_query_temporary($sql, $arguments, $temporary_table_name);

  • Names of temporary tables don’t require table prefixing and therefore { }.
  • Then query the temporary table as normal using the temporary table name.

Exposing Queries to Other Modules

Lets other modules modify our queries. For example, node module modifies queries for node listings to filter nodes protected by access fields.

In module that issues queries

Wrap query in db_rewrite_sql() before sending to db_query().

Note: db_rewrite_sql() cannot do complex queries. Implicitly joining tables in the FROM clause does not work.

Incorrect

SELECT * FROM node AS n, comment AS c WHERE n.nid=c.nid

Correct

SELECT * FROM node n INNER JOIN comment c on n.nid=c.nid

In module that modifies queries

Implement this hook.

function hook_db_rewrite_sql ($query, $primary_table='n', $primary_field='nid', $args=array())

Connecting to Multiple Databases

Make $db_url in setting.php an array.

$db_url['default'] = ... //reserved for Drupal site's database

$db_url['legacy'] = ... //another database; array key can be any name

Create more elements if needed.

To work with the legacy database, do

db_set_active('legacy');

When done, always switch back to the default database by

db_set_active('default');

Multiple kinds of database back-ends, such as MySQL and PostgreSQL, cannot be used simultaneously. To allow for this, see handbook.

Using Module .install Files

Creating Tables

To create database tables at the time your module is installed, create an .install file that contains an implementation of hook_install() with the SQL to create the tables. Provide all SQL statements specific to the most common database systems, such as MySQL and PostgreSQL. For example,

function book_install() {

switch ($GLOBALS['db_type']) { //Determines the database type
case 'mysql': //Same as mysqli
case 'mysqli':
db_query("CREATE TABLE {book} (...)
/*!40100 DEFAULT CHARACTER SET UTF8 */ ");
break;
case 'pgsql':
db_query("CREATE TABLE {book} (...) ");
db_query("CREATE INDEX ...");
break;
}
}

/*!40100 DEFAULT CHARACTER SET UTF8 */ will get executed only by MySQL version 4.1 or higher; otherwise, it is ignored.

Maintaining Tables

When a new version of your module calls for a change in the database table structure,

  1. Update hook_install() so that the new structure will be installed for new users.
  2. Implement an update function for existing users. Update functions are named starting at 1.

function book_update_1() {

$items = array();
$items[] = update_sql("ALTER TABLE ...");

}

Drupal keeps track of which version a module is using in its system table.

Deleting Tables on Uninstall

Implement hook_uninstall() in your module’s .install file.

function book_uninstall() {

db_query("DROP TABLE {book}"); //Delete table
variable_del('book_nodetypes"); //Delete any variables defined

}

Writing Your Own Database Abstraction Layer

  1. Copy includes/database.mysql.inc and rename it as includes/database.yourdb.inc.
  2. Change each wrapper function to map to your database’s functionality instead of MySQL’s.
  3. Connect to your database by updating $db_url in settings.php.
  4. Create a test module that calls the wrapper functions directly to make sure that they work.

11 responses

  1. Pingback: Drupal: Working With Users « mameou

  2. Prasath

    i can not understand

    April 20, 2010 at 4:24 pm

  3. Nice and clear.

    I’m writing a small patch to an existing module and want to use a separate new table. I’ll patch the install file to create the new table so that people that freshly install the patched module have the table in their database.

    However, if someone has the module already installed and patches the .module file, how do I make sure that the new table is added to their database?

    April 20, 2010 at 6:12 pm

  4. bradyo

    Thanks, this really helped me out. In addition to your “working with users” post, synchronizing the drupal users with a separate database application was a snap.

    September 4, 2010 at 8:56 am

  5. Nick Yeoman

    WordPress doesn’t process the code tags, css does.

    October 21, 2010 at 11:29 am

  6. Anonymous

    asfd
    asfd
    asfd
    dsaf
    ds
    df
    ds

    ff
    s
    fd

    August 20, 2011 at 6:30 pm

  7. Anonymous

    I love the irony of having Drupal posts on a WordPress site.

    September 13, 2011 at 5:57 am

  8. Hi I was wondering if you perhaps had a tutorial showing how to create a Drupal page/view which shows this data retrieved?

    Thanks
    gvanto

    March 18, 2012 at 6:21 pm

  9. Heya i’m for the primary time here. I found this board and I to find It really useful & it helped me out much. I hope to give something back and help others such as you aided me.

    December 21, 2012 at 11:42 am

  10. You must understand how the stock market operates prior to investing in it.
    Supply and demand is how the stock market operates. The amount of shares of stocks is what makes up the supply.
    The demand is determined by the amount of shares that investors are interested in purchasing.
    Furthermore, be aware that with every shared that is bought,
    there is a person on the other side that sells the share.

    __________________________________________________________________________

    I really Love stock picks from

    July 16, 2013 at 11:38 pm

  11. Generally I do not learn post on blogs, however I wish to say that
    this write-up very forced me to check out and do so! Your writing taste has
    been amazed me. Thanks, quite great post.

    July 26, 2014 at 12:36 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s