updating database

[stasJohn]

I'm fairly new to mySQL and php and seeking some input.

I created a database to hold a member directory. It is a single table.
The client uses some contact management software we're they keep track of their members. Therefore, the client does not want to enter the information through a form on the website. Instead the client wants to export a comma delimited file from the program and then use that exported file to update the database.

What would be the best approach to do this???

MySQL's LOAD DATA works fine as long as the original data does not change. If a member is removed or a primary key is changed, new rows are added but the old ones (that should of been updated) remain.

I figured whenever the client goes to update the db, a script deletes all data from the db, then imports the text file. This doesn't seem like a very efficient approach to me though.

Maybe importing the data into a temporary table and then comparing it to the actual table would work, though I am not sure how to go about doing this.

Sorry about the long post, any help would be appreciated,
thanks in advance

 

[bastienk]

if the text file to be loaded is consistent and accurate, who not truncate and load? i do that for several of our clients (after making a back up of the table should they realize that they messed up and offer a restore function as well) and it works well.


Bastien

Cat, the other other white meat

 

[stasJohn]

Unfortunately, the text file won't be consistent. Currently there are alot of empty fields they are working to fill in over the next several months. Thanks though.

 

[bastienk]

By consistent, I mean that its comma delimited or whatever and the total overall structure is the same. I realize that data may or may not change, additional data really ins't the issue....


Bastien

Cat, the other other white meat

 

[stasJohn]

oh, then yes, the structure of the text file will stay the same. And yes, truncating and uploading will work, to an extent.

My concern is, if the PK of an existing data row changes, (which it can) a new row will be added instead of modifying the one currently in the database. This is unexceptable because when people search the directory they will get two listing; the one that should of been changed and the new one just addeed.

 

[stasJohn]

whoops, okay, disregard my last post. I had a mind warp and totally thought truncate meant something else.

Truncating and loading will work. It is a solution I have thought about. I was just wondering if thats the most efficient way to do it?

 

[bastienk]

yes


Bastien

Cat, the other other white meat

 

[stasJohn]

works very well. Thanks!