[SOLVED] Uploading a CSV file via PHP and inserting data into multiple tables

[jaxdevil]

I have a script for uploading csv files into my MySQL Database tables. The problem is it loads one file into one table, what I need it to do is to mark the columns based on the column name and upload each column to the right table. The 4 tables and the columns they contained I listed below:

 

products

"categories","manufacturer","model_number","name","list_price"

 

products_description

"manufacturer","model_number","description"

 

products_media

"manufacturer","model_number","image_file","pdf_specs","pdf_one","pdf_two","pdf_three","pdf_four"

 

products_ship

"manufacturer","model_number","freight_class","ship_from","weight","height","width","depth"

 

And here is a copy of the contents of a one line csv file that would be uploaded, just as an example, in normal effect it will be hundreds of lines, but this is just a single line to show what I mean:

 

categories,manufacturer,model_number,name,description,image_file,pdf_specs,pdf_one,pdf_two,pdf_three,pdf_four,freight_class,ship_from,weight,height,width,depth,list_price
Cooking|Toasters_and_Toaster_Ovens,APW Wyott,AT-Express,AT Express Radiant Conveyor Toasters,ELECTRIC : 120V 1725W 14.4 Amps 60hz DIMENSIONS : 13 1/4inH x 15 3/64inW x 17 3/16inD LBS : 35,AT-Express.jpg,AT-Express.pdf,,,,,85,"75226 [Dallas, TX]",35,13.25,15.05,17.19,1144

 

So the base code I use for uploading one file to one database is below, I need to make it work for uploading data from one csv file into separate tables based on the column name.

 

<?

  $fcontents = file ('./products.csv'); 

  for($i=0; $i<sizeof($fcontents); $i++) { 
      $line = trim($fcontents[$i]); 
      $arr = explode(",", $line); 
     
      $sql = "insert into `products` values ('". 
                  implode("','", $arr) ."')"; 
      mysql_query($sql);
      echo $sql ."<br>\n";
      if(mysql_error()) {
         echo mysql_error() ."<br>\n";
      } 
}
?>

 

Any ideas?

 

Thanks in advance,

SK

[jaxdevil]

Ok, I have somewhat of an idea, but I am lost on the last piece of the puzzle. The below code will target the specific columns in the database insert query but not the $values variable, I need to split the array of columns from the csv file into the correct columns from the csv file for each sql insert query in the below function. Any ideas?

 

$table_products = "`categories` , `manufacturer`, `model_number`, `list_price` ";
$table_products_description = "`manufacturer` , `model_number`, `description` ";
$table_products_media = "`manufacturer`, `model_number`, `image_file`, `pdf_specs`, `pdf_one`, `pdf_two`, `pdf_three`, `pdf_four` ";
$table_products_ship = "`manufacturer`, `model_number`, `freight_class`, `ship_from`, `weight`, `height`, `width`, `depth` ";
$handle = fopen("product_update.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
foreach( $data as $v ) {
$insertValues="'".addslashes(trim($v))."'";
}
$values=inplode(',',$insertValues);
$sql = "INSERT INTO `products` ( $table_products ) VALUES ( $values )";
mysql_query($sql) or die('SQL ERROR:'.mysql_error());
$sql = "INSERT INTO `products_description` ( $table_products_description ) VALUES ( $values )";
mysql_query($sql) or die('SQL ERROR:'.mysql_error());
$sql = "INSERT INTO `products_media` ( $table_products_media ) VALUES ( $values )";
mysql_query($sql) or die('SQL ERROR:'.mysql_error());
$sql = "INSERT INTO `products_ship` ( $table_products_ship ) VALUES ( $values )";
mysql_query($sql) or die('SQL ERROR:'.mysql_error());
}
fclose($handle);