Keeping associated data after entering into a hash

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

Hi I have a script that draws two queries from a PostgreSQL database full of bus timetable data and then compares them for similar results in one column. The advice that I was given before was to use one ash and one array to fetermine which values are the same in either query. This ocde looks something like this:

my %ids;

$origsql = qq{ SELECT route_number, stop_reference, depart_time FROM r
+outes WHERE distance < 200...........}; #all SQL code not included he
+re
$sth = $dbh->prepare( $origsql );
$sth->execute();
$sth->bind_columns( undef, \$orig_service_id, \$orig_stop, $depart_ori
+g);
while ($sth->fetch())
{
push(@orig_service_ids, $orig_service_id);
}
for my $id (@orig_service_ids) {
    $ids{$id} = 1;
}

# ...


$destsql = qq{ SELECT route_number, stop_reference, depart_time FROM r
+outes WHERE distance < 200...........}; #all SQL code not included he
+re
$sth = $dbh->prepare( $destsql );
$sth->execute();
$sth->bind_columns( undef, \$dest_service_id, \$dest_stop, $depart_des
+t);
while ($sth->fetch())
{
push(@dest_service_ids, $dest_service_id);
}
for my $id (@dest_service_ids) {
    if (exists $ids{$id}) {
        print "ID $id exists in both origin and destination set\n";
    }
}
[download]

Now this works excellently as it tells me which service from my origin matches in my destination. However how can I also gain access to the associated data with each row in the query? For example, if I find that both queries have the same service, how can I then find the associate stops from the query relating to that service for each my origin and my destination? Do I have to make an array for each other variable in the queries too or a hash for each? Im not really an expert on hashes so any help and advice would be more than welcome. Thanks

Comment on Keeping associated data after entering into a hash Download Code

Replies are listed 'Best First'.
Re: Keeping associated data after entering into a hash by chromatic (Archbishop) on Jul 08, 2007 at 00:29 UTC
Are you looking for something like: `$sth->bind_columns( undef, \$orig_service_id, \$orig_stop, $depart_ori +g); while ($sth->fetch()) { $ids{$orig_service_id} = { stop_reference => $orig_stop, depart_time => $depart_orig, } }` [download] If you use that system, then you can write something like: `$sth->bind_columns( undef, \$dest_service_id, \$dest_stop, $depart_des +t); while ($sth->fetch()) { if (exists ($ids{$dest_service_id}) { print "ID $id exists in both origin and destination set\n"; print "\t$ids{$dest_service_id}{stop_reference} => $dest_stop\ +n"; print "\t$ids{$dest_service_id}{depart_time} => $depart_dest\n +"; } }` [download] Update: Typo fixed, thanks to johngg.	[reply] [d/l] [select]
Re: Keeping associated data after entering into a hash by almut (Canon) on Jul 08, 2007 at 00:33 UTC
If I'm understanding you correctly, something like the following might work. It would give you access to the data from the rows with the same ID in the other query. my %ids; # ... $sth->bind_columns( undef, \$orig_service_id, \$orig_stop, $depart_ori +g); while ($sth->fetch()) { push @{$ids{$orig_service_id}}, { stop => $orig_stop, depart => $depart_orig, # ... }; } # ... $sth->bind_columns( undef, \$dest_service_id, \$dest_stop, $depart_des +t); while ($sth->fetch()) { if (exists $ids{$dest_service_id}) { print "ID $dest_service_id:\n"; my $i = 0; # counter (only needed for demo purposes) for my $orig ( @{$ids{$dest_service_id}} ) { # $orig is a hashref to origin data printf "matching origin row #%d:\n", ++$i; printf " orig_stop = %s\n", $orig->{stop}; printf " depart_orig = %s\n", $orig->{depart}; # ... } } } [download]	[reply] [d/l]
Re: Keeping associated data after entering into a hash by runrig (Abbot) on Jul 08, 2007 at 03:12 UTC
You are selecting three columns, but your bind_columns() has 4 arguments. This should be an error. Why is the first argument undef? Each arg should correspond to a selected column. Are you checking for errors? Is RaiseError set? Update: nevermind...the docs say the first argument is ignored if undef or a hash reference...missed that part the first time...until Albannach made me read it again :-)	[reply]