Find odd/even elements of array

[perlPractioner]

Hi Monks, Not sure how to approach the following issue. I am retrieving values from a DB using fetchrow. I am pushing the values that are being returned into an array. I will then use the values from this array to issue another query to a different DB. The code looks something like this:

my $queryID = "select id, name from studentTable";
my $sth_queryID = $dbh->prepare($queryID);
$sth_queryID->execute();

my @studentInfo;
while (my @row = $sth_queryID->fetchrow){
   push @studentInfo, @row;
}

$sth_queryID->finish;

@studentInfo will look something like this: @studentInfo = ("001","John","002","Mary","003","Tom") Now, I want to use each id (each of the odd indices within @studentInfo) to issue another query. I will also need to use the corresponding name of that id to insert as a row of a HTML table. So, the code might resemble the following:

foreach (@studentInfo_with_an_odd_index){
    my $studentID = $_;
    my $queryGrade = "select id from grades where 
    id = '$studentID' and grade = 'A'";
    $my sth_queryGrade->prepare($queryGrade);
    $sth_queryGrade->execute();

    while (my @g = $sth_queryGrade->fetchrow){
        print "<TR>";
        print "<TD>$g[0]</TD>";
        print "<TD>$student_name_for_this_id</TD>";
        print "</TR>";
    }
}

$sth_queryGrade->finish;
$dbh->disconnect;

Any ideas on how I can find @studentInfo_with_an_odd_index and $student_name_for_this_id from the above code? Thanks in advance for your help monks.

[CountZero]

And that join could be as follows:

select name, grade from studentTable join grades where studentTable.id
+ = grades.id and grade = 'A';

[keszler]

(I'm assuming that the two databases aren't linked, i.e. you can't join their tables. I'm also assuming that you're using DBI despite the mention of a fetchrow function. (DBI has fetchrow_arrayref, fetchrow_array, and fetchrow_hashref, but no just plain fetchrow.))

If you don't need the even ids from studentTable why not add a where clause to return only the odds?

If all you're doing with the studentTable in the 1st DB is loading an array why not use fetchall_arrayref?

my $studentInfo = $sth_queryID->fetchall_arrayref;

If you do need the even ids elsewhere you can easily skip them when querying the grades in the 2nd DB like this:

for my $studentInfo_row (@{$studentInfo}) {
  next unless $studentInfo_row->[0] % 2;
  ...
}

You could also use a grep:

foreach my $studentInfo_row ( grep { $_->[0] % 2 } @{$studentInfo}) {
  ...
}

update: fixed arrayref typos

[AnomalousMonk]

A pure-database approach seems best, but here's an array manipulation approach that's almost straight from the List::MoreUtils docs:

>perl -wMstrict -le
"use List::MoreUtils qw(part);
 ;;
 my @si = qw(001 John  002 Mary  003 Tom);
 ;;
 my $i = 0;
 my @parts = part { $i++ % 2 } @si;
 print qq{even index elements: @{ $parts[0] }};
 print qq{ odd index elements: @{ $parts[1] }};
"
even index elements: 001 002 003
 odd index elements: John Mary Tom

[johngg]

Perhaps you could turn the array containing a flat list of ids and names into an AoH and use grep to pull out the odd ids when building it.

knoppix@Microknoppix:~$ perl -MData::Dumper -E '
> @studentInfo = qw{
>    001 John
>    002 Mary
>    003 Tom
>    };
> @oddStudents =
>    map  { { id => $_->[ 0 ], name => $_->[ 1 ] } }
>    grep { $_->[ 0 ] % 2 }
>    sub  {
>       push @ret, [ shift, shift ] while @_;
>       return @ret;
>       }->( @studentInfo );
> say Data::Dumper->Dumpxs(
>    [ \ @oddStudents ],
>    [ qw{ *oddStudents } ]
>    );'
@oddStudents = (
                 {
                   'name' => 'John',
                   'id' => '001'
                 },
                 {
                   'name' => 'Tom',
                   'id' => '003'
                 }
               );

knoppix@Microknoppix:~$

I hope this is helpful.

[RichardK]

Why not get the database to do it with a table join?

Then you'd only need one query ;)

[choroba]

You might use array of arrays for you data:

push @studentInfo, [@row];

Then, $studentInfo[$x][0] contains the ID and $studentIfno[$x][1] the name. If the IDs are uniqe, you may profit from Perl's hashes, so build your structure in a different way:

my %studentInfo;
while (my @row = $sth_queryID->fetchrow){
   $studentInfo{$row[0]} = $row[1];
}

In this case, $studentInfo{$id} contains the name (and should be renamed to %id_to_name or similar).
Update: see perldsc for details.

[dsheroh]

The simplest solution, assuming unique student IDs, is so simple and somewhat idiosyncratic to Perl that my reflex is to suspect this of being a homework question... That solution is "turn your list into a hash":

my @student_info = ("001","John","002","Mary","003","Tom");
my %hash = @student_info;

say join ", ", sort keys %hash;

As a side note, you really don't want to get into the habit of sticking variable data directly into your SQL statements. Use placeholders instead.

Taking all of the above into account, your posted code becomes something like:

my @student_info = ("001","John","002","Mary","003","Tom");
my %name_hash = @student_info;

my $grade_sth = $dbh->prepare("select id from grades where id = ? and 
+grade = 'A'");

for my $student_id (sort keys %name_hash){
    $grade_sth->execute($student_id);
    my $student_name = $name_hash{$student_id};

    # do output here
}