Defining Database Parameters [Working with Databases]

Drupal knows  which database to connect to and  what  username and  password to issue when establishing the database connection by looking in the settings.php file for your site. This file typically lives at sites/example.com/settings.php or

sites/default/settings.php. The code  that defines the database connection looks like this:

$databases = array  ( 'default' => array  ('default' => array  ( 'driver' => 'mysql', 'database' => 'databasename',

'username'  => 'username', 'password' => 'password', 'host' => 'localhost','port' => '', 'prefix' => '',),),); 

This example is for connecting to a MySQL database. PostgreSQL users would prefix the connection string with pgsql instead of

mysql. Obviously, the database name, username, and  password used here must be valid for your database. They are databas

ecredentials, not Drupal credentials, and  they are established when you set up the database account using  your database’s tool.

Drupal’s installer asks for the username and  password so that it can build the $databases  array in your settings.php file.

If you are using  sqlite  as the database for your site, the setup is slightly simpler. The driver  should be set to sqlite,  and  the

database should be set to the path including the name of the database.

$databases['default'['default']  = array( 'driver'  => 'sqlite','database'  => '/path/to/databasefilename',);

Understanding the Database Abstraction Layer

Working  with a database abstraction API is something you will not fully appreciate until  you try to live without one again. Have you ever had a project where you needed to change database systems and  you spent days sifting through your code  to changedatabase-specific function calls and  queries? With an abstraction layer, you no longer have to keep track of nuances in function names for different database systems, and  as long as your queries are American National Standards Institute (ANSI) SQL–compliant, you will not need to writeseparate queries for different databases. For example, rather than calling mysql_query()  or  pg_query(), Drupal uses db_query(), which keeps the business logic database- agnostic.

Drupal 7’s database abstraction layer is based on PHP’s Data Object (PDO) library  and  serves  two main purposes. The first is to

keep your code  from being tied to any one database. The second is to sanitize user submitted data placed into queries to prevent SQL injection attacks. This layer was built  on the principle that writing SQL is more convenient than learning a new abstraction layerlanguage.

Drupal also has a schema API, which allows you to describe your database schema (that is, which tables and  fields you will be using) to Drupal in a general manner and  have Drupal translate that into specifics for the database you are using. We’ll coverthat in a bit when we talk about .install files.

Drupal determines the type of database to connect to by inspecting the $database array inside your settings.php file. For example, if$databases['default']['default']['driver'] is set to mysql, then Drupal will include includes/database.mysql.inc.

If  it isequal to pgsql, Drupal will include includes/database.pgsql.inc, and  if it is equal to sqlite,  Drupal will include includes/database.sqlite.inc. This mechanism is shown in Figure  5-1.

If you use a database that is not yet supported, you can write your own driver  by implementing the wrapper  functions for your

database. For more information, see “Writing Your Own Database Driver” at the end of this chapter.

Figure 5-1. Drupal determines which database file to include by examining $databases.

Connecting to the Database

Drupal automatically establishes a connection to the database as part  of its normal bootstrap process, so you do not need

to worry about doing that. If you are working outside Drupal itself (for example, you’re writing a stand-alone PHP script 

or have existing PHP code  outside of Drupal that needs access to Drupal’s database), you would use the following approach

// Make  Drupal PHP's current   directory.

chdir('/full/path/to/your/drupal/installation');

// Bootstrap   Drupal up through  the  database  phase. include_once('./includes/bootstrap.inc'); drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE);

// Now  you can  run queries using  db_query().

$result = db_query('SELECT  title FROM   {node}');

...

Performing Simple Queries

Drupal’s db_query() function is used to execute a SELECT query to the active database connection.There are other functions for

performing INSERTS, UPDATES, and  DELETES, and  I’ll cover those in a moment, but first let’s look at extracting information from the database.

There is some Drupal-specific syntax  you need to know when it comes to writing SQL statements.

First, table names are enclosed within curly brackets so that table names can be prefixed to give them unique names, if necessary.

This convention allows users who are restricted by their hosting provider in the number of databases they can create to install 

Drupal within an existing database and  avoid table name collisions by specifying a database prefix in their  settings.php file. Here is an example of a simple query to retrieve the name of role 2:

$result = db_query('SELECT  name FROM   {role} WHERE   rid  = :rid',  array(':rid' => 2));

Notice the use of :rid as a named placeholder. In Drupal, queries are always written using placeholders, with the actual value

assigned as a key => value pair. The  :rid placeholder will automatically be replaced with the value assigned to :rid in the array that is used to define all of the values  assigned to placeholders in the query—in this case, 2.

Additional  placeholders mean additional parameters:

db_query('SELECT  name FROM   {role} WHERE   rid  > :rid AND  rid  < :max_rid', array(':rid'  => 0, ':max_rid' => 3);

The preceding line will become the following when it is actually executed by the database:

SELECT  name FROM   role WHERE   rid  > 0  AND  rid  < 3

User-submitted data must always be passed in as separate parameters so the values  can be sanitized to avoid SQL  

injection attacks. The first parameter for db_query() is always the query itself. The remaining parameters are the dynamic values

to validate and  insert into the query string. The values  are passed as an array of key => value pairs.

We should note that using this syntax  will typecast TRUE, FALSE, and  NULL to their decimal equivalents (0 or 1). In most cases, this should not cause problems. Let’s look at some examples. In these examples, we’ll use a database table called

joke that contains three fields: a node ID (integer), a version ID (integer), and  a text field containing a punch line.

Let’s start with an easy query. Get all rows of all fields from the table named joke where the field vid has an integer value that

is the same as the value of $node->vid:

db_query('SELECT  *  FROM   {joke} WHERE   vid  = :vid',  array(':vid' => $node->vid));

Next let’s insert a new row into the joke  table using  the  db_insert function. We’ll define the fields to insert using -> fields and  an array of key => value pairs  where the key is the name of the field and value is what  will be assigned to that field

in that  row. Also note ->execute() at the end of the statement, which does  just what  it sounds like,

executes the insert against  the database.

$nid  = db_insert('joke')->fields(array( 'nid' => '4', 'vid' => 1,'punchline' => 'And the  pig  said   oink!',))->execute();

Next let’s update all of the rows in the joke table, setting the punchline equal to “Take my wife, please!”,  where the nid is greater than or equal to 3. I’ll pass an array of fields and  values  to update using- >fields,  and  I’ll set the condition  that has to be met inorder to update the values  for those fields using the ->condition modifier. In this example,  I am going to update the punchline

field for any record in the joke table where the nid field is greater than or equal to 3.

$num_updated  = db_update('joke')->fields(array('punchline' => 'Take  my  wife  please!',))->condition('nid', 3, '>=')->execute();

If I wanted to see how many rows were affected by the update, I could use the value assigned to $num_updated after the update

is executed. Finally let’s delete all of the rows from the joke table where the punchline is equal to “Take my wife please!”  I’ll use

the db_delete function and  the ->condition modifier to specify the condition for deleting records from the table.

$num_deleted  = db_delete('joke')->condition('punchline', 'Take  my  wife  please!')->execute();

Retrieving Query Results

There are various ways to retrieve query results depending on whether you need a single row or the whole  result set, or whether 

you are planning to get a range of results for internal use or for display as a paged result set.

Getting a Single Value

If all you need from the database is a single value, you can use the  ->fetchField() method to retrieve that value.

Here is an example of retrieving the total  number of records from the joke table:

$nbr_records  = db_query("SELECT  count(nid) FROM   {joke}")->fetchField();

Getting Multiple Rows

In most cases, you will want  to return more than a single field from the database. Here is a typical iteration pattern for stepping through the result set:

$type  = 'page';

$status = 1;

$result = db_query("SELECT  nid, title  FROM   {node}  WHERE   type  = :type AND  status = :status", array(':type' => $type, ':status'  => 1,));

foreach   ($result as  $row)  { echo  $row->title."<br/>";}

The preceding code  snippet will print out the title of all published nodes that are of type page (the status field in the node table is

for unpublished nodes and  1 for published nodes). The call to db_query returns an array of results, with each  element ofthe arraybeing a row from the table that matches the criteria specified in the query. Using foreach I’m able to iterate through the result set

array,  and  in the preceding case, print out the title of each  of the nodes on a separate line.

Using the Query Builder  and  Query Objects

One of the new features that Drupal 7 provides is the ability to construct query objects using a query builder. In the previous

examples, my queries were relatively simple, but what  if I had more complex queries to write? That’s where the query builder using query objects comes in handy. Let me show you an example, and  then I’ll build on the concept as I demonstrate the creation of

more complex queries in Drupal 7.

In an earlier example, I created a query that selected values from the role table where the role ID was greater than or equal to 2. The query that I used is as follows:

$result = db_query('SELECT  name FROM   {role} WHERE   rid  = :rid',  array(':rid' => 2));

I’ll write the same query using a query object and  the query builder. First I’ll create the query object by selecting the table that I want  to use and  assign an identifier to the table (the r) so I can reference fields from that table.

$query  = db_select('role', 'r');

Next I’ll expand the query to include a condition that must be met (rid = 2) and  the fields that I want returned from thequery.

$query->condition('rid', 2)->fields('r',  array('name'));

Finally I’ll execute the query and  assign the result set to $result.

$result = $query->execute();

I’ll print out the results by iterating through the array returned from the query.

foreach($result as  $row)  { echo  $row->name."<br/>";}

Using the query object and  query builder makes it easier to construct complex database queries I’ll demonstrate how to use the query builder in the following examples.

Getting a Limited Range of Results

Executing queries that may return hundreds or even thousands of records is a risk that you’ll want  to think about as you

write queries. One of the mechanisms for minimizing that risk is to use the range modifier to restrict the maximum number ofrecords returnedby the query. An example might be a query that returns all nodes that are of the type “page.”  If the site

has thousands of nodes, the query may take a while to execute and  the user might be overwhelmed by the volume of 

information. You can use the rangemodifier to restrict the number of rows returned by your query, alleviating the potential of long-running queries and  too much information.

The following query adds the range modifier to the query by setting the offset (starting record) to 0 and  the maximum number of rows to return to 100.

$query  = db_select('node', 'n');

$query->condition('type', 'page')->fields('n', array('title'))->range(0,100);

$result = $query->execute();

foreach($result as  $row)  { echo  $row->title."<br/>";}

Getting Results  for Paged  Display

If your query returns a large number of rows, you may want  to consider using a pager. A pager limits the number of rows

displayed on the page while providing a navigational element that allows the site visitor to navigate, or page through the results. Anexample might be a query that returns 100 rows. You could configure the query to display the results 10 rows at a time with the ability to click on a “next” button to see the next 10 rows, “previous” to see the previous 10 rows, “first” to see the first 10

rows, “last” to see the last 10 rows, or by clicking  on a page number to jump to that specific page of results (e.g., clicking  on 5

would take the visitor to rows 51 through 60).

To demonstrate using  a pager, I’ll create a query that returns all page nodes in the node table  and displays the results with 10 rowsper page with a pager at the bottom. First I’ll create the query object and  extend the query object by instructing Drupal to create a query object that uses a pager.

$query  = db_select('node',  'n')->extend('PagerDefault');

Next I’ll add the condition, fields, and  the number of items that I want  to appear on a page using  the limit modifier.

$query->condition('type', 'page')->fields('n', array('title'))->limit(10);

Next I’ll execute the query and  iterate through the result set, adding each  row to an output variable that I’ve appropriately named $output.

$output  = '';

foreach   ($result as  $row)  {

$output  .=  $row->title."<br/>";}

Next I’ll call the theming function and  apply  the pager theme to my output, resulting in output that shows ten items per page witha pager at the bottom (see Figure  5-2), and  display the results. For details on how the pager handles database

results and  thedetails of how the theme layer renders paged results, please see /includes/pager.inc.

$output  .=  theme('pager'); print  $output;

Figure 5-2. Drupal’s pager gives built-in navigation through a result set.

Other Common Queries

Drupal 7’s database layer provides a number of other common functions that you’ll likely want  to use. The first example is sorting the result set. Using the orderBy method allows you to sort the result set.

The example sorts  the result set in ascending order bytitle.

$query->condition('type', 'page')->fields('n', array('title'))->orderBy('title',  'ASC');

The next example modifies the sort by first sorting by the date the node was changed in descending order, followed by sorting the title in ascending order.

$query->condition('type', 'page')->fields('n',  array('title',  'changed'))->orderBy('changed', 'DESC')

->orderBy('title',  'ASC');

There may be queries that product duplicate results. In that case, duplicate records can be filtered out by using the distinct method.

$query->condition('type', 'page')->fields('n',  array('title',  'changed'))->orderBy('changed', 'DESC')

->orderBy('title',  'ASC')->distinct();

For additional details and  examples, please check out http://drupal.org/node/310069.