Product Category Listing - Easier to Read

4 posts by 3 authors in: Forums > CMS Builder
Last Post: April 29, 2014   (RSS)

By Perchpole - April 24, 2014

Hello, All -

I'm trying to set up a product category list using an advanced MySQL query. The list will appear in the product editor and will enable the user to assign a product to the category of their choice.

Currently the code looks like this:

SELECT num, name
  FROM `<?php echo $TABLE_PREFIX ?>pages` WHERE pageType = "productCat" ORDER BY lineage

The problem is that there are LOTS of product categories. To make matters worse, some of them share the same name. What makes them unique is the relationship to their parent.

Example:

(Category) > (Sub-vategory)

Sports cars > Blue

SUV > Blue

However, even if I ORDER the list by breadcrumb or lineage it will still look like this:

Sports Cars
Blue
SUV
Blue
Etc..

To make the product assignment easier I want to make the category list as readable as possible. Ideally I'd like to do this:

Sports Cars- Red
- Blue
- Green
SUV
- Red
- Blue
- Green
etc...

The simple indent and hyphen will make it much easier for people to assign the products correctly.

How do I form the mySQL query to produce this result?

:0/

Perch

By Perchpole - April 29, 2014

Chris -

Heavens to Murgatroyd!

I wish you'd come up with this yesterday. I decided to have a go at it myself today and it took me hours!

My solution is not nearly as elegant as your own, however.

Thanks,

:0)

Perch

By rconring - April 29, 2014

Holy Smoke!  Been wanting that solution forever!

Thanks Chris

Ron Conring
Conring Automation Services
----------------------------------------
Software for Business and Industry Since 1987