dbi issues

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

assuming the database connects as it should do (it does) will the followind sql statement work?


$sport = 'basket ball'
$sth5 = $dbh->prepare("SELECT round, teamname, score from trees where 
+sport =\'$sport\' AND year = \'$year\', ORDER BY round DESC") || die 
+"Can't prepare SQL get data statement";

$sth5->execute || die "can't execute sql statement";
[download]

$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]

i want to produce the following data structure after this while loop executes


round 1  team 1 score 1
round 2  team 2 score 2
round 3  team 3 score 3
round 4  team 4 score 4
[download]

so that i can reference these variables from the lists of lists created by the above. and output them into the html as follows.


$compsize = $#list1+1;

  if ($compSize == 7){


    print "<tr>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">image3</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"10%\">&nbsp;</td>\n";
    print "</tr>\n";
    print "<tr>\n";
  print "<td width=\"9%\"><b><P align=center><SELECT name=Team6 size=1
+>\n";
  print "<OPTION selected value=101>$list_of_teamdata[5][1]</OPTION>\n
+";
  print "<OPTION value=102>Business School</OPTION>\n";
  print "<OPTION value=103>Admin/Estates</OPTION>\n";
  print "<OPTION value=104>CEAC </OPTION>\n";
  print "<OPTION value=105>Civ Eng</OPTION>\n";
  print "<OPTION value=106>Combined Honours</OPTION>\n";
  print "<OPTION value=107>CSAM</OPTION>\n";
  print "<OPTION value=108>EEAP</OPTION>\n";
  print "<OPTION value=109>MechEng</OPTION>\n";
  print "<OPTION value=110>ModLang</OPTION>\n";
  print "<OPTION value=111>Vision Sciences</OPTION>\n";
  print "<OPTION value=112>Pharmacy</OPTION>\n";
  print "&nbsp;</SELECT>\n";
  print "</P>\n";
print "</td>\n";
print "</b>\n";
  print "<td width=\"9%\"><b><P align=center><SELECT name=score6 size=
+1>\n";
  print "<OPTION selected value=101>$list_of_teamdata[5][2]</OPTION>\n
+";
  print "<OPTION value=103>0</OPTION>\n";
  print "<OPTION value=103>1</OPTION>\n";
  print "<OPTION value=104>2</OPTION>\n";
  print "<OPTION value=105>3</OPTION>\n";
  print "<OPTION value=106>4</OPTION>\n";
  print "<OPTION value=107>5</OPTION>\n";
  print "<OPTION value=108>6</OPTION>\n";
  print "<OPTION value=109>7</OPTION>\n";
  print "<OPTION value=110>8</OPTION>\n";
  print "<OPTION value=111>9</OPTION>\n";
  print "<OPTION value=112>10</OPTION>\n";
print "<OPTION value=113>11</OPTION>\n";
print "<OPTION value=114>12</OPTION>\n";
print "&nbsp;</SELECT>\n";
print "</P>\n";
print "</td>\n";
print "</b>\n";
print "<td width=\"9%\">image3</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"10%\">&nbsp;</td>\n";
    print "</tr>\n";
    print "<tr>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"10%\">&nbsp;</td>\n";
    print "</tr>\n";
    print "<tr>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"9%\">&nbsp;</td>\n";
    print "<td width=\"10%\">&nbsp;</td>\n";
    print "</tr>\n";
    print "</table>\n";

    print "</body>\n";

    print "</html>\n";
    }
[download]

Comment on dbi issues Select or Download Code

Replies are listed 'Best First'.
Re: dbi issues by lachoy (Parson) on Apr 01, 2001 at 03:31 UTC
I don't know whether you cut-and-pasted or typed it in directly, but some changes are below: # 'strict' is your friend! use strict; ... # be sure to end each statement with a semicolon, and declar # all variables with 'my' my $sport = 'basket ball'; my $year = '1999'; # Egads, escaping quotes is so un-Perlish. DBI # placeholders make life much easier my $sql = qq/ SELECT round, teamname, score FROM trees WHERE sport = ? and year = ? ORDER BY round DESC /; # Turn on DBI error checking -- anything with an error # will immediately trigger 'die' $dbh->{RaiseError} = 1; # Prepare and plug in the values for the placeholders my $sth5 = $dbh->prepare( $sql ); $sth5->execute( $sport, $year ); my $rowcount = 0; my @list_of_teamdata = (); # It's probably easier to do this with hashes, but we'll # stick with your use of arrays. Fetching arrayrefs can # be faster, however, so we'll use those while ( my $row = $sth5->fetchrow_arrayref() ) { my $round = $row->[0]; my $teamname = $row->[1]; my $score = $row->[2]; # Using 'qw' as you had done isn't what you wanted -- # instead, just create an anonymous arrayref and stick # it in the list of results push @list_of_teamdata, [ $round, $teamname, $score ]; $rowcount++; } # Now print out your results -- use 'printf' for nicer # formatting :-) foreach my $info ( @list_of_teamdata ) { print "round $info->[0] team $info->[1] score $info->[2]\n"; } [download] There are more concise ways of doing this, but I wanted to change things just enough to work. We can get to efficiency and readability later :-) Chris M-x auto-bs-mode	[reply] [d/l]

Replies are listed 'Best First'.

Re: dbi issues
by lachoy (Parson) on Apr 01, 2001 at 03:31 UTC

I don't know whether you cut-and-pasted or typed it in directly, but some changes are below:


# 'strict' is your friend!
use strict;

...

# be sure to end each statement with a semicolon, and declar
# all variables with 'my'
my $sport = 'basket ball';
my $year  = '1999';

# Egads, escaping quotes is so un-Perlish. DBI
# placeholders make life much easier
my $sql = qq/ 
   SELECT round, teamname, score 
     FROM trees
    WHERE sport = ? and year = ?
 ORDER BY round DESC
/;

# Turn on DBI error checking -- anything with an error
# will immediately trigger 'die'
$dbh->{RaiseError} = 1;

# Prepare and plug in the values for the placeholders
my $sth5 = $dbh->prepare( $sql );
$sth5->execute( $sport, $year );

my $rowcount = 0;
my @list_of_teamdata = ();

# It's probably easier to do this with hashes, but we'll 
# stick with your use of arrays. Fetching arrayrefs can
# be faster, however, so we'll use those
while ( my $row = $sth5->fetchrow_arrayref() ) {
  my $round    = $row->[0];
  my $teamname = $row->[1];          
  my $score    = $row->[2]; 

  # Using 'qw' as you had done isn't what you wanted  -- 
  # instead, just create an anonymous arrayref and stick
  # it in the list of results         
  push @list_of_teamdata, 
     [ $round, $teamname, $score ];
  $rowcount++;
}

# Now print out your results -- use 'printf' for nicer
# formatting :-)
foreach my $info ( @list_of_teamdata ) {
  print "round $info->[0] team $info->[1] score $info->[2]\n";
}
[download]

There are more concise ways of doing this, but I wanted to change things just enough to work. We can get to efficiency and readability later :-)

Chris
M-x auto-bs-mode

[reply]
[d/l]