Tuesday, March 19, 2024

Iterating over WordPress Database Rows

The wpdb class is defined in /wp-includes/wp-db.php. Unlike the wp_query class, the wpdb class should never be instantiated. Instead, WordPress provides a global object variable called $wpdb, which is an instantiated singleton of the wpdb class.

To access $wpdb in a function, declare it as a global variable using the global keyword:

function process_table_rows() {
  global $wpdb;
       
        $results = $wpdb->get_results("SELECT * FROM my_table_name WHERE id = `' . $id . '`;")
 
        //work with results here...
}

More on the get_results() Function

At first glance the get_results() function looks deceptively simple. It only takes two parameters, the query string and output type:

get_results( query_string, output_type );

The catch is that the object type you select will determine what type of looping structure and data access mechanism you use to get at your results. The output_type may be one of four pre-defined constants, or you can omit it entirely to return an OBJECT.

  • OBJECT – 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 first column’s values as keys (duplicates will be discarded).
  • ARRAY_A – result will be output as a numerically indexed array of associative arrays, using column names as keys.
  • ARRAY_N – result will be output as a numerically indexed array of numerically indexed arrays.

Looping Examples

To compare each return type, we’ll try out the same query with all the return type constants. In order to do so, I wrote the following function that passes along the return type. Being a constant, it can be used by the caller as well as within the function that uses it.

function get_menu_tags($output_type=ARRAY_A) {
  global $wpdb;
  return $wpdb->get_results("SELECT slug, name
                             FROM   `wp_terms`
                             WHERE  `slug` like 'menu_type_%'
                             OR     `slug` like 'menu_allergen_%'",
                             $output_type);
}

//call our method
$menu_tags = get_menu_tags(OBJECT_K);

Just as a reference, here is the data that will be returned:

The OBJECT Type

Whatever the return type, results are iterable using a foreach loop. The only thing that changes is how you access individual elements. Object properties are referenced using the -> object accessor.

$menu_tags = get_menu_tags(OBJECT);

echo '<ul>' . "\n";
foreach ( $menu_tags as $menu_tag ) {
        echo '<li>' . $menu_tag->slug . ' - ' . $menu_tag->name . '<\li>' . "\n";
}
echo '</ul>' . "\n";

You could also access an item directly using the numeric index. For example, the following code retrieves the second slug in the list:

$menu_tag[1]->slug;

The OBJECT_K Type

I find the OBJECT_K type to be the most interesting because associative arrays allow you to do something like this:

$saved_menu_tags = array('menu_allergen_nut', 'menu_allergen_peanut', 'menu_allergen_sesame');
$menu_tags       = get_menu_tags(OBJECT_K);

echo '<ul>' . "\n";
for ($i=0; $i<count($menu_tags); $i++ ){
          $menu_tag_desc = $menu_tags[$saved_menu_tags[$i]]->name;
                if ( strlen( $menu_tag_desc ) > 0 ) {
                    echo $menu_tag_desc;
                }
}
echo '</ul>' . "\n";

The ARRAY_A Type

Associative arrays are not all that different from objects except that square brackets [] are used instead of the -> accessor.

$menu_tags = get_menu_tags(ARRAY_A);

echo '<ul>' . "\n";
foreach ( $menu_tags as $menu_tag ) {
        echo '<li>' . $menu_tag["slug"] . ' - ' . $menu_tag["name"] . '<\li>' . "\n";
}
echo '</ul>' . "\n";

The ARRAY_N Type

Elements in numerically indexed arrays are identified using their numeric index so that $result[0] points to the first column of the current row in a loop.

$menu_tags = get_menu_tags(ARRAY_N);

echo '<ul>' . "\n";
foreach ( $menu_tags as $menu_tag ) {
        echo '<li>' . $menu_tag[0] . ' - ' . $menu_tag[1] . '<\li>' . "\n";
}
echo '</ul>' . "\n";

Conclusion

As long as you use the correct accessor for your chosen return type, you should have no trouble using which ever type best suits your purpose. It really comes down to how you want to access individual rows and columns.

Rob Gravelle resides in Ottawa, Canada, and is the founder of GravelleWebDesign.com. Rob has built systems for Intelligence-related organizations such as Canada Border Services, CSIS as well as for numerous commercial businesses.

In his spare time, Rob has become an accomplished guitar player, and has released several CDs. His band, Ivory Knight, was rated as one Canada’s top hard rock and metal groups by Brave Words magazine (issue #92) and reached the #1 spot in the National Heavy Metal charts on Reverb Nation.

Rob Gravelle
Rob Gravelle
Rob Gravelle resides in Ottawa, Canada, and has been an IT guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Popular Articles

Featured