Working on importing a 2.0.7 database

[Guest]

Okay, everyone, I'm biting the bullet (due to necessity) on one of my 2.0.7 sites changing it for 3.0. I've been writing it down step by step too. Perhaps if I post it here, someone can help/continue it. So this will be an ongoing thread.

Upgrading a 2.x site to 3.0.1

1. Backup your existing .sql database

2. Install 3.0 into a new directory using a new database

3. Set preferences, etc. in 3.0

4. Import existing data into the new database (this is the hard part - be sure you know how to use phpmyadmin before attempting)

a. Open phpmyadmin for the v3 store and open the .sql backup file in your favorite sql editor (I edit it thru cupeftp pro 6.0 that gives me line numbers).

b. I've compared the structure of each table and below is what you have to do for each one. Take this *slowly* step by step. If you've installed any mods or made any changes to your table, you might have to make adjustments to these instructions, adding fields you've created in the existing table to the new one, etc. You might want to rename the fields in the 2.x version to match those in 3.x before you export your backup or you can search and replace the old name with the new name in the backup file before you run the query). I cut and copied each section from the backup file to a new file to work on it.

These directions DO NOT include importing existing customers or existing orders into the database. There were soooo many changes between v2 and v3 in this area, I would strongly suggest that you put a note on the index page asking customers to please -re-register and use your print out of existing orders instead of trying to import them. Yes, it's a bit of a hassle, but unless someone better than me in sql can come up with an easier way, believe me, you DO NOT want to tackle it! )

If you mess up, empty the table and try again.

I _category

1) Changes from 2.x to 3.x

a) "category" was renamed to "cat_name"

New field "noProducts" was added

2) In the backup file:

a) Delete everything before the first "INSERT INTO" line

Find and Replace originalname_category with the newname_category (for example, default of v 2 was "store_category" while default of v3 is "CubeCart_category"

c) You need to add an blank field to the end to account for "noProducts" field added by v3, so Find & replace ");" with ",'');"

3) Delete the test category and any others you created directly in v3

4) Copy and paste the resulting text into "Run SQL query" box and press go

5) If you look at the site, you should see all your v2 categories are now displaying. If they are, congratulations! You’ve just learned the basic procedure you need to follow for each and every table. The remainder of these instructions will help guide you along, but remember to adjust it for your particular installation.

II. _inventory

1) Changes v2 to v3:

a) Added productId field

Renamed "product" to "productCode" and "title" to "name"

c) Added "noimage" after "image" and 7 additional fields after "sale_price"

2) Before you run the query:

a) Find & replace:

i) VALUES( with VALUES('',

ii) .jpg', with .jpg',1,

iii) ); with ,0 ,0 ,0, '', 0.25, 1, 0);

3) Run the query and import the inventory.

4) Then you need to run the following query (thanks to Sir William for figuring this one out!!) so the products will actually display in the store...be sure your _cats_idx table is empty before you do and again, adjust the name of the tables to match your actual installation - I'm using the defualt "CubeCart_" for demonstration.

 INSERT INTO CubeCart_cats_idx (cat_id, productId) SELECT CubeCart_inventory.cat_id, CubeCart_inventory.productId FROM CubeCart_inventory WHERE CubeCart_inventory.productId > 0;

You also need to run the following query to update the CubeCart_img_idx (modified from the one provided by Sir William)

INSERT INTO CubeCart_img_idx (productId, img) SELECT CubeCart_inventory.productId, CubeCart_inventory.image FROM CubeCart_inventory WHERE CubeCart_inventory.productId > 0

II. _options_bot , _options_mid & _options_top

Nothing changed in these other than the name from store_x by default to CubeCart_x by default, so just do a search and replace on that and run the query. You actually need to do these *after* you insert inventory as they use the product names.

While the options *are* showing in the database, they don't seem to be showing on the products. I'm still working on this one....

So, now you will have your products back, but no images. I'm working on this one yet! You *should* be able to move or copy all the existing images to the appropriate 3.x directories, but cuz I'm moving hosts too, my old images aren't currently available so I cannot test this one.

[Guest]

Glad to help. I saw your problem and just had to find a solution. I guess it's the geek in me. :on2long:

[Guest]

Hehe. Well in that case...

Everything is working except for a few minor things I'll post here to see if anyone can solve....

1) All products think they have more images (+ more images is appearing on every item). NONE of them do in fact.

(probably more to come as I work with the site tonight )

Oh, and the site I'm working on all this on is www.sensual-desire.com (for adults only :on2long:

[Guest]

Okay, I figured out why my view more images is on for everything, but don't know how to globally turn it off (thanks to my limited sql query knowledge).

What I need to do is change the field noImages in the table CubeCart_inventory from a 1 to a 0. I know there has to be a global way of doing this using phpmyadmin and an sql query, but no clue on the syntax. Sir William? Anyone?

Thanks!

SOLVED. The query to run is...

UPDATE CubeCart_inventory SET noImages='0' WHERE noImages=1;

[Guest]

Importing your customer database:

Okay, this one is harder than my previous instructions as you cannot simply search and replace pieces of code - you actually have to go thru the entire section of the backup file and modify it line by line before you can run the sql query to import it into 3.x.

Here goes...the original format of your 2x backup is shown in black - what you have to modify/add to make it compliant with 3x table is in red

INSERT INTO `CubeCart_customer` VALUES ('[email protected]', 'password', '',

'Firstname', 'Lastname', 'address1', 'address2', 'City', 'State', '', '32025', '226', (Change the country text to the country code 226=US) '1234567890', '',5, 20041120, (delete the time field here) '68.218.253.142', '',1 (CHANGE FROM Y TO 1), 1,1);

NOTE: '' above is 2 single quotes with no space, NOT a double quote!

This does seem to work, though it's a pain in the butt especially if you have a LOT of customers. If you have any better ideas, PLEASE let me know! lol

I made all the passwords the same and then did a mass emailing to all customers telling them their password has been reset temporarilty to "x" - please be sure to change it once you log in.

You can do small sections of search/replace to speed up the process a little:

Find 'United States' replace with '226' for example for each country

Find 'Y' replace with 0,1,1,1

but most of it has to be done line by line manually. NOT a fun proceedure!

[Guest vrakas]

Hi Mysty ,

i suggest you post this under downloads/ mods /minor then everyone can use it :)

[Guest]

Hi Mysty, you may find that your category counts are still off after the import. I have a free script on my site that will correct that.

Also, from what you've done, it would appear that a nice import script would be a worthwhile endeavour. If you'd be willing to send me a copy of your 2.x database, I'd take a look at automating the import / upgrade process. Lemme know. (I of course will agree not to mass e-mail your customers or sell your customer list. )

[Guest]

Yup, category items count never did work so I just deleted the display of the item counts. Didn't need it anyway. I did see your script and might try it when I upgrade one of my other stores. For now, most of them are still running under 2.0.7. Just upgrading the ones I have to for now.

My 2.x database is too huge to send anywhere - you might want to just do a 2.0.7 install for yourself and then go from there.

An import script is *definitely* needed. It's a royal pain in the butt to do everything manually.

[Guest]

Hello Mysty and others,

Thanks for the leads on how to upgrade to v3.

I tried upgrading the database from 2.0.7 to 3.0.4 following the directions outline, bu the products are not showing up under the categories (which was well imported).

Where do you think the problem is...could be that I should upgrade to 3.0 first and then upgrade stepwise to 3.04?

Also, the images refuses to allow me import them to from their 2.0.7 images directory.....suggesting a permission problem..

Thanks for your advise..

Ebabjohn