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.
Generally, this article assumes you have a working knowledge of PHP, MySQL/MariaDB, and WordPress capabilities.
Specific assumptions for the example, however, are:
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); ?>
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(', ') ?> | <?php edit_post_link('Edit', '', ' | '); ?> <?php comments_popup_link('No Comments »', '1 Comment »', '% Comments »'); ?></p> </div> <?php endforeach; ?> <?php else : ?> <h2 class="center">Not Found</h2> <p class="center">Sorry, but you are looking for something that isn't here.</p> <?php include (TEMPLATEPATH . "/searchform.php"); ?> <?php endif; ?>
And that's it!
To go through the important parts of the code, line by line, you have:
<?php if ($pageposts): ?>
<?php foreach($pageposts as $post): ?>
<?php global $post; ?>
<?php setup_postdata($post); ?>
Because setup_postdata($post); was called in our example, you can use the same template tags that can be included in a normal WordPress post loop, like the_content() and the_permalink(). This means that you can create your own post display results using a Page Template with a minimum amount of fuss, automatically taking advantage of the various plugins you may have activated in your WordPress blog to provide extra formatting and functionality.
Here is a complete example of the new template that works with the WordPress default theme.
<?php /* Template Name: Qbased */ ?> <?php get_header(); ?> <div id="content" class="narrowcolumn"> <?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); ?> <?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(', ') ?> | <?php edit_post_link('Edit', '', ' | '); ?> <?php comments_popup_link('No Comments »', '1 Comment »', '% Comments »'); ?></p> </div> <?php endforeach; ?> <?php else : ?> <h2 class="center">Not Found</h2> <p class="center">Sorry, but you are looking for something that isn't here.</p> <?php include (TEMPLATEPATH . "/searchform.php"); ?> <?php endif; ?> </div> <?php get_sidebar(); ?> <?php get_footer(); ?>
It is important to note here that the above example will work only when OBJECT or OBJECT_K (for unique results) is passed as the "output_type" parameter for $wpdb->get_results(). setup_postdata() requires an object and will not work when ARRAY_A or ARRAY_N are passed into $wpdb->get_results().
This next example sets the $querystr variable used in the above example, to get all posts in Categories 1,2, and 3, that have the meta_key 'paragraf', and then sorted ascending by the meta_values. This example gleaned from Otto42's response in Forum Topic 121011.
Use this example for version prior to WordPress 2.3:
$querystr = " SELECT $wpdb->posts.* FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id) WHERE $wpdb->postmeta.meta_key = 'paragraf' AND $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = 'post' AND $wpdb->post2cat.category_id IN (1,2,3) ORDER BY $wpdb->postmeta.meta_value ASC ";
Use this example for WordPress Version 2.3: This example gleaned from kernow's response in Forum Topic 121011
SELECT * FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id) LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) WHERE $wpdb->term_taxonomy.term_id IN (1,2,3) AND $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->posts.post_status = 'publish' AND $wpdb->postmeta.meta_key = 'paragraf' ORDER BY $wpdb->postmeta.meta_value ASC
Previous examples don't work with WordPress Version 2.5 or higher.
This example, tested Wordpress 2.9.2, is made to get posts in selected categories that are after a date, sorted ascending. It limits the number to just 4 items returned.
<?php global $wpdb; global $post; $querystr = " SELECT DISTINCT wposts.* FROM $wpdb->posts wposts LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) WHERE wpostmeta.meta_key = 'customDateField' AND wpostmeta.meta_value >= CURDATE() AND $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->term_taxonomy.term_id IN(1,2) ORDER BY wpostmeta.meta_value ASC LIMIT 4 "; ?>
The global $wpdb; line stops the Fatal error: Call to a member function get_var() on a non-object errors.
This example pulls pages with a certain custom field key, then sorts on the key's value.
$querystr = " SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'custom-key' AND wposts.post_type = 'page' ORDER BY wpostmeta.meta_value DESC ";
This example selects posts that have a common category name and custom field key, then sorts the selection by the custom key's value.
$querystr = " SELECT * FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id) LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id) WHERE $wpdb->terms.name = 'slides' AND $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = 'post' AND $wpdb->postmeta.meta_key = 'order' ORDER BY $wpdb->postmeta.meta_value ASC ";
Many thanks to Kafkaesquii for pointing out a simpler method of populating the appropriate global variables, etc, using setup_postdata().