script problems1

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

i have a script to take the input from 13 drop down boxes of team names and 13 boxes of their scores over a competition tree. if the same teamname is entered then the respective score variables updates the score where that team is in the database. i set up a hash to hold the variables and it seems like there are no values being passed to the variables. the code for the html source for a pair of variables is below.


print "<td width=\"9%\"><b><P align=center><SELECT name=team1 size=1>\
+n";
print "<OPTION selected>$list_of_teamdata[0][1]</OPTION>\n";
print "<OPTION>Business School</OPTION>\n";
print "<OPTION>Admin/Estates</OPTION>\n";
print "<OPTION>CEAC </OPTION>\n";
print "<OPTION>Civ Eng</OPTION>\n";
print "<OPTION>Combined Honours</OPTION>\n";
print "<OPTION>CSAM</OPTION>\n";
print "<OPTION>EEAP</OPTION>\n";
print "<OPTION>MechEng</OPTION>\n";
print "<OPTION>ModLang</OPTION>\n";
print "<OPTION>Vision Sciences</OPTION>\n";
print "<OPTION>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=score1 size=1>
+\n";
print "<OPTION selected>$list_of_teamdata[0][2]</OPTION>\n";
print "<OPTION>0</OPTION>\n";
print "<OPTION>1</OPTION>\n";
print "<OPTION>2</OPTION>\n";
print "<OPTION>3</OPTION>\n";
print "<OPTION>4</OPTION>\n";
print "<OPTION>5</OPTION>\n";
print "<OPTION>6</OPTION>\n";
print "<OPTION>7</OPTION>\n";
print "<OPTION>8</OPTION>\n";
print "<OPTION>9</OPTION>\n";
print "<OPTION>10</OPTION>\n";
print "<OPTION>11</OPTION>\n";
print "<OPTION>12</OPTION>\n";
print "&nbsp;</SELECT>\n";
print "</P>\n";
print "</td>\n";
print "</b>\n";
[download]

the form works the submit works there are no syntax errors comming up from the debugger.


#!/usr/local/bin/perl 


use DBI;
use CGI qw(:standard);
print header;
print start_html;




$ENV {'ORACLE_HOME'}= '/oracle/u01';

$host="??";
$sid="???";
$username="?????";
$password="??????";


$sport = 'basket ball';



$dbh = DBI->connect( "dbi:Oracle:host=$host;sid=$sid",$username, $pass
+word)|| die "Can't connect to Oracle";
 

$sth = $dbh->prepare("SELECT year from trees WHERE sport = \'$sport\' 
+AND year = \'$year\'")|| die "couldn't  prepareSQL statement";
$sth->execute || die "can't execute sql statement";

my @row;

while (@row = $sth->fetchrow_array()) {
  $year = $row[0];
}



$sth2 = $dbh->prepare("SELECT teamno from  compsize WHERE sport = \'$s
+port\' AND year = \'$year\'")|| die "couldn't  prepareSQL statement";

$sth2->execute || die "can't execute sql statement";


my @row2;

while (@row2 = $sth2->fetchrow_array()) {
  $compsize = $row2[0];
}



if ($compsize == 7){
my $cgi = new CGI;
my %teams;
$teams{"team$_"} = $cgi->param("team$_") for 1..($compsize * 2) - 1;
my %scores;
$scores{"score$_"} = $cgi->param("score$_") for 1..($compsize * 2) - 1
+;
}



print "<table border=1> \n";
print "<tr> \n"; 
print "<th> List of info </th> \n"; 
print "<tr> \n"; 

for $team(keys %teams){
print "<tr> \n";
print "<td>$team </td> \n";
print "</tr> \n";
}

for $team(keys %teams){
print "<tr> \n";
print "<td>$team</td> \n";
print "</tr> \n";

}


for $score(keys %scores){
print "<tr> \n";
print "<td>$score </td> \n";
print "</tr> \n";

}




my $teampos;
for $i (1..($compsize * 2) - 1 ){

for $team(keys %teams){

    $teampos = $I;

my $row1 = $dbh->do("UPDATE trees SET department = /'$team/', WHERE ro
+und = /'$teampos/'") || die "Can't execute SQL update statement 1";
my $row2 = $dbh->do("UPDATE results SET score = /'$score/', WHERE year
+ = /'$year/' AND sport = /'$sport/' AND department = /'$team/'") || d
+ie "Can't execute SQL update statement 2";

}

for $score (keys %scores){

    $teampos = $I;

my $row3 = $dbh->do("UPDATE trees SET score = /'$score/', WHERE round 
+= /'$teampos/'") || die "Can't execute SQL update statement 3";

}


}

print "<tr> \n";
print "<td>all done </td> \n";
print "</tr> \n";


print "</table>";
print "</body>";
print "</html>";
[download]

i know the oracle statements are just about right. can u help???

Comment on script problems1 Select or Download Code

Replies are listed 'Best First'.
Re: script problems1 by Masem (Monsignor) on Apr 04, 2001 at 17:10 UTC
This would easily be solved (I believe) if you `use strict;` and -w in the first line. Namely: `$teampos = $I;` [download] prior to your select statements seems highly questionable: you don't define $I, you define $i (case is sensitive). Since $I is undefined, it's not being set. Dr. Michael K. Neylon - mneylon-pm@masemware.com \|\| "You've left the lens cap of your mind on again, Pinky" - The Brain	[reply] [d/l] [select]
Re: script problems1 by rchiav (Deacon) on Apr 04, 2001 at 17:21 UTC
The first thing that I notice is that you don't have `<FORM></FORM>` tags listed here. If they're there, good.. but if not, that is probably part of your problem. You also aren't setting the `<option>` "value". The text that is shown isn't actually submitted. There's a hidden value that is actually submitted. It can be the same thing as the text shown if you like... `print "<OPTION value="0">0</OPTION>\n"; print "<OPTION value="1">1</OPTION>\n"; print "<OPTION value="2">2</OPTION>\n"; print "<OPTION value="3">3</OPTION>\n"; print "<OPTION value="4">4</OPTION>\n"; print "<OPTION value="5">5</OPTION>\n"; print "<OPTION value="6">6</OPTION>\n";` [download] Also as a style tip, I'd personally put all the values in an array and then iterate through them. It will make it a lot easier to update... instead of writing out each line. Hope this helps.. Rich	[reply] [d/l] [select]
Re: script problems1 by arturo (Vicar) on Apr 04, 2001 at 18:36 UTC
First, `use strict` and enable warnings (yes, again with that ... it will help you catch errors such as "unintialized value" errors (which, from your description, is highly likely to be the problem here), and keep you from having to come back to us as often as you are). It is not enough to sprinkle your code with `my` declarations, you need to enable the checks! Second, let me comment on your DB code: `$sth = $dbh->prepare("SELECT year from trees WHERE sport = \'$sport\' +AND year = + \'$year\'") \|\| die "couldn't prepareSQL statement"; $sth->execute \|\| die "can't execute sql statement"; my @row; while (@row = $sth->fetchrow_array()) { $year = $row[0]; }` [download] Learn to use placeholders: that `prepare` is more securely written as `$sth = $dbh->prepare('SELECT year FROM trees WHERE sport = ? AND year = ?' );` [download] When you do that, you put the variables that are going into the places that are being held into the `execute` call: `$sth->execute($sport, $year) or die "Can't execute: ". $db->errstr(). "\n";` [download] Finally, since you know you're only snagging one datum, that `while` loop (which appears in more than one spot here) has the potential to confuse. Just do `my ($year) = $sth->fetchrow();` [download] and follow that up with a `$sth->finish`. OK, now for your hashes: you could do both hashes in one go: `for (1.. $compsize 2 -1) { $teams{"team$_"} = $cgi->param("team$_"); $scores{"score$_"} = $cgi->param("score$_"); }` [download] Now, if the hashes aren't populated the way you think they should be, the most likely thing that's going on is that you're not getting parameters of the right form: so check that out first, put in some debugging code such as: `print "<ul>\n"; foreach ( $cgi->param) { print "<li>$_: '", $cgi->param($_) ,"'\n"; } print "</ul>\n";` [download] This will tell you whether you're getting the input you think you're getting. Philosophy can be made out of anything. Or less* -- Jerry A. Fodor	[reply] [d/l] [select]
Re: script problems1 by Anonymous Monk on Apr 04, 2001 at 18:10 UTC
i notice the $I problem and changed it i do have the form tags. and when i put the "value" into the option box the tree just out puts "Value=0>0" instead of "0". i'm still getting nothing into the database though. any more ideas???	[reply]
Re: Re: script problems1 by rchiav (Deacon) on Apr 04, 2001 at 19:20 UTC
this may be an over symplistic response here but is "value" inside your OPTION tag? Like.. `<OPTION VALUE="0"> 0 </OPTION>` [download] or `<OPTION> VALUE="0"> 0 </OPTION>` [download] The value parameter needs to be within the tag. Rich	[reply] [d/l] [select]