Well... None of the solutions that were suggested were what I was looking for so I took pieces from each and ended up creating my own version. show_cats_brands() did about 70% of the job but it lacked formatting for my needs.
Thought I'd post for anyone wanting to achieve the same goal. I simply took show_cats_brands() function and re-formatted it into a menu. I then added the top level group as the top-level menu and linked it to a page.
I also created a new option in db (wp_option) called display_empty_cats which is a flag to hide/show categories with 0 products. This function needs additional work because it will hide sub-categories making the category menu empty... Need to enhance the check on the category level to count products in sub-categories...
function display_wpsc_menu_subcategories($id) {
global $wpdb;
$output = "";
if(get_option('permalink_structure') != '') {
$seperator ="?";
} else {
$seperator ="&";
}
$subcategory_sql = "SELECT * FROM <code>".WPSC_TABLE_PRODUCT_CATEGORIES."</code> WHERE <code>active</code>='1' AND <code>category_parent</code> = '".absint($id)."' ORDER BY <code>nice-name</code>";
$subcategories = $wpdb->get_results($subcategory_sql,ARRAY_A);
if($subcategories != null) {
$output .= "<ul class='children'>";
foreach($subcategories as $subcategory) {
// Use db flag to show/hide product count within category
if (get_option('show_category_count') == 1) {
//show product count for each category
$count = $wpdb->get_var("SELECT COUNT(<code>p</code>.<code>id</code>) FROM <code>".WPSC_TABLE_ITEM_CATEGORY_ASSOC."</code> AS <code>a</code> JOIN <code>".WPSC_TABLE_PRODUCT_LIST."</code> AS <code>p</code> ON <code>a</code>.<code>product_id</code> = <code>p</code>.<code>id</code> WHERE <code>a</code>.<code>category_id</code> IN ('{$subcategory['id']}') AND <code>p</code>.<code>active</code> IN ('1') AND <code>p</code>.<code>publish</code> IN('1')");
$addCount = " (".$count.")";
// Check if we should display empty categories in menu
if (get_option('display_empty_cats') == 1) {
$addCount = " (".$count.")";
} else {
// If not, only continue if there are sub-categories for this category
if ($count==0) {
break;
} else {
$addCount = " (".$count.")";
}
} // end if getoption(display_empty_cats)
} // end if get_option(show_category_count)
$output .= "<li class='page_item page-item-".$subcategory['id']."'><a href='".wpsc_category_url($subcategory['id'])."'>".stripslashes($subcategory['name']).$addCount."</a></li>";
} // end foreach loop
$output .= "</ul>";
}
return $output;
}
// Build Menu - Pass Group_id for category_tree structure & Page_Id to link the Category Group to a Page
function show_wpsc_group_menu($category_group = null , $page_id = null, $order_by = 'name') {
global $wpdb;
$output = "";
if($category_group == null || $page_id == null) {
return;
} else {
$category_group = (int)$category_group;
$page_id = (int)$page_id;
$page_name = $wpdb->get_var("SELECT <code>meta_value</code> FROM <code>$wpdb->postmeta</code> WHERE <code>post_id</code> IN ('$page_id') AND <code>meta_key</code> = '_aioseop_menulabel' LIMIT 1 ");
$page_title = $wpdb->get_var("SELECT <code>meta_value</code> FROM <code>$wpdb->postmeta</code> WHERE <code>post_id</code> IN ('$page_id') AND <code>meta_key</code> = '_aioseop_titleatr' LIMIT 1 ");
}
if(get_option('permalink_structure') != '') {
$seperator ="?";
} else {
$seperator ="&";
}
// Setup Top Level Group Link
$output .= "<li class='page_item page-item-".$page_id."'><a href='".get_page_link($page_id)."' title=".$page_title.">".$page_name."</a>";
// Now extract 1st level categories
$categories = $wpdb->get_results("SELECT * FROM <code>".WPSC_TABLE_PRODUCT_CATEGORIES."</code> WHERE <code>group_id</code> IN ('$category_group') AND <code>active</code>='1' AND <code>category_parent</code> = '0' ORDER BY <code>".$wpdb->escape($order_by)."</code> ASC",ARRAY_A);
// If we have some category levels, let's process them
if($categories != null) {
$output .= "<ul class='children'>";
foreach($categories as $option) {
// check option for category count
if (get_option('show_category_count') == 1) {
// show sub-category count for each group
// (We need to change this to calculate the product count in sub-categories to determine whether or not to display the 2nd level category in menu)
$count = $wpdb->get_var("SELECT COUNT( * ) FROM <code>".WPSC_TABLE_PRODUCT_CATEGORIES."</code> WHERE <code>category_parent</code> IN ('".$option['id']."')");
// Check if we should display empty categories in menu
if (get_option('display_empty_cats') == 1) {
$addCount = " (".$count.")";
} else {
// If not, only continue if there are sub-cateogries for this category
if ($count==0) {
break;
} else {
$addCount = " (".$count.")";
}
} // end if (getoption(display_empty_cats))
} // end if get_option(show_category_count)
$output .= "<li class='page_item page-item-".$option['id']."'><a href='".wpsc_category_url($option['id'])."' title=".stripslashes($option['name']).">".stripslashes($option['name']).$addCount."</a>";
$subcategory_sql = "SELECT * FROM <code>".WPSC_TABLE_PRODUCT_CATEGORIES."</code> WHERE <code>group_id</code> IN ('$category_group') AND <code>active</code>='1' AND <code>category_parent</code> = '".$option['id']."' ORDER BY <code>id</code>";
$subcategories = $wpdb->get_results($subcategory_sql, ARRAY_A);
if($subcategories != null) {
$output .= display_wpsc_menu_subcategories($option['id']);
}
$output .= "</li>";
} // end foreach loop
$output .= "</ul></li>";
}
echo $output;
}
whitsey
desmillicious