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.)
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.)