CODEWITHSUNDEEP
phpmysql
lalaland
lalaland

Reputation: 9

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

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 ?

Upvotes: 0

Views: 3757

Answers (2)

Nana Partykar
Nana Partykar

Reputation: 10548

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;

Upvotes: 1

carol
carol

Reputation: 319

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.

Upvotes: 0

Related Questions