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

I have been working on the logic for this and after looking through the DBI docs, the Perl DBI book, and numerous threads, nodes and posts I am still stumped.

I have a db with records with say 40 pieces of data, if which I only want 3 fields per record. The id itself and two other pieces of data based on whether or not some bit is set, e.g.: 

SELECT extracted_ads_id, prop_street_no, prop_street_name
FROM extracted_ads_tbl 
WHERE pin-on-off=0

[download]

The WHERE statement is just something I set to 1 later when I have manipulated the data. I need to be able to access prop_street_no and prop_street_name to use as vars for a spider that populates an http request to a search engine to extract another piece of data..

I would LIKE to build a hash of arrays (I think) where the key is the "extracted_ads_id" and the value is an array reference of prop_street_no and prop_street_name. I would then like to build another hash that has the extracted_ads_id with the subsequent PIN referenced in the SQL statement above, so I can then insert extracted_ads_id and pin into a seperate table. 

INSERT INTO enhanced_ads_tbl 
(enhanced_ad_id,extracted_ads_id,pin) 
VALUES (NULL,?,?)

[download]

I am not sure which function I need to use from the DBI docs to achieve this (fetchrow_array(), fetchrow_arrayref(), etc.), or if that is even on the right track.

Thanks again for the help!!

####UPDATE###

Just in case ne1 else was trying to figure this out the first time... here is what I ended up doing: 

....snip....
my $query1 = "SELECT field1,field2,field3 FROM table WHERE `flag`=0";
my $sth = $dbh->prepare($query1);
$sth->execute();

# Loop query to build an array
# this pulls each row into an 
# array that we can manipulate
while ( ($var1,$var2,$var3) = $sth->fetchrow_array() ) {
# update the flag field to reflect the ad has been ehnanced
   my $query2 = "UPDATE table SET `flag`='1' WHERE field1=?";
   my $sth2 = $dbh->prepare($query2);
   $sth2->execute($var1);
   ....snip....   
   }

[download]

So what this does for me: query a table, load the returned values into an array, that I can then use as values I iterate through when updating a field in the db, or access each successive row returned as an array of values for variables... what I was doing with this was pulling an id, and the number and street name from a table of addresses, so I could use these values to pass in a URL for spidering some info off a site.. I could iterate through my db of addresses by $street_no, $street_name and push the returned results into anther table this way. It's yummy. =)

Replies are listed 'Best First'.
Re: Perl MySQL Table into an array
by pbeckingham (Parson) on Mar 13, 2004 at 21:47 UTC

    This node is currently regarded as the best of the year, so I don't think anyone is going to improve on that - DBI recipes

[reply]

      This is an awesome link. I did not know it was there. I had done several "DBI" searches, but I may NOT have done DBI recipes from within the perlmonks site.

      Thank you!

[reply]
Re: Perl MySQL Table into an array
by tachyon (Chancellor) on Mar 13, 2004 at 22:34 UTC 
    my $struct = $sth->fetchall_hashref('extracted_ads_id');

# $struct will look like
{  foo => { extracted_ads_id => foo,  prop_street_no => 12, prop_stree
+t_name => 'blah' }
   bar => { extracted_ads_id => bar, .....
}

# access like $struct->{foo}->{prop_street_no}

    [download]

    The hashref struct from DBI generally works but is almost never what you really want. To get a hash of array refs do: 

    my $h;
while ( my $row = $sth->fetchrow_arrayref() ) {
    $h->{$row->[0]} = [ @row[1,2] ];
}

# $h will look like
{ foo => [ 12, blah ],
  bar => [ ....
}

# access like $h->{foo}->[0] will give you street no.

    [download]

    I suggest you use Data::Dumper to look at what the return values look like if that does not make sense.

    cheers

    tachyon

[reply]
[d/l]
[select]
      Thanks. This looks a lot like what I'm after.
[reply]
Re: Perl MySQL Table into an array
by simonm (Vicar) on Mar 13, 2004 at 22:47 UTC
    As an alternative, consider using one of the higher-level DBI wrappers on CPAN; I'm personally fond of my own DBIx::SQLEngine, but there are a number of decent choices.

    Here's a possible skeleton implementation: 

    # Create a SQLEngine wrapper around your existing connection
my $sqldb = DBIx::SQLEngine->new( $dbh );

# Get an array of hashes
my $extracted = $sqldb->fetch_select( sql => <<'/' );
SELECT extracted_ads_id, prop_street_no, prop_street_name
FROM extracted_ads_tbl 
WHERE pin-on-off=0
/

# Build a hash of arrays mapping ad_ids to property info
my %extracted = map { 
    $_->{extracted_ads_id} => [ $_->{prop_street_no}, $_->{prop_street
+_name} ] 
} @$extracted;

# ... now build your web requests and hash of PINs
my %pin_results = ...;

# Do inserts for each resulting record
foreach my $ads_id ( keys %pin_results ) {
  $sqldb->do_insert( sql => [ <<'/', $ads_id, $pin_results{$ads_id} ] 
+);
INSERT INTO enhanced_ads_tbl 
(enhanced_ad_id,extracted_ads_id,pin) 
VALUES (NULL,?,?)
/
}

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom