There is an easier way to do what you want. DBI has a bunch of utility methods which replace common actions. In fact, more than half your code can be replaced with a single method call.

You should try replacing this:

$sth5 = $dbh->prepare("SELECT round,department,score FROM  trees WHERE
+ sport = \'$sport\' AND year = \'$year\' ORDER BY round DESC")|| die 
+"couldn't  prepareSQL statement";
$sth5->execute || die "can't execute sql statement";

$rowcount = 0;
while (@row = $sth5->fetchrow_array()) {
  $round = $row[0];
  $teamname = $row[1];          
  $Score    = $row[2];          

  push @list_of_teamdata, qw("$round $teamname $score");
    $rowcount++;

  }
[download]

With this: (Note: code is spread out for better formatting)

my $statement = q{
    SELECT round
         , department
         , score
      FROM trees
     WHERE sport = ?
       AND year = ?
  ORDER BY round DESC   
};

my $list_of_teamdata = $dbh->selectall_arrayref(
  $statement,
  {},
  $sport,
  $year,
);
[download]

Here's what I did:

• Used placeholders in the SQL query, which are the question marks. By placing these in the query we are telling DBI and/or the database that we are going to fill that information in later.

  As you can see, in selectall_arrayref, the $sport and $year are passed in as the third and fourth arguments, in the order corresponding to the SQL query. If you had more placeholders, you could pass in more arguments.

• Replaced the while loop with a single call to selectall_arrayref. The sequence of commands you are doing is a very common way of fetching data out of the database, and was placed in a single method to make our lives easier.

  IMHO, DBI's utility methods are underused and a few minutes familiarizing with them could not only save alot of typing, but simplify your code considerably, because they produce consistent results every time.

Update: as ar0n points out below, you can't initialize the array like that, but it's still possible to get everything into an MD array...trust myself to make that mistake while being able to spot all the April Fools jokes :-/ <?P>

No, you can't do that.

The '@' means whatever is right of '=' should be a list. [] returns a reference to an anonymous array, which is a scalar.

The qw construct is weird indeed. qw allows for a list of barewords separated by whitespace to be considered a list of elements in an array. Putting quotes around them makes it clear he doesn't understand what it does.

What the Anonymous Monk wants is this:

while (@row = $sth5->fetchrow_array) {
    push @list_of_teamdata, [ @row ];
}
[download]

This will push a reference to a copy of the @row array onto @list_of_teamdata.

Then you can access it like this:

print $list_of_teamdata[0][1]; # prints $teamname
print $list_of_teamdata[4][2]; # prints $score
[download]



Oh, and you might want to consider using placeholders (search for 'placeholder' in the DBI manpage)...

[ar0n]

i tried tah and i get a sytax error by the assignment step

i tried that and i get a sytax error by the assignment step

@list_of _teamdata = [][];
[download]

any suggesions??

push @list_of_teamdata, "$round $teamname $score";
[download]

Rather than this:

push @list_of_teamdata, qw("$round $teamname $score");
[download]

cLive ;-)

Found browsing randomly, Anonymous asked months ago:
the sql statement can return multiple rows. can you help?

@list_of _teamdata = [][];
[download]

Among other problems, it appears that he/she couldn't clearly visualize the structure of the data being created by the assignment to what DBI returned. I wonder if Data::Dumper is what Anonymous needed, to see how the data should be structured, in order to access it as he/she seemed to be trying to do ?

#!/usr/bin/perl -w
use strict;
use Data::Dumper;

my @list_of_teamdata;

my $i = 0;


# Anonymous seems to want to coerce the data into a 
# LoL structure similar to this

for (['game1',['lakers'   ,'96'   ],['blazers',  '95'    ]],
     ['game1',['bulls'    ,'100'  ],['lakers' ,  '107'   ]],
     ['game1',['mavericks','90'   ],['bulls'  ,  '86'    ]],
     ['game2',['bulls'    ,'90'   ],['lakers' ,  '88'    ]],){

  # And then put that into an array,
  # (although hashes of lists keyed on 'round' in a hash 
  # keyed on 'year' might be more useful)
  $list_of_teamdata[$i++] = $_;

}

# In such a structure, the data could be accessed this (ugly) way.
for (0..$#list_of_teamdata){
                                              #for each game in the li
+st
  print "$list_of_teamdata[$_][0]:\t",        #print round
        "$list_of_teamdata[$_][1]->[0]: ",    #get hometeam 
        "$list_of_teamdata[$_][1]->[1]\t",    #and scores
        "$list_of_teamdata[$_][2]->[0]: ",    #get visitor 
        "$list_of_teamdata[$_][2]->[1]\n\n";  #and scores  

}

# but he/she may be equally as unclear about the data's structure
#                           as he/she was about how to create the 
#                           structure needed
# that's why Data::Dumper is needed;

print Dumper([@list_of_teamdata]);
[download]

This is a repost. The original: dbi issues. Why repost the same reply? :-)

Chris
M-x auto-bs-mode