Link to home
Start Free TrialLog in
Avatar of wilson1000
wilson1000

asked on

Replacing MySQL database entries - LOAD DATA INFILE

Hello,

Thanks to experts, I have a functioning LOAD DATA INFILE application on my site however, I need to replace each row in the database with updated data on a regular basis using updated information.

Is there a way to ignore duplicate entries in a MySQL database table and continue with bulk data insertion or can I just replace the whole database table with an updated, pre-exported, tab delimited text file?

Background:

I keep my products on my PC (Sage) that I update as part of my business. I then export to Microsoft Excel and export it further to a tab-delimited text file for upload to my database on line.

The current working LOAD DATA INFILE code is included below, if there is any more i can give you to help solve this issue then please let me know.

Many thanks!!
if (isset($_FILES['dataFile_items'])) {
  if ($_FILES['dataFile_items']['size'] < 2000000) {
    if (is_uploaded_file($_FILES['dataFile_items']['tmp_name'])) {
      $sql = "LOAD DATA INFILE '".mysql_real_escape_string($_FILES['dataFile_items']['tmp_name'])."' INTO TABLE `items` FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\r\\n'";
      mysql_query($sql) or die(mysql_error());
    } else {
      $error = 'Upload failed.';
    }
  } else {
    $error = 'Upload exceeded size limit.';
  }
} else {
  $error = 'No file was uploaded.';
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Bernard Savonet
Bernard Savonet
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brycen
brycen

Good background for the question is the size of your data: 1000's of products or millions?

But almost either way, the easy solution is to replace the table.  Doing selective updates is harder. You either need to read the database and compare.  Or use an "update or insert" operation, which updates unless the row does not exist.

A stored procedure can help here.  With MySQL >= 4.1.0 you can insert ... ON DUPLICATE KEY update.

With MySQL >= 4.1.0 you can insert ... ON DUPLICATE KEY update.  See http://dev.mysql.com/doc/refman/5.0/en/insert.html

Or you can create a stored procedure, something like:

create procedure user_insert_or_update_scores (
IN uname_in varchar(20),
IN score_in varchar(20),
)
BEGIN
If exists (select 1 from scores where uname = uname_in) then
update scores set score=score_in where uname=uname_in;
else
insert into scores values(score_in,uname_in);
end if;
END
The big downside of both "replace or update" schemes is that a product you discontinue will stick in the web database.  That's bad.  In this case it sounds like you should delete the table each time, and recreate it.
Avatar of wilson1000

ASKER

Thank you, would you kindly show me the correct syntax please?
Please forget that last post...
I do have an administration section where I can create, delete, and insert products however I would prefer not to update my products twice.

In light of this, deleting products isn't a problem.

Would I use the following syntax?
$sql = "LOAD DATA INFILE '".mysql_real_escape_string($_FILES['dataFile_items']['tmp_name'])."' REPLACE INTO TABLE `items` FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\r\\n'";

Open in new window

Yes, that would be the syntax.
Thx for the points.
Be sure your data is really screened up before the updates... taking a backup before update might be a lifesaver...