LOAD DATA problems

[Zxin]

I currently have a roster setup for our game guild, and am able to import data just fine. But, if new data is imported like this
LOAD DATA LOCAL INFILE '$localfile' REPLACE INTO TABLE Roster FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'

The whole row is wiped out and new data placed, loosing all old data in collumns not even supported by the LOAD DATA.

So my 1st thought was to create a new temp table and some how detect changes and then use an UPDATE to write changes, and the DROP the temp table, but I am a noob with it comes to php/mysql, and this is beyond my experience.

Any ideas on how to accomplish this, or examples would be appreciated.

-Zxin

[AstroTeg]

Hmm... I haven't used the LOAD DATA statement much. I tend to load gobs of data at the command line and I'm loading it into empty tables so I haven't run across your particular problem.

The data thats getting wiped out per row is because LOAD DATA looks at the file and if you have few columns in the file than columns in the table, MySQL puts in the default values for those extra columns (and in your case, wiping out the data).

I'm not sure how many rows you're having to insert at one time. Granted LOAD DATA is very fast, but because of your circumstances, I think you're going to have to manually read the import file and perform the individual INSERT/UPDATEs as needed.

Or... Fiddle with the import file to fill in the extra columns that are missing data. That might work, but without knowing how you're database is structured, I'm not sure if this would be a workable solution or not. Once the file has been fiddled with, it should be ready for import.