I have successfully used the following code on a page to list all of the posts (links to the post only) with the key "color" in alphabetical order by their values. I would like to have the post link text be the value (meta_value) and not the post title, but some posts have more than one value, and I don't know how to get just one of the values and in the correct alphabetical order.
For example, I have a post titled Post A with two "color" keys, one with the value "blue" and one with the value "yellow" and another post titled Post B with one "color" key and the value "green".
The code gives me the following list of post links :
Post A (<- this is for the value "blue")
Post B (<- this is for the value "green")
Post A (<- this is for the value "yellow")
I would rather have the following list of post links:
blue (<- this is a link to Post A)
green (<- this is a link to Post B)
yellow (<- this is another link to Post A)
I need to replace <?php the_title(); ?> with something to get the associated meta value for each, but what?
Here's the code:
<ul><?php $querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'color'
AND wposts.post_status = 'publish'
AND wposts.post_type = 'post'
AND wposts.post_date < NOW()
ORDER BY wpostmeta.meta_value ASC
";
$pageposts = $wpdb->get_results($querystr, OBJECT); ?>
<?php if ($pageposts): ?>
<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>
<li><a href="<?php the_permalink() ?>" rel="bookmark">
<?php the_title(); ?></a></li>
<?php endforeach; ?><ul>
<?php else : ?>
<?php endif; ?>