By: Steve Williams – November 2, 2012

The release of Drupal 7 introduced a new database abstraction layer build on top of PHP's Data Object API (PDO). This new Database API provides an abstraction layer allowing for pluggable database drivers. The API is designed with an object-oriented approach providing the ability to dynamically build queries. DBTNG provides for extending, merging, transactions, and chaining of queries. This post will demonstrate how to build a query and return the output in a sort-able table.

You can still write static queries using the legacy db_query method, but for building dynamic queries you will need to use db_select. The first step in building your query is to define a table and table alias. In our example we will querying the node table with "n" as the alias.

function mynode_list() {<br>&nbsp; $query = db_select('node', 'n');

With our node table selected we now define our fields. You can call all fields in a table.

   $query->fields('n')

You can also select specific fields by adding an array of field names.

   $query->fields('n', array('nid', 'title', 'status', 'created', 'changed'))

Conditions can be defined by specifying the table alias, field to act on, the value of the condition and the operator. In our query we will add a condition for type = page.

      ->condition('n.type', 'page', '=');

With our fields selected and our condition in place we can now define the table headers, provide translatable labels and include fields for sorting.

&nbsp;&nbsp;&nbsp; $header = array(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array('data' =&gt; t('nid'), 'field' =&gt; 'nid', 'sort' =&gt; 'asc'),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array('data' =&gt; t('Title'), 'field' =&gt; 'title'),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array('data' =&gt; t('Status'), 'field' =&gt; 'status'),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array('data' =&gt; t('Created'), 'field' =&gt; 'created'),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; array('data' =&gt; t('Changed'), 'field' =&gt; 'changed'),<br>&nbsp; );

In order to provide dynamic header sorting we need to extend our query to add sorting and ordering on the header array.

&nbsp;&nbsp;&nbsp; $query-&gt;extend('TableSort')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;orderByHeader($header);

With the building of our query complete we can now execute and loop through the results.

&nbsp;&nbsp;&nbsp; // Execute the query.<br>&nbsp;&nbsp;&nbsp; $result = $query-&gt;execute();<br>&nbsp;&nbsp;&nbsp; // Loop through results and define map fields to a row output.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; foreach ($result as $record) {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $output_rows[] = array(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $record-&gt;nid,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $record-&gt;title,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $record-&gt;status,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; date("Y-M-d", $record-&gt;created),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; date("Y-M-d", $record-&gt;changed),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }

Finally, we pass the results into a table with sortable headers using Drupal's theme API and return the output.

// Pass your header and output rows to Drupal's theme.<br>&nbsp;&nbsp;&nbsp; $output = theme('table', array('header' =&gt; $header, 'rows' =&gt; $output_rows ) );<br>&nbsp;&nbsp;&nbsp; return $output;<br>}

For additional functionality PHP's PDO statements can be appended on execution.

Category: DrupalDrupal Planet