Suggested Modification to Manufacturer Page

[d4kk0n]

So,

 

I've created a category list that looks like this:

Digital Cameras ->

---Nikon ->

Accessories ->

---Lenses ->

------Nikon ->

 

The problem is, that in the dropdown on the manufacturer's page, I now get:

Nikon

Nikon

 

I'd rather get:

Digital Cameras :: Nikon

Accessories :: Lenses :: Nikon

 

I'm not sure how dificult a hack like this would be to pull off, because I haven't really looked into it, but I would imagine that quite a few people have run into this problem.

 

/dak

[MattM]

I need this too. I'm gonna search the forums for a bit and then write it myself if I can't find a ready-made solution.

 

In any case, I'll post the results here.

[MattM]

O.K., I couldn't find that anyone had done this already, so I tackled it myself.

 

The good news is that you can do it completely in SQL, so only one line in index.php needs to be changed.

 

Search for $filterlist_sql in the index.php file. It should be around line 250 or so. You will see it twice, the first one is the one you have to change.

 

Original:

$filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by cd.categories_name";

 

New version:

$filterlist_sql = "select distinct c.categories_id as id, coalesce(concat(pcd.categories_name, ' :: ', cd.categories_name), cd.categories_name) as name from " . TABLE_PRODUCTS . " p inner join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on (p.products_id = p2c.products_id) inner join " . TABLE_CATEGORIES . " c on (p2c.categories_id = c.categories_id) inner join " . TABLE_CATEGORIES_DESCRIPTION . " cd on (p2c.categories_id = cd.categories_id) left outer join " . TABLE_CATEGORIES_DESCRIPTION . " pcd on (c.parent_id = pcd.categories_id) where p.products_status = '1' and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by name";

 

How it works:

 

The original version gets the category names and id numbers for the dropdown menu by a series of joins. This is needed because the category names are in the categories_description table and we have to get there from the products table. So, they join as follows:

• products table to products_to_categories on the products_id field
  
• products_to_categories to categories on the categories_id field
  
• prodcuts_to_categories to categories_description on the categories_id field
  

What I did is add another join to the categories_description table, but on the parent_id field from the categories table instead. This second join to the categories_description table gets us the name of the parent category which is then tacked on to the category name with a concat function.

 

However, the second join has to be an outer join, otherwise we won't get back any categories where there is no parent. Specifing one join as outer means you have to change the join syntax to explicitly make the others inner. The coalesce function takes care of returning the category name when the parent is null.

 

You can set the seperator character in the concat function. I used ': ' so it looks lilke: parent: category, but you can use whatever you like:

' :: '

' | '

' -> '

etc.

 

Just make sure you use single quotes around the seperator character since a double quote will terminate the sql string and give you a parse error. Also, if you have categories nested two or more levels deep, this will only grabs one category up from the one with the products. In other words, if you have:

 

Category : Subcategory1 : Subcategory2 : Products

 

It will show up as:

 

Subcategory1 : Subcategory2

[d4kk0n]

Awesome... and EXACTLY how I was going to have it list them! You da man! Thanks for fixing this!

 

Hopefully others find this post, I can't imagine we're the first two to have this gripe :)

 

/dak

[HarryHook]

So first of all sorry for my bad english :)

Im searching forums 4 this kind of Problem now for more than 10 hours.

And now i find the Post which maybe can help me.

I dont understand why nobody needs to do this kind of changing???

 

 

I like to use the code MattM did to show the manufacturer Dropdown menu like this

 

Digital Cameras :: Nikon

Accessories :: Lenses :: Nikon

 

but what i get is if i use this code is

 

:: Nikon

:: Nikon

Digital Cameras :: Nikon

Accessories :: Lenses :: Nikon

 

what did i wrong, i change the code like u say.

 

Anybody had an idea what i do wrong ??? I have to change more then this one line which is shown ??

 

I Hope anybody can help me :( please

[MattM]

It should work with only that one line changed.

 

Can you post here what your actual categories are? List your top level categories and each category under them.

 

You should never be able to get more than one level deep with the code I posted. In other words, if you have a category named Accessories, and under it is a category named Lenses, and under that is a category named Nikon, then you will get:

 

Lenses :: Nikon

 

But never, ever:

 

Accessories :: Lenses :: Nikon

 

You only have products under the lowest level category right? Under the example above, if you have products under Accessories :: Lenses :: Nikon, and also under Accessories :: Lenses, then it will give you messed up categories. But you won't be able to see the products under Accessories :: Lenses, so I didn't think I needed to check for that case.

[HarryHook]

Hi thanks for fast reply.

 

My categories is like that :

 

Category : Subcategory1 : Subcategory2 : Products

 

Category is such like Printer

Subcategory is like Laser, ink jet, etc.

Subcategory2 is like Canon, Epson, HP etc.

 

its even with that structur you show in your post.

 

But if I change the code like u, I get an dropdown menu like that.

 

:: Canon

:: Canon

Laser :: Canon ( thats ok )

ink Jet :: Canon (thats ok )

 

If I Change to the first :: Canon (in dropdown menu) the results the same like Laser :: Canon

If I Change to the second :: Canon (in dropdown menu) the results the same like ink Jet :: Canon

 

 

So I have every Categorie 2 times. One in the Old way

 

Canon

Canon

and one in the new way Laser:Canon.

 

what did I wrong ? Any Idea?

THX :D

[mugitty]

It sounds like you added the "New Version" code above below the "Original" code in your file instead of overwriting it

[HarryHook]

Hi yes it sounds like but

 

I overwrite :) the original code with the NEW one.

[surfinagain]

I just installed this mod...and unfortunately I've having a similar problem. here's an idea of my categories/sub-category structure:

 

450R

-> Body

-> Controls

250R

-> Body

-> Controls

250X

-> Body

-> Controls

 

If there are a manufacture's products in 450R->Body and 250R->Body the drop down will look like the following:

 

-> Body

-> Body

250R -> Body

450R -> Body

 

I'm pleased with the 250R -> Body type listing but obviously the first two aren't what I'm looking for. Any ideas on how to fix what is otherwise exactly what i'm looking for?

 

Thanks Matt for developing this code!

 

Ken

[MattM]

O.K., I think I have some ideas of what is going wrong for some people but not others, but I'll need to investigate further. There are several possibilities I am working on:

 

1) This section:

 

coalesce(concat(pcd.categories_name, ' :: ', cd.categories_name), cd.categories_name)

 

The way this is supposed to work is if the category has a parent category, they are joined with the CONCAT function, then the COALESCE function returns the first non-null value (the parent with the seperator and subacategory). If there is no parent, then the CONCAT will return a NULL since any expression concatenated with a NULL is NULL. In that case, COALESCE returns the second value, just the category name.

 

If, somehow, the parent category name is an empty string instead of a NULL, then the separator and subcategory could be returned, like you are seeing.

 

2) This SQL relies on the category table having valid parent categories. If some categories have a parent_id that links to a category that no longer exists, then this could happen.

 

3) The OUTER JOIN is somehow acting like a simple JOIN and giving a cross-product. In other words, it is returning all the categories whether or not they have a parent, and then all the categories with a parent, resulting in some of them doubling up.

 

My problem is, that it works perfectly on my site, so until I can duplicate the error or look directly at someone else's database, I'm not too sure I can debug it successfully... :(

 

Can the people having problems tell me what version of MySQL they are running?

 

 

Ken -

 

I notice on your site that the category id is getting set successfully. In other words, if I click on one of the "-> Intake" options, it does take me to the right place. I need to think some more to figure out the significance of this.

[surfinagain]

Matt,

 

Thanks for your efforts! I'm running mySQL version 4.0.16. It appears in every case the number of empty parent categories is the correct number of matching cat/subcat entries...if that makes sense. and you are right they do actually work when clicked on. to me they appear to be duplicate links without the correct link text (which follows in a link below).

 

if you need more information let me know...thanks again.

[MattM]

OK, I'm using MySQL 3.23, so I thought there might nave been a change to MySQL that is causing this.

 

Downloaded and installed 4.0.20 on a test machine. Imported my categories, categories_description, products, and product_to_categories tables.

 

Ran my SQL and I got the same, correct set of menu text that I get on my site. So, I've ruled out any MySQL changes.

 

What I'm going to need is an export of the four tables listed above from someone who is having this problem. I can then import them into my test system and try to duplicate the problem. A CSV file would work, or Excel. They can be emailed to [email protected]

 

Any volunteers?

[surfinagain]

Matt....thanks for your efforts to help resolve this. I've emailed you the tables you requested. if you need anything else or if i can help please let me know.

 

thanks again!

 

ken

[MattM]

Alright, thanks to Ken sending me his tables, I think I have it fixed.

 

Here is the new SQL statement:

 

$filterlist_sql = "select distinct c.categories_id as id, coalesce(concat(pcd.categories_name, ' :: ', cd.categories_name), cd.categories_name) as name from " . TABLE_PRODUCTS . " p inner join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on (p.products_id = p2c.products_id) inner join " . TABLE_CATEGORIES . " c on (p2c.categories_id = c.categories_id) inner join " . TABLE_CATEGORIES_DESCRIPTION . " cd on (p2c.categories_id = cd.categories_id) left outer join " . TABLE_CATEGORIES_DESCRIPTION . " pcd on (c.parent_id = pcd.categories_id and pcd.language_id = '" . (int)$languages_id . "') where p.products_status = '1' and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by name";

 

The problem was languages. The categories_description table can have several entries for the same parent category in different languages. The SQL for the main join has this covered with the cd.language_id = statement in the where clause. I added a pcd.language_id = statement in my new join so I only grab the parent _id that is in the same language.

 

Let me know if this fixes the problem!

[surfinagain]

THANKS MATT! That works for me! You have some mad SQL skills! Thanks a ton as your mod was exactly what i've been needing for some time.

 

Ken