in reply to Creating a csv from two database tables

Untested, but just to give the general idea...on the assumption that the db isn't so big as to make this unwieldy:
$sql = "SELECT * FROM params" $sth = $dbh->prepare($sql) or die("Could not prepare!" . $dbh->errstr) +; $sth->execute() or die("Could not execute!" . $dbh->errstr); while (($param_id, $param_name) = $sth->fetchrow_array()) { $param_names{$param_id} = $param_name; } $sth->finish; $sql = "SELECT * FROM objects" $sth = $dbh->prepare($sql) or die("Could not prepare!" . $dbh->errstr) +; $sth->execute() or die("Could not execute!" . $dbh->errstr); while (($object_id, $param_id, $param_value) = $sth->fetchrow_array()) + { $objects{$object_id}{$param_id} = $param_value; } $sth->finish; #print column headers foreach $param_id (sort keys %param_names) { print $param_names{$param_id}.", "; } #print data foreach $object_id (sort keys %objects) { foreach $param_id (sort keys %param_names) { print $objects{$object_id}{$param_id}.", "; } }

Needs cleanup of trailing commas.




Time flies like an arrow. Fruit flies like a banana.

Replies are listed 'Best First'.
Re^2: Creating a csv from two database tables
by joec_ (Scribe) on Apr 07, 2009 at 08:47 UTC
    Hi thanks for the comments, am i ok to add this if statement around the object hash (if a row contains an empty value, use the value from the next col)?

    while ($row = $sth->fetchrow_arrayref()) { if (defined ($row->[2])){ $objects{$row->[0]}{$row->[1]} = $row->[2]; }else{ $objects{$row->[0]}{$row->[1]} = $row->[3]; } }

    Thanks

    -----

    Eschew obfuscation, espouse elucidation!