比较MySQL和CSV并发现差异 [英] Compare MySQL with CSV and find differences

问题描述

我有一个 CSV 文件，其中包含名为 EAN 的1列和带有 EAN 列的 MySQL 表也是.

I have a CSV file with 1 column named EAN and a MySQL Table with a column named EAN too.

这是我要比较两列的操作:

This is what I want to do comparing both columns:

CSV ||| MySQL ||| STATUS
123     123       OK
321     321       OK
444               MISSING IN MySQL
        111       MISSING IN CSV

有什么想法如何使用 PHP 实现吗?

Any ideas how to realize with PHP?

推荐答案

一种方法:

(假设您已经知道如何打开文件并执行查询.)

(Assuming you already know how to open a file and execute a query.)

首先从CSV中读取行，并假定SQL中的数据丢失.

First read rows from your CSV and assume the data is missing in SQL.

while (($row = fgetcsv($file)) !== FALSE) {
    $num = $row[0];  // or whatever CSV column the value you want is in
    $result[$num] = ['csv' => $num, 'sql' => '', 'status' => 'MISSING IN SQL'];
}

然后从查询中获取行，并相应地填充从CSV创建的数组.

Then fetch rows from your query and fill the array you created from the CSV accordingly.

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $num = $row['EAN']; // or whatever your column is named
    if (isset($result[$num])) {
        // This has a value from the CSV, so update the array
        $result[$num]['sql'] = $num;
        $result[$num]['status'] = 'OK';
    } else {
        // This doesn't have a value from the CSV, so insert a new row
        $result[$num] = ['csv' => '', 'sql' => $num, 'status' => 'MISSING IN CSV'];
    }
}

您可以更改顺序，然后先处理查询结果.只要您对第二个数据源进行更新/插入逻辑，任何一种顺序都将起作用.

You could change the order of this and process the query results first. Either order will work, just as long as you do the update/insert logic with the second data source.

如果希望合并值按顺序排列，则可以 ksort($ result); ，然后根据需要输出 $ result .

You can ksort($result); if you want the merged values to be in order, then output $result however you need to.

这篇关于比较MySQL和CSV并发现差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！