[Skip to main content](#vessel-content) # Import sql script ## - **[jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile")** - [![jameswadsworth's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/556-jameswadsworth "View jameswadsworth's Profile") Topic Author - [ Offline](/forum/user/556-jameswadsworth) Less More - Posts: 274 - Thank you received: 5 - - - - [](http://www.deanimaconsulting.com) - 15 years 8 months ago [\#8527](#8527) by [jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile") *[Import sql script](/forum/2-general-talk-about-hikashop/8527-import-sql-script#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")* was created by *[jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg1MjctaW1wb3J0LXNxbC1zY3JpcHQ/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86290 - Thank you received: 14268 - - 15 years 8 months ago - 15 years 8 months ago [\#8552](#8552) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Re: Import sql script](/forum/2-general-talk-about-hikashop/8527-import-sql-script#8552 "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")* Hi, You can read more about hierarchical tree data storing on that page: [ articles.sitepoint.com/article/hierarchical-data-database/2](http://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): Code: $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: 15 years 8 months ago by [nicolas](/forum/user/63-nicolas "View nicolas's Profile"). Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg1MjctaW1wb3J0LXNxbC1zY3JpcHQ/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile")** - [![jameswadsworth's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/556-jameswadsworth "View jameswadsworth's Profile") Topic Author - [ Offline](/forum/user/556-jameswadsworth) Less More - Posts: 274 - Thank you received: 5 - - - - [](http://www.deanimaconsulting.com) - 15 years 8 months ago - 15 years 8 months ago [\#8571](#8571) by [jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile") Replied by *[jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile")* on topic *[Re: Import sql script](/forum/2-general-talk-about-hikashop/8527-import-sql-script#8571 "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")* 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: 15 years 8 months ago by [jameswadsworth](/forum/user/556-jameswadsworth "View jameswadsworth's Profile"). Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg1MjctaW1wb3J0LXNxbC1zY3JpcHQ/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86290 - Thank you received: 14268 - - 15 years 8 months ago [\#8581](#8581) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Re: Import sql script](/forum/2-general-talk-about-hikashop/8527-import-sql-script#8581 "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")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg1MjctaW1wb3J0LXNxbC1zY3JpcHQ/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[cobinet\_edv](/forum/user/9990-cobinet-edv "View cobinet_edv's Profile")** - [![cobinet_edv's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/9990-cobinet-edv "View cobinet_edv's Profile") - [ Offline](/forum/user/9990-cobinet-edv) Less More - Posts: 107 - Thank you received: 0 - - 14 years 1 week ago [\#70705](#70705) by [cobinet\_edv](/forum/user/9990-cobinet-edv "View cobinet_edv's Profile") Replied by *[cobinet\_edv](/forum/user/9990-cobinet-edv "View cobinet_edv's Profile")* on topic *[Re: Import sql script](/forum/2-general-talk-about-hikashop/8527-import-sql-script#70705 "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")* Hello, and how can I the script execute? In joomla? On the shell? What is a simple way? Thanks Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg1MjctaW1wb3J0LXNxbC1zY3JpcHQ/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[apollo123](/forum/user/1244-apollo123 "View apollo123's Profile")** - [![apollo123's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/1244-apollo123 "View apollo123's Profile") - [ Offline](/forum/user/1244-apollo123) Less More - Posts: 22 - Thank you received: 0 - - 13 years 6 months ago [\#98410](#98410) by [apollo123](/forum/user/1244-apollo123 "View apollo123's Profile") Replied by *[apollo123](/forum/user/1244-apollo123 "View apollo123's Profile")* on topic *[Re: Import sql script](/forum/2-general-talk-about-hikashop/8527-import-sql-script#98410 "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")* This code saves a lot of time ![:)](/media/kunena/emoticons/8.png ":)") 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg1MjctaW1wb3J0LXNxbC1zY3JpcHQ/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86290 - Thank you received: 14268 - - 13 years 6 months ago [\#98455](#98455) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Re: Import sql script](/forum/2-general-talk-about-hikashop/8527-import-sql-script#98455 "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")* 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](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg1MjctaW1wb3J0LXNxbC1zY3JpcHQ/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[apollo123](/forum/user/1244-apollo123 "View apollo123's Profile")** - [![apollo123's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/1244-apollo123 "View apollo123's Profile") - [ Offline](/forum/user/1244-apollo123) Less More - Posts: 22 - Thank you received: 0 - - 13 years 6 months ago [\#98569](#98569) by [apollo123](/forum/user/1244-apollo123 "View apollo123's Profile") Replied by *[apollo123](/forum/user/1244-apollo123 "View apollo123's Profile")* on topic *[Re: Import sql script](/forum/2-general-talk-about-hikashop/8527-import-sql-script#98569 "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")* 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 ![;)](/media/kunena/emoticons/11.png ";)") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg1MjctaW1wb3J0LXNxbC1zY3JpcHQ/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[apollo123](/forum/user/1244-apollo123 "View apollo123's Profile")** - [![apollo123's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/1244-apollo123 "View apollo123's Profile") - [ Offline](/forum/user/1244-apollo123) Less More - Posts: 22 - Thank you received: 0 - - 13 years 6 months ago [\#98573](#98573) by [apollo123](/forum/user/1244-apollo123 "View apollo123's Profile") Replied by *[apollo123](/forum/user/1244-apollo123 "View apollo123's Profile")* on topic *[Re: Import sql script](/forum/2-general-talk-about-hikashop/8527-import-sql-script#98573 "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")* Yup. That was all me.. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzItZ2VuZXJhbC10YWxrLWFib3V0LWhpa2FzaG9wLzg1MjctaW1wb3J0LXNxbC1zY3JpcHQ/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. Board Categories HikaShop- Install & Update- Product & Category Display- Content Creation- Sales and Orders- - Orders management- - Reports & Statistics- Customers- Checkout- Payment Methods- Shipping Methods- Import & Export (CSV, XLS…)- Specific Features- - Affilation- - Taxes- - Wishlist- - Vote- - Carousel & Slider- - Mass Actions- - Filters & Search- - Discount & Coupon- - Feature Request- Integrations- Showcase- General Talk about HikaShop- Development- - HikaShop 3.0 Beta- Translation- Commercial Jobs HikaShop Components- HikaMarket- - Market: Feature requests- - Market: How to?- - Market: Bug Report- HikaSerial- - General talk about HikaSerial- - Serial: Bug Report- - Serial: How to?- - Support en Français de HikaSerial (French Support)- - HikaSubscription- HikaAuction- - Auction: Feature requests- - Auction: How to?- - Auction: Bug report Hikashop around the world- Support en Français (French Support)- Russian- - FAQ. Курс выживания с Hikashop. Частые вопросы и о- - Расширения Hikashop- - Наши сайты на Hikashop- 日本語版サポート(Japanese Support)- Thai- German Archives- Bug Report- How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.298 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org) [](#top)