in reply to Re^6: Help with MySQL SELECT into multidimensional array
in thread Help with MySQL SELECT into multidimensional array

I would be thinking along the following lines: 
use Data::Dumper;
my %hash;

my $client = "client1";
$hash{$client}{fieldA} = 12234;

#from another query:
$hash{$client}{fieldC} = 9876;

print Dumper \%hash;
__END__
$VAR1 = {
          'client1' => {
                         'fieldA' => 12234,
                         'fieldC' => 9876
                       }
        };

[download]
To generate your output, make an array to specify the column order: my @order = qw (fieldC fieldB fieldA); Then when processing each client, cycle through the order array, if value exists for that client, put it, if not then put "" or whatever. I am just thinking that a hash representation will work out better for accumulating the data, then make the 2D array for output after the data has been collected rather than keeping each row's columns straight during the data accumulation phase.

Update: I mentioned before that there is more than one way to retrieve the data, one of those ways is as a hash. That might be of use to you...you have a bit of reading to do! 

use Data::Dumper;
my %hash;

my $client = "client1";
$hash{$client}{fieldA} = 12234;

#from another query:
$hash{$client}{fieldC} = 9876;

my @order = qw (fieldC fieldB fieldA);

foreach my $client (keys %hash)
{
   print "$client";
   foreach my $field (@order)
   {
      if (exists $hash{$client}{$field})
      {
          print "\t$hash{$client}{$field}";
      }
      else
      {
         print "\tno_value";
      }  
   }
   print "\n";
}
#prints: client1 9876    no_value    12234

[download]

Replies are listed 'Best First'.
Re^8: Help with MySQL SELECT into multidimensional array
by btongeorge (Initiate) on Dec 02, 2011 at 16:54 UTC

    I realise that this might be horribly inefficient, but apart from that, is there any reason I couldn't just run the first query and populate the array (which already works, as tested previously) then run the subsequent query, and for each iteration, while loop through the array and populate the array for client IDs that match?

    I don't understand how to add the column to the array, but other than that, it seems the simplest way of achieving what I need. Yes it means quite a bit of extra computation, but this query will at most be generating a couple hundred lines (i.e. clients), and will complete in no time regardless.

[reply]
      No problem! If you have working code that you understand, go for it!

      I think my brain was getting off into more complicated stuff this morning. I just started working with one DB this morning and the document that describes the tables and fields is 85 pages! Geez! Excuse me if my brain was "getting out there" - everything I do with today's project is gonna be flavored "complicated"!

[reply]

        really it's no problem, you've helped me no end. Would be really grateful though if you could explain me this one thing.

        So I run query #1, and I now have an array with 2 columns - client ID and name. Great start. Then I run query #2 which generates another list, this time with client IDs and an integer for each ID. There will not be a value for each client returned by query 1. My question is, how do I refer to the new column of the array?

        Can I just put the output of query #1 into @array and then refer to @array[2] when running the second query? I have searched high and low but can't see this referenced anywhere - at the moment this is my missing link!

[reply]

In Section Seekers of Perl Wisdom