CODEWITHSUNDEEP
phpmysqlcsv
Shahzad Khoja
Shahzad Khoja

Reputation: 11

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

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>

Upvotes: 1

Views: 2177

Answers (4)

Dipen Soni
Dipen Soni

Reputation: 224

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];

Upvotes: 0

Hayk Manasyan
Hayk Manasyan

Reputation: 506

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?

Upvotes: 1

Narayan
Narayan

Reputation: 1668

There are 2 solution for that

  1. 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`);
  1. 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.

Upvotes: 0

Viktor
Viktor

Reputation: 827

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.

Upvotes: 0

Related Questions