PHP Upload CSV to update database records but exclude duplicate

I'm trying to figure out the best way to do this. I have a database with around 500 records/rows in MySQL and I need to update it daily. The problem is the file I upload will be Excel file (I probably need to convert it to CSV before upload?). Also I need to only upload records that don't exist yet in the current MySQL Database. The Unique field is named "MemberID".

What is the best way to achieve this? If I insert each rows (so I can check first if the record/row should be inserted to the Database) one by one like using a loop, will that be a slow process for uploading 500 records?

I'm new to PHP from VBA Programming and I only know how to insert records one at a time. Your suggestions is most appreciated.

Solution

You've got three options:

REPLACE (recommended as you're using a database that's updated daily - you'll never know, if old records didn't change from last update):
```
REPLACE INTO db_name (id,value) VALUES (1,1),(1,2),(1,3),(1,4)
```
It will affect all rows.
ON DUPLICATE KEY UPDATE (that's probably what you've been searching for, it will update whatever you want or simply leave the row 'as is'):
```
INSERT INTO db_name (id,value) VALUES (1,1) ON DUPLICATE KEY UPDATE id=id
```
INSERT IGNORE INTO (it will update only those rows that're new, skipping duplicates, but if you'll encounter key violations MySQL will NOT raise an error):
```
INSERT IGNORE INTO db_name (id,value) VALUES (1,1);
```

Also, some alternatives: SQL Merge.