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

In reply to Perl MySQL Table into an array by carric

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.