I have a database that include 5 tables, and they are related to each other through foreign key relations. The root is called colleges. There are multiple colleges, and each college has 1+ departments, each department has 1+ IT stuff, each IT stuff owns 1+ IP addresses. I have designed the database hierarchically as: Colleges->Departments->ITstuff->IP. Each child has a foreign that points to it's parent.
My goal is to read IP addresses of each department and scan them, then send a report to all IT stuff to the departments as well as cc to the college IT director. My strategy is to read all college (using fetchrow_arrayref or whatever works), and save it in an array. Then for each college, search all departments, and save into another array. Next, search each department, and find all IP assoicated to the department and save into a list for scan. In the code I have come up so far, I'm stuck at where highlighted in red. Thanks in advance.
$myQuery = "select college_id from Colleges";
$mysth = &excuteQuery($mydbh, $myQuery);
#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 = &excuteQuery($mydbh, $myQuery);
$j=0;
while (my @ary = $deptsth->fetchrow_array()){
push(@departments, [@ary]); # [@ary] is a reference
print "departments: ", @{departments->[$j]}, "\n";
$j++;
}
}$myQuery = "select college_id from Colleges";
$mysth = &excuteQuery($mydbh, $myQuery);
#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 = &excuteQuery($mydbh, $myQuery);
$j=0;
while (my @ary = $deptsth->fetchrow_array()){
push(@departments, [@ary]); # [@ary] is a reference
print "departments: ", @{departments->[$j]}, "\n";
$j++;
}
}