Efficient way to determine which values ​​in the CSV are NOT in the database?

Question

The supplier submits to us a CSV file of their products. A single column in a file (for example, column 3) is the style number. This file contains thousands of records.

We have a product database table with a column called manufacturer_num, which is the vendor style number.

I need to find which of the supplier’s products we currently do not have.

I know that I can loop every line in the CSV file and extract the style number and check if it is in our database. But then I call the database for each row. This will be thousands of database calls. I think this is inefficient.

I could also create a list of style numbers (either as a string or an array) to make one DB call. Something like: WHERE manufactuer_num IN(...) But will PHP not run out of memory if the list is too large? And actually it will give me those that we have, and not those that we do not have.

What is an effective way to do this?

Answer 1

Bulk load CSV into temporary table, do LEFT JOIN , then get records where RHS connections are NULL .