database export/import

[rick]

Hi,

We currently have an existing store using zencart. I am considering switching to the ms-max version of osCommerce for several reasons. My big concern is my existing database. Is there a utility that will bring my zencart data over to msmax/osc database without losing anything?

Thanks in advance.

Rick

[michael_s]

Basically, you need to make your existing db match structure with the max db. Then you can simply export the data to a sql batch file and run it on the max DB. I suggest working on a copy of your Zen DB, not the original.

Use a tool like MysqlDiff to compare the databases and generate the update scripting. Google mysqldiff for the application.

[rick]

Thanks, I will try it out.

Rick

[rick]

OK,

I have spent just about all day trying to get this done and I am not getting anywhere.

Is there anyone here who can convert this database for me?

Thanks

Rick

[michael_s]

Hi Rick,

It is fairly easy to do, so tell me where you are getting stuck and I can help you out...

Did you get mysqldiff? Did you install it and get it running? Let me know, I am sure I can talk you through it.

[rick]

Hi,

Thanks for your help. Yes I got it, installed it and ran it I don't know how many times. I ran it locally and on the server. The database is about 15 megs and on the server I would get the time out problem if I used the create insert/replace option and locally it did not go as far as it did on the server. Very weird.

Locally I tried using the sql and the database option and got nowhere with either one.

As I understand it I use my source file (current zen database) and my target file (current max database) and then run the script it creates. The script basically makes a create statement for all the new max tables and then drops all the zen tables and is supposed to give me the replace/insert statements to import if I choose that option.

Is that how you understand it to work?

Here is what the script produces concerning my customer table:
Below is the max table created

CREATE TABLE customers (
    customers_id int(11) NOT NULL DEFAULT 0 auto_increment,
    purchased_without_account tinyint(1) unsigned NOT NULL DEFAULT '0',
    customers_gender char(1) NOT NULL DEFAULT '',
    customers_firstname varchar(32) NOT NULL DEFAULT '',
    customers_lastname varchar(32) NOT NULL DEFAULT '',
    customers_dob datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    customers_email_address varchar(96) NOT NULL DEFAULT '',
    customers_default_address_id int(11) NOT NULL DEFAULT '0',
    customers_telephone varchar(32) NOT NULL DEFAULT '',
    customers_fax varchar(32) NULL DEFAULT NULL,
    customers_password varchar(40) NOT NULL DEFAULT '',
    customers_newsletter char(1) NULL DEFAULT NULL,
    customers_group_name varchar(27) NOT NULL DEFAULT 'Retail',
    customers_group_id int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (customers_id),
    INDEX purchased_without_account (purchased_without_account)
) TYPE=MyISAM;

Here is what the script produced from my customer table in the zen database:

CREATE TABLE zen_customers (
    customers_id int(11) NOT NULL DEFAULT 0 auto_increment,
    customers_gender char(1) NOT NULL DEFAULT '',
    customers_firstname varchar(32) NOT NULL DEFAULT '',
    customers_lastname varchar(32) NOT NULL DEFAULT '',
    customers_dob datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    customers_email_address varchar(96) NOT NULL DEFAULT '',
    customers_nick varchar(96) NOT NULL DEFAULT '',
    customers_default_address_id int(11) NOT NULL DEFAULT '0',
    customers_telephone varchar(32) NOT NULL DEFAULT '',
    customers_fax varchar(32) NULL DEFAULT NULL,
    customers_password varchar(40) NOT NULL DEFAULT '',
    customers_newsletter char(1) NULL DEFAULT NULL,
    customers_group_pricing int(11) NOT NULL DEFAULT '0',
    customers_email_format varchar(4) NOT NULL DEFAULT 'TEXT',
    customers_authorization int(1) NOT NULL DEFAULT '0',
    customers_referral varchar(32) NOT NULL DEFAULT '',
    PRIMARY KEY (customers_id)
) TYPE=MyISAM;

These both came from the file that mysqldiff produced. I just used the switch key to flip back and forth. You can see that neither one match so how will I be able to export/import my data?

Maybe I am doing something wrong and you can tell me. I appreciate you taking the time to help me.

Thanks


Rick

[rick]

Can anyone help me out.

Thanks

Rick

[jonthewebb]

Hi Rick

I've never used Zencart before, but looking at the db structure I can see that you can transfer across most of the structure fairly easily, but there are some incompatible features which could cause minor upsets. For example customer account histories might not work properly. You should be able to transfer across all customers, products, categories. There are several strange looking tables in zencart (records, media, record companies?!?!?!!?) which would be lost.

I should be able to carry out the conversion but it might cost up to £200, would need to see the structure first, you could probably find someone to do it for less!


jon

[jonthewebb]

Actually, looking at the structure of zencart it looks a bit of a mess....kind of like a hacked-about oscommerce....


jw

[neil]

Zen Cart is a project by some of the old Osc major contributors.
There may possibly be some answers at the Zen Cart forum.