• About
        • About
          • Overview
          • What to Expect
          • Careers
          • Team
          • CANDO Culture
          • FAQ
        • Praxent Pricing Guide

          To give you an idea of what investment looks like we've created a guide with estimates by product type as well as set-priced starter engagements.

          Download Now
  • Industries
        • Industries
          • Fintech
          • Insurance
          • Lending
          • Wealth Management
          • Real Estate
          • Other
        • Praxent Pricing Guide

          To give you an idea of what investment looks like we've created a guide with estimates by product type as well as set-priced starter engagements.

          Download Now
  • Services
    • Design
      • User Experience Design
      • Customer Journey Mapping
      • Design Sprints
      • UX Audit
      • User Testing
      • User Research
    • Development
      • Custom Software Development
      • Application Modernization
      • Mobile App Development
      • Web App Development
      • Web Portal Development
      • Front End Development
      • Backend Development
      • Cloud Deployment
      • Implementations
      • Staff Augmentation
  • Case Studies
  • Insights
  • Schedule a Call
  • About
    • About
    • Overview
    • Careers
    • CANDO Culture
    • What to Expect
    • Team
    • FAQ
    • #
  • Industries
    • Industries
    • Fintech
    • Insurance
    • Lending
    • Wealth Management
    • Real Estate
    • Other
    • #
  • Services
    • Services
    • Design
      • User Experience Design
      • Customer Journey Mapping
      • Design Sprints
      • UX Audit
      • User Research
      • User Testing
    • Development
      • Custom Software Development
      • Application Modernization
      • Mobile App Development
      • Web App Development
      • Web Portal Development
      • Frontend Development
      • Backend Development
      • Cloud Deployment
      • Implementations
      • Staff Augmentation
    • #
  • Case Studies
  • Insights
  • Contact

Speak with an expert

(512) 553-6830
Close menu

Topics

  • Uncategorized
  • Development
  • Life at Praxent
  • Project Management
  • Strategy
  • UX & Design
  • Tech & Business News
  • Mobile
  • Product Management
  • Featured
  • Financial Services Innovators
  • Awards
  • UX Insights

Types

  • article Article
  • podcastpodcast
  • presspress
  • webinarwebinar
  • whitepaperwhitepaper

Introduction to Drupal’s Database Abstraction Layer (DBTNG)

Authored by Steve Williams

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 sortable 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 be querying the node table with “n” as the alias.

function mynode_list() {      $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.

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

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

    $query->extend('TableSort')           ->orderByHeader($header);

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

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

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.        $output = theme('table', array('header' => $header, 'rows' => $output_rows ) );        return $output;    }

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

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Featured

Praxent Introduces Accelerator App, Helping WealthTechs Speed Time to Market 

Praxent Introduces Accelerator App, Helping WealthTechs Speed Time to Market 

Fintech product agency launches starter app to help wealthtechs accelerate innovation. Praxent Introduces Accelerator … Read More

What the Kardashians can teach your FI about fintech partners to identify niche markets.

What the Kardashians can teach your FI about fintech partners to identify niche markets.

Read more

The 4 Reasons Software Modernizations Fail (and 12 Strategies for Success)

The 4 Reasons Software Modernizations Fail (and 12 Strategies for Success)

We share the strategies you’ll need to modernize your online customer experience so you can outperform your competitor...Read more

Many companies have built software applications that no longer meet customer expectations. We help financial services companies modernize those applications so they can remain relevant against born-digital competitors.

4330 Gaines Ranch Loop, Suite 230
Austin, TX 78735

(512) 553-6830

hello@praxent.com

DESIGN
  • UX Design
  • Design Sprints
  • User Research
  • User Testing
DEVELOP
  • Custom Software
  • Web Portals
  • App Modernization
  • Web Apps
  • Mobile Apps
ABOUT
  • Case Studies
  • Team
  • Culture
  • Careers
  • Insights
  • Contact

© 2023 Praxent
Privacy Terms Site Map