Importing from CSV to MySQL using Update

Question

I have this php code that is aimed for importing all rows and columns in csv file into mysql table as records. It successfuly inserts from csv into mysql table called "import_items" BUT if i re-do the import, it will insert duplicate records. All i need is where to fit the Query Update in which it should check IF ITEM EXISTS in table name-> Update values , IF NOT EXISTS..Insert record!

Here is my code :

<form enctype="multipart/form-data" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

File to import:<br />

<input size='30' type='file' name='filename'>

<input type="submit" name="submit" value="Upload"></form>



<?php 
require_once('connect_db.php');

   //Upload File 
    if (isset($_POST['submit'])) { 
    if (is_uploaded_file($_FILES['filename']['tmp_name'])) { 


    //Import uploaded file to Database 
    $row = 1; 
    $handle = fopen($_FILES['filename']['tmp_name'], "r"); 


    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { 

              //Update Database Values 

        $import="insert into import_items (item_no,qty,actual_price,discount_price,difference_price,date) VALUES('".mysql_real_escape_string($data[0])."', '".mysql_real_escape_string($data[1])."', '".mysql_real_escape_string($data[2])."', '".mysql_real_escape_string($data[3])."', '".mysql_real_escape_string($data[4])."', '".$date = date('Y-m-d')."')"; 

        mysql_query($import) or die(mysql_error()); 

    } 



    fclose($handle); 
     }
} 
    ?>

Thank You

wroniasty · Accepted Answer · 2012-09-18 14:36:42Z

2

Check out the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax. Oh, and make sure you have a primary key in your table, it's almost always a good idea.

answered Sep 18, 2012 at 14:36

wroniasty

8,0822 gold badges34 silver badges24 bronze badges

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

Comments

Matt S · Accepted Answer · 2012-09-18 14:35:29Z

0

MySQL has a statement which will check for an existing row and only insert if it doesn't exist. Use

REPLACE INTO import_items...

instead of INSERT INTO. The syntax is otherwise identical.

This statement will check for duplicates by the primary key of the table. If the key doesn't exist, it'll insert a row. If the key exists, it will update the other fields in the row. It's equivalent to you doing a SELECT first, then branching to do an UPDATE or INSERT.

answered Sep 18, 2012 at 14:35

Matt S

15.5k6 gold badges60 silver badges79 bronze badges

3 Comments

Orbling Over a year ago

REPLACE usually overwrites via primary key, rather than inserting only when the primary key doesn't exist...

Alihamra Over a year ago

@Matt S , I've tried REPLACE INTO and I'm still having Duplicates :(

Matt S Over a year ago

@Alihamra, whichever solution you go with, make sure you have a primary key on the table to avoid duplicates.

Mike Brant · Accepted Answer · 2012-09-18 14:44:21Z

0

You need to share your table schema. Using REPLACE INTO or INSERT ... ON DUPLICATE KEY UPDATE will not work if you don't have proper keys across the columns for which you want to enforce no duplicates. The other thing that is easy to do (one you have de-duped the table), is place a unique key on the column(s) where you want to prevent duplicates.

answered Sep 18, 2012 at 14:44

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Collectives™ on Stack Overflow

Importing from CSV to MySQL using Update

3 Answers 3

Comments

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Related