how to get all products for a top level category that includes subcategories

[Guest]

Hi! I'm stuck and need a friendly jumpstart.

 

I did a bunch of searches on this and came up empty. Then I wrote some stuff and wasn't quite satisified. Now I'm asking the experts. Thx!

 

What I want to do is be able to add a default set of product attributes per category/sub category. Which can then be edited or added to in each product. Saves on input time.

 

I've written most of this, but now I need to be able to get all the products in a category that could have tiers of sub categories.

 

I know it's probably sql plus some looping, but for the life of me I've hit a wall.

 

Thanks for any help you can provide!

[Guest]

Hi! I'm stuck and need a friendly jumpstart.

 

I did a bunch of searches on this and came up empty. Then I wrote some stuff and wasn't quite satisified. Now I'm asking the experts. Thx!

 

What I want to do is be able to add a default set of product attributes per category/sub category. Which can then be edited or added to in each product. Saves on input time.

 

I've written most of this, but now I need to be able to get all the products in a category that could have tiers of sub categories.

 

I know it's probably sql plus some looping, but for the life of me I've hit a wall.

 

Thanks for any help you can provide!

 

no longer stuck! Here's what I came up with, the functions below retrieve all the product ids for a provided category or for that matter sub category and any nested sub categories or sub sub categories it may have.

 

function get_product_ids_per_category($current_category, $link = 'db_link'){

 

$select_str = "select distinct p.products_id ";

$from_str = "from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

$where_str = " where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

 

$a_subcategories = array();

$a_product_ids = array();

 

tis_get_subcategories($a_subcategories, $current_category);

 

$where_str .= " and p2c.products_id = p.products_id and (p2c.categories_id = '" . (int)$current_category . "'";

 

for ($i=0, $n=sizeof($a_subcategories); $i<$n; $i++ ) {

$where_str .= " or p2c.categories_id = '" . (int)$a_subcategories[$i] . "'";

}

 

$where_str .= ")";

 

$order_str = ' order by p.products_id';

 

$products_sql = $select_str . $from_str . $where_str . $order_str;

 

$products_query = tep_db_query($products_sql);

 

//$no_of_products = tep_db_num_rows($products_query);

 

while ($_products = tep_db_fetch_array($products_query)) {

$a_product_ids[] = $_products['products_id'];

}

return $a_product_ids;

}

 

function tis_get_subcategories(&$subcategories_array, $parent_id = 0) {

$subcategories_query = tep_db_query("select categories_id from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$parent_id . "'");

while ($subcategories = tep_db_fetch_array($subcategories_query)) {

$subcategories_array[sizeof($subcategories_array)] = $subcategories['categories_id'];

if ($subcategories['categories_id'] != $parent_id) {

tis_get_subcategories($subcategories_array, $subcategories['categories_id']);

}

}

}

 

Hope this helps someone! It would be easy enough to expand this to include other product info as well.

 

Looks like a small thing, but with the above I can pick attributes sets(price configurable, selectable dropdowns shown to customers), for an entire category or subcategory of products and they will apply to and display for all of them including any sub categories as well, all in less than 3sec.

 

Makes getting up and running that much faster for stores where the items have the same or similiar options.

 

You only have to edit the ones that are different.

 

Best,

 

-Colin