Drupal Database API Extended with Statements

By: Steve Williams – January 3, 2013

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("&lt;pre&gt;" . print_r($return, TRUE) . "&lt;/pre&gt;");

Category: Drupal Drupal Planet

Original Article:

Drupal Database API Extended with Statements