編輯“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 [[WordPress:Wikipedia:SELECT|SELECT]] criteria not provided by WordPress' [[WordPress:Template Tags/query posts | query_posts]] architecture.  For instance, it may become necessary to [[WordPress:Wikipedia:JOIN|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 [[WordPress:Writing Posts|posts]] should be displayed.

The practical example, outlined below, demonstrates a process of selecting all posts with a particular [[WordPress:Using Custom Fields | Custom Field]] value stored, and displaying them in a [[WordPress:Pages|Page]] based on a [[WordPress:Pages#Creating_your_own_Page_Templates|Page Template]]. Originally, this code was used to implement a post tagging [[WordPress:Plugins|plugin]], which allowed organizing posts in less structured collections than the WordPress [[WordPress:Manage_Categories_SubPanel|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 [[WordPress:Glossary#PHP|PHP]], [[WordPress:Glossary#MySQL|MySQL]], and WordPress capabilities.

Specific assumptions for the example, however, are:

* You have at least one post with [[WordPress:Using Custom Fields | Custom Fields]] data.  The Custom Fields should have a key of 'tag' and a value of 'email'
* You have created a [[WordPress:Pages|Page]] and associated a [[WordPress:Pages#Page_Templates|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 [[WordPress:Pages#Creating_your_own_Page_Templates|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. 
 
== Code for the Page Template ==
===The query===
To begin with, it is necessary to retrieve the [[WordPress:Glossary#Recordset|recordset]] containing the posts you want to display.  To do this, create a result set using the WordPress [[WordPress:Function_Reference/wpdb_Class|$wpdb database class]].  Note that the [[WordPress:Glossary#MySQL|MySQL]] [[WordPress:Wikipedia:SELECT|SELECT]] statement illustrates a '''simple''' [[WordPress:Wikipedia:JOIN|JOIN]].  Here, <tt>$pageposts</tt> will contain an [[WordPress:Glossary#Array|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':
<pre>
 <?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);
 
 ?>
</pre>

===The Revised Loop===
Now, to display posts collected into <tt>$pageposts</tt> by the previous [[WordPress:Wikipedia:SELECT|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 <tt>$pageposts</tt> and displays them. Note: the structure / markup in the loop below is taken from the WordPress '''default''' [[WordPress:Using Themes|theme]].

<pre>
 <?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 &raquo;'); ?>
    </div>
    <p class="postmetadata">Posted in <?php the_category(', ') ?> | <?php edit_post_link('Edit', '', ' | '); ?>  
    <?php comments_popup_link('No Comments &#187;', '1 Comment &#187;', '% Comments &#187;'); ?></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; ?>
</pre>

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 <tt>$pageposts</tt> actually found some posts that matched the [[WordPress:Wikipedia:SELECT|SELECT]] criteria:
<pre>
<?php if ($pageposts): ?>
</pre>
* A [[WordPress:Wikipedia:Foreach|foreach loop]] to go through the posts returned in <tt>$pageposts</tt> and display them:
<pre>
<?php foreach($pageposts as $post): ?>
</pre>

* And, a call to the WordPress post formatting function, <tt>setup_postdata()</tt>, that automatically populates the required variables: 
<pre>
<?php setup_postdata($post); ?>
</pre>

==== Within the Loop ====
Because <tt>setup_postdata($post);</tt> was called in our example, you can use the same [[WordPress:Template Tags | template tags]] that can be included in a normal WordPress post loop, like <tt>the_content()</tt> and <tt>the_permalink()</tt>. 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.

=== The Completed Page Template ===
Here is a complete example of the new template that works with the WordPress '''default''' theme.
<pre>
<?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(); ?>
</pre>
It is important to note here that the above example will work '''only''' when OBJECT is passed as the "output_type" parameter for <code>$wpdb->get_results()</code>. setup_postdata() does not seem to work when ARRAY_A or ARRAY_N is passed in $wpdb->get_results().
==Query based on Custom Field and Category==
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 [http://wordpress.org/support/topic/121011 Forum Topic 121011].
<pre>
 $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
    ";

</pre>



'''with wordpress 2.3 you need to update the sql query shown above to this:'''
This example gleaned from kernow's response in [http://wordpress.org/support/topic/121011 Forum Topic 121011]

<pre>
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
</pre>

== Acknowledgements ==

Many thanks to [http://wordpress.org/support/profile/6445 Kafkaesquii] for pointing out a simpler method of populating the appropriate global variables, etc, using setup_postdata().
@@ 第1行： / 第1行： @@
-== 描述 ==
+== Description ==
-在你的WordPress發(fā)展歷程的某個(gè)時(shí)刻，或許你需要通過(guò)非WordPress的[[WordPress:Template Tags/query posts | query_posts]] 架構(gòu)提供的[[WordPress:Wikipedia:SELECT|選擇]]標(biāo)準(zhǔn)來(lái)顯示一篇或更多的文章。例如，你有時(shí)或許必須[[WordPress:Wikipedia:JOIN|加入]] WordPress表格以確定要顯示的文章或使用自己表格中的儲(chǔ)存數(shù)據(jù)來(lái)確定要顯示[[WordPress:Writing Posts|正在寫(xiě)的文章]]。
+At some point in your WordPress development career you may be presented with the need to display one or more posts using [[WordPress:Wikipedia:SELECT|SELECT]] criteria not provided by WordPress' [[WordPress:Template Tags/query posts | query_posts]] architecture.? For instance, it may become necessary to [[WordPress:Wikipedia:JOIN|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 [[WordPress:Writing Posts|posts]] should be displayed.
-以下概述的實(shí)例展示了這樣一個(gè)過(guò)程：選擇有[[WordPress:Using Custom Fields | 定制區(qū)域]]存入值的所有文章并把它們?cè)诮⒃?/del>[[WordPress:Pages#Creating_your_own_Page_Templates|頁(yè)面模板]]基礎(chǔ)上的[[WordPress:Pages|頁(yè)面]]顯示出來(lái)。這些編碼最初用來(lái)執(zhí)行文章標(biāo)簽[[WordPress:Plugins|插件]]，它允許以比WordPress[[WordPress:Manage_Categories_SubPanel|分類]]稍無(wú)序的構(gòu)架來(lái)組織文章。你的使用或許有所不同，但以下的例子仍會(huì)給你提供些有用的一般過(guò)程說(shuō)明。
+The practical example, outlined below, demonstrates a process of selecting all posts with a particular [[WordPress:Using Custom Fields | Custom Field]] value stored, and displaying them in a [[WordPress:Pages|Page]] based on a [[WordPress:Pages#Creating_your_own_Page_Templates|Page Template]]. Originally, this code was used to implement a post tagging [[WordPress:Plugins|plugin]], which allowed organizing posts in less structured collections than the WordPress [[WordPress:Manage_Categories_SubPanel|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è) ===
+=== Assumptions made in this Article ===
-?
+Generally, this article assumes you have a working knowledge of [[WordPress:Glossary#PHP|PHP]], [[WordPress:Glossary#MySQL|MySQL]], and WordPress capabilities.
-本文通常假設(shè)你擁有[[WordPress:Glossary#PHP|PHP]], [[WordPress:Glossary#MySQL|MySQL]]知識(shí)和WordPress 使用能力。
-?
-但此例的特定假設(shè)是：
-*你至少擁有一篇[[WordPress:Using Custom Fields |定制區(qū)域]]數(shù)據(jù)文章。自定義區(qū)域有“標(biāo)簽”鍵和“電子郵件”鍵值。
-?
-*你已創(chuàng)建一個(gè)[[WordPress:Pages|頁(yè)面]]并有一個(gè)[[WordPress:Pages#Page_Templates|頁(yè)面模板]]鏈接。在此例中，假定模板名稱為'Qbased'，它是從wp-content/themes/index.php模板復(fù)制的。如果你對(duì)此過(guò)程不熟悉，按照[[WordPress:Pages#Creating_your_own_Page_Templates|創(chuàng)建你自己的頁(yè)面模板]]中的說(shuō)明進(jìn)行操作。
-?
-*由于這是稍先進(jìn)的開(kāi)發(fā)主題，推薦掌握[[WordPress:The Loop|循環(huán)]]的WordPress核心概念。
-?
-== 網(wǎng)頁(yè)模板編碼 ==
-?
-===查詢===
-?
-?
-首先，需要檢索[[WordPress:Glossary#Recordset|數(shù)據(jù)集]，里面包含你要顯示的文章。要做到這一點(diǎn)，需要使用WordPress[[WordPress:Function_Reference/wpdb_Class|$wpdb 數(shù)據(jù)庫(kù) 類]]創(chuàng)建一個(gè)（結(jié)果集）。注意[[WordPress:Glossary#MySQL|MySQL]] [[WordPress:Wikipedia:SELECT|SELECT]]指令闡明了一個(gè)“簡(jiǎn)單的” [[WordPress:Wikipedia:JOIN|JOIN]]。<tt>$pageposts</tt>在此會(huì)包含[[WordPress:Glossary#Array|數(shù)組]]對(duì)象。每個(gè)對(duì)象代表一篇有自定義字段key-value（鍵-鍵值）配對(duì)且鍵名為tag，值為email的“已發(fā)布”文章。
+Specific assumptions for the example, however, are:
+* You have at least one post with [[WordPress:Using Custom Fields | Custom Fields]] data.? The Custom Fields should have a key of 'tag' and a value of 'email'
+* You have created a [[WordPress:Pages|Page]] and associated a [[WordPress:Pages#Page_Templates|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 [[WordPress:Pages#Creating_your_own_Page_Templates|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.
+== Code for the Page Template ==
+===The query===
+To begin with, it is necessary to retrieve the [[WordPress:Glossary#Recordset|recordset]] containing the posts you want to display.? To do this, create a result set using the WordPress [[WordPress:Function_Reference/wpdb_Class|$wpdb database class]].? Note that the [[WordPress:Glossary#MySQL|MySQL]] [[WordPress:Wikipedia:SELECT|SELECT]] statement illustrates a '''simple''' [[WordPress:Wikipedia:JOIN|JOIN]].? Here, <tt>$pageposts</tt> will contain an [[WordPress:Glossary#Array|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':
 <pre>
 ? <?php
@@ 第42行： / 第35行： @@
 </pre>
+===The Revised Loop===
+Now, to display posts collected into <tt>$pageposts</tt> by the previous [[WordPress:Wikipedia:SELECT|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 <tt>$pageposts</tt> and displays them. Note: the structure / markup in the loop below is taken from the WordPress '''default''' [[WordPress:Using Themes|theme]].
-===已修改的Loop（循環(huán)）===
-現(xiàn)在，若要通過(guò)以前的[[WordPress:Wikipedia:SELECT|SELECT]] 標(biāo)準(zhǔn)來(lái)顯示<tt>$pageposts</tt>中的文章，你需要用Qbased網(wǎng)頁(yè)模板中你自己的循環(huán)編碼來(lái)替代[[WordPress:The Loop]]。這需要?jiǎng)?chuàng)建一個(gè)已修改的循環(huán)（loop），使它能夠循環(huán)<tt>$pageposts</tt>中的文章并顯示它們。注意：下面loop(循環(huán))中的結(jié)構(gòu)/標(biāo)記取自WordPress“默認(rèn)”[[WordPress:Using Themes|主題]].
-。
 <pre>
 ? <?php if ($pageposts): ?>
@@ 第69行： / 第61行： @@
 </pre>
-就是它！
+And that's it!
-?
-一行一行地審查編碼的重要部分，你必須：
-*測(cè)試以確保<tt>$pageposts</tt>中的查詢可以查到符合[[WordPress:Wikipedia:SELECT|SELECT]]標(biāo)準(zhǔn)的文章：
+To go through the important parts of the code, line by line, you have:
+* A test to make sure that the query that populated <tt>$pageposts</tt> actually found some posts that matched the [[WordPress:Wikipedia:SELECT|SELECT]] criteria:
 <pre>
 <?php if ($pageposts): ?>
 </pre>
-*[[WordPress:Wikipedia:Foreach|foreach loop]]審查已返回<tt>$pageposts</tt>的文章，并顯示文章：
+* A [[WordPress:Wikipedia:Foreach|foreach loop]] to go through the posts returned in <tt>$pageposts</tt> and display them:
 <pre>
 <?php foreach($pageposts as $post): ?>
 </pre>
-*調(diào)用WordPress文章格式化函數(shù)，<tt>setup_postdata()</tt>,自動(dòng)填入所需變量：
+* And, a call to the WordPress post formatting function, <tt>setup_postdata()</tt>, that automatically populates the required variables:
 <pre>
 <?php setup_postdata($post); ?>
 </pre>
-==== Loop （循環(huán)）內(nèi)部====
+==== Within the Loop ====
-由于例子中調(diào)用了<tt>setup_postdata($post);</tt>，你可以使用可包括在正常Wordpress循環(huán)（loop）中的相同[[WordPress:Template Tags |模板標(biāo)簽]]，如<tt>the_content()</tt> 和 <tt>the_permalink()</tt>。這意味著你能夠較方便地用網(wǎng)頁(yè)模板創(chuàng)建自己的文章顯示結(jié)果，并自動(dòng)利用你的Wordpress博客已激活的各種插件提供額外的格式和功能。
+Because <tt>setup_postdata($post);</tt> was called in our example, you can use the same [[WordPress:Template Tags | template tags]] that can be included in a normal WordPress post loop, like <tt>the_content()</tt> and <tt>the_permalink()</tt>. 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.
-===已完成的網(wǎng)頁(yè)模板===
+=== The Completed Page Template ===
-這是Wordpress“默認(rèn)”主題運(yùn)行的新模板的完整事例。
+Here is a complete example of the new template that works with the WordPress '''default''' theme.
 <pre>
 <?php
@@ 第151行： / 第141行： @@
 <?php get_footer(); ?>
 </pre>
-?
+It is important to note here that the above example will work '''only''' when OBJECT is passed as the "output_type" parameter for <code>$wpdb->get_results()</code>. setup_postdata() does not seem to work when ARRAY_A or ARRAY_N is passed in $wpdb->get_results().
-這里值得注意的是，上述例子“只有”當(dāng)<code>$wpdb->get_results()</code>是以"output_type"為參數(shù)，面向?qū)ο笸ㄟ^(guò)時(shí)才運(yùn)行。當(dāng)ARRAY_A 或ARRAY_N在$wpdb->get_results()通過(guò)時(shí)，setup_postdata()似乎不運(yùn)行。
+==Query based on Custom Field and Category==
-==定制區(qū)域和類別基礎(chǔ)上的查詢==
+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 [http://wordpress.org/support/topic/121011 Forum Topic 121011].
-此事例設(shè)置了上個(gè)事例中使用的$querystr變量以獲得類別1，2，和3中的有meta_key 'paragraf'的所有文章，并按meta_values升序排列。此例來(lái)源于Otto42在[http://wordpress.org/support/topic/121011 Forum Topic 121011].
-中的回復(fù)。
 <pre>
 ? $querystr = "
@@ 第172行： / 第160行： @@
-'''使用 wordpress2.3，你需要把以上顯示的sql查詢更新為：'''
-此例來(lái)源于kernow在[http://wordpress.org/support/topic/121011 Forum Topic 121011] 中的回復(fù)
+'''with wordpress 2.3 you need to update the sql query shown above to this:'''
+This example gleaned from kernow's response in [http://wordpress.org/support/topic/121011 Forum Topic 121011]
 <pre>
@@ 第187行： / 第175行： @@
 ORDER BY $wpdb->postmeta.meta_value ASC
 </pre>
-==致謝 ==
+?
-非常感謝[http://wordpress.org/support/profile/6445 Kafkaesquii]指出填寫(xiě)適當(dāng)全局變量等的更簡(jiǎn)便方法：使用setup_postdata()。
+== Acknowledgements ==
+?
+Many thanks to [http://wordpress.org/support/profile/6445 Kafkaesquii] for pointing out a simpler method of populating the appropriate global variables, etc, using setup_postdata().