On Wed, 22 Jun 2005, Ben Duffy wrote: } I would read in the CSV file, and populate two arrays, using the } ordernumber as a key in the header array, loop through the header array } containg $o_num, $date, $name, $addr, to do the db inserts. Ok, I think I get this, though I am confused about doing the inserts - I don't want duplicate rows to be inserted into the header table. Eg: 1116,6/22/2005,Jim Smith,44 Here St,19191980,1,CD 1116,6/22/2005,Jim Smith,44 Here St,77736222,1,Tape The two rows from the CSV above produce the same row data for the header table: '1116','6/22/2005','Jim Smith','44 Here St' '1116','6/22/2005','Jim Smith','44 Here St' How do I make sure that only one row is inserted? How do I detect the same $o_num when building the header array? /vjl/ } The details table would be an array of arrays, key would be ordernumber } again, then the sub array would be the line number. You can set to 1, then } increment until you detect a new ordernumber The contents of the detail sub } array contains $item_num, $quan, $desc. Loop through this this array to } produce your details table inserts. } } Ben. } } } } "Vince LaMonica" <vjl@xxxxxxxxxxxxx> wrote in message } news:Pine.OSX.4.61.0506220322220.8121@xxxxxxxxxxxxxxxxxxx } > On Wed, 22 Jun 2005, Sergey wrote: } > } > } 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. } > } > Actually, I can't, as the CSV contains fields for two different tables. I } > may have explained it better here: } > } > I have a CVS file that has order header *and* line item info on each line, } > like: } > } > 1110,6/20/2005,Jan Doe,123 Main St,99990000,1,Book } > 1116,6/22/2005,Jim Smith,44 Here St,19191980,1,CD } > 1116,6/22/2005,Jim Smith,44 Here St,77736222,1,Tape } > } > The above is actually two orders - one with one line item, and the 2nd } > with two line items. I need to insert data from those lines into two } > tables: } > } > insert into order_header (o_num, date, name, addr) } > values ('1110','6/20/2005','Jan Doe','123 Main St'), } > ('1116','6/22/2005','Jim Smith','44 Here St'); } > } > insert into line_items (o_num, item_num, quan, desc, line_order) } > values ('1110','99990000','1','Book','1'), } > ('1116','19191980','1','CD','1'), } > ('1116','77736222','1','Tape','2'); } > } > Note the line_order field - it needs to increment per order for each line } > item added to the line_items table. To complicate matters a bit, I'm } > actually massaging the data before inserting [eg: splitting the name field } > from the CSV into two fields for the mysql db, formatting the date field } > for mysql, etc]. } > } > I'm currently doing this process via a form where a user uploads the CVS } > file [created with Excel, complete with the first row being made up the } > Excel table's header]. } > } > I currently do something like this: } > } > $fp = fopen("/tmp/"."$txt_file", "r"); } > while ($line = fgets($fp,1024)) } > { } > $i++ } > if ($i > 1) { // skip excel header row } > list ($o_num, $date, $name, $addr, $item_num, $quan, $desc) = } csv_explode($line); } > // i can now print the vars, but i get duplicate header records when } > // there are multiple line items for a particular order. also, i } > // need to generate the line_order field for insertion into the } > // line_items table } > } } > } } > } > If I try and do any processing up where my comments are, well, the } > comments tell you what happen. I know I am reading this file line by line, } > so I can't compare order numbers [o_num] to group multiple line item } > orders together. So how do I go about doing that? Read the entire CSV into } > an array? How can that help? Any tips would be most appreciated! } > } > Thanks! } > } > /vjl/ } > } > p/s - FYI, cvs_explode() is: } > } > function csv_explode($str, $delim = ',', $qual = "\"") } > { } > $len = strlen($str); } > $inside = false; } > $word = ''; } > for ($i = 0; $i < $len; ++$i) { } > if ($str[$i]==$delim && !$inside) { } > $out[] = $word; } > $word = ''; } > } else if ($inside && $str[$i]==$qual && ($i<$len } && $str[$i+1]==$qual)) { } > $word .= $qual; } > ++$i; } > } else if ($str[$i] == $qual) { } > $inside = !$inside; } > } else { } > $word .= $str[$i]; } > } } > } } > $out[] = $word; } > return $out; } > } } } -- Vince J. LaMonica Knowledge is knowing a street is one way. vjl@xxxxxxxxxxxxx <*> Wisdom is still looking in both directions. When there's nothing else to read: http://w3log.vjl.org/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
