You are using $post for cross purposes. By convention it is the current post object within the WP loop, but in your case it is merely a product name from post meta.
I’m assuming all of the products you end up with in $prodnames have an associated post ID that you can use to get the permalink, but you can’t simply stick ->ID on to a product name and the associated ID magically appears. You must first get the associated post object before that will work!
You get a post object with get_post() but you must have the post ID for that to work. If you had the ID, you wouldn’t need the object! Thus you must query for the post by some other value using get_posts() along with the slug or by searching for the title.
As you can imagine, all these queries for each product can incur a lot of overhead. It’d be much better if the ID were stored in post meta instead of name, then getting the other data needed is much more efficient.
Thread Starter
lolo23
(@lolo23)
Thanks for you reply. I had guessed the code was probably a bit of a mess. I had also tried to do the same thing with sql, which successfully got me the list of product names with their permalinks, but then I wasn’t sure where to make it DISTINCT!
<?php
$querystr = "
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)
LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE $wpdb->term_taxonomy.term_id = '10'
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->postmeta.meta_key = 'product_name'
ORDER BY $wpdb->postmeta.meta_value ASC
";
$pageposts = $wpdb->get_results($querystr, OBJECT);
?>
<?php if ($pageposts): ?>
<?php global $post; ?>
<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>
<li>
<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>">
<?php the_field('product_name');?></a>
</li>
<?php endforeach; ?>
<?php endif; ?>
My SQL is weak but AFAIK DISTINCT always occurs after SELECT, so SELECT DISTINCT * FROM $wpdb->posts ...
Thread Starter
lolo23
(@lolo23)
Thanks. The above code didn’t work out for me either, I think it was the JOIN section that was wrong.
Just to update for anyone else who should come across this, I solved my problem with the below code:
`<?php
$cat_id = 10;
$querystr = “
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = ‘product_name’
AND wposts.post_type = ‘post’
AND ID IN ( SELECT object_id FROM $wpdb->term_relationships WHERE term_taxonomy_id = $cat_id )
GROUP BY wpostmeta.meta_value
“;
$pageposts = $wpdb->get_results($querystr, OBJECT);
?>
<?php if ($pageposts): ?>
<?php global $post; ?>
<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>
“><?php echo the_field(‘product_name’);?>
<?php endforeach; ?>
<?php endif; ?>’
