Without seeing what you have tried and what problems you are having, I can't be much help. But when I encountered a similar problem some years ago, I was given some very helpful pointers. The thread is Reading a MySQL table into an array.

Regards,

John Davies

btongeorge:

Try something like this (untested, etc.):

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $DB=DBI->connect( ..fill this in.. );
my $data = $DB->selectall_arrayref("select ...");
print "Third column of fourth row is: $$data[3][2]\n";
[download]

...roboticus

When your only tool is a hammer, all problems look like your thumb.

From the description, you appear to be talking about a spreadsheet type of representation with clientNames in column A and more columns B...XX with more info. For some clients, there is no relevant info in some columns.

Rather than a 2D matrix, I would consider a Hash of Hash,HoH. The data stucture would look like:

 ( client23 => { columnAname => 123, columnXYname => 'Bob'},
   client42 => { columnXYname => 'Jim', columnBname => 765}, );
[download]

Thanks to everyone for their suggestions. Let me provide some more info.

The first query looks like this:

SELECT c2.id, c2.name as 'client' FROM client c2 WHERE level = 50 and status = 1;

This will produce a list of clients with a unique ID in the first column. The subsequent queries will create values for *some* of the clients, although all will have at least one value in one of the queries.

An example of a subsequent query is this:

SELECT c2,id, c2.name as 'client', count(*) as 'vm' FROM extension ext, client c1, client c2, extension_prefs exp WHERE ext.client_id = c1.id AND c1.level=100 AND exp.param="voicemail" AND exp.value=1 AND c1.parent_client_id = c2.id AND exp.extension_id = ext.id GROUP BY c2.name;

The resulting data set will indeed be a 2D structure resembling a spreadsheet. Once I have all the data compiled, I will need to generate an email (have done at least this part before in Perl) and send the data as an HTML table.

Well, see if you can get to "first base", connect to the DB and run the first query.

See some untested code below... Once you get connected and have a database handle, the sequence is : prepare query ->execute query -> retrieve results. There are several "flavors" of result retrieval. I show an easy way below.

I recommend the following book, what DB are you using?
Programming the Perl DBI, Database programming with Perl By Tim Bunce, Alligator Descartes.

use DBI;

my $database = 'somedatabase';  #your system specific
my $hostname = 'localhost';     ##your system specific
my $port     = 3306;            #default mysql port
my $user     = 'someuser';      #your system specific
my $password = 'somepassword';  #your system specific

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"
                or die "DB data set name failed $!\n";
my $dbh = DBI->connect($dsn, $user, $password, {RaiseError => 1}) 
                or die "DB connect failed $!\n";

my $query = $dbh->prepare("SELECT c2.id, c2.name as 'client' 
                          FROM client c2 
                          WHERE level = 50 and status = 1");
$query->execute();
while (my ($id, $name) = $query->fetchrow_array)
{
  print "$id \t $name\n";
}

__END__
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 = ?");
$query->execute(50,1);
[download]

Update: added placeholder comment in code above. Also just tested a very similar query on my local MySQL installation. See if you can get this first part working.

use DBI;

my ($db, $user, $pass, $dbh, $query, $sth, $record, %clients);

$db = 'database';
$user = 'user';
$pass = 'password';

### Log into database
die "Couldn't connect to database: " . DBI->errstr
    if !($dbh = DBI->connect("DBI:mysql:$db", $user, $pass));

### Select client list
$query = 'SELECT * FROM clients';
$sth = $dbh->prepare($query);
$sth->execute();    

### Store by record IDN or other identifying field
$clients{$record->{'idn'}} = $record
    while $record = $sth->fetchrow_hashref();

### Add data from different table
$query = 'SELECT * FROM client_data';
$sth = $dbh->prepare($query);
$sth->execute();    

### Index on same IDN
while ($record = $sth->fetchrow_hashref()) {
    push @{$clients{$record->{'client'}}{$_}}, $record
        for qw/client data fields here/;
}

### Sort on some field to return to desired order
for $record (sort { $a->{'name'} cmp $b->{'name'} } values %clients) {
    ### Do something with record
}
[download]

What is the multidimensional data being used for, exactly? There may be a better way to approach this depending on what you're really trying to do.

It might be possible, and I did start down that road, but the query got pretty scary. I think I'm more comfortable with multiple queries, but maybe I'm wrong. Here are all the queries, the first generates the client list, the rest populate the integer fields:

SELECT c2.id, c2.name as 'client'
  FROM client c2
  WHERE level = 50 and status = 1;

SELECT c2.id, c2.name as 'client', count(*) as 'ext' 
  FROM client c1, client c2
  WHERE c1.level=100 AND c1.status=1 AND ext.type = "phone" 
   AND c1.parent_client_id = c2.id
  GROUP BY c1.parent_client_id ORDER BY c2.name;

SELECT c2,id, c2.name as 'client', count(*) as 'vm'
  FROM extension ext, client c1, client c2, extension_prefs exp
  WHERE ext.client_id = c1.id AND c1.level=100
   AND exp.param="voicemail"
   AND exp.value=1 AND c1.parent_client_id = c2.id
   AND exp.extension_id = ext.id
 GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', count(*) as 'ivr'
   FROM extension ext, client c1, client c2
   WHERE ext.client_id = c1.id AND ext.type = 'ivr'
   AND c1.status = 1 AND c1.parent_client_id = c2.id
   GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', count(*) as 'queues'
  FROM extension ext, client c1, client c2
  WHERE ext.client_id = c1.id AND ext.type = 'queue'
   AND c1.status = 1 AND c1.parent_client_id = c2.id
  GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', sum(if(value<11,1,0)) AS "conf10"
  FROM extension ext, client c1, client c2, extension_prefs exp
  WHERE ext.client_id = c1.id AND exp.param="conf_size"
   AND ext.type="conference" 
   AND c1.parent_client_id = c2.id AND exp.extension_id = ext.id
  GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', 
 sum(if(value<20 AND value>10,1,0)) AS "conf20"
  FROM extension ext, client c1, client c2, extension_prefs exp
  WHERE ext.client_id = c1.id AND exp.param="conf_size"
   AND ext.type="conference"
   AND c1.parent_client_id = c2.id AND exp.extension_id = ext.id
  GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', sum(if(value>20,1,0)) AS "conf30"
  FROM extension ext, client c1, client c2, extension_prefs exp
  WHERE ext.client_id = c1.id AND exp.param="conf_size"
   AND ext.type="conference"
   AND c1.parent_client_id = c2.id AND exp.extension_id = ext.id
  GROUP BY c2.name;
[download]

I would consider making views out of the other queries (then the main query and the joins are simple), but I would also probably change the queries. The usual thing to do is to include everything but the aggregate in the GROUP BY clause, but I'm not sure what MySQL does when you don't do that and I don't know, e.g., what the keys of the tables are or if you have duplicate client name's with multiple client id's and how you want to handle that, etc.