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

Understood, thanks for the explanation. In this case I will need different queries for each column, but that's useful info nonetheless.

The next step is for me to understand the best way to loop through the array and populate the next column - if I get that, I feel I can do the rest of the coding without too much more help - famous last words I know!

  • Comment on Re^6: Help with MySQL SELECT into multidimensional array

Replies are listed 'Best First'.
Re^7: Help with MySQL SELECT into multidimensional array
by Marshall (Canon) on Dec 02, 2011 at 16:18 UTC
    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]
[reply]
[d/l]
[select]

      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]

In Section Seekers of Perl Wisdom