synchronization update between db

[Sailor]

Ok… first I'm not an DB expert like all of you, just a common break fix guy!

I really need ideas from you guys out there how to do the following;

given:

a) MySQL v4.0 db on aPlus Hosting provider

b) inventory db has over 10500 records

c) inventory from various manufactures are changing nightly

problem:

1) how can I synchronization my inventory db on my host on a nightly schedule, to reflect only the new changes from the other manufactures flat files that I have local?

Any successions or even scripts that really work, I will reward that person to a +25% discount from my just-pocket-knives.com web site.

Cheers,

Sailor

[CalEvans]

Sailor,

1: Are the flat files 1 per table?

2: Do they replace the entire contents of the table?

If the answer to those two questions are yes then I have a couple of ideas

First, If the files are coming into you as flat files then check out mysql docs to see how to load a flat file into a table. This is the easiest way to get data into the system from a flat file. If you can't get that to work then it gets a bit trickier.

10,500 records is not a lot of records. I currently update a database with about 1.5 million records from FoxPro DBFs every evening. I do it in a CRON job. DO you have access to a crontab on your account with aPlus?

It won't help you but I use a program called dbf2mysql to do the import. takes about 30 minutes to do the entire thing. The answer to both questions above in my case is yes, that's what makes it work so well.

If either of the questions above is no then you are going to have to write some code. (or hire a programmer) I'd use PHP since' it's easy to work with. Then you read in the fields one row at a time and use whatever logic you need to determine what table it goes in and/or does it replace an existing record or is it a new record.

Another advantage of PHP is that if you do not have access to a crontab then you can make the update a webpage. Then you can setup a task in your windows scheduler to call your webpage (www.mydomain.com/myNightlyUpdate.php) and it will do the job. Hey, it's not pretty but it would work.

In short, it is possible to do what you want done but how easy it is depends on a lot of information you didn't provide.

HTH,

=C=

[Author]

Posts