You can insert file data in DB first, using LOAD DATA INTO FILE, after it'll be easy to manipulate this DB table with a help of php-script. "Vince LaMonica" <vjl@xxxxxxxxxxxxx> ???????/???????? ? ???????? ?????????: news:Pine.OSX.4.61.0506211303140.5885@xxxxxxxxxxxxxxxxxxx >I sent this note off to the php-db list last night, but the more I thought >about it, the more I figured this was a general looping question rather >than a mysql-specific one. > > I am attempting to take a CSV file that has order header and line item > data on each line and split it into mulitple insert queries into a mysql > db. Each line in the CSV file may only be one order, though it is common > for there to be more than one item ordered, so I need to be able to loop > to insert the line item values into the proper line item table. I also > need to build a counter for a value that is not provided in the CVS for > each line item. > > Here is a sample of a few lines of the CSV: > > 1110,6/20/2005,Jan Doe,1234 Spring > St.,Anytown,PA,17033,0618456990,22.50,1,The Sample Book > 1114,6/22/2005,Jon Smith,888 Main St.,Big > City,CA,92648,0444409444,19.95,1,Coloring Book > 1114,6/22/2005,Jon Smith,888 Main St.,Big > City,CA,92648,9834119933,4.40,1,Picture Book > 1114,6/22/2005,Jon Smith,888 Main St.,Big > City,CA,92648,9482222922,59.99,4,Coffee Book > > In the above file, the last 4 fields [item_num, cost, quantity, title] > belong in a line_items table. The first number, the order_number, also > goes into the line_items table, as well as the order_header table. The > contact info for each customer also goes into the order_header table. I do > not want duplicate entries in the order_header table, so I can't just to a > simple loop through each line in the text file and do an insert. I need to > be able to group an order by the order_number [the 1st field] and insert > the correct number of rows in both tables. I also need to create a counter > per order showing which line item number each item is. Eg: the Coloring > Book would be assigned a 1, the Picture book a 2, and the Coffee Book a 3 > for order #1114. The Sample Book in order #1110 would be given a 1, since > it is the first [and only] item in that order. > > I have been successful in assigning each value to a varable and looping > through the file via: > > while ($line = fgets($fp,1024)) > { > $i++; > if ($i > 1) { // using 1 because CSV includes a header row > list($order_number, ...) = csv_explode($line); > > [i am using an Excel generated CSV with double quotes around each value > and so i have a csv_explode function setup to properly extract each value; > also the real CSV has about 2 dozen fields - i just cut it down to its > basics for the example here] > > Doing 2 inserts here and closing the loop is obviously not the answer, > since I get duplicate header rows, and I haven't built a counter for the > line_item's table's line counter field. The primary key in the line item > table is a combo of the order_number and the line_counter. Each fresh > order_number needs to reset the line_counter to 1, until all line items > for that order are inserted. > > I am having difficulty figuring out how to loop through the CSV to do the > inserts and create a line item counter. Any tips? > > TIA, > > /vjl/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
