Page 1 of 1

[php]transferring data from text file to Mysql Table

Posted: Thu Jul 09, 2009 2:47 am
by cooweck
I've a example text file below. I need to pass this data to mysql.
and can anyone help with this.
I figure out the way of connecting database,
and the thing I am suffering with is transferring below data to mysql
ignore dots

Phone......MCO Accessory.........VPS Bluetooth.........VPC Bluetooth (FACTORS)
White..... .T950....................off.....................off (CHOICES)
White......C300....................on.....................off (CHOICES)
White......T950....................on.....................off (CHOICES)
White......T950....................off.....................on (CHOICES)
White......C300....................off.....................off (CHOICES)
White..... .C300....................on.....................on (CHOICES)
White..... .C300....................off.....................on (CHOICES)
White..... .T950....................on......................on (CHOICES)

Re: transferring data from text file to Mysql Table

Posted: Thu Jul 09, 2009 3:01 am
by SvanteH

Code: Select all

<?php
 
  $data = "Phone MCO Accessory VPS Bluetooth VPC Bluetooth (FACTORS)
White..... T950....................off.....................off (CHOICES)
White..... C300....................on.....................off (CHOICES)
White..... T950....................on.....................off (CHOICES)
White..... T950....................off.....................on (CHOICES)
White..... C300....................off.....................off (CHOICES)
White..... C300....................on.....................on (CHOICES)
White..... C300....................off.....................on (CHOICES)
White..... T950....................on......................on (CHOICES)
";
  
  $arrData = explode("\r\n", $data);
  
  foreach($arrData as $row)
  {
    $arrRowData = explode(".", $row);
    
    echo $arrRowData[0]; //Color
    echo $arrRowData[5]; //Name
    echo $arrRowData[25]; //on/off
    echo $arrRowData[46]; //on/off (choices)
    echo "<br>";
  }
?>

Re: transferring data from text file to Mysql Table

Posted: Thu Jul 09, 2009 3:12 am
by cooweck
thanks for reply.
actually factors are arbitrary so do choices,
so I don't know how many factors and choices there are.
there are plenty of data I need to handle with
and every data has different number of factor, and choice and also names
this data is just an example.

Re: [php]transferring data from text file to Mysql Table

Posted: Thu Jul 09, 2009 3:43 am
by onion2k
The first thing to do is design a database structure that will work with all the options. By the sounds of it, if there are lots of different options, you'll be best off with 2 tables. One will hold the basic product data with the fields that are common to every product (name, sku, color, etc), and a second that holds key/value data for each product. Eg

Code: Select all

Products Table
id | product  | sku | color | price
1  | Product1| 123 | White | 200.00
2  | Product2| 124 | White | 250.00
3  | Product3| 125 | White | 300.00
 
Product Data Table
id | key           | value
1  | MCO Accessory | T950
1  | VPS Bluetooth | Off
1  | VPC Bluetooth | Off
2  | MCO Accessory | T950
2  | VPS Bluetooth | On
3  | MCO Accessory | T950
3  | VPS Bluetooth | On
3  | VPC Bluetooth | Off
 
Once that's in place you'll need to write a script that takes each line from the data file, explodes it into individual fields, and then inserts the product into the products table, and rows for each data field into the data table.