CODEWITHSUNDEEP
phpmysqluploadphpexcel
KillABug
KillABug

Reputation: 1414

Eliminating duplicate entries while uploading excel files to mysql


I am working on an application where the website admin can upload excel sheets to upload data to the database. Offcourse I use this excellent library for uploading files to my mysql database.

Issue: It all works fine,but the issue now is,if the admin uploads a list,I have to show the admin the uploaded data on a page and ask him to confirm the upload by hitting a button.Till the confirmation point I store the data in a temporary table in the DB.Once the admin confirms,I move it to the permanent table in DB and delete the entries in the temporary table.Now if the admin edits the same excel,i.e he adds some more records to it and uploads it again,the previous records get duplicated,which should not happen.

I want to check if the records from the excel file already exists(based on a unique id that is associated with each record). This is not the primary key but a no. associated with each record present in DB and the excel sheet.

I am looking for suggestion on how can I check if the record already exists as I delete the temporary table info. Can I compare the temp table data(if not deleted)with the file data each time its uploaded?How can I check if the file records are similar to the once in the database and upload only newly added records?.

Any suggestions,would be helpful to go the right way .
Thank you for the attention

Upvotes: 1

Views: 2400

Answers (2)

shreyas
shreyas

Reputation: 208

You can use insert ignore which will ignore duplicate record provided appropriate unique index is applied. this will ignore already inserted entries and add only new entries. this is what u want i guess

All you need to do is instead of INSERT INTO tbl_name (col1,col2) VALUES(val1,val2); do INSERT IGNORE INTO tbl_name (col1,col2) VALUES(val1,val2); This should stop the duplicate and only insert new entries. Please note this relies on proper indexing applied.

Upvotes: 1

Namphibian
Namphibian

Reputation: 12221

To identify new records use a left or right join(your preference) on the temp and permanent tables to insert the new records into the permanent tables. You can also use this technique to determine if you need to delete records from the permanent tables.

The use a inner join to execute a update the records currently in the database as @Pynner pointed out in his earlier comment.

See this answer for a explanation of how to use left and right join etc Is there any way to check whether or not the query inside a MySQL trigger returns an empty set?

Upvotes: 1

Related Questions