Products: CMS Builder: Category selection populates sub categories : Interactivetools.com Forum

Home: Products: CMS Builder:
Category selection populates sub categories

benedict
User

Jan 18, 2012, 3:34 PM

Post #1 of 20 (1798 views)
Shortcut

Category selection populates sub categories	Can't Post

Hi guys,

I have 3 sections - product category, product sub-category and then the products section.

I have added my 4 categories to the category section, then in the sub-category section I have pulled in the category values with a list. I have added my 40 or so sub categories.

It's now time to add my products in the products section - when I select the category, I then want the sub-category list to auto populate with the corresponding sub-categories, not show all 40 sub-categories. Any ideas?

Jason
Staff / Moderator

Jan 19, 2012, 11:09 AM

Post #2 of 20 (1790 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

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:

Code

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
---------------------------------------------------
Jason Sauchuk - Programmer
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

benedict
User

Jan 30, 2012, 1:10 AM

Post #3 of 20 (1760 views)
Shortcut

Re: [Jason] Category selection populates sub categories [In reply to]	Can't Post

Works great - thanks!

benedict
User

Feb 15, 2012, 5:24 PM

Post #4 of 20 (1699 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

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

benedict
User

Feb 19, 2012, 12:53 PM

Post #5 of 20 (1673 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

Hi guys,

Any luck with this?

Jason
Staff / Moderator

Feb 20, 2012, 9:39 AM

Post #6 of 20 (1643 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

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:

Code

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

Hope this helps
---------------------------------------------------
Jason Sauchuk - Programmer
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

benedict
User

Feb 20, 2012, 1:25 PM

Post #7 of 20 (1640 views)
Shortcut

Re: [Jason] Category selection populates sub categories [In reply to]	Can't Post

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:

Code

<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,

aev
User

Feb 21, 2012, 3:31 AM

Post #8 of 20 (1624 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

Code

<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-

benedict
User

Feb 21, 2012, 5:28 PM

Post #9 of 20 (1615 views)
Shortcut

Re: [aev] Category selection populates sub categories [In reply to]	Can't Post

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?

Jason
Staff / Moderator

Feb 22, 2012, 11:57 AM

Post #10 of 20 (1582 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

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:

Code

  $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:

Code

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

benedict
User

Mar 6, 2012, 2:51 AM

Post #11 of 20 (1300 views)
Shortcut

Re: [Jason] Category selection populates sub categories [In reply to]	Can't Post

OK, I think I;ve completely painted myself into a corner here with these breadcrumbs. Hope you can help. The example of category down to product detail page is this:

Home > Agriculture > All Boxes > Oyster Box
or;
Home > Category > Sub Category > Product Detail

1. Top Level Categories - no problem with breadcrumbs as I just repeat the title in the breadcrumb and it does not need to be hyperlinked (only home does) - i.e. Home > Agriculture

2. Sub Category Page - I can get the text to present correctly but can not get the links to go to the correct page in the breadcrumbs.

I am using this in the header:

Code

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

and this is in breadcrumbs:

Code

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

I don;t know what code to put for the link for the sub category label.

3. Product detail pages - I can not get the labels correct (showing wrong sub category) nor links for the subcategories.

I am using this in the header:

Code

 
  // load records 
  list($product_sub_categoriesRecords, $product_sub_categoriesMetaData) = getRecords(array( 
    'tableName'   => 'product_sub_categories', 
    'limit'       => '1', 
  )); 
  $product_sub_categoriesRecord = @$product_sub_categoriesRecords[0]; // get first record 
  $subCategories         = mysql_select("product_sub_categories");  
  $subCategoryNumToTitle = array_combine(array_pluck($subCategories, 'num'), array_pluck($subCategories, 'title'));

and this in the body:

Code

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

Any ideas what I am doing wrong?

Thanks in advance.

Jason
Staff / Moderator

Mar 8, 2012, 11:12 AM

Post #12 of 20 (1231 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

Hi,

For the first problem, if you're trying to link to the $product_sub_categoriesRecord, you should be able to use the _link pseudo field.

For example:

Code

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

Does this seem like what you're looking for?

For the problem using subCategoryNumToTitle, is your code producing an error, or just outputting the wrong label?

The subCategoryNumToTitle variable pulls from the product_sub_categories section. Which section does the category field of the $product_sub_categoriesRecord variable pull from?

Let me know and we'll see what we can do.

Hope this helps
---------------------------------------------------
Jason Sauchuk - Programmer
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

benedict
User

Mar 13, 2012, 4:28 AM

Post #13 of 20 (1122 views)
Shortcut

Re: [Jason] Category selection populates sub categories [In reply to]	Can't Post

Thanks Jason,

Actually, regarding the first problem, it is the second item, not the third that I am trying to work out the link for i.e.:

Code

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

Any ideas?

For my second problem, no, no error, just the wrong label.

Quote

Which section does the category field of the $product_sub_categoriesRecord variable pull from?

The category field of $product_sub_categoriesRecord pulls from the product_categories sections

The 3 sections I'm using are product_categories, product_sub_categories and products.

Thanks in advance.

Jason
Staff / Moderator

Mar 13, 2012, 12:22 PM

Post #14 of 20 (1101 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

Hi,

I'm not what the name of your page that you want to link to is, or how it's set up, but you should be able to manually create a link.

For example:

Code

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

For the second problem, what's happening is the subCategoryNumToTitle variable pulls from the product_sub_categories section, where you want labels from the product_categories section. You'll want to set up a second variable like this:

Code

  $categories            = mysql_select("product_categories");   
  $categoryNumToTitle = array_combine(array_pluck($categories, 'num'), array_pluck($categories, 'title'));

You can then output the label like this:

Code

<li><a href="#"><?php echo $categoryNumToTitle[$product_sub_categoriesRecord['category']]; ?></a></li>

benedict
User

Mar 13, 2012, 11:16 PM

Post #15 of 20 (1087 views)
Shortcut

Re: [Jason] Category selection populates sub categories [In reply to]	Can't Post

OK, great, everything is working, except for the label for the subcategory on the product detail page.

I got it wrong when I said I wanted labels from the categories section Blush

. I want the labels from the product_sub_categories section (the 'title' field of that section, to be specific).

My code at the moment is:

Code

  // load records  
  list($product_sub_categoriesRecords, $product_sub_categoriesMetaData) = getRecords(array(  
    'tableName'   => 'product_sub_categories',  
    'limit'       => '1',  
  ));  
  $product_sub_categoriesRecord = @$product_sub_categoriesRecords[0]; // get first record  
  $subCategories         = mysql_select("product_sub_categories");   
  $subCategoryNumToTitle = array_combine(array_pluck($subCategories, 'num'), array_pluck($subCategories, 'title'));

Thanks for all your efforts thus far...

benedict
User

Mar 13, 2012, 11:18 PM

Post #16 of 20 (1086 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

And my code in the body is:

Code

<ul class="breadcrumbs"> 
						<li><a href="#">Home</a></li> 
						<li><a href="#">Products</a></li> 
						<li><a href="polystyrene-product-cat.php?<?php echo $productsRecord['category'] ?>"><?php echo $productsRecord['category:label'] ?></a></li> 
						<li><a href="polystyrene-product.php?<?php echo $productsRecord['sub_category'] ?>"><?php echo $subCategoryNumToTitle[$product_sub_categoriesRecord['category']]; ?></a></li> 
						<li><?php echo $productsRecord['title'] ?></li> 
					</ul>

Jason
Staff / Moderator

Mar 14, 2012, 12:04 PM

Post #17 of 20 (1049 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

Hi,

You're really close. You need to be using the sub_category field as the index of your label array though.

Try this:

Code

<li><a href="polystyrene-product.php?<?php echo $productsRecord['sub_category'] ?>"><?php echo $subCategoryNumToTitle[$product_sub_categoriesRecord['sub_category']]; ?></a></li>

---------------------------------------------------
Jason Sauchuk - Programmer
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

benedict
User

Mar 14, 2012, 2:52 PM

Post #18 of 20 (1046 views)
Shortcut

Re: [Jason] Category selection populates sub categories [In reply to]	Can't Post

Not quite, I get this:

Notice: Undefined index: sub_category in /home/jpoly/public_html/foam-polystyrene.php on line 56 Notice: Undefined index: in /home/jpoly/public_html/foam-polystyrene.php on line 56

Jason
Staff / Moderator

Mar 15, 2012, 11:56 AM

Post #19 of 20 (1016 views)
Shortcut

Re: [benedict] Category selection populates sub categories [In reply to]	Can't Post

Hi,

We keep getting our variable names mixed up. :)

This should be it:

Code

<li><a href="polystyrene-product.php?<?php echo $productsRecord['sub_category'] ?>"><?php echo $subCategoryNumToTitle[$productsRecord['sub_category']]; ?></a></li>

benedict
User

Mar 16, 2012, 5:28 PM

Post #20 of 20 (990 views)
Shortcut

Re: [Jason] Category selection populates sub categories [In reply to]	Can't Post

Great - works! Again, apologies for the changes.

interactivetools.com

Company Info

Contact Us

Newsletter

interactivetools.com

Company Info

Contact Us