This is an example of how to select and list specific fields from a specific content type. Why am I putting this here instead of the Snippets section on drupal.org? There are two main reasons: 1) it's based on the Quotes module, which I doubt many people are using, and 2) because it provides a fairly good primer to some basic Drupal functions.

As I mentioned above, this is based on using the Quotes module, but that is mostly immaterial. The point is to show how to select additional information that cannot be stored in a basic node. In this case, the quote's author is maintained in a separate table, as is required of any module that wants to supply fields that are not part of the standard node structure. You might run into this same situation with CCK or other modules.

Node Structure

So, let's start with what a standard node looks like. Contrary to how it sounds, a node requires (at least) two tables, "node" and "node_revisions." The "node_revisions" table is where the actual body and teaser information is kept. The "node" table holds the current title (it could change with revisions to the node) and other information, such as the content type and whether it is published, promoted to the front page, sticky, etc. [If you use the Weight module or others that use the same technique, the node's weight is also stored in the node table, in a coded fashion.]

In mathematics, a node i sthe intersection of two or more sets of data, and this is exactly how Druapl uses the term. A complete node is the intersction of at least two database tables, and could be many more, such as taxonomy terms and votes.

The main key for the node table is the node's ID (or "nid") and the main key for the node_revisions table is the version ID (or "vid"). So how do they link to each other? Simple, both tables contain the "nid" and "vid." The node table keeps only information on the current revision. The node_revisions table keeps all revisions of the node, so it can be reverted if needed.

You need to know this in order to properly link additional fields that are kept in different tables. For example, the Quotes module creates a new table called "quotes." This table holds the author's name. To link it back to the node, it also stores both the "nid" and "vid" so that it can be linked to the proper revision (you could, after all, realize that you had the author's name wrong when you originally entered the quote. If you have revisions turned on for the node, then the quotes table will allow you to track the author field for each revision.

Okay, now you know enough to be able to code a MySql SELECT statement to get the data from all of the tables.

The Task

Here's what we want to do: create a display table showing only the quote's body and author, showing the most recent first and oldest last, but sticky nodes on top. Oh, while we're at it, let's take revisions into account, just in case we decide to use them. It sounds pretty simple - and it is. The "trick" is linking the node, node_revisions, and quotes table to get the information.

"Just hold a minute, Nancy. I allow my users to create quotes but I have the quotes content type set to not published so that only I can approve them. I only want to show quotes that are in a published state." Hee, hee, you thought that would throw me for a loop? That's trivial! The node table contains a field called, confusingly, "status." It's a boolean variable, so "1" means published and "0" means not published.

The Data

The first piece we need is the content type field, which is in the node table. The field's name is "type" (notice that Drupal's convention is that all database field names be all lower case).

The next piece we need is the body of the quote; that's in the node_revisions table in a field called "body."

The last piece we need is the author's name. That's in the quotes table in a field named "author."

"Oh, great, we have to hit THREE database tables to make one simple little list?" Yes, I'm afraid so. But that doesn't mean three queries. "Oh, no, she's going to bring up the dreaded JOIN?" Yes, I'm afraid so. But it's not so bad.

The first thing to remember, is that we are only looking for rows that are in all three tables, so we will use an "INNER JOIN" to include only stuff that is in the tables on both sides of the JOIN. "Okay, that makes sense, I guess."

Remember that all three tables also contain both the nid and vid keys. While we could probably link these tables on vid alone, let's use both fields just to make sure. [In the case of quotes, you probably don't have enough of them to really make a noticeable difference in the query length.]

Getting (SELECTing) the Data

The SQL standard verb for retrieving data is the SELECT statement. Also, it is Drupal convention that SQL reserved words are capitalized while column names are in lower case. It may sound strange, but if you look at enough Drupal code you will begin to appreciate this convention. Additionally, Drupal will automatically add a database prefix, if appropriate, to all table names that enclosed in curly brackets, so you will see that in the code below.

Now we know everything we need to create the SELECT statement. It ends up looking like this: SELECT nr.body, q.author FROM {node} n INNER JOIN {node_revisions} nr USING(nid, vid) INNER JOIN {quotes} q USING(nid, vid) WHERE n.type = 'quotes' AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC

Simple, huh? Hopefully the description above made this understandable.

[To be honest, there is a shortcut: the "node_load" function. However, in this case, the Quotes module changes the name of the author field to "quotes_author." Without viewing the module, you would not know this, and would think that the author field hadn't been loaded.]

The Full Code Sample

Okay, here's the whole thing. It can be put into a php-formatted page (i.e. a Page content type with an Input Format specifying "PHP") and run from there. There are bank lines for readability; you may leave them out if you want.

<?php
      $how_many_per_page = 50;

      $query = "SELECT nr.body, q.author FROM {node} n INNER JOIN {node_revisions} nr USING(nid, vid) LEFT JOIN {quotes} q USING(nid, vid) WHERE n.type='quotes' ORDER BY n.sticky ASC, n.created DESC";
      $results = pager_query($query, $how_many_per_page);

      $header = array(t('Quote'), t('Author'));
      $attributes = array('width' => '75%',
        'align' => 'center',
        'border' => '2',
        );
      $rows = array();

      while ($quote = db_fetch_array($results)) {
        $rows[] = array($quote['body'], $quote['author']);
      }

      if (count($rows)) {
        echo theme('table', $header, $rows, $attributes);
        echo theme('pager', NULL, $how_many_per_page);
      }
      else {
        echo 'No quotes found';
      }
?>

"Wow, that's not a very big piece of code at all!" No, it isn't, and it could be made smaller if you prefer.

The Code Explained

A few pieces of the code will be delayed because they go together and I want to explain them all together.

$how_many_per_page - This is just a variable I added to make it easy to set how many quotes will occur on a page before Drupal adds the "next" and "previous" pager links (Oh, yes, I threw that in just to show how it's done).

$results = pager_query($query, $how_many_per_page); - Here's where the actual query is sent to the database. I used the "pager_query" function because we want to possibly produce multiple pages of quotes. If you didn't want to use the pager feature, or don't need to, you could use "db_query."

while ($quote = db_fetch_array($results)) - This is a basic loop that says to keep going as long as there are more rows returned from the query. It also says to return the rows to us as an array. That means each row will have field values that are accessed by "$quotes['field-name1'], etc.

$rows[] = array($quote['body'], $quote['author']); - We are building a two element array from the data that was selected. This will be the data in the rows of the final table.

if (count($rows)) - This just checks to see if there were any table rows created and if not, displays a message to that effect rather than showing a blank page.

echo theme('table', $header, $rows, $attributes); - This is where we actually create the HTML for the table and send it to the screen. More on tables in just a moment.

echo theme('pager', NULL, $how_many_per_page, $count); - This adds the pager links to the bottom if they are needed. Notice that we finally use that COUNT query to tell the pager routine how many total pages will be created (for the "last" link). The second parameter (NULL) allows you to specify custom pager links, which I didn't want to do.

Okay, now let's bring all the table elements together even though they are scattered throughout the code.

Let's start at the end:

• theme('table', $header, $rows, $attributes) - The theme function is a Drupal function that can be used not only for tables, but lists, links, and even content types. In this case, we've said we're want a table.
• $header = array(t('Quote'), t('Author')) - This specifies the header row we want on the table. It is an array, with one element per column. Note that I used the "t" function, which allows the header text to be translated into the user's local language (if you support that).
• $rows = array() and $rows[] = array($quote['body'], $quote['author']) - This parameter is an array that represents each row in the table and has an element for each column. The first use just says to create an array and make it empty. The second use actually loads the data into the row, in this case it's the data selected by the query.
• $attributes - This is an optional parameter that specifies HTML attributes for the table as a whole. In this case, I wanted the table to be centered in the content area ('align' => 'center'), take up only three fourths of the available space ('width' => '75%'), and have a slightly thickened border ('border' => '2').

There's a little extra feature that's not immediately obvious. If you look at the HTML that is produced, you'll notice that the table rows alternate with class="odd" and class="even". This allows you (instead of using a border) to "colorize" your rows. For example, you could include these definitions in your CSS and have alternating blue and green rows.

.odd {background: #E0E0FF;} .even {background: #E0FFE0;}

Drupal IS Hard?

"Wow, that wasn't so bad at all! Why does everyone say Drupal is hard? How long did it take you to learn all this?" Well, let's answer the last question first. As of this writing, I've been using Drupal for only six months and I've already contributed two modules. So, the second question: why do they say Drupal is hard? It can get complex if you let it. But start small, like writing little snippets like this; you'll get the hang of it quickly. Drupal won't overwhelm you - you will.

I hope this has helped you get a little more familiar with Drupal's "inner workings." It was a simple example that, IMHO, teaches a lot.