The Drupal Database: Querying From the PHP filter

If you have a multi-site Drupal 7 install, or simply another database living alongside your Drupal database, sometimes you may want to pull a few records from the other database for display on your Drupal site. Displaying data from the Drupal database is pretty easy since you can just use Drupal’s built-in db_query function:

$uid = 1;
$result = db_query("SELECT * FROM {users} u WHERE u.uid = :uid, array(':uid' => $uid")); 

foreach ($result as $person) {
   print "<p>" . check_plain($person->name) . "</p>";
}

(If you’re taking user input, parameterize your queries!)

The Drupal Database with PDO

But what about your alternate database? One possibility is that Drupal 7 requires and utilizes PDO, so you can always query a database for which you have a driver installed (we’ll use MySQL here):

$user = 'username';
$pass = 'password';

try {
    $dbh = new PDO('mysql:host=localhost;dbname=db_name', $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
    // (needed to set connection encoding to same as db)
} catch (PDOException $e) {
    return "Fail: " . $e;
}
	
$user_id = 25;
$stmt = $dbh->prepare('SELECT * FROM users WHERE uid < :user_id ORDER BY name');
$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  print "<p>" . check_plain($row['name']) . "</p>";
}

$dbh = null;

The Drupal Database With settings.php

Better yet, since storing your database credentials in a Drupal node isn’t the best idea, or if you’re going to be accessing this external database frequently, you can make your external database connection available throughout your site by adding it to your settings.php:

    [default database]
    ),
  ),
   'external_database' =>
  array (
    'default' =>
    array(
      'database' => 'db_name',
      'username' => 'username',
      'password' => 'password',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),

Then run your query:

$user_id = 10;
$sql = "SELECT f.field1, f.field2 FROM {tablename} f WHERE f.uid = :user_id";

// temporarily use another database connection
db_set_active('external_database');
$result = db_query($sql, array(':user_id' => $user_id), array('target' => 'external_database'));
// reset connection to default
db_set_active();

foreach ($result as $row) {
   echo $row->field1
   echo $row->field2
   etc.
}

Finally, you may choose not to run queries from the PHP filter at all and instead put all your external database queries in custom modules. This gives you much more flexibility in that you can run a whole bunch of Drupal web sites from a single code base.

Using the PHP filter to display data from a module that queries a database is pretty easy. For example, if your module has a function called node_list() that returns a list of nodes in the current web site, just call it by name with any appropriate arguments:

$nodes = node_list('argument1', 'argument2');
$output = '<div class="justified">';
$output =. $nodes;
$output =. '</div>'; 
return $output;