Post by amvisser » Sat Jan 24, 2015 12:17 am

Dear all,

I've made an OC webshop and manually deleted most countries and zones that I was not using. (Not knowing that this could be done by editing the database :( !!) Unfortunately, I want to restore some of the countries/zones now.

Could anybody tell me how to do this, avoiding to set up the store from scratch? If this is not possible, is there a list avaiable of countries/zones used in OC, to manually insert them? I've searched the internet/forum for some while, but I can not find the right information.

Thanks all!
Annemiek

Newbie

Posts

Joined
Thu Jan 15, 2015 7:14 pm

Post by tjsystems » Sat Jan 24, 2015 12:34 am

Hi Annemieke,

Witch version are you using? Should be possible to add then directly to the db.

Gr,
Tim

Live with 2.2.0.0 fully SSL.


Active Member

Posts

Joined
Sun Aug 26, 2012 5:39 pm
Location - NL, Amsterdam

Post by amvisser » Sat Jan 24, 2015 12:37 am

Hi tjsystems,

Owh that sounds great. I'm using 1.5.6.1.

Could you maybe explain this newby what to do ::)?

Newbie

Posts

Joined
Thu Jan 15, 2015 7:14 pm

Post by tjsystems » Sat Jan 24, 2015 3:26 am

What you can do is import the 'country' and the 'zone' table from the opencart install sql file into you database.

***Make backup before doing this***


If you need any help with that please PM me.

Live with 2.2.0.0 fully SSL.


Active Member

Posts

Joined
Sun Aug 26, 2012 5:39 pm
Location - NL, Amsterdam

Post by Johnathan » Sun Jan 25, 2015 12:05 am

1. Open the /install/opencart.sql file in a standard installation. That contains the structure and data for every table.

2. Look for the line starting with "Dumping data for table `oc_country`".

3. Copy the data there into an SQL editor like phpMyAdmin. Note that if you have a different table prefix, you'll need to change "oc_country" to "YOURPREFIX_country".

4. If you still have data in those tables, make sure you run this query first to remove all current data:

Code: Select all

TRUNCATE TABLE `oc_country`;
5. Do the same for the "oc_zone" table.

Image Image Image Image Image


User avatar
Administrator

Posts

Joined
Fri Dec 18, 2009 3:08 am


Post by Johnathan » Mon Jan 26, 2015 11:10 pm

amvisser wrote:I've located the opencart.sql and have find the line you were refering to: "Dumping data for table `oc_country`" (By using Notepad).

I've also opened my database by means of PHPAdmin and at the left side I see the folder "oc_country". If I click on that folder, I see 12 countries which I have not deleted before.

However, from that point I actually don't know how to restore the "oc_country" folder/content, despite your instructions. Do I have to copy the data from the opencart.sql to the "oc_country" folder? And if so, what data exactly, untill where? And how would I do such a thing in PHPAdmin? If you could give me further instructions, that would be such a relief.

I'm sorry for my inexperience in OC, but thank you!
Annemiek
If you're not familiar with how to use phpMyAdmin, I highly recommend you hire someone to do this for you, or at the very least, try this in a test installation first. Playing around with your database without knowing what you're doing can cause serious issues. Make sure you make a backup of your database before editing anything.

The database does not have folders, it stores things in tables (like a spreadsheet). You need to paste the TRUNCATE query text into the query editor area of phpMyAdmin. You then execute that query, and it will empty the oc_country table. (If you can't find the query editor, you might consider looking up a tutorial on phpMyAdmin, so you understand what it does, and how it works.)

After running the TRUNCATE query, then you need to paste the query text that you copied from opencart.sql into the query editor, and execute that query. That should restore all the countries.

Image Image Image Image Image


User avatar
Administrator

Posts

Joined
Fri Dec 18, 2009 3:08 am


Post by amvisser » Tue Jan 27, 2015 7:06 am

This message is directed to everybody with a similar problem. Here a short update on the steps that you should take. (This information has been made possible by Johnathan and tjsystems, thank you both!!) Step 8 below corresponds to OC 1.5.6.1, but is easily adapted to your version.

1. Backup your database (By using PhpAdmin or OC: Admin>Systems>backup)
2. Login to your phpAdmin
3. Select the right database (left)
4. Select the right table, being 'oc_country'
5. Click on SQL (top center of your page)
6. Paste the following code into the SQL area: TRUNCATE TABLE `oc_country`;
7. Press start/go
8. Paste the code from your opencart.sql file (Upload>Install>opencart.sql) into the SQL area.

This code will start with the following line:
INSERT INTO `oc_country` (`country_id`, `name`, `iso_code_2`,

And will end with the line:
(251, 'Canary Islands', 'IC', 'ICA', '', 0, 1);

9. Press start/go
10. Repeat the above steps for table oc_zone

Newbie

Posts

Joined
Thu Jan 15, 2015 7:14 pm

Post by jdiai » Wed Nov 22, 2017 4:53 am

Hello i too had this issue. I deleted my country and zones list from my back end running on oc 1.5.6 & this post helped me. also had additional help from a forum member on how to go about it. Getting the country list in the php admin table wasnt hard for me. What was hard was getting the zone list in the phpadmin database table cos it was soo long more than 3000 zones lol took me a while and some errors here and there bt i scaled through. If you need help on how i did mine u can chat me up or post here. I'll try to help with what i know. Thanks all

Newbie

Posts

Joined
Tue Nov 12, 2013 11:30 pm

Post by onefish » Thu Nov 08, 2018 4:11 pm

When I setup the store for my wife she was adamant she would never ship overseas...… about a month later she changed her mind (woman's prerogative aye!). I've put off restoring all the countries and zones as long as I can, which led me here.

The instructions are pretty easy but I am wondering if anyone can tell me if country and zone data used on all the orders in the system are referential (relational) by the ID's? I think from memory that I deleted all countries and zones and added back the ones I wanted so the ID's on existing order records will not match the original install. This won't be a problem if the names and codes are stored in the oc_order table and not referenced by the ID's.

Just looked at the table structure for oc_order in the install\opencart.sql file and it looks like there are fields payment and shipping descriptions not just ID's. However in the table oc_address there are only 'country_id' and 'zone_id' fields, with no description fields. Does this sound correct? If so then the order records would be unaffected but customer's address records would be affected?

Would this possibly be best managed by simply reallocate the ID's so that the ID's in the upload match the ID's I currently have for country and zones? If so, how do I find the ID's currently allocated to them?

New member

Posts

Joined
Mon Nov 20, 2017 3:19 pm
Who is online

Users browsing this forum: Amazon [Bot] and 41 guests