Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Help with MySQL SELECT into multidimensional array

by btongeorge (Initiate)
on Dec 01, 2011 at 22:29 UTC ( #941185=perlquestion: print w/ replies, xml ) Need Help??
btongeorge has asked for the wisdom of the Perl Monks concerning the following question:

Greetings Monks,

I'm hoping you can help advise me on my most recent Perl/MySQL related query. I seek enlightenment...

I need to extract some data from a MySQL DB and process it in Perl. The data relates to client accounts, I will run a series of queries that will extract data relating to these accounts. What I want to be able to do is run a first query to populate an array with the list of clients, then run subsequent queries to create additional columns. The secondary queries will only generate values for some clients, so the array should have zero values in the array elements that haven't been populated.

I have written my SQL and from the many posts that I have read, I need to use DBI or DBIx::Simple to get the data into the array. Beyond that, I am in the dark.

Would appreciate any pointers that the wise monks could give me, I am in need of wisdom.

Thanks in advance Brother George

Comment on Help with MySQL SELECT into multidimensional array
Re: Help with MySQL SELECT into multidimensional array
by davies (Vicar) on Dec 01, 2011 at 22:42 UTC

    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

Re: Help with MySQL SELECT into multidimensional array
by roboticus (Canon) on Dec 01, 2011 at 23:27 UTC

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

    ...roboticus

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

Re: Help with MySQL SELECT into multidimensional array
by ww (Bishop) on Dec 01, 2011 at 23:30 UTC
    Your next step, if not already taken, should be to read the documentation for the modules you mentioned: eg, perldoc modulename at your CLI.
Re: Help with MySQL SELECT into multidimensional array
by TJPride (Pilgrim) on Dec 02, 2011 at 03:11 UTC
    This is untested except for syntax errors, but should work in theory - it contains snippets of code from working projects.

    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 }

    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.

Re: Help with MySQL SELECT into multidimensional array
by Marshall (Prior) on Dec 02, 2011 at 04:48 UTC
    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}, );
    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.

      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);
        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.
Re: Help with MySQL SELECT into multidimensional array
by runrig (Abbot) on Dec 02, 2011 at 17:36 UTC
    Any reason you couldn't do it in one query with outer joins?

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://941185]
Approved by davies
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (8)
As of 2014-08-29 01:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (275 votes), past polls