Category selection populates sub categories

Hi,

Sure. What you need to do is for the sub-category list, under list options select "Get options from MySQL query (advanced)". Then in the query box, put in something like this:

SELECT num, title
FROM `<?php echo $TABLE_PREFIX ?>product_sub_category`
WHERE category = '<?php echo $ESCAPED_FILTER_VALUE;?>'

Finally, under "Advanced Filter" select your category field.

In the above example, a couple of assumptions where made:
-the name of your sub category section was product_sub_category
-you want to use num as a value and title as a label
-the sub category section has a field called category.

You will need to adjust the above query if any of these assumptions were incorrect.

Hope this helps

Hi Jason,

Following on from the above issue, I am now coding the front end and have got a bit stuck. here is the scenario:

1. User arrives at the home page and is able to select a category tab and see all the subcategories of that category and click through to subcategory page.

2. Subcategory page needs to show the subcategory content plus the products that fit within that category > subcategory. This is where I am getting stuck with the header code.

Currently, I am trying this:

// load records
list($product_sub_categoriesRecords, $product_sub_categoriesMetaData) = getRecords(array(
'tableName' => 'product_sub_categories',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));
$product_sub_categoriesRecord = @$product_sub_categoriesRecords[0]; // get first record

// load records
list($productsRecords, $productsMetaData) = getRecords(array(
'tableName' => 'products',
'where' => "sub_category LIKE '". $product_sub_categoriesRecord['title'] ."'" ,
'debugSql' => true,
));


But I think this is relying on the name of categories too much, i.e. shouldn;t it relate to record numbers? I also note that this does not make any reference to the category above the subcategory. This is important because many sub-categories actually appear in multiple categories:

For example:

Agriculture and Aquaculture > Ice Boxes
Packaging > Ice Boxes
Building and Construction > Ice Boxes

I'd appreciate any assistance you could offer.

Cheers,

B

Hi guys,

Any luck with this?

Hi,

So if I understand this correctly, the problem is that if you select a sub-category of "Ice Box" your query will return products of sub-category "Ice Box", regardless of what the parent category of "Ice Box". Is that right?

If so, you're correct, the best option here would be to store the record number in your subcategory field. Since every separate instance of Ice Box is a separate record, they will all have different record numbers. If you make this change in the CMS, you'll need to re-associate all of your product records. Once that is done, you can then change your query like this:

// load records
list($productsRecords, $productsMetaData) = getRecords(array(
'tableName' => 'products',
'where' => "sub_category = '". intval($product_sub_categoriesRecord['num']) ."'" ,

));

Hope this helps

Thanks Jason - yes, you understand correctly and your solution below has worked (all my product records already had associations with sub category record numbers, so no rework there).

My only outstanding question relates to my breadcrumbs at the top of the page, where I want to show Home > Category > Subcategory. The code I am currently using is:

<ul class="breadcrumbs">
<li><a href="index.php">Home</a></li>
<li><?php echo $product_sub_categoriesRecord['category'] ?></li>
<li><?php echo $product_sub_categoriesRecord['title'] ?></li>
</ul>

But this is giving me a result of showing "category" as a number, when I need it show the label i.e. it is showing '1' instead of 'Agriculture' - how do I pull the label?

Cheers,

<ul class="breadcrumbs">
<li><a href="index.php">Home</a></li>
<li><?php echo $product_sub_categoriesRecord['category:label'] ?></li>
<li><?php echo $product_sub_categoriesRecord['title'] ?></li>
</ul>

Try to add the code in red.


-aev-

Thanks for that - that worked fine on the list that is populated where you select label to be "title" and value to be "num" in CMSB.

But my subcategories are created by "Get Options From SQL Query (Advanced) in the list options and this code:

SELECT num, title
FROM `<?php echo $TABLE_PREFIX ?>product_sub_categories`
WHERE category = '<?php echo $ESCAPED_FILTER_VALUE;?>'

The problem is that obviously there is no label. The result when I use your solution in red?

Nothing displayed. So all I need to know is, how do I pull a label for something in this kind of list?

Hi,

You're correct, when using an advanced filter field, getRecords() doesn't correctly get the list labels. This is something we'll need to look into for a future release.

As a work around for now, you can create an array that uses all the product sub category nums as an index, with the title as a value like this:

$subCategories = mysql_select("product_sub_categories");
$subCategoryNumToTitle = array_combine(array_pluck($subCategories, 'num'), array_pluck($subCategories, 'title'));

That means you can output the title of any category num like this:

<?php echo $subCategoryNumToTitle[$product_sub_categoriesRecord['category']]; ?>

Hope this helps