PHP MYSQL导入CSV，然后比较和删除冗余条目 [英] PHP MYSQL import CSV and then compare and remove redundant entries

问题描述

我遇到了一个特殊的问题。我有一个脚本，基本上使用 fgetcsv（）在PHP中导入CSV文件到数据库。完全没有问题，我可以更新旧的条目以及使用MySQL语法 ON DUPLICATE KEY UPDATE （我不是一个MySQL专家，因此

 <$ c $ 
 c> $ handle = fopen（$ file，r）; 
 fgetcsv（$ handle，1000，，）; //跳过第一行，因为它们是头文件
 while（（$ fileop = fgetcsv（$ handle，1000，，） false）//逐行读取到$ fileop 
 {
 //读取数组值到vars 
 $ item1 = $ fileop [0]; 
 $ item2 = $ fileop [1]; 
 $ key = $ fileop [2]; 
 //和一对夫妇
 
 //现在在MySQL表中插入/更新数据
 $ sql = mysql_query（INSERT INTO table（item1，item2，key）
 VALUES（'$ item1'，'$ item2'，'$ key'）
 ON DUPLICATE KEY UPDATE item1 ='$ item1'，item2 ='$ item2'） 
 
} 
  

我困惑的是，一些条目可能已从实际的CSV中删除（因为在密钥可能不再存在）。我想要做的是从MySQL表中删除不再存在于CSV中的条目。

含义如果 $ key 不在CSV中，也会删除数据库表中的那一行。我想我会在MySQL表上运行Insert / Update查询之前执行它。

我会感谢任何帮助。

解决方案

只要记住你的钥匙。

在你的while中的数组中保存每个 $ key ，最后运行一个查询， p>

  DELETE FROM tabel WHERE key NOT IN（listofcommaseparatedkeysgoeshere）
   
 
 
 
 
  $ arrayThatYouNeedToTest = array 
 $ handle = fopen（$ file，r）; 
 fgetcsv（$ handle，1000，，）; //跳过第一行，因为它们是头文件
 while（（$ fileop = fgetcsv（$ handle，1000，，） false）//逐行读取到$ fileop 
 {
 //读取数组值到vars 
 $ item1 = $ fileop [0]; 
 $ item2 = $ fileop [1]; 
 $ key = $ fileop [2]; 
 //和一对夫妇
 
 //现在在MySQL表中插入/更新数据
 $ 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）; 
  
 
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.
解决方案
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);


                        
这篇关于PHP MYSQL导入CSV，然后比较和删除冗余条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！