I am having trouble correctly updating a database. . . here's the code:


$nada = "0";
&OPENdb;
$table = "info";
$checkit = "SELECT * FROM $table WHERE id_clicks > '$nada'";
$results = $dbh->prepare($checkit);
$results->execute();
while ($ref = $results->fetchrow_hashref()) {
$clicks = $ref->{'id_clicks'};
$weekly = $ref->{'weekly_id_clicks'};
$new_week = $clicks + $weekly;
$table = "info";
$change = "UPDATE $table SET id_clicks='$nada', weekly_id_clicks='$new_week' WHERE id_clicks >'$nada'";
$dbh->do($change);}
&CLOSEdb;


If I take the $change part off and print the info, (like the $clicks, weekly, and $new_week )it prints all the data out. . . but when I go to update the database, all the $new_week values are the same. . . Someone please help!

I am having trouble correctly updating a database. . . here's the code:


$nada = "0";
&OPENdb;
$table = "info";
$checkit = "SELECT * FROM $table WHERE id_clicks > '$nada'";
$results = $dbh->prepare($checkit);
$results->execute();
while ($ref = $results->fetchrow_hashref()) {
$clicks = $ref->{'id_clicks'};
$weekly = $ref->{'weekly_id_clicks'};
$new_week = $clicks + $weekly;
$table = "info";
$change = "UPDATE $table SET id_clicks='$nada', weekly_id_clicks='$new_week' WHERE id_clicks >'$nada'";
$dbh->do($change);}
&CLOSEdb;

If I take the $change part off and print the info, (like the $clicks, weekly, and $new_week )it prints all the data out. . . but when I go to update the database, all the $new_week values are the same. . . Someone please help!

try it like this, remove the $change line in your code :


$dbh->do("UPDATE $table SET id_clicks='$nada', weekly_id_clicks='$new_week' WHERE id_clicks >$nada");
}

(do not use any quote marks around $nada at the end of the line)

and see if that helps

That did not work. . .they still come out the same. I don't get it cuz when I print it out it works, but setting it into the database it only has one value. . . .still clueless

Something like this should do the trick:


my $nada = 0;
&OPENdb;

my $table = 'info';
my $sql_1 = qq~SELECT * FROM $table WHERE id_clicks > ?~;
my $results = $dbh->prepare($sql_1);
$results->execute($nada);

# Prepare the SQL only once outside of your loop. This will
# save you some overehad. Also, check the DBI docks for placeholders (?),
# it makes your life much easier when writing SQL statements in perl.
my $sql_2 = qq~UPDATE $table
SET id_clicks = ?, weekly_id_clicks = ?
WHERE id_clicks > ?~;
my $sth = $dbh->prepare($sql_2);

while (my $ref = $results->fetchrow_hashref()) {
my $clicks = $ref->{'id_clicks'};
my $weekly = $ref->{'weekly_id_clicks'};
my $new_week = $clicks + $weekly;
# Execute your query with the bind values
$sth->execute($clicks, $weekly, $new_week);
}
&CLOSEdb;


Check into placeholders and bind values. That will make writing your queries a little easier. Here's a link to that section in the DBI pod: http://search.cpan.org/~timb/DBI-1.38/DBI.pm#Placeholders_and_Bind_Values. It takes a bit to get used to them at first but it's worth it. Also, see me comments in the script for a little more detail.

Check out the perl quote operators too: http://www.perldoc.com/perl5.8.0/pod/func/q.html. Not much of an explanation there but q{tetxt} single quotes the text in side the {}. That satement above would be 'text'. The qq{text} is for double quotes: "text". The {} is arbitrary; you can use anything as long as it's not in the string to be quoted: () or [] or !! or $$ and the list goes on. I often ues the ~ in my queries: qq~SELECT foo FROM bar~;. This eliminates the need to escape any quote chars in your queries.

~Charlie