I am working on an internal tool for my team to retrieve data on products based on manufacturer SKU numbers. I tested an idea on my website where you upload a CSV file with a list of SKUs into the MYSQL database using LOAD DATA INFILE through PHP, then output the results into a table using SELECT/JOIN.
The issue with this would be if multiple users upload the CSV at the same time, their results would be combined.
Is there a way to query between a database and CSV file without uploading to the database or a possible way to differentiate each user so their results aren't combined?
Basic elements for the tables would include
CSV File (Currently being imported into a MYSQL table)
| Manufacturer | SKU |
|---|---|
| MFG_A | 123 |
| MFG_A | 456 |
| MFG_A | 789 |
Database File
| Manufacturer | SKU | Description | AVG_Price | UOM |
|---|---|---|---|---|
| MFG_A | 123 | T-Shirt | 1.00 | EA |
| MFG_A | 456 | Sweater | 5.00 | EA |
| MFG_A | 789 | Pants | 3.00 | EA |
| MFG_A | 999 | Shorts | 2.00 | EA |
| MFG_A | 888 | Shoes | 8.00 | PR |
RESULTS
SELECT a.*, b.Description, b.AVG_Price, b.UOM
FROM CSV_Table a
LEFT JOIN Database_Table b
on a.Manufacturer = b.Manufacturer
and a.SKU = b.SKU;
| Manufacturer | SKU | Description | AVG_Price | UOM |
|---|---|---|---|---|
| MFG_A | 123 | T-Shirt | 1.00 | EA |
| MFG_A | 456 | Sweater | 5.00 | EA |
| MFG_A | 789 | Pants | 3.00 | EA |