How to insert data over old data?

I know practically nothing about mysql, but I use it with my webpage. I keep a record of students’ attendance and scores. They can click a button ‘Check my score’ and get their details.

I need to update each week.

At the moment, I only know how to do it like this:

I keep a csv file of names, student numbers, total scores. I upload this file to my webhost.

There is a file, say 19OEscores.csv and a mysql table 19OEscores. I truncate 19OEscores, then use:

LOAD DATA LOCAL INFILE ‘/home/mywebuser/public_html/includes/mycsvfiles/19OEscores.csv’ INTO TABLE 19OEscores CHARACTER SET utf8 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES

This works using php and does the job well. No problems.

If I do not truncate first, the data is inserted after the old data, which is not good.

Can I save myself the trouble of truncate and overwrite the old data?

Names, student numbers all stay the same, just each students total score changes.

Well, that was easier than I thought: just put REPLACE in the command:

LOAD DATA LOCAL INFILE ‘/home/mywebuser/public_html/includes/mycsvfiles/19OEscores.csv’ REPLACE INTO TABLE 19OEscores CHARACTER SET utf8 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES

1 Like
Sponsor our Newsletter | Privacy Policy | Terms of Service