Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re^3: Creating a csv from two database tables

by linuxer (Curate)
on Apr 06, 2009 at 16:43 UTC ( #755785=note: print w/replies, xml ) Need Help??

in reply to Re^2: Creating a csv from two database tables
in thread Creating a csv from two database tables

For creating the objects hash, I would rely upon autovivification.

And I would create a plain hash-of-hash of the data and create the csv format afterwards.

here's my example, as I tried to figure your problem without database access (So I used DBD::CSV to have a "database"). It's just my hack, needs some modifications before production usage, and I'm sure there's a more efficient/stylish way to do that ;o)

#! /usr/bin/perl use strict; use warnings; use DBI; my $dbh = DBI->connect( 'DBI:CSV:f_dir=.' ) or die "cannot connect: " +. $DBI::errstr; sub _get_param_fieldnames { my %field; my $sth = $dbh->prepare( 'SELECT param_id,name FROM x_param' ) or die "cannot prepare: " . $dbh->errstr; $sth->execute or die $dbh->errstr; while ( my $row = $sth->fetchrow_arrayref ) { $field{ $row->[0] } = $row->[1]; } $sth->finish; return \%field; } sub _get_object_entries { my %object; my $sth = $dbh->prepare( 'SELECT * FROM x_object' ) or die "cannot prepare: " . $dbh->errstr; $sth->execute or die $dbh->errstr; while ( my $row = $sth->fetchrow_arrayref ) { $object{ $row->[0] }->{ $row->[1] } = $row->[2]; } $sth->finish; return \%object; } # output stuff { my $field_by = _get_param_fieldnames(); my $object_by = _get_object_entries(); local $, = ','; local $\ = "\n"; # create csv output; check for proper modules print 'OBJECT_ID', map { $field_by->{$_} } sort { $a <=> $b } key +s %$field_by; for my $oid ( keys %$object_by ) { my @param_values; for my $pid ( sort { $a <=> $b } keys %$field_by ) { push @param_values, exists $object_by->{$oid}->{$pid} ? $o +bject_by->{$oid}->{$pid} : ''; } print $oid, @param_values; } } $dbh->disconnect; __END__

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://755785]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2017-12-11 19:12 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (310 votes). Check out past polls.