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 Chris - April 29, 2014

Hi Perch,

Wow, I literally just came up with a hyphen-space solution for someone else on our forums who wanted parent categories bolded (in a checkbox list). Try this:

List Options:

Get options from MySQL query (advanced)

SELECT `num`, CONCAT(IF(`depth`, '- ', ''), `name`)
FROM `<?php echo $TABLE_PREFIX ?>pages`
WHERE pageType = "productCat"
ORDER BY lineage

My initial solution was CONCAT(REPEAT('- ', `depth`), `name`), but that mysteriously crashed my local Mysql server. Let me know if you have records with more than 2 levels of depth.

Does that help? Let me know if you have any questions.

All the best,
Chris

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