WordPress:Displaying Posts Using a Custom Select Query
描述
在你的WordPress發(fā)展歷程的某個時刻，或許你需要通過非WordPress的 query_posts 架構(gòu)提供的選擇標準來顯示一篇或更多的文章。例如，你有時或許必須加入 WordPress表格以確定要顯示的文章或使用自己表格中的儲存數(shù)據(jù)來確定要顯示正在寫的文章。
以下概述的實例展示了這樣一個過程：選擇有 定制區(qū)域存入值的所有文章并把它們在建立在頁面模板基礎(chǔ)上的頁面顯示出來。這些編碼最初用來執(zhí)行文章標簽插件，它允許以比WordPress分類稍無序的構(gòu)架來組織文章。你的使用或許有所不同，但以下的例子仍會給你提供些有用的一般過程說明。
Assumptions made in this Article
文章假設(shè)
Generally, this article assumes you have a working knowledge of PHP, MySQL, and WordPress capabilities. 本文通常假設(shè)你擁有PHP, MySQL知識和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ù)文章。自定義字段有“標簽”鍵和“電子郵件”鍵值。
- 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)建一個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 循環(huán) is suggested.
- 由于這是稍先進的開發(fā)主題，推薦掌握循環(huán)的WordPress核心概念。
Code for the Page Template
網(wǎng)頁模板編碼
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 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ù)集），里面包含你要顯示的文章。要做到這一點，需要使用WordPress$wpdb database class創(chuàng)建一個result set（結(jié)果集）。注意MySQL SELECT指令闡明了一個“簡單的” JOIN。$pageposts在此會包含array對象。每個對象代表一篇有自定義字段key-value（鍵-鍵值）配對且鍵名為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);
?>
The Revised Loop
已修改的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)在，若要通過以前的SELECT 標準來顯示$pageposts中的文章，你需要用Qbased網(wǎng)頁模板中你自己的循環(huán)編碼來替代WordPress:The Loop。這需要創(chuàng)建一個已修改的循環(huán)（loop），使它能夠循環(huán)$pageposts中的文章并顯示它們。注意：下面loop(循環(huán))中的結(jié)構(gòu)/標記取自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:
- foreach loop審查已返回$pageposts的文章，并顯示文章：
<?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(),自動填入所需變量：
<?php setup_postdata($post); ?>
Within the Loop
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)頁模板創(chuàng)建自己的文章顯示結(jié)果，并自動利用你的Wordpress博客已激活的各種插件提供額外的格式和功能。
The Completed Page Template
已完成的網(wǎng)頁模板
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"為參數(shù)，面向?qū)ο笸ㄟ^時才運行。當ARRAY_A 或ARRAY_N在$wpdb->get_results()通過時，setup_postdata()似乎不運行。
Query based on Custom Field and Category
自定義字段和類別基礎(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 Forum Topic 121011. 此事例設(shè)置了上個事例中使用的$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
Acknowledgements
致謝
Many thanks to Kafkaesquii for pointing out a simpler method of populating the appropriate global variables, etc, using setup_postdata(). 非常感謝Kafkaesquii指出填寫適當全局變量等的更簡便方法：使用setup_postdata()。