Compare MySQL with CSV and find differences

Question

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

Any ideas how to realize with PHP?

Possible duplicate of [Highlight the difference between two strings in PHP](https://stackoverflow.com/questions/321294/highlight-the-difference-between-two-strings-in-php) — snipsnipsnip, Dec 04 '18 at 00:15
Load into a temporary table and then do a [full outer join](https://stackoverflow.com/questions/7978663/mysql-full-join). `IF(CSV.id=MySQL.id, "OK", IF(CSV.id IS NULL, "Missing IN CVS", "Missing in MySQL")) AS status` — danblack, Dec 04 '18 at 00:19

score 1 · Accepted Answer · answered Dec 04 '18 at 01:01

One way to do it:

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

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'];
}

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.

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

Thanks for your Input, this was the right Medusa :) – Kerim Yagmurcu Dec 05 '18 at 08:48 — Kerim Yagmurcu, Dec 05 '18 at 08:48

Compare MySQL with CSV and find differences

1 Answers1

Linked