Nik has asked for the wisdom of the Perl Monks concerning the following question:

I have this code to load the name of the games, descriptions and how many times a game was downloaded. problem is that if i change the description file, by for example adding more games or editing existing ones this snippet will have to rerun form games.pl thts is inside. Can i do something that even if i cahnge the descriptions.txt file i wont have to initialize all this every time games.pl is loading and the gamecounetr not to be lost? Thank you and here is the snippet: 
#================================================

my @row;
$sth = $dbh->prepare( 'INSERT INTO games (gamename, gamedesc, gamecoun
+ter) VALUES (?, ?, ?)' );

open (FILE, "<../data/games/descriptions.txt") or die $!;
    while (<FILE>) {
        chomp;

        @row = split /\t/;
        push @row, 0;

        $sth->execute( @row );
    }
close (FILE);

#================================================

[download]

Replies are listed 'Best First'.
Re: loading a .txe into a database
by thor (Priest) on May 03, 2005 at 10:21 UTC
    Sure thing. From the looks of it, you have a tab delimited file. Let's assume that gamename is the primary key on the database table. You could do something like this: 
    my @row;
my $select = $dbh->prepare( '
SELECT COUNT(*) FROM games where gamename=?
' );
my $insert = $dbh->prepare( '
INSERT INTO games (gamename, gamedesc, gamecounter) VALUES (?, ?, ?)
' );
my $update = $dbh->prepare( '
UPDATE games SET gamedesc=? where gamename=?
' );

open (FILE, "<../data/games/descriptions.txt") or die $!;
    while (<FILE>) {
        chomp;

        my ($gamename, $gamedesc) = split /\t/;
        $select->execute($gamename);
        my $count;
        while( my $ref = $select-> fetchrow_arrayref() ) {
            $count = $ref->[0];
        }
        if( $count == 0 ) { #a new game
            $insert->execute( $gamename, $gamedesc, 0 );
        }
        else {
            $update->execute( $gamedesc, $gamename );
        }
    }
close (FILE);

    [download]
    I'm sure that there are cooler ways to do it, but this one's pretty transparent.

    thor

    Feel the white light, the light within
    Be your own disciple, fan the sparks of will
    For all of us waiting, your kingdom will come

[reply]
[d/l]
      Thank you very much Thor!
      It worked great!
[reply]
        Why when i run this i get an error saying Undefined subroutine &main::start_table called at the line where the pritn start_table is:
        How can i write it? 
        ====================================================
print start_form(-action=>'games.pl');
    print start_table( {class=>'games'} );
        while( $row = $sth->fetchrow_hashref )
        {
            print Tr(
                     td( {-width=>'20%'},  submit( $row->{gamename} ))
+,
                     td( {-width=>'75%'},  $row->{gamedesc}         ),
                     td( {-width=>'5%'},   $row->{gamecounter}      )
                  );
        }
    print end_table;
print end_form, br;

====================================================

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom