Working with WordPress Result Sets

Monday Apr 10th 2017 by Rob Gravelle
Share:

Whether you’re fetching a single row or an entire result set, WordPress offers plenty of array types to work with. Read on to learn how to employ WordPress’ $wpdb’s get_row() and get_results() methods.

In the Querying the WordPress Database tutorial, we reviewed the WordPress (WP) MySQL database structure and learned how to utilize the $wpdb object to SELECT a single value or column from a couple of key WP tables.  In today’s follow-up, we’ll find out how to employ $wpdb’s get_row() and get_results() methods.

Selecting a Single Row

Just as get_column() retrieves one column of data, the get_row() function returns an entire row from the WP database. Things get interesting here because the function can return row data within three different types of data structures, depending on the value of the second – output_type – argument. They are: object, associative array, or numerically indexed array. No matter the data type, NULL is returned if the query doesn’t match any rows.  Similar to other get functions, more than one row may be returned by the query, but only the specified row is returned by the function; all of the rows are cached for later use and may be accessed on subsequent get_row() invocations by passing NULL as the query argument, along with a different row_offset.  Here is the syntax:

<?php $wpdb->get_row'query'output_typerow_offset ); ?> 

All three arguments are optional so that you can omit theoutput_type and row_offset on the first invocation, and the query on subsequent calls.  That being said, I wouldn’t recommend calling it without any arguments!  $wpdb methods don’t typically raise exceptions, so if you don’t supply a query on the first invocation, provide an invalid output_type, or out of bounds row_offset, get_row() will simply return NULL.

The output_type must be one of three pre-defined constants:

  • OBJECT – DEFAULT.  Result will be output as an object.
  • ARRAY_A - result will be output as an associative array.
  • ARRAY_N - result will be output as a numerically indexed array.

Here are some examples to highlight the differences. 

In this WordPress database, the wp_posts table contains some basic information about restaurant locations; the wp_post_meta table stores a varchar field that holds a restaurant’s base name.  A location would be something like “Westside Mall”, whereas the restaurant_base_name would simply be “MacDonald’s”:

Example 1

Here, get_row() is employed to retrieve all the information about the twentieth restaurant location in the wp_posts table.  

$query = "SELECT p.id, 
                  p.post_title  AS location_name,
                  pm.meta_value AS restaurant_base_name
           FROM $wpdb->posts AS p 
               LEFT JOIN $wpdb->postmeta AS pm
                 ON  p.id = pm1.post_id
                 AND pm.meta_key   = 'restaurant_base_name'
           WHERE p.post_type   = 'restaurant'
           AND   p.post_status = 'publish';";            
 
 //rows start at 0
 $restaurants = $wpdb->get_row($query, 21, OBJECT);
The properties of the $restaurants object are the column names of the result from the SQL query so that you can refer to it as follows:

echo $restaurants->restaurant_base_name; // prints "MacDonald’s"

As mentioned above, if there are less than twenty rows, you’ll simply get a NULL back.  Therefore it’s a good practice to always check for NULL in your PHP code before trying to access the result set data.

if ( $restaurants !== null ) {
   // proceed! 
   echo $restaurants->restaurant_base_name;
 } 
 else {
   echo 'no restaurants found!';
 }

Example 2

In contrast, using:

$restaurants = $wpdb->get_row( $query, ARRAY_A );

...would result in an associative array, whereby a field’s value may be accessed directly using its name:

echo $restaurants['restaurant_base_name']; // also prints "MacDonald’s"

Example 3

Invoking get_row() with the ARRAY_N output_type constant:

$restaurants = $wpdb->get_row( $query, ARRAY_N );

...would result in a zero-based numerically indexed array:

echo $restaurants[2]; // still prints "MacDonald’s"

Selecting a Full Result Set

As a DBA, you’re probably most familiar with fetching entire result sets in one go.  If that’s the case, then I have just the function for you.  The get_results() function returns the entire query result as an array where each element corresponds to one row of the query result. Like get_row(), each row can be stored within an object, an associative array, or a numerically indexed array. If no matching rows are found, or if there is an error, the function will return an empty array.  However, if your $query string is empty, or you pass an invalid $output_type, NULL will be returned instead.  Here’s the syntax:

<?php $wpdb->get_results'query'output_type ); ?> 

This time, the output_type can be one of four pre-defined constants.

  • OBJECT - DEFAULT.  Result will be output as a numerically indexed array of row objects.
  • OBJECT_K - result will be output as an associative array of row objects, using the first column's values as keys. Note that duplicates will be discarded.
  • ARRAY_A - result will be output as a numerically indexed array of associative arrays, using the column names as keys.
  • ARRAY_N - result will be output as a numerically indexed array of numerically indexed arrays. (fun stuff!)

Some examples might be in order.

Example 1

This code prints the base name of the first restaurant in the result set. Notice that $restaurants is a numerically indexed array of row objects:

$restaurants = $wpdb->get_results($query); // OBJECT is default 
 echo $restaurants[0]->restaurant_base_name; //"MacDonald’s" again! 

Example 2

In contrast, using:

$restaurants = $wpdb->get_results($query, OBJECT_K);

...would result in an associative array of row objects, using the first column's values as keys.  Since our first field is the post ID, we can use it to refer to a specific post, if we know the ID:

	echo $restaurants[13145]->restaurant_base_name; // also prints "MacDonald’s"

Example 3

Invoking get_results() with the ARRAY_A output_type constant:

$restaurants = $wpdb->get_results( $query, ARRAY_A );

...would result in a numerically indexed array of associative arrays, using the column names as keys.  Hence, the following statement displays the first row’s restaurant base name:

echo $restaurants[0]['restaurant_base_name']; // still prints "MacDonald’s"

Example 4

Invoking get_results() with the ARRAY_N output_type constant:

$restaurants = $wpdb->get_results( $query, ARRAY_N );

...would result in a numerically indexed array of numerically indexed arrays:

echo $restaurants[0][2]; // you guessed, it - "MacDonald’s"

Iterating over Result Sets

The get_column(), get_row(), and get_results() functions all offer the possibility for iterating over individual rows and/or columns.  The only caveat is that you have to coordinate your loop code with the output object type.  Whatever the return type, results are iterable using a foreach loop. The only thing that changes is how you access individual elements.  Here’s some PHP code that fetches the restaurant data as the default OBJECT type and prints each base name and location within an unordered HTML list:

$restaurants = $wpdb->get_results( $query );

echo '<ul>' . "\n";
 foreach ( $restaurants as $restaurant ) {
     echo '<li>' 
          . $restaurant->restaurant_base_name . ' - ' . $restaurant->location_name 
        . '<\li>' . "\n";
 }
 echo '</ul>' . "\n";

This is some of the resulting HTML output:

<ul>
 <!-- ... -->
 <li>NULL – Luisianie's</li>
 <li>NULL – Marty's Fish 'n Chips</li>
 <li>MacDonald's – 199 Reynold's Street</li>
 <li>MacDonald's – 1245 Reynold's Street</li>
 <li>MacDonald's – Airport, level 1</li>
 <li>MacDonald's – Airport, level 2</li>
 <li>MacDonald's - Bayview Mall</li>
 <li>MacDonald's – Cardiff Road</li>
 <!-- etc... -->
 </ul>

The entries with NULL locations are one-off restaurants, whereas those that have a location are part of a chain.  If you’d rather not see NULLs in your result set, you can substitute a string of your choice using the ISNULL() function.  Just pass it the meta_value field that contains the restaurant_base_name along with the text that you’d like to replace NULLs with:

SELECT p.id, 
        p.post_title AS location_name,
        ISNULL(pm.meta_value, 'N/A') AS restaurant_base_name

Conclusion

Whether you’re fetching a single row or an entire result set, WordPress offers plenty of array types to work with.  Unless prohibitive, I favor those data types that allow me to reference fields by their names. I find the code more descriptive that way. 

In an upcoming article, we’ll learn how to sanitize user input as well as how to query other WordPress tables.      

See all articles by Rob Gravelle

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved