SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CSV data and mysql

    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:

    Code:
    <?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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why not try the ON DUPLICATE KEY option of the INSERT statement?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you I will take a look at that


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •