Vince,
I have made a couple of changes in the code below, none of this has been
tested, but I have done similar in the past.
As long as u don't have too mauch data to read in, this method gives you the
most flexibility.
If the CSVs are large, you might have to process line by line instead of
using arrays.
If doing line by line, have you considered sql "if not exists (select o_num
from order_header where o_num = '$o_num') insert into" etc. to prevent
duplicates?
Ben
> 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?
The key in the array is for example above is 1116, when you read in the CSV
file for the second line, the array vales for 1116 will just be overwritten
again.
They will not be duplicated.
The way I normally do this type of thing is to do this in your loop of
reading in the CSV variables...
$header= array();
$details= array();
$prev_o_num = 0;
Loop through CVS...
// you should probably do your manipulation on variables here before filling
the arrays.
$header[$o_num]['date'] = $date;
$header[$o_num]['name'] = $name;
$header[$o_num]['addr'] = $addr;
if ($prev_o_num == $o_num){$lineNo++;} //test to see if new o_num.
else{$lineNo = 1;}
$details[$o_num][$lineNo] ['item_num'] = $item_num;
$details[$o_num][$lineNo] ['quan'] = $quan;
$details[$o_num][$lineNo] ['desc'] = $desc;
End loop
You now should have two arrays with no duplicates.
The slightly more difficult part is now to loop through the arrays...
foreach ($header as $o_num => $value) {
extract($value, EXTR_OVERWRITE);
// etc you should now have your original variable name back.
// insert into db here
insert into order_header (o_num, date, name, addr)
values ('$o_num','$date','$name','$addr'),
foreach ($details[$o_num] as $line_no => $detailsvalues) {
extract($detailsvalues, EXTR_OVERWRITE);
// should have access to $line_no, and the variables within
insert into line_items (o_num, item_num, quan, desc, line_order)
values ('$o_num','$item_num','$quan','$desc','$line_no'),
}
}
Not tested, something for u to try.
>
> /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
