comment on

You might want to consider a simpler more SQL solution

#!perl
use strict;
use DBI;
use Data::Dumper;

my $dbh = create_db();

my $sql = 'SELECT P.common_name,S.plant_size,S.unit,G.abbr
           FROM plants as P
           LEFT JOIN sizes as S
             ON S.plantid = P.id
           LEFT JOIN growers as G
             ON  S.plantid    = G.plantid
             AND S.plant_size = G.plant_size';
my $sth = $dbh->prepare($sql);
$sth->execute();
my %hash=();
while (my @f = $sth->fetchrow_array){
  $hash{$f[0]}{$f[1].$f[2]}{$f[3]} = 1
}
print Dumper(\%hash);
[download]

sub create_db {
  unlink 'mydb.sqlite' if -e 'mydb.sqlite';
  my $dbh = DBI->connect("dbi:SQLite:dbname=mydb.sqlite","","");
  $dbh->do('CREATE TABLE plants (genus,common_name,type,chicagoland_gr
+ows,id )');
  $dbh->do('CREATE TABLE sizes (show_plant,plant_size,plantid,unit,use
+rid)');
  $dbh->do('CREATE TABLE growers (plantid,plant_size,abbr)');

  my $sql = 'INSERT INTO plants VALUES (?,?,?,?,?)';
  my $sth = $dbh->prepare($sql);
  $sth->execute("Roses","Roses",1,0,1000);
  $sth->execute("Violets","Violets",1,0,1001);

  $sql = 'INSERT INTO sizes VALUES (?,?,?,?,?)';
  $sth = $dbh->prepare($sql);
  my @sizes = (
    [1,2,1000,'"',4],[1,4,1000,'"',4],
    [1,3,1001,'"',4] );
  $sth->execute(@$_) for @sizes;
 
  $sql = 'INSERT INTO growers VALUES (?,?,?)';
  $sth = $dbh->prepare($sql);
  my @growers = (
    [1000,2,'MA'],[1000,2,'CD'],[1000,2,'TH'],
    [1000,4,'CD'],[1000,4,'TH'],[1001,3,'MA'],
    [1001,3,'TH'] );    
  $sth->execute(@$_) for @growers;
  
  return $dbh;
}
[download]

poj

In reply to Re^3: Another "Can't use string as an ARRAY ref" by poj
in thread Another "Can't use string as an ARRAY ref" by bradcathey

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


Come for the quick hacks, stay for the epiphanies.
	PerlMonks