WordPress:Displaying Posts Using a Custom Select Query

目錄

• 1 Description
• 2 描述
  • 2.1 Assumptions made in this Article
  • 2.2 文章假設(shè)
• 3 Code for the Page Template
• 4 網(wǎng)頁(yè)模板編碼
  • 4.1 The query
  • 4.2 查詢(xún)
  • 4.3 The Revised Loop
  • 4.4 已修改的Loop（循環(huán)）
    • 4.4.1 Within the Loop
    • 4.4.2 Loop （循環(huán)）內(nèi)部
  • 4.5 The Completed Page Template
  • 4.6 已完成的網(wǎng)頁(yè)模板
• 5 Query based on Custom Field and Category
• 6 自定義字段和類(lèi)別基礎(chǔ)上的查詢(xún)
• 7 Acknowledgements
• 8 致謝

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發(fā)展歷程的某個(gè)時(shí)刻，或許你需要通過(guò)非WordPress的' query_posts 架構(gòu)提供的SELECT標(biāo)準(zhǔn)來(lái)顯示一篇或更多的文章。例如，你有時(shí)或許必須JOIN WordPress表格以確定要顯示的文章或使用自己表格中的儲(chǔ)存數(shù)據(jù)來(lái)確定要顯示的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.

以下概述的實(shí)例展示了選擇有 Custom Field存入值的所有文章并把它們?cè)诮⒃?a href="/wiki/WordPress:Pages#Creating_your_own_Page_Templates" title="WordPress:Pages">Page Template基礎(chǔ)上的Page顯示出來(lái)的過(guò)程。這些編碼最初用來(lái)執(zhí)行文章標(biāo)簽plugin，它允許以比WordPressCategories稍無(wú)序的構(gòu)架來(lái)組織文章。你的使用或許有所不同，但以下的例子仍會(huì)給你提供些有用的一般過(guò)程說(shuō)明。

Assumptions made in this Article

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

文章假設(shè)

本文通常假設(shè)你擁有PHP, MySQL知識(shí)和WordPress 使用能力。

Specific assumptions for the example, however, are:

但此例的特定假設(shè)是：

• 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數(shù)據(jù)文章。自定義字段有“標(biāo)簽”鍵和“電子郵件”鍵值。

• 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.
• 你已創(chuàng)建一個(gè)Page并有一個(gè)Page Template鏈接。在此例中，假定模板名稱(chēng)為'Qbased'，它是從wp-content/themes/index.php模板復(fù)制的。如果你對(duì)此過(guò)程不熟悉，按照Creating your own Page Templates中的說(shuō)明進(jìn)行操作。

• As this is a somewhat advanced developer topic, familiarity with the core WordPress concept of WordPress:The Loop is suggested.
• 由于這是稍先進(jìn)的開(kāi)發(fā)主題，推薦掌握WordPress:The Loop的核心WordPress概念。

網(wǎng)頁(yè)模板編碼

查詢(xún)

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（數(shù)據(jù)集）。要做到這一點(diǎn)，需要使用WordPress$wpdb database class創(chuàng)建一個(gè)result set（結(jié)果集）。注意MySQL SELECT指令闡明了一個(gè)“簡(jiǎn)單的” JOIN。$pageposts在此會(huì)包含array對(duì)象。每個(gè)對(duì)象代表一篇有自定義字段key-value（鍵-鍵值）配對(duì)且鍵名為tag，值為email的“已發(fā)布”文章。

 <?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（循環(huán)）

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.

現(xiàn)在，若要通過(guò)以前的SELECT 標(biāo)準(zhǔn)來(lái)顯示$pageposts中的文章，你需要用Qbased網(wǎng)頁(yè)模板中你自己的循環(huán)編碼來(lái)替代WordPress:The Loop。這需要?jiǎng)?chuàng)建一個(gè)已修改的循環(huán)（loop），使它能夠循環(huán)$pageposts中的文章并顯示它們。注意：下面loop(循環(huán))中的結(jié)構(gòu)/標(biāo)記取自WordPress“默認(rèn)”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:
• 測(cè)試以確保$pageposts中的查詢(xún)可以查到符合SELECT標(biāo)準(zhǔn)中的文章：

<?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); ?>

	*調(diào)用WordPress文章格式化函數(shù)，setup_postdata(),自動(dòng)填入所需變量：

<?php setup_postdata($post); ?>

Loop （循環(huán)）內(nèi)部

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. 由于例子中調(diào)用了setup_postdata($post);，你可以使用可包括在正常Wordpress循環(huán)（loop）中的相同 template tags，如the_content() 和 the_permalink()。這意味著你可以最小的麻煩用網(wǎng)頁(yè)模板創(chuàng)建自己的文章顯示結(jié)果，并自動(dòng)利用你的Wordpress博客已激活的各種插件提供額外的格式和功能。

已完成的網(wǎng)頁(yè)模板

Here is a complete example of the new template that works with the WordPress default theme. 這是Wordpress“默認(rèn)”主題運(yùn)行的新模板的完整事例。

<?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(). 這里值得注意的是上述例子“只有”在對(duì)象通過(guò)的是$wpdb->get_results()的"output_type"參數(shù)時(shí)才運(yùn)行。當(dāng)ARRAY_A 或ARRAY_N在$wpdb->get_results()通過(guò)時(shí)，setup_postdata()似乎不運(yùn)行。

自定義字段和類(lèi)別基礎(chǔ)上的查詢(xún)

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. 下個(gè)事例設(shè)置了上個(gè)事例中使用的$querystr變量以獲得類(lèi)別1，2，和3中的有meta_key 'paragraf'的所有文章，并按meta_values升序排列。此例來(lái)源于Otto42在Forum Topic 121011. 中的回復(fù)。

 $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查詢(xún)更新為： 此例來(lái)源于kernow在Forum Topic 121011 中的回復(fù)

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指出填寫(xiě)適當(dāng)全局變量等的更簡(jiǎn)便方法：使用setup_postdata()。