Hallo zusammen,
ich sitze vor einer sql-Abfrage, die ich zugegebenermaßen nicht verstehe.
Die Erklärung hilft mir nicht weiter, auch wenn dort alles steht, was nötig ist.
Hier der Text:
$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";
... macht genau das, was ich möchte, aber...
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 to products_to_categories on the products_id field, products_to_categories to categories on the categories_id field, and 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 and explicitly state the others as 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 like: 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 grab 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
-------------------------------------------------------------------------
Was muss ich in der $filterlist_sql ändern, um eine dritte Unterkategorie abzufragen und darzustellen?
Ich hoffe, ich strapaziere damit nicht Eure Geduld
und vielen Dank Euch
Oliver
ich sitze vor einer sql-Abfrage, die ich zugegebenermaßen nicht verstehe.
Die Erklärung hilft mir nicht weiter, auch wenn dort alles steht, was nötig ist.
Hier der Text:
$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";
... macht genau das, was ich möchte, aber...
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 to products_to_categories on the products_id field, products_to_categories to categories on the categories_id field, and 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 and explicitly state the others as 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 like: 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 grab 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
-------------------------------------------------------------------------
Was muss ich in der $filterlist_sql ändern, um eine dritte Unterkategorie abzufragen und darzustellen?
Ich hoffe, ich strapaziere damit nicht Eure Geduld
und vielen Dank Euch
Oliver
Kommentar