Description

At some point in your WordPress development career you may be presented with the need to display one or more posts using SELECT criteria not provided by WordPress' query_posts architecture. For instance, it may become necessary to JOIN WordPress tables to determine which posts should be displayed, or you may want to use data stored in your own tables to determine which posts should be displayed.

The practical example, outlined below, demonstrates a process of selecting all posts with a particular Custom Field value stored, and displaying them in a Page based on a Page Template. Originally, this code was used to implement a post tagging plugin, which allowed organizing posts in less structured collections than the WordPress Categories. Your own usage may be very different, but the content and example should still give you a useful introduction to the general process involved.

Assumptions made in this Article

Generally, this article assumes you have a working knowledge of PHP, MySQL/MariaDB, and WordPress capabilities.

Specific assumptions for the example, however, are:

• You have at least one post with Custom Fields data. The Custom Fields should have a key of 'tag' and a value of 'email'
• You have created a Page and associated a Page Template with that page. For this example, assume the Template Name is 'Qbased' and was copied from the wp-content/themes/index.php template. If you are not familiar with this process, follow the instructions in Creating your own Page Templates.
• As this is a somewhat advanced developer topic, familiarity with the core WordPress concept of The Loop is suggested.

Code for the Page Template

The query

To begin with, it is necessary to retrieve the recordset containing the posts you want to display. To do this, create a result set using the WordPress $wpdb database class. Note that the MySQL/MariaDB SELECT statement illustrates a simple JOIN. Here, $pageposts will contain an array of objects. Each object will represent a published post that has custom field key-value pair - with the key being 'tag' and the value being 'email':

 <?php

 $querystr = "
    SELECT $wpdb->posts.* 
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
    AND $wpdb->postmeta.meta_key = 'tag' 
    AND $wpdb->postmeta.meta_value = 'email' 
    AND $wpdb->posts.post_status = 'publish' 
    AND $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_date < NOW()
    ORDER BY $wpdb->posts.post_date DESC
 ";

 $pageposts = $wpdb->get_results($querystr, OBJECT);
 
 ?>

The Revised Loop

Now, to display posts collected into $pageposts by the previous SELECT criteria, you need to replace The Loop with your own loop code in the Qbased Page Template. This requires creating a revised loop that cycles through the posts stored in $pageposts and displays them. Note: the structure / markup in the loop below is taken from the WordPress default theme.

 <?php if ($pageposts): ?>
 <?php global $post; ?>
 <?php foreach ($pageposts as $post): ?>
 <?php setup_postdata($post); ?>
 
 <div class="post" id="post-<?php the_ID(); ?>">
 <h2><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>">
    <?php the_title(); ?></a></h2>
    <small><?php the_time('F jS, Y') ?> <!-- by <?php the_author() ?> --></small>
    <div class="entry">
       <?php the_content('Read the rest of this entry »'); ?>
    </div>
    <p class="postmetadata">Posted in <?php the_category(', ') ?>