|
|
Posted by Vince LaMonica on 09/28/28 11:19
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@cullasaja.com> wrote in message
} news:Pine.OSX.4.61.0506220322220.8121@crichton.vjl.org...
} > 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@cullasaja.com <*> Wisdom is still looking in both directions.
When there's nothing else to read: http://w3log.vjl.org/
Navigation:
[Reply to this message]
|