比较CSV文件中1列中的值与1列中的MySQL值，并在PHP中删除

问题描述：

I'm new to mysql and php and found similar function at http://answers.yahoo.com/question/index?qid=20080717110807AAsXkGL:

However this code compare mysql vs mysql:

3 columns in first table t1: first, last, email. Same columns in t2.

DELETE FROM t1 WHERE t1.email IN (SELECT t2.email from t2)

However, I need a PHP code that will delete entire row from MySQL table in column names if the names value is present in CSV file in Column A (names).

Thanks in advance for the help! I've already spend good 4 hours looking online and can't find the solution.

我是mysql和php的新手，在 http://answers.yahoo.com/question/index?qid=20080717110807AAsXkGL ： p>

但是这段代码比较mysql vs mysql： p>

第一个表t1中的3列：first，last，email。 t2中的相同列。 p>

  DELETE FROM t1 WHERE t1.email IN（从t2选择t2.email）
  code>  pre> 
 
 但是，如果名称值出现在A列（名称）的CSV文件中，我需要一个PHP代码，它将删除列 names  code>中MySQL表的整行。 p> 
 \  n 提前感谢您的帮助！ 我已经花了4个小时在线查找，但找不到解决方案。 p> 
  div>

答

Here is full answer for those that are seeking:

$dbhost = "localhost";
$dbuser = "root";
$dbpass = "root";

$dbname = "yourdatabase";

$link = mysql_connect($dbhost, $dbuser, $dbpass);

$lines =file('active.csv');
foreach($lines as $line){
$na[]=trim($line);
}

$in=implode(',',$na);


$query = "DELETE FROM table1 WHERE t2.value IN ($in)";
$del=mysql_query($query,$link);

If you want to delete in Table 1 entire row if the value is not present in CSV file then change the query to:

    $query = "DELETE FROM table1 WHERE t2.value NOT IN ($in)";

IMPORTANT NOTE: the header in CSV file should NOT be present because otherwise this script will delete entire Table 1.

答

Make an array tmpArray of the result from SELECT t2.email from t2

and do something like this:

$in = implode(',', $tmpArray);

DELETE FROM t1 WHERE t1.email IN ($in)

比较CSV文件中1列中的值与1列中的MySQL值，并在PHP中删除

相关推荐