Before anything else, I would like to explain the scenario first.
The company has a lot of websites. The websites are hosted on different web hosting providers and each of the websites are totally different from each other especially on their database structure. But the websites contain similar products.
These websites are e-commerce sites. When a new item arrives, we have to add them one by one manually to each sites. The problem with this is that, it is very much time consuming not to mention that we have a lot of products to add(more than 1000+).
We are planning to fix this issue by developing an application that would allow users to add all of the products and store it on a local database. Then dump the database and import it on each websites. But there's a problem, as I said, the websites doesn't have an identical database structure. Importing the dump file directly using phpmyadmin will be useless.
I already have an idea on how to do it using php but I haven't tried it yet. Does anyone has a suggestion on how to do it? Or does anyone know an application that has this kind of capability?
Thank you in advance.
converting a database structure to another structure
Moderator: General Moderators
-
rhinestone89
- Forum Newbie
- Posts: 1
- Joined: Tue Oct 12, 2010 4:55 pm
Re: converting a database structure to another structure
a possible alternative: LOAD DATA INFILE could do the job... is perfectly possible to choose what fields of your csv file should be used to insert in the table...
even you can skip specific lines if you want... as per the manual..
By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.
even you can skip specific lines if you want... as per the manual..
"If all the lines you want to read in have a common prefix that you want to ignore, you can use LINES STARTING BY 'prefix_string' to skip over the prefix, and anything before it. If a line does not include the prefix, the entire line is skipped. Suppose that you issue the following statement:
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';
If the data file looks like this:
xxx"abc",1
something xxx"def",2
"ghi",3
The resulting rows will be ("abc",1) and ("def",2). The third row in the file is skipped because it does not contain the prefix."
Re: converting a database structure to another structure
It sounds like your problem might go deeper than that, depending on the specifics of how the database structures are different. Are we talking about a relational database with more than one table, or just one table with different fieldnames, or one table with different data in the fields? We would need to know considerably more of the details to give you much help.