OpenCart: How to accurately populate oc_category_path

Question

I have used an online service to transfer data from my other ecommerce website into OpenCart and everything seems to have been transferred correctly.

There is however one issue with the product categories. The categories have been transferred to the oc_category table; however, looks like there is another table called oc_category_path that needs to be populated as well if I want to be able to edit my categories in the admin.

Do you know what this table is and how I can correctly populate it (manually in my case I suppose). What is the path_id and level to be exact and what determines the level of a category?

Related Tables:

CREATE TABLE `oc_category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `image` varchar(255) DEFAULT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `top` tinyint(1) NOT NULL,
  `column` int(3) NOT NULL,
  `sort_order` int(3) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL,
  `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

CREATE TABLE `oc_category_description` (
  `category_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `meta_description` varchar(255) NOT NULL,
  `meta_keyword` varchar(255) NOT NULL,
  `u_title` varchar(255) NOT NULL,
  `u_h1` varchar(255) NOT NULL,
  `u_h2` varchar(255) NOT NULL,
  PRIMARY KEY (`category_id`,`language_id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `oc_category_path` (
  `category_id` int(11) NOT NULL,
  `path_id` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  PRIMARY KEY (`category_id`,`path_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Answer 1

If a category in your oc store is a root category, it gets an entry in the path table as such "category_id,category_id,0". if that category has a child it will get two entries in the table, namely :-"category_id,category_id,1" as well as "category_id,parent_id,0".

If that child has a it's own child, that new child will have three entries as such :-
"category_id,category_id,2"
"category_id,parent_id,1"
"category_id,parents parent category_id,0"

To illustrate this, assume a category has a category_id of "14". It is the first child of a category with a category_id of "11". That category, with the category_id of "11" is the child on a category with the category id of "1". (1>11>14 as show in the admin panel, except with the name instead of the category_id)

The above will have 3 entries as such :
"14","14","2"
"14","11","1"
"14","1","0"

So the root category to it will get 0, the next one gets 1, and the next 2, and so forth, all depending on how many category levels down it is.

I hope that explain things well enough.

As for populating it, the simplest method, but not a complete method, is to just create the table with "category_id,category_id,0". This will get them to show up in the admin panel. If you then click on "repair" it will generate this table correctly.

Alternatively you would have to step through you category table, creating an array with it's parent_id, looking up that parent_id for it's parent_id and adding it to the array, and so forth. When the array is complete, ie no more parents, it will be a simple task of adding them to the table with the correct "level".

FYI, there is another table that needs populating as well, category_to_store, which is very simply "category_id,store_id". Without this table you will not see your categories in your store.