CSV data and mysql

PHP
1

I have a script that inserts csv data into my mysql database. I need to update a database table with csv data that i get daily from a supplier regarding stock levels.

I have a table called products with 2 columns, modelNo and quantity. What I want to do is use the daily csv file i get to match the model numbers and update the quantity column accordingly. However, the csv file data is not in a fixed order so I need to loop through my table looking for the model number, matching the model number to that on the csv file and then updating the quantity value taken from the csv file.

here’s my csv upload script:

<?php

//open the file
$handle = fopen("quantity_update.csv", "r");

//get the data from each line of the file
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    //get the data from that row
    $column1 = $data[0];
    $column2 = $data[1];

    //put it into the database
   $query = "INSERT INTO products (prodID, prodName, prodModel) VALUES (NULL, '$column1', '$column2')";
   mysql_query($query) or die('Error, insert query failed');
}

//close the file
fclose($handle);

//reset to use again
$comma_seperated = "";

echo "The file has been uploaded";

?>

Any ideas on where to start this? Should I just run a query that runs through my mysql data and then opens the csv checks to see if the model number exists, updates the quantity value and then closes the file. Repeating for every row. Or is there a better way to do it?

Many thanks

2

why not try the ON DUPLICATE KEY option of the INSERT statement?

3

Thank you I will take a look at that