Transfering Customers & Orders

[DrByte]

With advanced database skills and a solid deep understanding of the Zen Cart database architecture and thorough awareness of all the tables and relationships affected by data between tables, yes it's possible. But if you're asking the question then you're telling me you don't have those skills.

That's why we tell you to use the existing database ... which is the opposite way around what you're trying to do. Tackle it from the other direction and you'll keep all your data.

[twi]

Dr Byte. this one is addressed to you and I hope you can help me with this.

I currently have 2 separate ZC 1.3.9h web stores with 2 URLS. I am in the process in to combining the 2 in one ZC 1.5.1 ising the MULTISITE Mod.

The task at hand is combining the customers' data and the customers orders from two databases into one database for the MULTISITE ZC.

At this point, I am fairly versed in inserting and updating data in PhpMyAdmin using sql statements.

Since both databases customers and data start with "1" and etc for customer numbers and orders, there is a need to change the customers numbers and orders for the 2 databases to not overlap with each other.

eg. customer database1 has customers 1 to 100
and customer database2 has customers 1 to 50
so what I did was to have database1 1 to 100 and change database2 from 101 to 150.

for orders: database1 1 to 100, and database2 1 to 70 combined to become 170.

I am able to combine and upload these into the appropriate new database tables.

I have used the same method to transfer the orders files too. The orders are displaying correctly with the customers names and all other info.

The problem is that the customer appear in the Zencart Cpanel under the customer list. But went I click on the edit details of these customers, there are no information displayed except for a country that is not belonging to any of the customers in the customers list.

What am I missing? Can you give me some pointers in this?

[twi]

This is the format I used to insert into the "emptied database tables of ZC 1.5.1:

INSERT INTO `customers` (`customers_id`, `customers_gender`, `customers_firstname`, `customers_lastname`, `customers_dob`, `customers_email_address`, `customers_nick`, `customers_default_address_id`, `customers_telephone`, `customers_fax`, `customers_password`, `customers_newsletter`, `customers_group_pricing`, `customers_email_format`, `customers_authorization`, `customers_referral`, `customers_paypal_payerid`, `customers_paypal_ec`) VALUES

(1, 'm', 'name1', 'name2', '2010-05-21 00:00:00', 'customer##################', '', 1, '123-123-456', '', 'fcb6097abb488511206d91dbaa123456:83', '1', 0, 'HTML', 0, '', '', 0), ....... ;




INSERT INTO `customers_info` (`customers_info_id`, `customers_info_date_of_last_logon`, `customers_info_number_of_logons`, `customers_info_date_account_created`, `customers_info_date_account_last_modified`, `global_product_notifications`) VALUES


(1, '2013-04-12 15:19:03', 48, '2010-05-21 14:46:34', '2012-03-08 22:00:54', 0), ....... ;


Can you tell me what is wrong with this? Data did insert but they just don't display as described in my previous post.

Any help is appreciated.

[twi]

[QUOTE=twi;1206593]This is the format I used to insert into the "emptied database tables of ZC 1.5.1:

INSERT INTO `customers` (`customers_id`, `customers_gender`, `customers_firstname`, `customers_lastname`, `customers_dob`, `customers_email_address`, `customers_nick`, `customers_default_address_id`, `customers_telephone`, `customers_fax`, `customers_password`, `customers_newsletter`, `customers_group_pricing`, `customers_email_format`, `customers_authorization`, `customers_referral`, `customers_paypal_payerid`, `customers_paypal_ec`) VALUES

(1, 'm', 'name1', 'name2', '2010-05-21 00:00:00', 'customer@ email.com', '', 1, '305-123-456', '', 'fcb6097abb488511206d91dbaa123456:83', '1', 0, 'HTML', 0, '', '', 0), ......... ;


correction on the above values.

[frank18]

......I currently have 2 separate ZC 1.3.9h web stores with 2 URLS. I am in the process in to combining the 2 in one ZC 1.5.1 ising the MULTISITE Mod.

That means asking for trouble on a grand scale - the DB's of 1.3.9 and 1.5.x are slightly different.

At this point, I am fairly versed in inserting and updating data in PhpMyAdmin using sql statements.

Export - Import would be a better option. Still does not satisfy my first comment

Since both databases customers and data start with "1" and etc for customer numbers and orders, there is a need to change the customers numbers and orders for the 2 databases to not overlap with each other.

eg. customer database1 has customers 1 to 100
and customer database2 has customers 1 to 50
so what I did was to have database1 1 to 100 and change database2 from 101 to 150.

for orders: database1 1 to 100, and database2 1 to 70 combined to become 170.

Customer ID's AUTO_INCREMENT.

By using your approach you are breaking relationships with other tables...... same applies to Product ID's, Order ID's etc

See DrByte's comment in post #11

The problem is that the customer appear in the Zencart Cpanel under the customer list. But went I click on the edit details of these customers, there are no information displayed except for a country that is not belonging to any of the customers in the customers list.

Address book maybe.....

As I said above, you are asking for trouble. I truly hope you are not doing this on a live store but on a local development server ........

[twi]

I do know they have differences. For this reason, I have downloaded the following tables from zc 1.3.9 and zc 1.5.1 for comparisons :

address_book, customers, customers_info, orders, orders_products, orders_status_history, orders_total, paypal, paypal_payment_status_history, paypal_sessions, so_payments

since some of the above mentioned tables have differences in their structure, I uploaded/inserted only the data that needs to be updated into the new tables.


I have attempted to use the export and import method as well after combining the appropriate data in the various tables first on open spread sheet. Then created sql files for each table separately.


that I have taken into account too. that is why database1 was used as first database and database2 entries are incremented from the last/largest value of the first database.

See DrByte's comment in post #11

I will take a look at that. Is that post 11 in this forum you are referring to?

Address book maybe.....

can you elaborate on this? this is actually the only problem I am encountering. the customers appears on the customer list in the ZC cpanel. Only when you try to click on a individual customer, the details are not displaying.

Orders and invoices and all other tables are fine. All the correct items, and data are there including the customers info are in the correct orders, so are the shipping info. In other words, the uploads/insets/updates are working except the customers data are not appearing when you want to see the details.

Any body can help out on this one?


no. it is my server that I cam trying to setup for future use. Thanks on this reminder.

[DrByte]

Agreed, you are playing with fire, because you are not keeping all the relational keys in sync. And you left out the address_book table, thus your customer data is now corrupt.

[twi]

Agreed, you are playing with fire, because you are not keeping all the relational keys in sync. And you left out the address_book table, thus your customer data is now corrupt.

But I did take into account the address_book table too.
From my understanding. the 3 tables that deals with customers and its info are:

address_book
customers
customers_info




What I did was incrementing the numbers under address_book table uner colimns
address_book_id and customers_id by the same number to create the sequence.


for customers table, customers_id matched that of the address_book

for the customers_info table, the customers_id sequence are the same as well,

Can you elaborate a bit more on the part of address_table? what am I missing there? Or are there more than three tables involved here? I need to combine all the customers. Any suggestions in doing this other than what I am trying to do?

[DrByte]

You didn't mention customers_default_address_book_id
And you didn't mention any of the other tables that have a customers_id in them.

[twi]

Agreed, you are playing with fire, because you are not keeping all the relational keys in sync. And you left out the address_book table, thus your customer data is now corrupt.

what do you mean by keeping the all the relational keys in sync? How do you do that?