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

Hello Monks,
I have a code that opens .htm files, searches for certain statistics, and enters them into an array. I have two different sets of information, ID numbers and statistics, so I enter both of them into seperate arrays and then create a hash using the ID number as the key to the statistics as follows:

            if ($element =~ m/\<td/){
                $element =~ s/^\<td>//;
                $element =~ s/\<\/td>$//;
                $element =~ s/[,]//g;
                next if ($element !~ m/^\d{1,3}\d{3}\d{3}/);
                push @stats, $element;
                
                
            }
            elsif ($element =~ m/\<h1>/){
                chomp $element;                
                $element =~ s/^<h1>//;
                $element =~ s/<\/h1>$//;
                push @id, $element;
                
            }
            

        }
        push @{$hash{$id}}, @stats;

[download]
My problem is that when I enter the information into MySQL, the value comes out null. I know that the information for each value is stored in an array, and is null as a result - there are multiple statistics for every ID number. I've tried several other solutions, such as putting the information directly from the arrays into MySQL, but as it's contianed within a foreach loop, the statistics get repeated and the information is wrong.

Is there any way to input information so that the ID matches the statistics? Thank you!

Replies are listed 'Best First'.
Re: Inserting Hash Information Into MySQL
by CountZero (Bishop) on Jun 20, 2012 at 16:36 UTC
    A database cannot save a Perl hash (and certainly not a hash of array-refs)in its native format.

    You can save the "id" in the database, but the "array" which is the "value" part of the hash cannot be saved as such. Of course this "array" is really an array-ref and you will have to dereference it to get the data itself. But that means you will have a multiple of data-items which of course will never fit in one field in your database.

    You will have to review and revise your database schema to make this work.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
[reply]
      A database cannot save a Perl hash (and certainly not a hash of array-refs)in its native format.

      A database should have no problem with a hash. For instance, PostgreSQL has hstore [1], which not only makes implementing a hash easy, but can also index them (with either GIN (speed of lookup) or GiST (speed of writing)).

      You will have to review and revise your database schema

      Perhaps revise not 'database schema' but just 'database'... ;-)

      [1] http://www.postgresql.org/docs/current/static/hstore.html

[reply]
[d/l]
[select]
        Thank you for the information, that is really interesting. I wonder how it integrates with SQL.

        CountZero

        A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

        My blog: Imperial Deltronics
[reply]
Re: Inserting Hash Information Into MySQL
by NetWallah (Canon) on Jun 20, 2012 at 15:07 UTC
    Are you using "use strict;" ?

    You have 2 variables: $id, and @id - perhaps you are confusing their usage.

    Also - you have not shown the code that actually inserts the data into MySQL.

                 I hope life isn't a big joke, because I don't get it.
                       -SNL

[reply]
      I am using strict. The hash uses the values in @id as the key, which is why I used $id. My apologies - the code to enter into MySQL is: 
      my $insert_query = "INSERT INTO test(id, stats) VALUES (?, ?);";    
$sth = $dbh->prepare($insert_query);

while(($key, $value) = each(%hash)){
    $sth->execute($key, $value);
    }

      [download]
      It inserts the ID without a problem. The only issue is that arrays can't be stored in MySQL, but I don't know how to get the information out of the array to store it otherwise.
[reply]
[d/l]
        It seems to me that the values in your %hash are array-refs.

        So you cannot simply insert them into the table as is.

        I am not sure what you are trying to achieve but I assume you want something like this: 

        while(($key, $value) = each(%hash)){
    for my $stat_value (@$value) {
       $sth->execute($key, $stat_value);
    }
}

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom