It looks to me that you cut & pasted a bit to place the code in this node, but just to double check... You should only call ->disconnect() once. In DBI, you do...

$dbh = DBI->connect();

$dbh->do();
$sth = $dbh->prepare();
$sth->execute();
# And other methods for dealing with your data

$dbh->commit() or $dbh->rollback();

$dbh->disconnect();
[download]

In the code snippets you provided, you have a ->disconnect() after every ->do(). The other thing is that you seem to be using undef to mean a NULL. I am not sure if you can do that with placeholders. Just for kicks, try using '' (empty string) instead of undef and let us know.

Update: I just noticed that I gave out bad advice. The undef does not stand for a NULL but for an empty \%attr. Sorry for that.

Best regards

-lem, but some call me fokat

Ok, I replaced "undef" with "''"

It now looks like this... I'll show a little more code:

if (defined($in{new_aff}) && $in{new_aff} == 1) { # Checks to see if
        # they are adding our Affiliate Program to their registration
    if ($in{tax_id} ne "") { # Checks to see if they
        # added a tax id number for when we pay them
        $dbh->do (qq{ UPDATE reg_users SET tax_id = ? WHERE username =
+ ? },
                    '', $in{tax_id}, $username);
    }
        # Ok, these were required fields IF they checked the box which
+ made this
        # execute from above
    $dbh->do (qq{ UPDATE reg_users SET aff_url = ? WHERE username = ? 
+},
                '', $in{aff_url}, $username);
        # the one above added their Website for us to view
    $dbh->do (qq{ UPDATE reg_users SET aff = ?, aff_su_date = ? WHERE 
+username = ? },
                '', "yes", $formated_date, $username);
        # This one adds the affiliate program to the reg_users table. 
+it basically just
        # turns it 'on'
}
    $dbh->do (qq{ UPDATE reg_users SET html_email = ? WHERE username =
+ ? },
                '', $in{html_email}, $username);
        # this one just above was in a different part of the form, I j
+ust added it.
        # It asks them if they support HTML email for our eMail update
+s, they can
        # turn it off and on in the "registered users" manager I built
+.

    $dbh->disconnect();
        # Ok, now this finally disconnects.
[download]


It still does not work. It does not update these columns: html_email, tax_id and aff_url

The rest are updated (aff and aff_su_date)

Since those two get updated and the rest do not, I'm very lost. Like I said the $in{var} variables that are being called do have values. I don't know why MySQL is not updating them in the SQL syntax Perl is giving it.

Still lost.

Any other ideas?

Thx,
Richard.

Take a look at the ->trace() method. It allows your script to generate copious amounts of debug information related to DBI. Also, try moving this script to a standalone (as opposed to a CGI) so that you can easily check and troubleshoot.

Also please be aware that I made a mistake in my earlier post. You were indeed correct when using undef in the ->do() statements. Probably it was the time...

Best regards

-lem, but some call me fokat

In addition to fokat's remarks on not disconnecting, I see some other problems.

• Don't quote the table and column names. You appear to be using backticks on them.
• Your undef value for the second argument of $dbh->do should instead be an empty hash reference. Replace 'undef' with {}.

Update: Ok, time for some debug-fu. If you are not already doing so, open the DBI connection with the {RaiseError=>1} attribute. That will cause your program to die with an error message whenever MySQL access causes an error. Scatter some print statements inside your conditionals to mark what code is reached.

After Compline,
Zaxo

Ok. I've changed the undefs to '' and then to {}.
It now looks like this:



if (defined($in{new_aff}) && $in{new_aff} == 1) {
            if ($in{tax_id} ne "") {
                $dbh->do (qq{ UPDATE reg_users SET tax_id = ? WHERE us
+ername = ? },
                            {}, $in{tax_id}, $username);
            }
            $dbh->do (qq{ UPDATE reg_users SET aff_url = ? WHERE usern
+ame = ? },
                        {}, $in{aff_url}, $username);

            $dbh->do (qq{ UPDATE reg_users SET aff = ?, aff_su_date = 
+? WHERE username = ? },
                        {}, "yes", $formated_date, $username);
        }
        $dbh->do (qq{ UPDATE reg_users SET html_email = ? WHERE userna
+me = ? },
                    {}, $in{html_email}, $username);

        $dbh->disconnect();
[download]


Same Effect. It updates ONLY the columns: "aff" and "aff_su_date"!

Any other ideas?

Thx,
Richard.

How about some error handling? Why are we asking this person to turn on DBI's tracing before he adds any normal error handling? Do something like the following with your database requests to see if the database is returning any errors.

$dbh->do(qq{sql statement}) || die $dbh->errstr;
[download]

---
print map { my ($m)=1<<hex($_)&11?' ':'';
$m.=substr('AHJPacehklnorstu',hex($_),1) }
split //,'2fde0abe76c36c914586c';
[download]

Ok, Nothing has worked yet. I do have the RaiseError set to 1 in my module I created.

My Databases all work, and update correctly with that module, this is the only problem I'm having.

I'm getting even more confused now :o(

Why would everything else work, but those two fields not?
Is there a problem with MySQL using those two table names?(tax_id && aff_url)
Here is the part of my code that is not working completely:


if (defined($in{new_aff}) && $in{new_aff} == 1) {
    if ($in{tax_id} ne "") {
        $dbh->do (qq{ UPDATE reg_users SET tax_id = ? WHERE username =
+ ? },
                    undef, $in{tax_id}, $username) || error("Error on 
+MySQL Update: $DBI::errstr");
    }
    $dbh->do (qq{ UPDATE reg_users SET aff_url = ? WHERE username = ? 
+},
                undef, $in{aff_url}, $username) || error("Error on MyS
+QL Update: $DBI::errstr");

    $dbh->do (qq{ UPDATE reg_users SET aff = ?, aff_su_date = ? WHERE 
+username = ? },
                undef, "yes", $formated_date, $username) || error("Err
+or on MySQL Update: $DBI::errstr");
}
    $dbh->do (qq{ UPDATE reg_users SET html_email = ? WHERE username =
+ ? },
                undef, $in{html_email}, $username) || error("Error on 
+MySQL Update: $DBI::errstr");

    $dbh->disconnect();
[download]


I added the || error for each of them. I never errors out.

any other Ideas?

Should I try that trace idea now?

Thx,
Richard.

Where is $username coming from? Did you read it from a file and forget to chomp? Do other queries using this value in a where clause work?

---
print map { my ($m)=1<<hex($_)&11?' ':'';
$m.=substr('AHJPacehklnorstu',hex($_),1) }
split //,'2fde0abe76c36c914586c';
[download]

Have you checked the value of $in{tax_id}?

e.g.

if ($in{tax_id} ne "") {
   warn "updating $username tax to $in{tax_id}";
   $dbh->do (qq{ UPDATE reg_users SET tax_id = ? WHERE username = ? },
                    undef, $in{tax_id}, $username) || error("Error on 
+MySQL Update: $DBI::errstr");
 } else {
    warn "Not updating tax_id for $username (\$in{new_aff} == $in{new_
+aff})";
 }
[download]

If you don't see a warning then the code isn't be executed for some reason. If you do see a warning, then you'll know exactly what's going on.

rdfield

If you are going to use placeholders you might try prepare()ing and then executing queries rather than using do()

my $query = "UPDATE sometable SET someRecord=? WHERE otherRecord=?";
$sth = $dbh->prepare($query);
$sth->execute('value1','value2') or die $sth->errstr();
[download]

This seems to be the generally more accepted way of doing things if you are using placeholders.

Also I don't think you want single quotes around table and column names

Hope that helps
Chris

This is sooooo strange...
I changed $in{} to param('')

like this: $in{tax_id} to param('tax_id') since I'm using cgi-lib for the parsing into %in and :standard for the html creating(textfield(-name ...ect.)

So even though $in{tax_id} did contain a value, it did not work. the others all work, but not those three feilds. Changing it to param('') worked!

If they both contain a value, what would make one work and not the other?

I'm very confused by this. I have at least 15 fields in the update form in the profile form, all of them work with the $in{} method. only those few did not work (html_email, aff_url & tax_id) Every other one, worked, just like it should.

Any Guru's out there that can help me understand why that is?
I'd be very thankful!

Thx,
Richard.