database question: insert new users, orders

[rebusB]

I am working on rolling out an updated version of my site, switching hosts in the process. So now I have the old site live, and the new site hosted and viewable, just not under the official domain. Just before I go live with the new store I would like to bring in any changes made to the users and orders (and possibly parts of the product catalog) sections of the old store's database leaving the new configuration intact.

I started with a new install of the latest version (1.3.9h), importing and updating the database of the existing store at the time for a base of products, users, etc. Now that I am close to rolling out the new site there are new users and order history in the old sites database.

I dumped both databases and using FileMerge I can see the differences and could easily merge those into the new database. Is this the best way to do this? It is only a few dozen additions so any syntax changes I could edit by hand. Just wondering if this is safe and effective or if there is another recommended method/tool for doing this kind of thing.

Thanks in advance,
Rebus

(version 1.3.9h)

[DrByte]

I always recommend keeping the live database as the main database, and then simply applying any changes to that live database ... instead of what you're suggesting which is the other way around. Far less likely to lose critical data that way.

However, if your database skills are strong enough, and you know exactly what you'll do to recover from a problem if you have one, then proceeding with the approach you mentioned, could work. Riskier, but can work. As long as you understand how databases work and how table structures and data records interact, etc. Many people don't have any clue about those aspects, and thus they should never foray into that space .. which is another reason why I strongly encourage people to always keep the live database as the main one, and merely update IT, as I said in my opening paragraph.
(Yes, those are disclaimer comments, and especially intended for the benefit of others who read this post in the future.)

[rebusB]

Thanks for the response Doctor!

I do understand why that is the sensible approach. Figured that since this is my 2.0 site, with an updated ZC, and was going to be fairly different I didn't want to mess with the live database and could take my time with the new site. At this point, I have already gone down that road with the 2.0 site about ready to go. Just pushed the complicated bits down the road to now.

I have done a little mySQL work and plenty of coding, enough to know the basic concepts of relational databases, so I am okay with messing around with them. It just isn't my main thing and this is far more complicated an db app than I have dealt with. (That is why I was trying to find out more about the API.)

So my main concern is that I be sure to update all the tables involved in recording the users and transactions. Is there a document that shows the relationships, or can you tell me which tables I should pay attention to?

[DrByte]

www.zen-cart.com/wiki

[rebusB]

So I think I can get this to work. The docs, especially the ones at http://www.zen-cart.com/docs/ look like great reference.

Needed to update the database to 1.3.9 from 1.3.8 which was easy, just exported from the live store and imported it into my localhost database (yeah, there are actually three databases involved but the local and the new host are in sync.) where I ran the install/sql/ patch to bring it up to date.

Then it I exported it again (all import and export through phpMyAdmin) and ran it against the export from the new host database in FileMerge. Here is my strategy...

The tables to change are:
address_book; authorizenet; coupon_redeem_track; customers; customers_basket; customers_basket_attributes; customers_info; orders; orders_products; orders_products_attributes; orders_status_history; orders_total; products; products_description

not sure about:
counter; counter_history;

the rest of the changes are in the configuration of the site, things like ez-pages, which I do not want to change.

thankfully I have not made any changes to the catalog of products, and the only differences are in inventory and view count. I could probably leave those alone.

So when the merge is done (merging the changes and additions from the live site in) the dump file should look like it came from the live store, only configured visually to match the new store.

Only other concern is the new store has database caching enabled. Hopefully that wont be a problem. Or maybe I should turn it off before I import the new dump?

[DrByte]

Needed to update the database to 1.3.9 from 1.3.8 which was easy, just exported from the live store and imported it into my localhost database (yeah, there are actually three databases involved but the local and the new host are in sync.) where I ran the install/sql/ patch to bring it up to date.

I STRONGLY recommend using zc_install as per the official upgrade instructions. Manually running SQL scripts causes you to miss the programmatic changes that are included in the zc_install program.

Then it I exported it again (all import and export through phpMyAdmin) and ran it against the export from the new host database in FileMerge.

Assuming you do a thorough very careful study of the data you're moving, that process will probably work.

BUT I MUST ADD THIS IMPORTANT DISCLAIMER:, even if only for future readers of this discussion
WE ALWAYS RECOMMEND USING THE ORIGINAL LIVE DATABASE, and applying your new changes to it. NOT the other way around.
ANY DEVIATION FROM THIS RECOMMENDED PROCESS is risky. Be sure to "count the cost" before deviating from the advised approach.

Only other concern is the new store has database caching enabled. Hopefully that wont be a problem. Or maybe I should turn it off before I import the new dump?

If by "database caching" you're referring to settings in configure.php, don't worry about it. If you set your store to Down For Maintenance there won't be a problem.