Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted April 10, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Working with WordPress Result Sets

By Rob Gravelle

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



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM