Re^2: Help with MySQL SELECT into multidimensional array

[btongeorge]

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.

[Marshall]

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);

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.

[btongeorge]

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.

[Marshall]

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);

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.

[btongeorge]

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?

[Marshall]

Yes, Learning SQL by Alan Beaulieu,
I can't find my copy at the moment, but if I remember right this book uses MySQL for the examples which would be perfect for you. Both of my recommendations are O'Reilly books.