Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Stuck on updating a field

Quote Reply
Stuck on updating a field
I'm working on a validate_records conversion and I'm stuck trying to change the Validate field to Yes. I've got it so it deletes a record and sends and email, but need some help getting it to validate a record. This is for DBMan SQL version 1.
Note: as it stands right now, this what returns when trying to validate a record:
'Records with the following IDs were not validated: 2'.

Here's what I have.. it's still pretty rough:

sub validate_records {
# --------------------------------------------------------
# Validates or deletes a single or multiple records.
my ($data, $auth_email_field, $query, $key, %delete_list, $rec_to_delete, %validate_list, $rec_to_validate, @lines, $line,
@data, $errstr, $succstr, $output, $restricted, $found, $fieldnum);

for ($i = 0; $i <= $#db_cols; $i++) {
if ($db_cols[$i] eq $db_validated_field) {
$fieldnum = $i; $found = 1;
last;
}
}
if (!$found) {
&cgierr ("error in validate_records. No Validated field defined");
}
$rec_to_delete = 0;
$rec_to_validate = 0;
foreach $key (keys %in) {
if ($in{$key} eq "delete") {
$delete_list{$key} = 1;
$rec_to_delete = 1;
}
elsif ($in{$key} eq "validate") {
$validate_list{$key} = 1;
$rec_to_validate = 1;
}
}

if ((!$rec_to_delete) && (!$rec_to_validate)) {
&html_validate_form("no records specified.");
return;
}

foreach $key (keys %delete_list) {
$db_is_int{$db_key} ?
($key_q = int($key)) :
($key_q = $DBH->quote($key));


if ($in{$key} eq "delete") {

$query = qq!
SELECT * FROM $db_table
WHERE $db_key = $key_q
!;
my $sth = $DBH->prepare($query);
$sth->execute();
if ($sth->rows) {
while (@data = $sth->fetchrow_array) {
$email = $data[50];
}
}
open (MAIL, "$mailprog") or &cgierr("Can't start mail program");
print MAIL "To: $email\n";
print MAIL "BCC: $admin2_email\n";
print MAIL "From: $admin_email\n";
# you can change the subject line to whatever you want
print MAIL "Subject: $html_title: Record deleted\n\n";
print MAIL "-" x 75 . "\n\n";

# Here's where you create your canned delete message. You can use the $rec{'fieldname'} variables
# just like in sub html_record to include the values of any fields that you'd like to.
# As you define your message, use carriage returns for a newline
$email_message = qq|
I'm sorry, but your record could not be added to $html_title.

We appreciate your coming by and possibly we can be of assistance to you later.

Sincerely,
John Doe
Webmaster
$html_title
|;
# be sure to leave in the last |; to close off your quoted text.

print MAIL $email_message;
close (MAIL);

}
$query = qq!
DELETE FROM $db_table
WHERE $db_key = $key_q
!;


$rc = $DBH->do($query);

if ($db_upload) {
if (-e "$SAVE_DIRECTORY/$key/") {
opendir (GRAPHIC, "$SAVE_DIRECTORY/$key/") or &cgierr("unable to open directory in delete records: $SAVE_DIRECTORY/$data[$db_key_pos]. Reason: $!");
@files = readdir(GRAPHIC);
closedir (GRAPHIC);
foreach $file (@files) {
unlink ("$SAVE_DIRECTORY/$key/$file");
}
rmdir "$SAVE_DIRECTORY/$key/";




}

else { $output .= $line . "\n"; }

}

if ($rc) {
$delete_list{$key} = 0;
foreach (keys %db_indexed) { &delete_index ($key, $_); }
}

}


foreach $key (keys %delete_list) {
if ($delete_list{$key}) { # Check to see if any items weren't deleted
$errstr .= "$key,"; # that should have been.

}




# elsif ($validate_list{$data[$db_key_pos]}) {
# $validate_list{$data[$db_key_pos]} = 0;
# %rec = &array_to_hash(0,@data);

elsif ($in{$key} eq "validate") {

$query = qq!
SELECT * FROM $db_table
WHERE $db_key = $key_q
!;
my $sth = $DBH->prepare($query);
$sth->execute();
if ($sth->rows) {
while (@data = $sth->fetchrow_array) {
$email = $data[50];
}
}

open (MAIL, "$mailprog") or &cgierr("unable to open mail program");
print MAIL "To: $email\n";
print MAIL "From: $admin_email\n";
# you can change the subject line to whatever you want
print MAIL "Subject: $html_title: Record validated\n\n";
print MAIL "-" x 75 . "\n\n";

# Here's where you create your canned validate message. You can use the $rec{'fieldname'} variables
# just like in sub html_record to include the values of any fields that you'd like to.
# As you define your message, use carriage returns for a newline
$email_message = qq|
I'm pleased to say that your record has been added to $html_title.

We look appreciate your addition to our database. Please let us know if there is anything we
can do to assist you.

Sincerely,
John Doe
Webmaster
$html_title
|;
# be sure to leave in the last |; to close off your quoted text.

print MAIL $email_message;
close (MAIL);

$query = qq!
UPDATE $db_table SET Validated = 'Yes'
WHERE $db_key = $key_q
!;

$rc = $DBH->do($query);
}


else {
$output .= $line . "\n"
}
}




foreach $key (keys %delete_list) {
$delete_list{$key} ?
($delerrstr .= "$key,") :
($delsuccstr .= "$key,");
}
chop($delsuccstr);
chop($delerrstr);
foreach $key (keys %validate_list) {
$validate_list{$key} ?
($valerrstr .= "$key,") :
($valsuccstr .= "$key,");
}
chop($valsuccstr);
chop($valerrstr);
if ($delsuccstr) { $resultstr = "Records with the following IDs were deleted: $delsuccstr<BR>"; }
if ($delerrstr) { $resultstr .= "Records with the following IDs were not deleted: $delerrstr<BR>"; }
if ($valsuccstr) { $resultstr .= "Records with the following IDs were validated: $valsuccstr<BR>"; }
if ($valerrstr) { $resultstr .= "Records with the following IDs were not validated: $valerrstr"; }



# open (DB, ">$db_file_name") or &cgierr("error in validate_records.
# unable to open db file: $db_file_name.\nReason: $!");
# if ($db_use_flock) {
# flock(DB, 2) or &cgierr("unable to get exclusive lock on $db_file_name.\nReason: $!");
# }
# print DB $output;
# close DB;
&auth_logging("deleted records: $delsuccstr") if ($auth_logging);
&auth_logging("validated records: $valsuccstr") if ($auth_logging);
&html_validate_success($resultstr);
}


Thanks in advance for any help!


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)
Quote Reply
Re: [shann123] Stuck on updating a field In reply to
Never mind.. I finally figured it out and will post the mod on my website (link below) when I'm finished cleaning it up. Wink


DBMan SQL Version 1 mods available at:
http://dbmansqlmods.rainbowroomies.com
(Mods based on JPDeni's original mods.)