Transfering data

dannylewin

Hey guys,

I will be acquiring a site of a competitor who no longer wants to run his site. My issue will be transferring all his data over to my database (600+ listings) as we use completely different programing languages and data storage.

He has his info stored in text files. Mine, mysql.

I know I can read from a text file using php, and input the info that way. But if I also have to insert his member info into my members table, how do I link their listing to their account using my format?

I have a listing table, image table and member table. He has his listing and image info in one text file, and the user info in another. How would I even begin a task like this? Using loops id guess, but what would be the best order?

I guess basically my main question is, if I'm looping through his members file. How would I then get the associated data from his listing file to insert them into their respective tables in my DB? Baring in mind there could be multiple listings for each user.

Thanks for any help you can give me!

Danny

bradgrafelman

One possibility would be to create a separate database that models how the information is laid out in the text files. It should then be a straight forward 'LOAD DATA INFILE' query to load the data from the text files into this MySQL database. From there, you could use a SQL query or procedure (depending upon how much data massaging needs to be done to match your structure) to combine that data with your existing database.

It's also quite possible that it would be simpler to load the data from the text files into arrays in PHP and then directly INSERT the information from those arrays into your database.

Without more concrete information/examples (e.g. your DB structure, the structure of the flat files, etc.), though, it's hard to say.

dannylewin

Hi,

I think the latter example would work best. There's some data manipulation required before its inserted into my tables.

I just don't know where to start or how to explain, but ill try.

This is my members table:

member_id int(9) NOT NULL AUTO_INCREMENT,
first_name varchar(100) NOT NULL,
last_name varchar(100) NOT NULL,
member_country char(2) NOT NULL,
member_email varchar(100) NOT NULL DEFAULT '',
member_ip varchar(20) NOT NULL DEFAULT '',
member_nickname varchar(50) NOT NULL,
member_password varchar(100) DEFAULT '0',
member_level tinyint(1) DEFAULT '1',
m_active tinyint(1) NOT NULL DEFAULT '0',
m_random varchar(100) DEFAULT NULL,
date_joined date DEFAULT NULL,
last_login datetime DEFAULT NULL,
login_count smallint(3) DEFAULT '0',
loyalty_points decimal(10,0) NOT NULL DEFAULT '0',
login_attempts tinyint(1) NOT NULL DEFAULT '0',
login_disabled varchar(20) NOT NULL DEFAULT '0',

This is his member structure:

userID FirstName LastName Zip Country Email Password
i.e. ID-87648764 First Second 33837 U.S.A their@email.com *********

This is my listing table:

dress_id int(9) NOT NULL AUTO_INCREMENT,
member_id int(9) NOT NULL,
designer_id int(9) NOT NULL,
condition_id tinyint(2) NOT NULL,
age_id tinyint(2) NOT NULL,
shipping_id tinyint(2) NOT NULL,
terms_id tinyint(2) NOT NULL,
dress_title varchar(100) NOT NULL,
dress_desc text NOT NULL,
dress_currency char(3) NOT NULL,
dress_price double(10,2) NOT NULL,
dress_old_price double(10,2) NOT NULL,
dress_reduced tinyint(1) DEFAULT '0',
dress_sku varchar(50) DEFAULT NULL,
dress_tidy_url varchar(100) NOT NULL,
dress_chest float DEFAULT NULL,
dress_waist float DEFAULT NULL,
dress_drop_waist float DEFAULT NULL,
dress_sleeve float DEFAULT NULL,
dress_skirt float DEFAULT NULL,
dress_across_shoulders float DEFAULT NULL,
dress_bodice_shoulder_waist float DEFAULT NULL,
dress_bodice_neck_waist float DEFAULT NULL,
dress_full_length float DEFAULT NULL,
dress_status char(1) NOT NULL DEFAULT 'P',
dress_added datetime NOT NULL,
dress_updated datetime NOT NULL,
dress_official tinyint(1) DEFAULT '0',
discount_id varchar(20) DEFAULT NULL,
use_points tinyint(4) DEFAULT '0',

This is his listing structure:

SKU seller status lastActivity orig_Price price currency condition ageRange chest waist sleeve skirt neck length shoulder cover bloomers crown pic_1 pic_1W pic_1H pic_2 pic_2W pic_2H oire description views referrals wishlists inquiries

this is my image table:

image_id int(11) NOT NULL AUTO_INCREMENT,
dress_id int(11) NOT NULL DEFAULT '0',
image_file varchar(200) NOT NULL DEFAULT '',
is_main varchar(1) DEFAULT '0',

Is there a way to loop each line of the members file, in that loop bring in the related listing from the listing file using his userID format, have all data from both text files in arrays. Then insert the member data into my members table, insert the listing info into my listing table (and add my id by getting the ID of the last inserted record from the members table ect....)

I know how to do joins and the like in mysql, and im trying to look at flat files in the same way. Its probably just a little different than putting in "LEFT JOIN A TABLE WHERE A=B"...

Thanks for any help!!! :o)