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

In your statement, this is not needed, but I added this to just "show how its done". When you prepare the statement, you can put in a ? to mean that parameter will be supplied when you call execute.

A complicated statement will take a while to prepare as the data base will figure out its strategy for executing it. You can keep "reusing" a prepared query with different values. This way of doing it is also better than interpolating a different Perl variable into the statement for security reasons. 

To make a parameter a variable, use a placeholder:
my $query = $dbh->prepare("SELECT c2.id, c2.name as 'client' 
                          FROM client c2 
                          WHERE level = ? and status = ?");

my $level = 50;
$query->execute($level,1);
$level = 60;
$query->execute($level,1);

[download]
Update: you may find this feature useful in dealing with other of your queries. A common technique to run essentially the same query multiple times with different values is to create a data structure, an array, a hash table, etc and then make a loop to cycle through that structure, executing the query again and again. with different values. This can reduce the clutter of repeating the same SQL again and again.

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

    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!

[reply]
      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]

In Section Seekers of Perl Wisdom