How to remove duplicate row records when importing CSV files to mysql using php?

Question

I need to remove duplicate records when importing my current CSV files into the database. My files has been successfully updated, but when I tried to upload the same files again, it straight away inserted into it again. is there any way of I can remove duplicate records if i am importing the files?

<?
if(isset($_POST["submit"])){
    $file = $_FILES['file']['tmp_name'];
    //echo 'upload file name: '.$file.' ';
    $handle = fopen($file, "r");
    $c = 0;
    $count =0;

    while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
    {
        $count ++;
        $ID = $filesop[0];
        $Name = $filesop[1];
        $Contact = $filesop[2];
        $Email =$filesop[3];

        if($count>1){
        $sql = "INSERT INTO clients(id,name,contact,email,)VALUES($ID,'$Name',$Contact,'$Email',')";
        $resultsql = mysqli_query($link, $sql);
        //echo $resultsql; //how am i going to remove duplicates when if there is a duplicate record ?

For each row in the CSV you query the database to see if that data exists. If it does, it is a duplicate and you can skip inserting. — Jay Blanchard
– Jay Blanchard, Commented Jan 19, 2016 at 14:08
I have usual tasks like this and I truncate the table before importing csv files but that only works if the data is always the same and only gets updated. — sinaza
– sinaza, Commented Jan 19, 2016 at 14:10
I believe the best way will be to add an unique key on name,contact and email. That way if the record already exists it will not be inserted and you can use the mysql return value and error codes to count the duplicate records that were skipped — Dobromir Velev
– Dobromir Velev, Commented Jan 19, 2016 at 14:12

Nana Partykar · Accepted Answer · 2016-01-19 15:47:01Z

1

1) Before inserting, check existing data.

<?php

if(isset($_POST["submit"])){
    $file = $_FILES['file']['tmp_name'];
    //echo 'upload file name: '.$file.' ';
    $handle = fopen($file, "r");
    $c = 0;
    $count =0;

    while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
    {
        $count ++;
        $ID = $filesop[0];
        $Name = $filesop[1];
        $Contact = $filesop[2];
        $Email =$filesop[3];

        $checkExistingData = "SELECT * FROM clients WHERE name='$Name' AND contact='$Contact' AND email='$Email'";
        $resultcheckExistingData = mysqli_query($link, $checkExistingData);
        $countExistingData = mysqli_num_rows($resultcheckExistingData);     

        if($countExistingData == 0)
        {
            if($count>1) {
                $sql = "INSERT INTO clients(id,name,contact,email,)VALUES($ID,'$Name',$Contact,'$Email',')";
                $resultsql = mysqli_query($link, $sql);
                //echo $resultsql; //how am i going to remove duplicates when if there is a duplicate record ?
        .
        .
        }
.
.
}?>

2) If data got inserted and you want to delete duplicate rows from table. You can try this too.

DELETE c1 FROM clients c1, clients c2 WHERE c1.name = c2.name AND c1.contact = c2.contact AND c1.email = c2.email AND c1.id > c2.id;

edited Jan 19, 2016 at 15:47

answered Jan 19, 2016 at 14:16

Nana Partykar

10.5k10 gold badges50 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

lalaland Over a year ago

thank you , it is able to help me to check for the existing data, but what if u need to insert the files into the data and remove duplicates in the entire whole system ?

Nana Partykar Over a year ago

Please see my 2nd point @lalaland . It will delete duplicate values.

lalaland Over a year ago

do i put this query just right below this line ? if($count>1) { $sql = "INSERT INTO clients(id,name,contact,email,)VALUES($ID,'$Name',$Contact,'$Email',')"; $resultsql = mysqli_query($link, $sql); And what does the c1 and c2 stands for ?

Nana Partykar Over a year ago

No. First one is for not allowing duplicate data to enter. Second one is for deleting duplicate data. You can keep 2nd query in any where and run. It will delete duplicate record from that particular table. I found this query for you. c1, c2 are alias name. Don't worry. It will work @lalaland

Nana Partykar Over a year ago

@lalaland : You didn't responded back.

|

carol · Accepted Answer · 2016-01-19 14:16:17Z

0

Add unique key to one or more of your columns, column with unique key will store unique values only, duplicate values will not be inserted.

answered Jan 19, 2016 at 14:16

carol

3192 gold badges4 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to remove duplicate row records when importing CSV files to mysql using php?

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related