Dave Thacker wrote:
>
> Thanks for the help you folks gave me on my format problems earlier. I've
> resolved those problems and moved to another! My program is supposed to
> create a series of roster files. It creates the files, but only the first
> file gets the header I defined in RF_TOP. What do I need to change?
>
> TIA DT
>
> #!/usr/bin/perl
>
> use strict;
> use warnings;
>
> use DBI;
> use Getopt::Long;
> our ($opt_league, $opt_div);
>
>
> &GetOptions("league=s", "div=s");
>
> print "Working on the $opt_league league, division $opt_div\n";
>
> #connect to database
> my $dbh = DBI->connect("DBI:mysql:database=efl",
> 'user',
> 'password'
> ) or die "Can't connect to database";
>
> #set the root directory of the installation
> my $rootdir= "/home/dthacker/efl/dev/";
>
>
> #open teams.dir for reading
> open( CLUB, "<$rootdir/teams.dir" ) or die "Can't open teams.dir : $!";
> while (<CLUB>) {
> print $_;
> my $roster_file=$_;
> my $club = substr($_, 0,3);
> my $strsql = <<EOT;
>
> select name, age, nat, st, tk, ps, sh, agg
> from players where players.club="$club"
> EOT
>
> my $sth = $dbh->prepare($strsql);
>
>
> $sth->execute() or die "Couldn't execute statement: $DBI::errstr;
> stopped";
>
> my ($name, $age, $nat, $st, $tk, $ps, $sh, $agg);
>
> format RF =
> @<<<<<<<<<<< @< @<< @< @< @< @< @<
> $name, $age, $nat, $st, $tk, $ps, $sh, $agg
> .
>
> format RF_TOP =
> Name Age Nat St Tk Ps Sh Ag KAb TAb PAb SAb Gam Sav Ktk Kps Sht Gls
> Ass DP Inj Sus
> -------------------------------------------------------------------------------------------
> .
>
> open (RF, ">$roster_file") or die "Can't open roster file $roster_file";
>
> while ( ($name, $age, $nat, $st, $tk, $ps, $sh, $agg ) =
> $sth->fetchrow_array() ) {
> write RF;
> }
>
> close RF;
>
> }
> $dbh->disconnect();
It looks like the count of format lines left doesn't get reset when you close
and reopen a file handle. Change your code to this
open RF, '>', $roster_file or die "Can't open roster file $roster_file";
$- = 0;
and all should be well.
By the way, you really should only prepare your SQL statement once, outside the
loop, and bind the value of $club when you call execute - like this
#connect to database
my $dbh = DBI->connect('DBI:mysql:database=efl',
'user',
'password'
) or die "Can't connect to database";
my $sth = $dbh->prepare(<<EOT);
SELECT name, age, nat, st, tk, ps, sh, agg
FROM players
WHERE players.club = ?
EOT
# set the root directory of the installation
my $rootdir= "/home/dthacker/efl/dev/";
# open teams.dir for reading
open CLUB, '<', "$rootdir/teams.dir" or die "Can't open teams.dir : $!";
while (my $roster_file = <DATA>) {
print $roster_file;
chomp $roster_file;
my ($club) = $roster_file =~ /([^.]+)/;
$sth->execute($club) or die "Couldn't execute statement: $DBI::errstr;
stopped";
... and so on.
HTH,
Rob
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
