DBI with hash of arrays

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

Hello Monks,
I would like to minimize the amount of code lines and I would like to query a database using DBI and first find out the criteria to search by:

$query="select distinct number from first_table";
$sth = $dbh->prepare($query);
$sth->execute() or die "$DBI::errstr";

while ( ($row) = $sth->fetchrow_array() )
{
push (@query_results, $row);

}

Then here is when i am confused. I would like to do a foreach loop on query results and place it into a hash of arrays:

foreach $line(@query_results)
{
select record_ids from second_table where number= $line;

####not sure what the code is next to create my hash of arrays based on each number as there could be more than one record id and each "number" mill have more than one hash of array value assigned to them####

}

Thanks so much!

Comment on DBI with hash of arrays

Replies are listed 'Best First'.
Re: DBI with hash of arrays by moritz (Cardinal) on Mar 22, 2011 at 16:25 UTC
I would like to minimize the amount of code lines Please read The path to mastery. I don't quite understand what you want your resulting data structure to look like, and I don't know what you want to do with it, so I fear your need to provide more information. For example when you talk about a hash, what should be the keys, what should be the values? Which values do you expect in the arrays? But one thing I know: if you do a query, and use the results only as input for another query, using just a single query with a join over both tables is probably more efficient and results in less Perl code. If you need the results in a particular order, a GROUP BY or ORDER BY clause can help. Perl 6 - second systems done right	[reply]
Re: DBI with hash of arrays by pipeops (Novice) on Mar 22, 2011 at 17:23 UTC
Sorry for the confusing explanation. I was able to join the two queries and now I will have records that look similar like this but are currently stored in an array: 11 test 11 test2 22 break 22 break2 33 break3 Now I do not know my first column as there are more entries and I would like my hash of arrays to look like this: %HoA = ( 11 => "test", "test2" , 22 => "break", "break2", 33 => "break3" , etc. ); But I cannot even begin thinking of how I can accomplish this other than looping through the array and somehow exporting the first number and will need your help Thank you!	[reply]
Re^2: DBI with hash of arrays by runrig (Abbot) on Mar 22, 2011 at 18:30 UTC
`my $sql = <<EOT SELECT col1, col2 FROM table.... EOT my $sth = $dbh->prepare($sql); $sth->execute(); $sth->bind_columns(\my ($col1, $col2)); my %data; while ($sth->fetch) { push @{$data{$col1}}, $col2; }` [download]	[reply] [d/l]
Re: DBI with hash of arrays by CountZero (Bishop) on Mar 22, 2011 at 23:16 UTC
That is not a Perl problem, but an SQL question. You want to join two tables. You will probably need something like: `SELECT DISTINCT first_table.number, record_ids FROM first_table, secon +d_table WHERE second_table.number = first_table_number;` [download] Always let the database do as much work as possible: it will be more efficient and faster. 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	[reply] [d/l]