CSV upload, once uploaded to a temp folder overwrite the existing database

[eluser]davidMC1982[/eluser]
Here's some code I've used to do something similar. I'm using Datamaper ORM throughout my app but in this case, it's only specifically used to clear my table data. After that I used the standard CI DB class.

I'm also getting my CSV file from an external data source. You'd have to replace this to open the csv file.

My code builds one giant INSERT statement. In my case, if the INSERT fails, it has no bearing on the rest of my app. I wouldn't attempt to do this on a table that is critical to the functioning of your site. Instead, I would do the INSERT on a temp table first.

Anyway, this should give you an idea of the steps you might need to take.

Code:

function refresh_rates() {
  
  //Clear our table ready for fresh data
  $scraped_product = new Scraped_product();
  $scraped_product->truncate();
  
  //Get the data from some web resource in csv format
  $data = file_get_contents("https://somedatasource.com");
  
  //Explode the csv into an array of lines
  $lines = explode("\n", $data);
  
  //Get rid of the first line
  $header = str_getcsv(array_shift($lines));
  
  //SQL for bulk insert
  $sql = "INSERT INTO scraped_products VALUES ";
  
  //For each of the models in the csv
  foreach ($lines as $row) {
   //Turn the csv into an array
   $row = str_getcsv($row);
  
   if ($row[0]) {
    //Give the array the named keys extracted from the header
    $product = array_combine($header, $row);
    
    //Set the price for our product
    $product['price'] = filter_var($product['price'], FILTER_SANITIZE_NUMBER_FLOAT, array('flags' => FILTER_FLAG_ALLOW_FRACTION));
    
    $product['comp_price'] = filter_var($product['comp_price'], FILTER_SANITIZE_NUMBER_FLOAT, array('flags' => FILTER_FLAG_ALLOW_FRACTION));
    
    //Add our product/price into the sql insert statement
    $sql = $sql . "('','" . $product['manufacturer'] . "','" . $product['model'] . "','" . $product['price'] . "','" . $product['link'] . "','" . $product['comp_price'] . "'),";
   }
  }
  
  //Trim the trailing , and add the ;
  $sql = rtrim($sql, ',');
  $sql = $sql . ";";
  
  //Load codeigniters db class
  $this->load->database();
  
  //Execute the query, if it succeeds, go back to the rates page, otherwise give an error message
  if ($this->db->query($sql)) {
  // redirect('admin/rates/list_rates');
  } else {
   echo "Refreshing Rates Failed!";
  }
}