add data from csv file to mysql database avoiding duplicates in php

Question

I want to add data to database via csv file. Am able to achieve this but whenever i upload that file again it gets duplicated, I dont want the data of a particular row to get entered again in a row but a specific column field may get repeat.The following code is for upload file am having database with table location having fields for ID(auto incremented), State, City.

<?php
ini_set('display_errors',1);
error_reporting(E_ALL);
require ('dbconfig.php');

if(isset($_POST['submit']))
{
    $fname = $_FILES['sel_file']['name'];
    echo 'upload file name: '.$fname.' ';
    $chk_ext = explode(".",$fname);

    if(strtolower(end($chk_ext)) == "csv")
    {
        $filename = $_FILES['sel_file']['tmp_name'];
        $handle = fopen($filename,"r");

        while (($data = fgetcsv($handle, 10000, ",")) !== FALSE)
        {
            $sql = "INSERT into location(State,City,District) values('$data[0]','$data[1]','$data[2]')";
            mysqli_query($conn,$sql) or die ;
        }
        fclose($handle);
        echo "Successfully imported";
    }
    else
    {
        echo "Invalid File";
    }

}
?>
<h1>import CSV file</h1>
<form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post' enctype="multipart/form-data">
Import File: <input type='file' name='sel_file' size='20'>
<input type='submit' name='submit' value='submit'>
</form>

got the solution using insert ignore into(........) while making city as unique in table — Shahzad Khoja, Apr 11 '17 at 07:56

score 1 · Answer 1 · edited May 23 '17 at 10:30

1

The first way: Combine unique State,City,District together :

ALTER TABLE `DB Name`.`Table Name` ADD UNIQUE (`State`, `City`, `District`);

and insert query should be in try catch

The second way: After inserting you should manually delete duplicates from your table How to delete duplicates on a MySQL table?

edited May 23 '17 at 10:30

Community

1
1

answered Apr 07 '17 at 12:48

Hayk Manasyan

508
2
20

score 0 · Answer 2 · answered Apr 07 '17 at 12:44

0

You need to create SELECT query before INSERT to check record existance by some fields. If select found record - do not insert it into DB.

answered Apr 07 '17 at 12:44

Viktor

819
1
12
26

score 0 · Answer 3 · answered Apr 07 '17 at 12:48

There are 2 solution for that

if you don't want to duplicate the combination of state, city and district then add a unique key to all the column

try

ALTER TABLE `location` ADD UNIQUE `unique_index`(`State`,`City`,`District`);

Before inserting data to table check with select query its already exist or not. If exist then skip that row/date. If not exist Insert the row data.

score 0 · Answer 4 · answered Apr 07 '17 at 12:49

you can use insert and update query in one.

if data is already exists then update that data just change your insert query to my insert query.

INSERT INTO location(State,City,District) VALUES($data[0]','$data[1]','$data[2]') ON DUPLICATE KEY UPDATE    
State=$data[0], City=$data[1] ,District=$data[2];

add data from csv file to mysql database avoiding duplicates in php

4 Answers4