How to use PHP Load Data Infile to replace the current copy of the table with the new one?

Question

We get a new copy of data in a pipe-delimited text file from time to time. I have to refresh the table or in other words, replace the existing copy with that of the newly generated copy (output.txt).

Any ideas are highly appreciated. Thank you..

TRUNCATE Table elements;

LOAD DATA INFILE '/data/out.txt' IGNORE INTO TABLE elements FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' (ID, Title, date, time);

Answer 1

I agree more information is needed to answer your question. As I understand it you need to empty a database table and reload it with the information in the text file you received, which sounds like a CSV file that uses pipes instead of commas. Do I have this right?

So you need to...

Step 1 - Get your information out of the text file. You can try something like fgetcsv() (setting the delimiter to '|'), or you could just use fgets() and use explode() to put the data in an array.

Step 2 - Insert data into your database table Loop through you data until you have it all in there. If all goes well then...

Step 3 - Delete the old data It might seem easier to empty the database first, and then add your data. You could do that, but if something goes wrong with the new data then what? Now you're stuck with an empty database table until you fix it. Depending on what this is used for that could be undesirable.