Hello, Have u check the @new_orders used in the foreach where u execute The insert
Don't u have a primary key causing an error for duplicate entry ??? It's seems that u've got a db design problem too ... Regards, Didier Degey System & Network Administrator TI Automotive Belgium http://www.tiautomotive.com http://www.entreprises-wallonnes.com/tiautomotive > -----Original Message----- > From: Morrison, Trevor (Trevor) [mailto:[EMAIL PROTECTED] > Sent: samedi 16 ao�t 2003 17:50 > To: [EMAIL PROTECTED] > Subject: Double entries > > > Hi, > > I am running the latest MySql on a Windows 2000 machine. I > also use the > control center gui to do all of my work. I have a Perl program that > parses online order data and then using Perl's DBI, I write > this data to the > database. Now, It all works fine except that each order and > that orders' items > are written twice to the database. I have looked over my > code but only see > one insert statement for the order information another for > the ordered items. I guess I > need a fresh set of eyes to see where my error is! > > I have included the code below with the database connection > information > highlighted in red: > > > TIA > > Trevor > > #!/usr/local/bin/perl > > #============ > # Main script > #------------ > use strict; > use MivaOrder; > use Data::Dumper; > > > #Use Perl's Database Interface (DBI) with the NySQL module to > connect the > Maverick database > use DBI; > my %attr = (PrintError => 1, RaiseError => 1); > my $dbname='maverick'; > my $dbuser='root'; > my $dbpass=''; > my > $dbh=DBI->connect('DBI:mysql:database=maverick;host=localhost; > port=3306',"$d > buser","$dbpass", \%attr) || die "Unable to connect to > database maverick on > localhost: $DBI::errstr\n"; > > my @new_orders; > #my $email = qw(c:\\maverick\\trevor_trial2.txt); > open(ORDER,$ARGV[0]) or die "Error opening \"$ARGV[0]\": $!\n"; > > my $order = undef; > while(<ORDER>) { > > # Keep appending to the order string until we reach the end > of the order. > unless(/^Site rating\.\.\: \"/) { > $order .= $_ and next; > } > > # We are done with the order. > > my $obj = MivaOrder->new($order); > #print Dumper(\$obj); > push @new_orders,$obj; > $order = undef; > } > > my $sth1 =$dbh->prepare("INSERT INTO > miva_retail_orders(wholesale_company,order_number,date,credit_ > card_type,ship > ped,shiptype,bill_name,bill_company,bill_addr1,bill_addr2,bill > _city,bill_sta > te,bill_zip,bill_country,ship_name,ship_addr1,ship_addr2,ship_ > city,ship_stat > e,ship_zip,ship_country,email,bill_phone_number,bill_phone_num > ber2,ship_phon > e_number,ship_phone_number2,sales_tax,shipping_amount,coupon_t > ype,coupon_amo > unt,total) VALUES > (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); > foreach my $new_order(@new_orders) { > > # Enter the database and write the data > $sth1->execute($new_order->wholesale_company(),$new_order->ord > er_number(),$n > ew_order->date(),$new_order->credit_card_type(),$new_order->sh > ipped(),$new_o > rder->ship_type(),$new_order->bill_name(),$new_order->bill_com > pany_name(),$n > ew_order->bill_to_street(),$new_order->bill_to_street2(),$new_ > order->bill_to > _city(),$new_order->bill_to_state(),$new_order->bill_to_zip(), > $new_order->bi > ll_to_country(),$new_order->ship_name(),$new_order->ship_to_st > reet(),$new_or > der->ship_to_street2(),$new_order->ship_to_city(),$new_order-> > ship_to_state( > ),$new_order->ship_to_zip(),$new_order->ship_to_country(),$new > _order->email_ > address(),$new_order->bill_phone_number(),$new_order->bill_pho > ne_number2(),$ > new_order->ship_phone_number(),$new_order->ship_phone_number2( > ),$new_order-> > sales_tax(),$new_order->shipping_amount(),$new_order->coupon_t > ype(),$new_ord > er->coupon_amount(),$new_order->total()); > > } > > $sth1->finish; > $dbh->disconnect; > > #================== > # Package MivaOrder > #------------------ > package MivaOrder; > use strict; > use Carp; > use English; > use vars qw($AUTOLOAD); > > > my %fields = ( > wholesale_company =>undef, > order_number =>undef, > date =>undef, > > bill_name =>undef, > bill_email_address =>undef, > bill_phone_number =>undef, > bill_phone_number2 =>undef, > bill_business_name =>undef, > bill_to_street =>undef, > bill_to_city =>undef, > bill_to_state =>undef, > bill_to_zip =>undef, > bill_to_country =>undef, > > ship_name =>undef, > ship_email_address =>undef, > ship_phone_number =>undef, > ship_phone_number2 =>undef, > ship_business_name =>undef, > ship_to_street =>undef, > ship_to_city =>undef, > ship_to_state =>undef, > ship_to_zip =>undef, > ship_to_country =>undef, > > code =>undef, > name =>undef, > quantity =>undef, > price =>undef, > > shipping_method =>undef, > shipping_amount =>undef, > > sales_tax =>undef, > total =>undef, > > notes =>undef, > coupon_type =>undef, > coupon_amount =>undef, > > credit_card_type =>undef, > COD > =>undef > ); > > sub AUTOLOAD { > my ($self,$value) = @_; > $AUTOLOAD =~ /.*::(\w+)/; > $self->{$1} = $value if($value); > return $self->{$1}; > } > > > sub new { > my ($that,$order) = @_; > croak "Order file is undefined" if(!$order); > my $class = ref($that) || $that; > my $self = {}; > $self->{$_} = $fields{$_} foreach(keys %fields); > bless $self, $class; > $self->init($order); > return $self; > } > > sub init { > open(TREVOR, "> c:\\maverick\\HITHERE.txt") or die "Error opening > HITHERE.txt $!\n"; > my ($self,$order) = @_; > my @lines = split(/\n/,$order); > my $in_address = undef; > my $in_order = undef; > > foreach my $line(@lines) { > > #our $n += 1 if $line =~/Order Number :/; > #print $n; > #next unless($line =~ /\w+/); > chomp ($line); > > if($line=~ /([EMAIL PROTECTED])\s+\"/i) { > $self->{wholesale_company} = $1; > #print $1 . "\n"; > } > > elsif($line =~ /Order\s+Number\s+:\s+(.*)$/i) { > $self->{order_number} = $1; > #print $1 . "\n"; > } > > elsif ($line =~ > /Placed\s+:\s+([0-9]{2})\/([0-9]{2})\/([0-9]{4})\s+([0-9]{2}:[ > 0-9]{2}:[0-9]{ > 2})/i) { > > my $mon=$1; > my $day=$2; > my $year = $3; > my $time=$4; > my $date = $year . "-" > . $mon . "-" . $day . " " . $time ; > $self->{date} = $date; > #print $date . "\n"; > } > > elsif (($line =~ /Ship To:/i) || defined($in_address)) { > $in_address = 1 and next if(!defined($in_address)); > my $regex = qr#(.{0,41})(.{0,41})#; > if (!defined($self->{bill_name})) > > ($self->{bill_name},$self->{ship_name}) > = ($line =~ /$regex/); > } elsif (!defined($self->{ship_email_address})) { > > ($self->{ship_email_address},$self->{bill_email_address}) > = ($line =~ /$regex/); > > #print $1 . "\n"; > > #print $2 . "\n"; > } elsif (!defined($self->{ship_phone_number})) { > ($self->{ship_phone_number},$self->{bill_phone_number}) > = ($line =~ /$regex/); > > #print $1 . "\n"; > > #print $2 . "\n"; > } elsif > (!defined($self->{ship_phone_number2})) { > > ($self->{ship_phone_number2},$self->{bill_phone_number2})= ($line =~ > /$regex/); > # $self->{ship_phone_number2}= ($line =~ > /^\(?[0-9]{3}\)?-?|s?[0-9]{3}-?|s?[0-9]$/); > > #$self->{bill_phone_number2} = ($line =~ > /\(?[0-9]{3}\)?-?|s?[0-9]{3}-?|s?[0-9]$/); > > #print $1 . "\n"; > > #print $2 . "\n"; > } elsif > (!defined($self->{bill_business_name}) > ||!defined($self->{ship_business_name})) { > > ($self->{ship_business_name},$self->{bill_business_name}) > = ($line =~ /$regex/); > > #print $1 . "\n"; > > #print $2 . "\n"; > } elsif (!defined($self->{bill_to_street}) > ||!defined($self->{ship_to_street})) > > ($self->{ship_to_street},$self->{bill_to_street}) > = ($line =~ /$regex/); > > #print $1 . "\n"; > > #print $2 . "\n"; > } elsif (!defined($self->{ship_to_city}) > ||!defined($self->{bill_to_city})){ > my ($ship_to,$bill_to) > = ($line =~ /$regex/); > > ($self->{ship_to_zip},$self->{ship_to_state},$self->{ship_to_city} ) > # = split(/\s+/,$ship_to); > > # = ($ship_to =~ > /^(.*)\s+([A-Za-z]{2})\s+([0-9]{5}(-[0-9]{4})?)/);#^(\w+\s+?\w > +?\s+?\w+?\s+\w+)\s(\w+)\s(\d{5}-?(\d{4})?)/); > #^(\w+\s?\w*.?\s?\w+)\s(\w+)\s(\d > {5}(\W)?(\d{5})?)/); > > = map { scalar reverse } split ' ', ( > reverse $ship_to), 3; > > print TREVOR $self->{order_number}. "\n"; > > print TREVOR $1 . "\n"; > > print TREVOR $2 . "\n"; > > print TREVOR $3 . "\n"; > > > > ($self->{bill_to_zip},$self->{bill_to_state},$self->{bill_to_city} ) > #= split(/\s+/,$bill_to); > > #= ($line =~ > /\s{2,}(\w+.?\s?\w*.?\s?\w+.?)\s?(\w{2})\s?([0-9]{5}-?([0-9]{4})?)/); > > #= ($bill_to =~ > /\s{2,}(.*)\s+([A-Za-z]{2})\s+([0-9]{5}(-[0-9]{4})?)$/);#\s{2, > }(\w+(\s+\w+)? > \s+\w+)\s+(\w{2})\s?([0-9]{5}-?([0-9]{4})?)/); > > = map { scalar reverse } split ' ', ( > reverse $bill_to), 3; > > #print $1 . "\n"; > > #print $2 . "\n"; > > #print $3 . "\n"; > > > } elsif > (!defined($self->{ship_to_country})) { > > ($self->{ship_to_country},$self->{bill_to_country}) = ($line > =~ /$regex/); > > #print $1 . "\n"; > > #print $2 . "\n"; > > $in_address = undef; > } > } > > elsif (($line =~ /Code/) || defined($in_order)) { > $in_order = 1 and next if(!defined($in_order)); > #if(!defined($self->{code}) && > !defined($self->{quantity}) && > !defined($self->{price})) { > > if($line =~ /^(\w+)\s+(.*?)\s+(\d+)\s+(\$\d+\.\d+)/) > > > ($self->{code},$self->{name},$self->{quantity},$self->{price}) = > ($line =~ /^(\w+)\s+(.*?)\s+(\d+)\s+(\$\d+\.\d+)/); > > #print $1 . "\n"; > > #print $3 . "\n"; > > #print $4 . "\n"; > > # Need > to write items to table at this point in case we have multiple > items ordered. > #if > (defined($self->{code})&&defined($self->{quantity}) > &&defined($self->{price})) { > my > $sth =$dbh->prepare("INSERT INTO > miva_retail_items_ordered(order_number,part_number,quantity,pr > ice) VALUES > (?,?,?,?)"); > > $sth->execute($self->{order_number},$self->{code},$self->{quan > tity},$self->{ > price}); > > # > ($self->{code},$self->{quantity},$self->{price}) = undef; > #} > > #print" Defined is: $self->{coupon_type} \n"; > }elsif ($line > =~ /Coupon:\s+(.*):\s+\(\$(\d+\.\d+)\)/){ > > #print "In coupon\n"; > ($self->{coupon_type},$self->{coupon_amount}) > = ($line =~ > /Coupon:\s+(.*):\s+\(\$(\d+\.\d+)\)/); > #print "Coupon is " . $1 . "\n"; > > #print "Coupon type is " . $2 . "\n"; > }elsif > ($line =~ /Shipping:(\s+.*:)?\s+\$(\d+\.\d+)/){ > > ($self->{shipping_method},$self->{shipping_amount}) = ($line > =~ /Shipping:(\s+.*:)?\s+\$(\d+\.\d+)/); > > #print "In shipping\n"; > > #print "Shipping Method is: " . $1 . "\n"; > > #print $2 . "\n"; > }elsif ($line > =~ /Sales Tax\:\s+\$(\d+\.\d+)/) { > > ( $self->{sales_tax}) = ($line =~ /Sales Tax\:\s+\$(\d+\.\d+)/); > > # print "In sales tax: $self->{sales_tax}\n"; > > #print "Sales Tax is: " . $1 . "\n"; > > #}elsif > (!defined($self->{COD})){ > }elsif($line =~ /COD\s+Charge:\s+\$(\d+\.\d+)/){ > > ($self->{COD}) = ($line =~ /COD\s+Charge:\s+\$(\d+\.\d+)/); > > #print "DOC is: ". $1 . "\n"; > > #$in_order = undef; > > }elsif ($line =~ /\s+Total:\s+\$(\d+\.\d+)/) { > > $self->{total}= $1; > > print PARSED "Shippint: $self->{total}\n"; > > } # endif > > } > } > > # Trim all the spaces before and after the field. > $self->{$_} =~ s/^\s+|\s+$// foreach (keys %$self); > > } >
