Import sql script

  • Posts: 272
  • Thank you received: 3
13 years 2 months ago #8527

Hi there,

we are evaluating the Hikashop to replace the shops we have RedShop and JHshop. We have written a sql script to ship the data across. Everything works except when we populate the category tabel. When you view the categories in the back end the filters' display is nonsense. We understand that this is something to do with the category_left and category_right. At the beginning we left these blank. We then tried setting them to maximum and minimum category ids of all the child categories. While this is better it still not right. Could you help us out please?

Thanks, James

Please Log in or Create an account to join the conversation.

  • Posts: 81511
  • Thank you received: 13066
  • MODERATOR
13 years 2 months ago #8552

Hi,

You can read more about hierarchical tree data storing on that page: articles.sitepoint.com/article/hierarchical-data-database/2
Left and right columns need to modified for some categories when you add/remove categories like explained in the article.
The easiest will be to use the code below to recalculate the left and right values automatically for all categories once you inserted your new categories (as long as the category_parent_id values are correct, that should work):

			$database =& JFactory::getDBO();
			$categoryClass = hikashop::get('class.category');
			$query = 'SELECT category_left,category_right,category_depth,category_id,category_parent_id FROM '.hikashop::table('category').' ORDER BY category_left ASC';
			$database->setQuery($query);
			$categories = $database->loadObjectList();
			$root = null;
			$categoryClass->categories = array();
			foreach($categories as $cat){
				$categoryClass->categories[$cat->category_parent_id][]=$cat;
				if(empty($cat->category_parent_id)){
					$root = $cat;
				}
			}
	
			$categoryClass->rebuildTree($root,0,1);

Last edit: 13 years 2 months ago by nicolas.

Please Log in or Create an account to join the conversation.

  • Posts: 272
  • Thank you received: 3
13 years 2 months ago #8571

Hi Nicolas,

thanks for the pointer. The script works perfectly. In fact we have added a rebuild Category button to the categories listing page in the admin. This rebuild category button is linked to a function that contains the script you posted.

Now we can import the category data and press the rebuild category button and everything is like brand new again. If you are interested in the few lines of code we added to the categoryClass, categoryController and category view.html.php file let me know and I will post it here.

Thanks again, James

And the article you indicated is very interesting as well.

Last edit: 13 years 2 months ago by jameswadsworth.

Please Log in or Create an account to join the conversation.

  • Posts: 81511
  • Thank you received: 13066
  • MODERATOR
13 years 2 months ago #8581

Well, I don't think that we should add a button for that be default in HikaShop as the category tree left and right are handled automatically by HikaShop when managing the categories so that the tree is always valid. It's really only useful when you bulk insert categories in SQL. However, I'm sure that other users would be interested for a script to transfer the data from redshop to hikashop.

Please Log in or Create an account to join the conversation.

  • Posts: 107
  • Thank you received: 0
11 years 6 months ago #70705

Hello,

and how can I the script execute?
In joomla? On the shell?
What is a simple way?

Thanks

Please Log in or Create an account to join the conversation.

  • Posts: 22
  • Thank you received: 0
11 years 1 week ago #98410

This code saves a lot of time :) Thank you for posting.

I am however having one problem;
Running the code, I can see through phpMyAdmin that it has been executed with success and sorted out everything.
-Unfortunately, none of the categories are listed in the back-end explorer.

I had this working previously, so I am not sure what went wrong this time.
-Also a quirky thing; Last time (when the explorer window was working), on the front end, none of the categories were working.
When I manually edited the categories, they came to life (I only added a space and saved them again).
How come?

Please Log in or Create an account to join the conversation.

  • Posts: 81511
  • Thank you received: 13066
  • MODERATOR
11 years 1 week ago #98455

You should try to click on the "rebuild tree" button of the categories listing.
That should set everything properly automatically without any code change.

Please Log in or Create an account to join the conversation.

  • Posts: 22
  • Thank you received: 0
11 years 1 week ago #98569

Thank you for the quick reply, but it did not help.

I suspect hitting the Rebuild button, runs the same code as above, because I can see the changes have been made to the table.
-But, below the Explorer heading there is still nothing, just a blank space where the categories should be.
I will try to reinstall (I load all the products by CSV, so it is ok) and see if I can get it working again.

I am pretty sure I screwed this up myself, so it is only fair I try to fix it myself ;)

Please Log in or Create an account to join the conversation.

  • Posts: 22
  • Thank you received: 0
11 years 1 week ago #98573

Yup.
That was all me..

Please Log in or Create an account to join the conversation.

Time to create page: 0.097 seconds
Powered by Kunena Forum