List values for a given meta_key in the current category WordPress
- SOLVED
Im attempting to list on basic category pages (category.php) a list of all values for a certain meta_key ('product_brands' in this case) for the currently showing category. Any takers?
Im using this query to do the heavy lifting on this category template:
<?php
$sort= $_GET['sort'];
if($sort == "brand") {
// sort by brand;
$meta_key= "product_brand";
$orderby= "meta_value";
$order= "ASC";
} else {
// sort by price;
$meta_key= "Retail_Price";
$orderby= "meta_value_num";
$order= "DESC";
}
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
query_posts('paged='.$paged.'&cat=' . $thiscategory . '&meta_key='. $meta_key .'&orderby='. $orderby .'&order='. $order .'');
?>
Answers (2)
Oleg Butuzov answers:
$posts = get_objects_in_term(array($YourCategoryID), 'category');
$results = $wpdb->get_results("SELECT meta_value FROM {$wpdb->postmeta} WHERE meta_key = 'product_brands' AND post_id in('.implode(",", $posts).')");
var_dump($results);
// your brends
Oleg Butuzov comments:
$YourCategoryID can be replaced by the $wp_query->query_vars['cat'];
Adam Bundy comments:
Oleg- Ive tried quite a few variations on what you posted, including the following, but the return is always NULL.
<?php
$myPosts = get_objects_in_term(array($thiscategory), 'category');
$myBrands = $wpdb->get_results("SELECT meta_value FROM {$wpdb->postmeta} WHERE meta_key = 'product_brands' AND post_id in('.implode(",", $myPosts).')");
var_dump($myBrands);
?>
Oleg Butuzov comments:
var_dump($wpdb);
// do you have this variable?
Adam Bundy comments:
Yep, I got a load of output when I inserted that...
Oleg Butuzov comments:
step by step
<?php
$myPosts = get_objects_in_term(array($thiscategory), 'category');
var_dump($myPosts);
// should return the list of IDS of the posts...
$sql = "SELECT meta_value FROM {$wpdb->postmeta} WHERE meta_key = 'product_brands' AND post_id in('.implode(",", $myPosts).')";
var_dump($sql);
$myBrands = $wpdb->get_results($sql);
var_dump($myBrands);
?>
actualy you shoulnd get null, you should at elast get empty array...
Adam Bundy comments:
There seems to be something wrong with the second part- the first part outputs something like this:
array(20) { [0]=> string(4) "2238" [1]=> string(4) "2239" [2]=> string(4) "2249" [3]=> string(4) "2250" [4]=> string(4) "2869" [5]=> string(4) "3405" [6]=> string(4) "3792" [7]=> string(4) "4043" [8]=> string(4) "4792" [9]=> string(4) "4815" [10]=> string(5) "21829" [11]=> string(5) "21987" [12]=> string(5) "24148" [13]=> string(5) "24156" [14]=> string(5) "24157" [15]=> string(5) "29710" [16]=> string(5) "31267" [17]=> string(5) "31712" [18]=> string(5) "35099" [19]=> string(5) "35117" }
When I insert the second step, I get blank page...
Oleg Butuzov comments:
global $wpdb;
and step 2
Adam Bundy comments:
OK now I have this, and still getting a blank page:
<?php
$myPosts = get_objects_in_term(array($thiscategory), 'category');
var_dump($myPosts);
global $wpdb;
$sql = "SELECT meta_value FROM {$wpdb->postmeta} WHERE meta_key = 'product_brands' AND post_id in('.implode(",", $myPosts).')";
var_dump($sql);
//$myBrands = $wpdb->get_results($sql);
//var_dump($myBrands);
?>
Oleg Butuzov comments:
can you give me an ftp access to debug it?
Oleg Butuzov comments:
oops!
soorry correct string for sql
$sql = "SELECT meta_value FROM {$wpdb->postmeta} WHERE meta_key = 'product_brands' AND post_id in(".implode(",", $myPosts).")";
Adam Bundy comments:
Now we're getting somewhere! The dump for that gives me this:
string(194) "SELECT meta_value FROM wp_postmeta WHERE meta_key = 'product_brands' AND post_id in(2238,2239,2249,2250,2869,3405,3792,4043,4792,4815,21829,21987,24148,24156,24157,29710,31267,31712,35099,35117)"
But the dump for the $myBrands gives this:
array(0) { }
Adam Bundy comments:
Found a goof- 'product-brands' should be 'product-brand'. That was the ticket. So, here's the final output:
array(20) { [0]=> object(stdClass)#5588 (1) { ["meta_value"]=> string(9) "Big Agnes" } [1]=> object(stdClass)#5587 (1) { ["meta_value"]=> string(9) "Big Agnes" } [2]=> object(stdClass)#5586 (1) { ["meta_value"]=> string(9) "Big Agnes" } [3]=> object(stdClass)#5585 (1) { ["meta_value"]=> string(9) "Big Agnes" }...
Oleg, could you help me echo this in a nicer list (comma sep.)?
Oleg Butuzov comments:
are you sure product_brands is your meta_key for the brends ?
you can debug it by
$sql = "SELECT meta_value, meta_key FROM {$wpdb->postmeta} WHERE AND post_id in(".implode(",", $myPosts).")";
this will return all meta_keys and meta_values from the posts inside this category... is there product_brands?
Oleg Butuzov comments:
1) replace wpdb->get_results by wpdb->get_col
2) and
echo implode(', ', $results);
Oleg Butuzov comments:
and this is a better sql
$sql = "SELECT meta_value FROM {$wpdb->postmeta} WHERE meta_key = 'product_brands' AND post_id in(".implode(",", $myPosts).") GROUP BY meta_value ";
Adam Bundy comments:
Take a bow, Oleg. Made my day. Have a great weekend!
