WordPress:Displaying Posts Using a Custom Select Query

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.

描述

在你的WordPress发展历程的某个时刻，或许你需要通过非WordPress的' query_posts 架构提供的SELECT标准来显示一篇或更多的文章。例如，你有时或许必须JOIN WordPress表格以确定要显示的文章或使用自己表格中的储存数据来确定要显示的posts。 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.

以下概述的实例展示了选择有 Custom Field存入值的所有文章并把它们在建立在Page Template基础上的Page显示出来的过程。这些编码最初用来执行文章标签plugin，它允许以比WordPressCategories稍无序的构架来组织文章。你的使用或许有所不同，但以下的例子仍会给你提供些有用的一般过程说明。

Assumptions made in this Article

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

文章假设

本文通常假设你拥有PHP, MySQL知识和WordPress 使用能力。

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'

	*你至少拥有一篇 Custom Fields数据文章。自定义字段有“标签”键和“电子邮件”键值。

• 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.
• 你已创建一个Page并有一个Page Template链接。在此例中，假定模板名称为'Qbased'，它是从wp-content/themes/index.php模板复制的。如果你对此过程不熟悉，按照Creating your own Page Templates中的说明进行操作。

• As this is a somewhat advanced developer topic, familiarity with the core WordPress concept of WordPress:The Loop is suggested.
• 由于这是稍先进的开发主题，推荐掌握WordPress:The Loop的核心WordPress概念。

网页模板编码

查询

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 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':

首先，需要检索包含你要显示文章的recordset（数据集）。要做到这一点，需要使用WordPress$wpdb database class创建一个result set（结果集）。注意MySQL SELECT指令阐明了一个“简单的” JOIN。$pageposts在此会包含array对象。每个对象代表一篇有自定义字段key-value（键-键值）配对且键名为tag，值为email的“已发布”文章。

 <?php

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

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

 <?php

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

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

已修改的Loop（循环）

Now, to display posts collected into $pageposts by the previous SELECT criteria, you need to replace WordPress: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.

现在，若要通过以前的SELECT 标准来显示$pageposts中的文章，你需要用Qbased网页模板中你自己的循环编码来替代WordPress:The Loop。这需要创建一个已修改的循环（loop），使它能够循环$pageposts中的文章并显示它们。注意：下面loop(循环)中的结构/标记取自WordPress“默认”theme. 。

 <?php if ($pageposts): ?>
 <?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(); ?>">
    <?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; ?>

 <?php if ($pageposts): ?>
 <?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(); ?>">
    <?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:

	一行一行地审查编码的重要部分，你必须：

• A test to make sure that the query that populated $pageposts actually found some posts that matched the SELECT criteria:
• 测试以确保$pageposts中的查询可以查到符合SELECT标准中的文章：

<?php if ($pageposts): ?>

• A foreach loop to go through the posts returned in $pageposts and display them:

<?php foreach($pageposts as $post): ?>

<?php if ($pageposts): ?>

• A foreach loop to go through the posts returned in $pageposts and display them:

<?php foreach($pageposts as $post): ?>

• And, a call to the WordPress post formatting function, setup_postdata(), that automatically populates the required variables:

<?php setup_postdata($post); ?>

	*调用WordPress文章格式化函数，setup_postdata(),自动填入所需变量：

<?php setup_postdata($post); ?>

Loop （循环）内部

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. 由于例子中调用了setup_postdata($post);，你可以使用可包括在正常Wordpress循环（loop）中的相同 template tags，如the_content() 和 the_permalink()。这意味着你可以最小的麻烦用网页模板创建自己的文章显示结果，并自动利用你的Wordpress博客已激活的各种插件提供额外的格式和功能。

已完成的网页模板

Here is a complete example of the new template that works with the WordPress default theme. 这是Wordpress“默认”主题运行的新模板的完整事例。

<?php
/*
Template Name: Qbased
*/
?>

<?php get_header(); ?>

<div id="content" class="narrowcolumn">

<?php

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

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

?>
 <?php if ($pageposts): ?>
  <?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(); ?>">
      <?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(); ?>

<?php
/*
Template Name: Qbased
*/
?>

<?php get_header(); ?>

<div id="content" class="narrowcolumn">

<?php

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

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

?>
 <?php if ($pageposts): ?>
  <?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(); ?>">
      <?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 is passed as the "output_type" parameter for $wpdb->get_results(). setup_postdata() does not seem to work when ARRAY_A or ARRAY_N is passed in $wpdb->get_results(). 这里值得注意的是上述例子“只有”在对象通过的是$wpdb->get_results()的"output_type"参数时才运行。当ARRAY_A 或ARRAY_N在$wpdb->get_results()通过时，setup_postdata()似乎不运行。

自定义字段和类别基础上的查询

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. 下个事例设置了上个事例中使用的$querystr变量以获得类别1，2，和3中的有meta_key 'paragraf'的所有文章，并按meta_values升序排列。此例来源于Otto42在Forum Topic 121011. 中的回复。

 $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
    ";

 $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
    ";

with wordpress 2.3 you need to update the sql query shown above to this: This example gleaned from kernow's response in Forum Topic 121011

使用 wordpress2.3，你需要把以上显示的sql查询更新为： 此例来源于kernow在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 = 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

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 = 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

致谢

Many thanks to Kafkaesquii for pointing out a simpler method of populating the appropriate global variables, etc, using setup_postdata(). 非常感谢Kafkaesquii指出填写适当全局变量等的更简便方法：使用setup_postdata()。