PHP MYSQL import CSV and then compare and remove r

问题:

I am stuck with a peculiar issue here. I have a script that basically imports a CSV file into a database using fgetcsv() in php. There is no problem in doing this at all and I am able to update old entries as well using MySQL syntax ON DUPLICATE KEY UPDATE (I am in no way a MySQL expert, hence me asking here).

Here is that part of the code:

$handle = fopen($file,"r");
fgetcsv($handle, 1000, ",");//skip first row since they are headers
while(($fileop = fgetcsv($handle, 1000, ",")) !== false) //read line by line into $fileop
{
  //read array values into vars
  $item1 = $fileop[0];
  $item2 = $fileop[1];
  $key = $fileop[2];
  // and a couple more

  // now INSERT / UPDATE data in MySQL table
  $sql = mysql_query("INSERT INTO table (item1,item2,key) 
    VALUES ('$item1','$item2','$key') 
    ON DUPLICATE KEY UPDATE item1='$item1',item2='$item2'");

}

This all works fine. What I am stuck with is the fact that some entries may have been removed from the actual CSV (as in the key may no longer be existant). What I would like to do is remove the entries from the MySQL table that are no longer present in the CSV.

Meaning if $key is gone from CSV also remove that row in the database table. I suppose I would do it before I run the Insert / Update query on the MySQL table?

I would appreciate any help guys.

回答1:

Just keep an account of your keys.

Save every $key in an array in your while, and in the end run a query that says

DELETE FROM tabel WHERE key NOT IN (listofcommaseparatedkeysgoeshere)

---

$arrayThatYouNeedToTest = array();
$handle = fopen($file,"r");
fgetcsv($handle, 1000, ",");//skip first row since they are headers
while(($fileop = fgetcsv($handle, 1000, ",")) !== false) //read line by line into $fileop
{
  //read array values into vars
  $item1 = $fileop[0];
  $item2 = $fileop[1];
  $key = $fileop[2];
  // and a couple more

  // now INSERT / UPDATE data in MySQL table
  $sql = mysql_query("INSERT INTO table (item1,item2,key) 
    VALUES ('$item1','$item2','$key') 
    ON DUPLICATE KEY UPDATE item1='$item1',item2='$item2'");

   $arrayThatYouNeedToTest[] = $key;    

}

$stringThatYouNeedToInspect = implode(",",$arrayThatYouNeedToTest);
$queryYouREALLYneedToCheckFirst = "DELETE FROM tabel WHERE key NOT IN  (".$stringThatYouNeedToInspect.")";

//$result = mysql_query($queryYouREALLYneedToCheckFirst);

回答2:

I do something very similar to this with an affiliate website - having just under 500,000 products.

In your database, simply add another column named "update_flag" or something similar. Set the default to be 0. As you add items from the CSV file, set the update_flag to be "1". In your 'on duplicate statement', set the filed to be "2". I also went and added 2 other fields: "date_added" and "date_updated".

After your import is complete, you can count the old items (to be deleted), newly added items, and those that have been updated. You can then simple delete from table where update_flag = 0

I hope this helps.