PHP skip duplicates

Question

I have serious question about importing data from CSV to Database. Import script:

if (file_exists('temp.csv')) {   

        $i=0;
        require "connection.php";
        $handle = fopen("temp.csv", "r");
        try {
  $import= $db->prepare("INSERT INTO adherence(
                          dateandtime,
                          lastname,
                          firstname,
                          paidtime,
                          approvedtime,
                          notadhering) VALUES(
                          ?,?,?,?,?,?)");
$i = 0;        
while (($data = fgetcsv($handle, 1000, ",", "'")) !== FALSE) {
    if($i > 0) {
        $data = str_replace('"', '', $data); 
        $myDate =  date("Y/m/d",strtotime(str_replace('/','-',$data[0])));
        $import->bindParam(1, $myDate, PDO::PARAM_STR);             
        $import->bindParam(2, $data[1], PDO::PARAM_STR);                
        $import->bindParam(3, $data[2], PDO::PARAM_STR);                
        $import->bindParam(4, $data[3], PDO::PARAM_STR);                
        $import->bindParam(5, $data[4], PDO::PARAM_STR);
        $import->bindParam(6, $data[5], PDO::PARAM_STR);            
        $import->execute();
    }
    $i++;
}
fclose($handle);

Problem is, I need some sort of conditional logic to check, if row allready exist in database before importing, and skip it - if it exist. How to handle this kind of thing?

Add those attributes/columns that make 2 rows a duplicate as a mysql key/index. Then use "insert ignore..." to insert only non existing rows. — maxhb
– maxhb, Commented Dec 2, 2015 at 19:48
Problem is, that there is some tables, that have 8 items in table, 7 of them are like keys. — JustinasT
– JustinasT, Commented Dec 2, 2015 at 19:49

Bjoern · Accepted Answer · 2015-12-02 20:02:26Z

2

Basically you have two different ways to approach it.

1. via the rdbms:

Use an unique index in your table. Once you insert a duplicate, you'll encounter an error, which can be properly displayed/logged/whatever.

2. via application logic:

Search for your item before inserting with a proper SELECT statement.. If you find a match, don't insert it.

Example:

$sth = $db->prepare("SELECT yourfields FROM yourtable WHERE yourcondition = :cond");
$sth->bindParam(':cond',$yourvariable, PDO::PARAM_STR);
$sth->execute();

if ($sth->rowCount() > 0) {
    // results - don't insert
} else {
    // place your insert terms here
}

In most conditions, coders will implement the first way, since it reduces traffic between application and RDBMS and makes your data model more robust. If this is any issue, try the second way.

edited Dec 2, 2015 at 20:02

answered Dec 2, 2015 at 19:54

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

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

3 Comments

JustinasT Over a year ago

Thank you. By any chance, maybe you have any example of 2nd way?

Strawberry Over a year ago

Option 2 isn't really an option

Bjoern Over a year ago

@JustinasT I've added an example.

Collectives™ on Stack Overflow

PHP skip duplicates

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related