while loop just won't work

Here is the code:

while (my @ary = $sth->fetchrow_array ())

{
foreach (my $i=0; $i < $sth->{NUM_OF_FIELDS}; $i++)
{
print "$ary[$i] ";
$dbh->do("INSERT INTO terms (table_fk, term_fk) VALUES (7, $ary[$i])");

}


}

##print "$ary[$i] "; prints out the values 7 8 9, but it won't perform the INSERT.
i would like to know if you can perform an sql statement within a while loop???????

i think there is something wrong with your query if the code is printing out the numbers.

you can put up die statement in or with query statement to see if it is succeeding. otherwise, it should work.

jd

you are right...the query is the problem

but what could be wrong with the query. the syntax looks fine and works fine outside of the loop.

but when i place it out side the loop it only inserts the final number

eg. the loop pull the data -- 7 8 9

and then would insert only 9

how comes $ary[$i] is not available outside the loop???

thanks for your input. much appreciated.

yeah,


u have error in query.
dbh->do("INSERT INTO terms (table_fk, term_fk) VALUES (7, $ary[$i])");


you would have to put $ary[$i] in single quotes if its not number. so put it like

dbh->do("INSERT INTO terms (table_fk, term_fk) VALUES (7, '$ary[$i])'");


also make sure that $ary[$i] doesnt not contain characters such as " or ' otherwise you would need to escape them using \.

to do this,

$ary[$i] =~ s/\'/\\\'/gi;
$ary[$i] =~ s/\"/\\\"/gi;

these 2 statements would escapte ' and " with \' so that wont produce error in ur query.

hope that helps,
jd

no...that didn't work either.

still getting an error.

what confusses me is that the query works fine outside the loop, but when placed there it will not insert all three terms -- just the last one.

thanks for all your help again.

your website looks great!

would u plz put up error message that you get ?

and yeah, thx for checking my site and signing, i think u signed lastly , right ??

jd

p.s. : devshed forum is one of my top referer !!!

I added this after my sql statement:
|| die "do: $$dbh: $DBI::errstr";

and I get this error message:
Not a SCALAR reference at g.cgi line 112.

A couple of things-

1) You need to start checking the return status of your DBI queries. When a query fails, it will tell you why. The die() statement is the error checking part.



2)You're making your loops a little too complicated. Learn how to use foreach, and $_, the default scalar in a loop like this.

3) I really like the "prepare-execute-finish" cycle- It's also more efficient- you prepare the statement once, and then it's reused throughout your entire loop. With a do(), it's reprepared on each iteration. This is partially a preference thing, but I think it's better to separate it out a little more.

4) Learn to use placeholders- the "?" in the prepare statement. These will automatically escape your values, saving you massive time and from using kludgy regexes which are bound to break in some circumstances.

5) If you use/figure out all this stuff, your DBI programming will be much stabler, faster, and easier to debug.

Good luck!

A couple of other problems-

1) fetchrow_array does exactly what it says- it fetches a row from your query as an array. It doesn't return an array of all the records. What you're doing is getting an array _of_the_first_row_of_records and then only passing that to your for loop. This is why you're getting the results you are.

The correct way to use fetchrow_array:

i want to thank you guys for all your help.

i have printed off your responses for future reference and have learned alot today from you guys.

i still get an error:
Couldn't prepare insert!: Commands out of sync; You can't run this command now at g.cgi line 104

i've come to the conclusion that you cannot perform an insert statement within a while loop.

thanks again

That is the wrong conclusion. I do it all the time. I have dozens of production scripts doing it.

There's something wrong with your code. Post your entire code and I'll take a look at it.

the SQL syntax works fine within the mysql module

thank you so much even for just looking at the code.

I was unable to attach a .cgi file or a .txt file......

#!/usr/bin/perl -w

print "Content-type:text/html\n\n";

read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs)
{
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$FORM{$name} = $value;
}

print <<EndHTML;
<html><head><title>Form Output</title></head>
<body><font color = cyan face = arial>
<h2>Results from FORM post</h2>
</font></body>
EndHTML
;

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=database_name;host=host_name", "user_name", "password");

use diagnostics;

use CGI::Carp qw(fatalsToBrowser);

# **************************************
# I have 36 tables -- one for each letter of the alphabet a-z
# and tables for 0-9
# This particular code is for TABLE G that only contains terms
# starting with the letter g.
# I have a table called TERMS that is a mask over all 36 tables
# contain all the terms.
# I created G_TEMP to pull out all the terms from TABLE G to get
# a snap shot before any new inserts
# **************************************

$dbh->do("CREATE TABLE g_temp(
table_fk INT(11),
term_fk INT(11))");
$dbh->do("INSERT INTO g_temp
SELECT table_fk, term_fk
FROM terms
WHERE table_fk = 7");

foreach $key (keys(%FORM))
{
my $val = $FORM{$key};
$dbh->do("INSERT INTO g (name) VALUES (UCASE('$val'))");
$dbh->do("DELETE FROM g WHERE name NOT LIKE 'G%'");
}

my $sth = $dbh->prepare("SELECT pk, name FROM g");
$sth->{"mysql_use_result"} = 1;
$sth->execute();
my $numFields = $sth->{'NUM_OF_FIELDS'};
my $names = $sth->{'NAME'};

# ***********************************************
# The following code outputs all the terms found
# in the TABLE G of the database into a selection
# box.
# repeat.pl contains a fake case statement to
# determine if the term already exists in TABLE G
# and if it does to pre-select it.
# ***********************************************


print '<select name=Choice>';
while (my $ref = $sth->fetchrow_arrayref)
{
$pk = $ref->[0];
$name = $ref->[1];

require 'repeat.pl'; # include code from library repeat.pl
&repeatedcode(); # call subroutine from repeat.pl
}
print '</select><br><br>';

# ***********************************************
# The following code is my problem
# I create the join to get a list of the new
# inserts. It contains values - 7 8 9 (3 rows)
# I want these new inserts (g.pk) to be inputted
# into TABLE TERMS (term_fk) and the value 7
# into TABLE TERMS (table_fk)
# ***********************************************

my $sth = $dbh->prepare("SELECT g.pk
FROM g LEFT JOIN g_temp gt
ON g.pk = gt.term_fk
WHERE gt.term_fk IS NULL");
$sth->{"mysql_use_result"} = 1;
$sth->execute();
my $numFields = $sth->{'NUM_OF_FIELDS'};

while ($arrayref = $sth->fetchrow_arrayref)
{
$termfk = $$arrayref[$_];
print "$termfk "; ## this prints out 7
$dbh->do("INSERT INTO terms (table_fk, term_fk) VALUES (7, $termfk)") or die("Couldn't execute insert!: ".$dbh->errstr());
## this gives me the following error:
Couldn't execute insert!: Commands out of sync; You can't run this command now at g.cgi line 107.
}

$dbh->do("DROP TABLE g_temp");

$dbh->disconnect();

print <<EndFoot;
<form>
<input type="button" onClick="parent.location='/glossary.html'" value="Glossary">
<input type="button" onClick="parent.location='/g.html'" value="Entry">
</form>
EndFoot
;