in reply to Help with MySQL SELECT into multidimensional array

The first step is to make sure that you actually understand the SQL for the queries that you need to do. From your post, I can't really tell. I would start with first query, the one to get the list of client's. Then code just that one SQL statement. See if you can get that to work and share your progress with us.

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]
The clients are the hash key and sub-hashes correlated the column Name with a value. This way as the structure each row of a 2D matrix expands to more columns (it sounds like that can dynamically happen), you don't have to expand each row with zero/null placeholders, just fill in relevant data. Anyway try one SQL query and tell us how that goes.

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

    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.

[reply]
      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.
[reply]
[d/l]

        Works like a charm, without any changes other than the obvious DB name/uid/pw etc. The code is far simpler than what I had previously cobbled together, for which I am thankful. I can read it now!

        Please excuse my ignorance but I don't understand the placeholder comment?

        Will look up the books mentioned.

[reply]
        Thanks Marshall, will give that a try. Am using MySQL on CentOS. We have an O'Reilly online subscription, can you recommend any books in their library at all?
[reply]

In Section Seekers of Perl Wisdom