Post by Rrr » Mon Dec 29, 2014 7:16 pm

Hi,
I'm looking for someone to create the mySQL query code wich will get all categories in wich a product exists, I need their full lenght name\path. I posted this in my topic here on the forums http://forum.opencart.com/viewtopic.php?f=20&t=136086
Basically, my categories in the shop are actually cars, in form of "make > model > engine" like "Ford > Fiesta 2004 > 1.4i" followed by the parts-section like "Fuel filters". Many parts apply to several cars, so I'm trying to figure out how to get "All categories in wich a product_id applies with full path".

So let's say we have product "Fuel Filter" with product_id 40, wich applies to 40 cars. I can get the categories from DB > product_to_category but I get a list of parents, childrens, sub-childrens, etc, a mess. I know I can use the "category_path", "category" and "category_description" to build the list using levels, parents, but I'm not that advanced in mysql.
I think this feature is already implemented in the admin when editing a category, in the Links > Categories you get all categories in wich the product resides, but I can't get that query to work on my front-end.

Rrr
New member

Posts

Joined
Thu Nov 15, 2012 5:37 am

Post by tri1976 » Wed Dec 31, 2014 2:23 am

Try this function

Code: Select all

public function getCategory($category_id) {
  $query = $this->db->query("SELECT DISTINCT *, (SELECT GROUP_CONCAT(cd1.name ORDER BY level SEPARATOR ' > ') FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (cp.path_id = cd1.category_id AND cp.category_id != cp.path_id) WHERE cp.category_id = c.category_id AND cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY cp.category_id) AS path, (SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'category_id=" . (int)$category_id . "') AS keyword FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (c.category_id = cd2.category_id) WHERE c.category_id = '" . (int)$category_id . "' AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'");

  return $query->row;
}
Tri

email: trile7 at gmail dot com
Checkout My Extensions


User avatar
New member

Posts

Joined
Mon Mar 08, 2010 2:48 am

Post by JAY6390 » Wed Dec 31, 2014 2:31 am

I think you're missing what the OP has asked there tri1976 - The OP wants a query to get all categories for a product, not the category query

@Rrr - You are not likely going to be able to get this from one single query. You'll need to get a list of categories for a product, then work out its category paths from there

Image


User avatar
Guru Member

Posts

Joined
Wed May 26, 2010 11:47 pm
Location - United Kingdom

Post by nvedia » Wed Dec 31, 2014 2:38 am

we can find all the parents(categories) of a products using hierarchical queries
In Oracle its very simple by using CONNECT BY clause but there is a workaround in mysql too


http://explainextended.com/2009/07/20/h ... one-query/

Donate here to show support if you think I have helped you today!

Opencart Documentation


User avatar
Active Member

Posts

Joined
Sun May 22, 2011 12:54 pm

Post by tri1976 » Wed Dec 31, 2014 2:47 am

@Jay6390 You're correct, that's not what OP is asking for. How about this code

Code: Select all

public function getCategoriesByProduct($product_id) {
  $query = $this->db->query("SELECT DISTINCT *, (SELECT GROUP_CONCAT(cd1.name ORDER BY level SEPARATOR ' > ') FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "category_description cd1 ON (cp.path_id = cd1.category_id AND cp.category_id != cp.path_id) WHERE cp.category_id = c.category_id AND cd1.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY cp.category_id) AS path, (SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'category_id=" . (int)$category_id . "') AS keyword FROM " . DB_PREFIX . "product_to_category ptc JOIN " . DB_PREFIX . "category c ON (ptc.category_id = c.category_id) LEFT JOIN " . DB_PREFIX . "category_description cd2 ON (c.category_id = cd2.category_id) WHERE ptc.product_id = '" . (int)$product_id . "' AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'");

  return $query->rows;
}
Tri

email: trile7 at gmail dot com
Checkout My Extensions


User avatar
New member

Posts

Joined
Mon Mar 08, 2010 2:48 am

Post by Rrr » Fri Jan 02, 2015 8:21 pm

Hi,
Yes, I needed to get a list of all categories in wich a product has been inserted into.

A fellow forum user was the first to PM me with an offer to do this mod and he did it.

@tri1976 - something like that yes

Rrr
New member

Posts

Joined
Thu Nov 15, 2012 5:37 am
Who is online

Users browsing this forum: paulfeakins and 4 guests