List posts by Key

oops, I had removed that between testing it myself.

You want the query to read:
SELECT ID, post_title, post_date, post_content, meta_value, guid FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id WHERE post_status = ‘publish’ AND meta_key=’BookTitle’ ORDER BY meta_value, post_date DESC LIMIT 5

it’s in the WHERE statement. I also added in post_date in the ORDER BY statement

2 questions:
Which version of WP are you using? Are you putting the snippet into a post (even a Page post) or into a template (such as index.php)?

EDIT: you also need to add in a < /a > tag after {$post->post_date} — the formatting here removed that on me.

Geoffe,

She be workin’ perfect matey!

Here is the code I am using (in case anyone else is trying to do the same thing):

<?php
if ( $recentposts =
$wpdb->get_results("SELECT ID, post_title, post_date, post_content, meta_value, guid FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id WHERE post_status = 'publish' AND meta_key='BookTitle' ORDER BY meta_value, post_date DESC LIMIT 5")) {
foreach ($recentposts as $post) {
echo "<a href="{$post->guid}" title="Permanent Link: {$post->post_title}">{$post->meta_value}</a>
";
} } ?>

Comes out brilliant for both BookTitles and AuthourNames. Thanks so much for your help.

And for the record, I am using WP 2.0.2 and I have the snippet in a special template called indexes.php which will contain both books and interviews in two long lists.

Thanks again!

excellent. congratulations.

Wowzers, what a thread. I’m gonna try to figure out that sollution, b/c this is more like what I was trying to accomplish when I asked Kaf for the Custom Fields Search.

In my case, Posts are Songs, which have custom fields: Singer, Songwriter, Album…

and I would like to make a Page that lists, for example:

All songs by Singer(KEY): Susan Smith(VALUE)
–Songtitle (post title) 1 which is sung by Susan
–Songtitle (post title) 2 which is sung by Susan
–Songtitle (post title) 3 which is sung by Susan

All songs on Album(KEY): Best Of Susan
–Songtitle (post title) 1 which is on Best Of
–Songtitle (post title) 1 which is on Best Of
–Songtitle (post title) 1 which is on Best Of

@Younghus, do you think your solution would fit my scenario? It seems similar?

It should fit, although you will want to remove the LIMIT 5 if you want to get all songs.

And you’ll need to specify WHERE post_status = ‘publish’ AND meta_key=’Singer’ AND meta_value=’Susan Smith’

just to get all the songs by Susan Smith.

Cool, thanks for your work on this. I am going to try it this weekend.

Obviously, you could also pretty easily connect up with something like CG-Amazon to give you live links direct to a single book, or a list of books by an author, prices or not, etc. Just a thought — happened upon this discussion and thought I’d throw in $0.02. ??

-d

Ok, I’m trying to figure out how this applies to what I’m trying to do, and right now I’m lost! I hope someone can help me sort through it.

I have one custom field on which I want to sort: author.

I want a list of post titles, sorted by the “author” field, and I want to display the post title, and the author’s name.

At present, I have them sorted by the post title, which is the name of the book. See https://www.timelessword.com/wordpress

I just want to sort by author (the meta_key), not post title. I’ve read https://codex.www.ads-software.com/Displaying_Posts_Using_a_Custom_Select_Query but I’m lost….it says you have to have a meta_key of “tag” and a meta_value of “email” — I realize I could change those for my needs, but it doesn’t seem to accomplish a sorting function.

Any help would be most appreciated. I can tell from posts that this is something lots of people want to do, so I’m surprised that there isnt’ a simpler way to accomplish it.

Thanks in advance for any help you can provide!

Looks like you want to do much the same thing as Younghusband and Dgold above.

Try:
<?php
if ( $recentposts =
$wpdb->get_results("SELECT ID, post_title, post_date, meta_value, guid FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id WHERE post_status = 'publish' AND meta_key='author' ORDER BY meta_value, post_title DESC")) {
foreach ($recentposts as $post) {
echo "< a href="/wordpress/?p={$post->ID}" title="{$post->post_title}">{$post->post_title}< /a> by {$post->meta_value}
<hr>
";
} } ?>

…to get a list of every Book sorted by author name (1st) and secondarily sorted by title.

If you had permalinks set up, you could use this in the foreach loop:
<?php
foreach ($recentposts as $post) {
echo "< a href="{$post->guid}" title="Permanent Link: {$post->post_title}">{$post->post_title}< /a> by {$post->meta_value}
";
} ?>
…but you haven’t set permalinks, which is okay.

If you were to set a value for meta_value, it would be an author’s name if the key was ‘author’. When you want to list every book/post, of every author, you wouldn’t want to set meta_value.

Thanks Geoff. Where exactly in my file do I put this? Inside the loop? What do I remove? Here is my present code:

<?php
$posts = query_posts($query_string . 'cat=2&orderby=title&order=asc&posts_per_page=-1');
if ( have_posts() ) : while ( have_posts() ) : the_post(); ?>
<div class="post" id="post-<?php the_ID(); ?>">
<a href="<?php the_permalink(); ?>"><?php the_title(); ?></a> by <?php foreach(get_post_custom_values('author') as $author) {
echo $author;
} ?>
</div>
<?php endwhile; else: ?>
Sorry, no posts matched your criteria.
<?php endif; ?>


No idea if it’s “good” code or not, but it works. When I try to add the new hunk of code, I get error messages. I’m sure I’m putting it in the wrong place.

Also, I do intend to use permalinks ultimately, I just haven’t set them up yet. Trying to get the bugs worked out before I add that, because it always causes grief with my server. But the code when using permalinks is certainly simpler. Maybe I should get that in place first.

Thanks again for your help. ??

The code I offered is a replacement for the Loop script that you have pasted there.

When I do that, I get:

Parse error: parse error, unexpected ‘?’ in /var/www/public_html/kalico2/virtualhosts/timelessword/wordpress/wp-content/themes/tw/index.php on line 11

Line 11 is the “echo” line.

I appreciate your help with this. I’m stymied. ??

oops, you need to escape (by \ – backslash) the quotes in the code for the echo statement. I’d copied what Younghusband had written for his modification but you’ll need to escape the quotes in the echo statement because that was an oversight and obvious error.

So:

<?php
if ( $recentposts =
$wpdb->get_results("SELECT ID, post_title, post_date, meta_value, guid FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id WHERE post_status = 'publish' AND meta_key='author' ORDER BY meta_value, post_title DESC")) {
foreach ($recentposts as $post) {
echo "< a href=\"/wordpress/?p={$post->ID}\" title=\"{$post->post_title}\">{$post->post_title}< /a> by {$post->meta_value}
<hr>
";
} } ?>

is what you want. And I’ve discovered why this probably occurred: If I put in a backslash \ here, when I go back and edit the post, silly WordPress Support forums will remove all \’s in my post.

Let that be a WARNING to anyone copying some of the code above or ever attempting to use backslashes in support forums. The echo statements above that contain quote marks – ” – between their quote marks need to have those inner quote marks escaped with a backslash – \.

And these forums don’t remove all \’s, only the ones that precede a “. So if I type \”, and go back and edit this post, it will become “. Word to the wise.

Thanks again Geoff! Warning indeed — in fact, I had to also escape the < in the closing “a” tag. And after I removed the “hr” tag, and replaced it with “p” tags, well that made a further mess, so that I didn’t have to escape the < after all….point being, watch for those escaping characters!! No wonder Younghusband’s code didnt’ work when I tried it earlier. That PLUS knowing it replaces the loop, made the whole thing work.

I think this has solved my problem. All the best to you, Geoff. Thanks for your help!