data import to DB

[54uydf]

Hi,
Need some guidance here
parts of my DB need to be updated by data from another system's DB, what I'm gonna get are CSV files with data that isn't exactly the same as my DB, meaning, the tables aren't the same structure, the fields don't have the same name/format..some fields might not even exist, parts of the same record might be in different CSV files..bottom line I need to make it work somehow lol the new data isn't just added to my DB, but it has to be compared with data that already exists and only the changes would be updated (and even that not always).

I don't know even where to start with this, my DB is ready, but I need to make an option that once in few months someone updates the DB without me, it can't be just a simple import in phpmyadmin because of all that I explained above, so I'm not sure where to begin and how exactly to make this work? do I write a bunch of php functions to manipulate the CSV files and then just insert it? or how? I need this to be as easy as possible for the people who are gonna use this system, I mean, I don't know who's gonna be responsible for the updates but they definitely not gonna sit and change the XSL files by hand to fit the DB every time obviously..

the thing is, the only way I know how to import data into DB from CSV files is by clicking "import" so even if the file is exactly the format of my table, I still don't know how to check the data before it saved and control what's added and what's updated.

please share any idea or advice you have :)

[smantscheff]

Write a script which imports all CSV data into a separate database. Use the mysqlimport command line utility for that purpose.
Then write an SQL script which compares the imported CSV data with the data in place and updates them as necessary. Store this script as a stored procedure so that anyone can run it from a database interface.
Do not use PHP nor phpMyAdmin as you will spent much time coding for results which are already integrated in the various mysql command line tools. Rather spend your time learning how to use those.

[54uydf]

Ooo! nice, thanks..I have basic SQL skills, and I've made like 1 stored procedure once but I never understood what to do with it..so you are saying it has nothing to do with php code and I should focus on learning advanced SQL now?

[smantscheff]

Yes, that's the essence. You can do quite a lot with plain SQL.
As for the workflow: Write a script which start always on a fresh copy of your original database and do not do any manual database editing. Integrate all editing (if necessary) into your script. That way you will be able to apply global changes and correct conversion errors rapidly.

[54uydf]

by script you mean php?

[smantscheff]

No, by script I mean a text file containing SQL statements and which can be fed into mysql via the command line by means of input/output redirection (pipes).
Of course you can use PHP as a script language from the command line, too. But do not use it via a webserver with lengthy operations as this will make the workflow quite awkward.