• 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

Drupal Database API Extended with Statements

By Steve Williams

In a previous post, we discovered how to write a database query using Drupal’s Next Generation DB API (DBTNG). We also learned how to extend the query with a TableSort before executing. After executing our query we mapped the return to Drupal’s theme layer and echoed a sortable table. This post will expand on the same query to show how PDO statements execute, allow for flexibility with chaining and format returns as objects, associative arrays and single values.

Execute & Chaining The most common PDO statement used is the execute statement. Once all query parameters have been defined the execute statement is applied to build the query. Once the query is executed results are returned as an associative array of objects. In order to reduce collision with previous variables best habit is to assign the execute statement to a new variable.

$result = $query->execute();

Statements are also chainable. Chaining statements allows for multiple operations on the same result set. A common example of a chained statement is the rowCount statement. We can chain the execute statement and assign returns to a new variable to display number of rows.

$result = $query->execute();    $rowcount = $result->rowCount();

Formatting Returns by chaining additional Prepared Statements By default the results are returned as an associative array of objects.

$result = $query->execute()->fetchAll();

Returns:

Array    (     [0] => stdClass Object      (        [nid] => 4        [title] => How to publish content        [status] => 1        [created] => 1340754431        [changed] => 1351886962       )     [1] => stdClass Object      (        [nid] => 5        [title] => Example 1        [status] => 1        [created] => 1341511461        [changed] => 1351886919      )     [2] => stdClass Object      (        [nid] => 6        [title] => Hello World        [status] => 1        [created] => 1343402019        [changed] => 1351886884      )    )

* Printing result values in Drupal.

Define keys: You can define the associative array key by executing the prepared statement with fetchAllAssoc(‘nid’) were nid is any preferred key field. In the example below the nid field is Drupal’s node id field.

$result = $query->execute()->fetchAllAssoc(‘nid’);

Returns:

Array    (      [4] => stdClass Object      (        [nid] => 4        [title] => How to publish content        [status] => 1        [created] => 1340754431        [changed] => 1351886962      )      [5] => stdClass Object      (        [nid] => 5        [title] => Example 1        [status] => 1        [created] => 1341511461        [changed] => 1351886919      )      [6] => stdClass Object      (        [nid] => 6        [title] => Hello World        [status] => 1        [created] => 1343402019        [changed] => 1351886884      )    )

Define associative array keys and values: To return a simple associative array use the fetchAllKeyed() prepared statement . By default the key with be filled with the first column and the value with be filled with the second column. You can also define the columns to associate with the key and value by defining the column number.

$result = $query->execute()->fetchAllKeyed(0,1);

Returns:

Array    (      [4] => How to publish content      [5] => Example 1      [6] => Hello World    )

Column: Another option is to format returns by column. Use the fetchCol() to return an array of results along with the specified column. If no column is defined the first column, column 0, will be returned. In addition a single column’s value can be returned using the full fetchColumn() statement.

$result = $query->execute()->fetchCol(1); // returns the second column as associative array.

Returns:

Array    (      [0] => How to publish content      [1] => Example 1      [2] => Hello World    )
$result = $query->execute()->fetchColumn(1); //returns “How to publish content”

Return next row: In order to return one row of results as an object use the fetchObject() statement. To return one row of results as an associative array us the fetchAssoc() statement. Both statements will always return the next row.

$result = $query->execute()->fetchObject();    $result = $query->execute()->fetchAssoc();

Returns:

stdClass Object    (      [nid] => 4      [title] => How to publish content      [status] => 1      [created] => 1340754431      [changed] => 1351886962    )
Array    (      [nid] => 4      [title] => How to publish content      [status] => 1      [created] => 1340754431      [changed] => 1351886962    )

Additional Information: Additional statements you may find helpful during development are columnCount() and getColumnMeta(). When dealing with large data sets it is sometimes hard to determine how many fields are being returned, or if you have mapped the correct amount of fields. Returning a count of columns can help alleviate this concern and can be easily executed.

$columncount = $query->execute()->columnCount(); // Returns “5”.

Returning column metadata can help when you find yourself wondering if a leading zero is allowed, if a negative value is permitted, or what the max character limit is. You may choose to access the database directly, or you can execute a getColumnMeta() prepared statement.

$columnmeta = $query->execute()->getColumnMeta(0);

Returns:

Array    (      [native_type] => LONG      [flags] => Array      (        [0] => not_null        [1] => primary_key       )      [table] => n      [name] => nid      [len] => 10      [precision] => 0      [pdo_type] => 2    )

Review:

//Execute the query and return a associative array of objects.    $result = $query->execute();         //Returns a count of rows.    $rowcount = $query->execute()->rowCount();         //Returns associative array of objects with a column as keys.    //A field name is required.    $result = $query->execute()->fetchAllAssoc(‘nid’);         //Returns associative array with key and value defined.    //If no columns are defined column 0 is the key and column 1 is the value.    $result = $query->execute()->fetchAllKeyed();    //Defines column 1 as the key and column 2 as the value.    $result = $query->execute()->fetchAllKeyed(1,2);         //Returns a defined column as an associative array.    $result -> $query->execute()->fetchCol(1);         //Returns a single value column from the next row.    $result->$query->execute()->fetchColumn(1);         //Returns next row as an object.    $result->$query->execute()->fetchObject();    //Returns next row as an associative array.    $result->$query->execute()->fetchAssoc();         //Returns a column count.    $columncount = $query->execute()->columnCount();    //Returns column metadata.    $columnmeta = $query->execute()->getColumnMeta(0);

Conclusion: Drupal’s database API provides a clean framework to write SQL queries using the PHP PDO abstraction layer. With the new API you can chain executions and format returns as objects, associative arrays, single values, row counts, column counts and schema information. These features should be enough to get excited about, but you may have noticed one more powerfull feature, the ability to execute multiple prepared statements on a single query. If you have not been using Drupal’s Next Generation Database API now is the time to start.

  • Note: PHP provides the print_r() function to return variable values. In Drupal you can combine the print_r() function with drupal_set_message() to echo variable values into the message region.
    drupal_set_message("<pre>" . print_r($return, TRUE) . "</pre>");

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