If you have all the data in a database, why are you using Perl to sort it? Just query the information per departement in one go:
select college.name, department.name, itstuff.name, ip.address
from college
left join department on college.id = department.college
left join itstuff on department.id = itstuff.department
left join ip on itstuff.id = ip.itstuff
... at least that's how I read your textual description. | [reply]
[d/l] |
Maybe my description is not clear. I'm trying to get every IP that is associated with one particular department, so I can create a list and feed into an external program. Since there are multiple colleges, and each may have multiple departments, I figured that I need to save them into arrays, so I can have a nested loop. I'd be delighted if there are better ways to do it. Thanks!
| [reply] |
| [reply] |
The point about the query that Corion suggested is that you get a single stream of rows returned from the database, each row contains a college name, a department name, an "itstuff" name, and an IP address, and you get one row for every IP address that exists in your IP table.
From that single stream of rows, you can split the results into multiple arrays if you want to (one for each college, or one for each college/department, or one for each college/department/itstuff) -- although it might be better to arrange them into a hash structure -- but in any case, you get to process all the data in a single loop over the query output. Something like this:
my $sql = "select ..."; # query string as suggested by Corion
my $sth = $dbh->prepare( $sql );
$sth->execute;
my $row_data = $sth->fetchall_arrayref; # gets all data from query
my %data_structure;
for my $row ( @$row_data ) {
my ( $college, $dept, $it, $ip ) = @$row;
$data_structure{$college}{$dept}{$it} = $ip;
}
You haven't said yet what you really want to do once you have the data from the tables, but there's a good chance that a hash structure will be more useful than a bunch of arrays.
BTW, you said at the top that you had 5 tables, but you only talk about 4. If there really are 5, you should be able to figure out how to extend Corion's query and the code snippet shown here.
Also, do please look at some of the links provided on the posting form: Writeup Formatting Tips, How do I post a question effectively, and Markup in the Monastery -- it's for your own good. | [reply]
[d/l] |
Take a look at an ORM, such as DBIx::Class.
My criteria for good software:
- Does it work?
- Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
| [reply] |
So this is the final code I've got, but it doesn't read into college_id as it should be. Thanks!
my (@colleges) = ();
while (my @ary = $mysth->fetchrow_array()){
push(@colleges, @ary); # @ary is a reference
$i++;
}
$mysth->finish();
$college_id;
$i = 0;
foreach(@colleges){
# print "colleges: ", @{colleges->$i},"\n";
$college_id=@{colleges->$i};
$i++;
print "college id: ", $college_id, "\n";
$myQuery = "select d.departmentName from Colleges c join Departments d on c.college_id=d.college_id where c.college_id='$college_id'";
$deptsth = &exeQuery($mydbh, $myQuery);
$j=0;
while (my @ary = $deptsth->fetchrow_array()){
push(@departments, @ary); # @ary is a reference
print "departments: ", @{departments->$j}, "\n";
$j++;
}
}
| [reply] |
- You left out the initial query that was used to create "$mysth", so we don't really know what is being pushed onto "@colleges"
- You still haven't learned about using <code> tags; this is getting in the way, and it's getting tiresome.
- You call a function named "exeQuery", but you give us no clue what this is, or what it does (if anything) besides "prepare" and "execute".
- You haven't told us what else you plan to do besides fetch stuff from the database and print things to show that the queries are working; the nature of the "real" task will have an impact on how you set up the query and how you handle the data returned by the query.
Start using <code> tags (edit the posts you've already made to include these tags), tell us more about what you are really trying to accomplish (beyond just getting stuff out of a set of tables), and try to show some evidence that you are able to learn from the advice you are getting (so we know that we aren't wasting our time trying to help you).
| [reply] |