Parsing CSV File to MySQL DB in PHP

Question

I have a some 350-lined CSV File with all sorts of vendors that fall into Clothes, Tools, Entertainment, etc.. categories. Using the following code I have been able to print out my CSV File.

<?php
    $fp = fopen('promo_catalog_expanded.csv', 'r'); 
    echo '<tr><td>'; 
    echo implode('</td><td>', fgetcsv($fp, 4096, ',')); 
    echo '</td></tr>'; 
    while(!feof($fp)) { 
        list($cat, $var, $name, $var2, $web, $var3, $phone,$var4, $kw,$var5, $desc) = fgetcsv($fp, 4096); 
        echo '<tr><td>'; 
        echo $cat. '</td><td>' . $name . '</td><td><a href="http://www.' . $web .'" target="_blank">' .$web.'</a></td><td>'.$phone.'</td><td>'.$kw.'</td><td>'.$desc.'</td>' ;
        echo '</td></tr>'; 
    } 

    fclose($file_handle); 
    show_source(__FILE__); 
?>

First thing you will probably notice is the extraneous vars within the list(). this is because of how the excel spreadsheet/csv file:

Category,,Company Name,,Website,,Phone,,Keywords,,Description  
,,,,,,,,,,  
Clothes,,4imprint,,4imprint.com,,877-466-7746,,"polos, jackets, coats, workwear, sweatshirts, hoodies, long sleeve, pullovers,  t-shirts, tees, tshirts,",,An embroidery and apparel company based in Wisconsin.
,,Apollo Embroidery,,apolloemb.com,,1-800-982-2146,,"hats, caps, headwear, bags, totes, backpacks, blankets, embroidery",,An embroidery sales company based in California.

One thing to note is that the last line starts with two commas as it is also listed within "Clothes" category.

My concern is that I am going about the CSV output wrong.

Should I be using a foreach loop instead of this list way?
Should I first get rid of any unnecessary blank columns?

Please advise any flaws you may find, improvements I can use so I can be ready to import this data to a MySQL DB.

Answer 1

Im not sure of the overall structure of your CSV - its hard to make rule assumptions based on two lines... but something like the following should work:

$fp = fopen('promo_catalog_expanded.csv', 'r');

// normalize the column names
$columns = array_change_key_case(fgetcsv($fp, 0, ','), CASE_LOWER);
$lastCategory = null;

while(false !== ($data = fgetcsv($fp, 0, ','))) {
   $data = array_combine($columns, $data); // make it an assoc array

   // test if category has a value - if it doesnt use the last category
   if(empty($data['category']) && null !== $lastCategory ){
      $data['category'] = $lastCategory;
   }

   // if we have started a new set of entries for a cat, we need to make it the $lastCategory
   if($lastCategory !== $dataCategory && null !== $data['category']) {
      $lastCategory = $data['category'];
   }

   // your sql to do the insert

}

Answer 2

This was helpful