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 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.

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]

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.

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

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