Hi,
I keep on getting the following error message:
Software error:
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-50,50' at line 1 at arp3-data.pl line 48.
My host is a novice who couldn't really help.
Here is the arp3-data.pl coding:
use DBI;
$global_dbh = DBI->connect("DBI:$_db_type:$db_name:$db_host", $db_login, $db_password, {RaiseError => 1, AutoCommit => 1 }) or die "Unable to connect to db";
sub db_connect {
my $result = $global_dbh;
return $result;
} # sub db_connect
sub db_execute {
my $sql = shift;
if ($_reconnect == 1) {
$global_dbh = DBI->connect("DBI:$_db_type:$db_name:$db_host", $db_login, $db_password, {RaiseError => 1, AutoCommit => 1 }) or die "Unable to connect to db";
} # if
my $dbh = &db_connect;
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
return $sth;
} # sub db_execute
sub db_select_row {
my $sql = shift;
my $dbh = &db_connect;
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my $result = $sth->fetchrow_hashref;
$sth->finish;
return $result;
} # sub db_select_row
sub db_count_subscribers {
my $aid = shift;
my $result = 0;
if (! $aid) {return $result}
$table = $db_table_CAM;
my $dbh = &db_connect;
my $sql = qq|SELECT count(id) FROM $table WHERE $where aid=$aid|;
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my @rows = $sth->fetchrow_array;
$result = $rows[0];
$sth->finish;
return $result;
} # sub db_count_subscribers
sub db_count_link_hits {
my $lid = shift;
my $result = 0;
if (! $lid) {return $result}
$table = $db_table_CAM;
my $dbh = &db_connect;
my $sql = qq|SELECT count(id) FROM $table WHERE link_hit_codes LIKE '%[$lid]%'|;
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my @rows = $sth->fetchrow_array;
$result = $rows[0];
$sth->finish;
return $result;
} # sub db_count_link_hits
sub db_ar_counter {
my $aid = shift;
my $status = shift;
my $result = 0;
if ((! $aid) or ($status eq '')) {return $result}
my $dbh = &db_connect;
my $sql = qq|SELECT count(1) FROM $db_table_CAM WHERE aid=$aid AND status='$status'|;
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my @rows = $sth->fetchrow_array;
$result = $rows[0];
$sth->finish;
return $result;
} # sub db_ar_counter
sub db_ar_counter_conf {
my $aid = shift;
my $status = shift;
my $result = 0;
if ((! $aid) or ($status eq '')) {return $result}
my $dbh = &db_connect;
if ($status eq '*') {
$sql = qq|SELECT count(1) FROM $db_table_CAM WHERE aid=$aid AND double_status=1|;
} # if
else {
$sql = qq|SELECT count(1) FROM $db_table_CAM WHERE aid=$aid AND status='$status' AND double_status=1|;
} # else
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my @rows = $sth->fetchrow_array;
$result = $rows[0];
$sth->finish;
return $result;
} # sub db_ar_counter_conf
sub db_artt_counter {
my $aid = shift;
my $tt = shift;
my $start_date = shift;
my $end_date = shift;
my $result = 0;
if ((! $aid) or ($start_date eq '') or ($end_date eq '')) {return $result}
my $dbh = &db_connect;
if (($tt eq '') or ($tt eq 'none')) {
$sql = qq|SELECT count(1) FROM $db_table_CAM WHERE $where aid=$aid AND (tracking_tag='' OR tracking_tag='none') AND subscribe_date>=$start_date AND subscribe_date<=$end_date|;
} # if
else {
$sql = qq|SELECT count(1) FROM $db_table_CAM WHERE $where aid=$aid AND tracking_tag='$tt' AND subscribe_date>=$start_date AND subscribe_date<=$end_date|;
} # else
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my @rows = $sth->fetchrow_array;
$result = $rows[0];
$sth->finish;
return $result;
} # sub db_artt_counter
sub db_artt_counter_unsub {
my $aid = shift;
my $tt = shift;
my $start_date = shift;
my $end_date = shift;
my $result = 0;
if ((! $aid) or ($start_date eq '') or ($end_date eq '')) {return $result}
my $dbh = &db_connect;
if (($tt eq '') or ($tt eq 'none')) {
$sql = qq|SELECT count(1) FROM $db_table_CAM WHERE $where aid=$aid AND (tracking_tag='' OR tracking_tag='none') AND unsubscribe_date>=$start_date AND unsubscribe_date<=$end_date|;
} # if
else {
$sql = qq|SELECT count(1) FROM $db_table_CAM WHERE $where aid=$aid AND tracking_tag='$tt' AND unsubscribe_date>=$start_date AND unsubscribe_date<=$end_date|;
} # else
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my @rows = $sth->fetchrow_array;
$result = $rows[0];
$sth->finish;
return $result;
} # sub db_artt_counter_unsub
sub db_arsource_counter {
my $aid = shift;
my $source = shift;
my $start_date = shift;
my $end_date = shift;
my $result = 0;
if ((! $aid) or ($source eq '') or ($start_date eq '') or ($end_date eq '')) {return $result}
my $dbh = &db_connect;
$sql = qq|SELECT count(1) FROM $db_table_CAM WHERE $where aid=$aid AND source='$source' AND subscribe_date>=$start_date AND subscribe_date<=$end_date|;
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my @rows = $sth->fetchrow_array;
$result = $rows[0];
$sth->finish;
return $result;
} # sub db_arsource_counter
sub db_count_all {
my $table = shift;
my $result = 0;
my $dbh = &db_connect;
my $sql = qq|SELECT count(id) FROM $table|;
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my @rows = $sth->fetchrow_array;
$result = $rows[0];
$sth->finish;
return $result;
} # sub db_count_all
sub db_count_matching {
my $table = shift;
my $where = shift;
my $result = 0;
my $dbh = &db_connect;
my $sql = qq|SELECT count(id) FROM $table $where|;
my $sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my @rows = $sth->fetchrow_array;
$result = $rows[0];
$sth->finish;
return $result;
} # sub db_count_matching
sub data_Save {
my($record) = shift;
my($table) = shift;
my($result) = 0;
my $dbh = &db_connect;
my $sql = qq|INSERT INTO $table (|;
foreach (keys %{$record}) {
$fields .= $_ . ",";
$vals .= $dbh->quote($record->{$_}) . ",";
} # foreach
$vals =~ s/,$//;
$fields =~ s/,$//;
$sql .= $fields . ") VALUES (" . $vals . ")";
$sth = $dbh->prepare($sql) or die "Unable to Prepare $sql";
$sth->execute or die "Can't execute $sql";
# Get the last id created
$sql = qq|SELECT LAST_INSERT_ID() AS id FROM $table|;
$sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Can't execute $sql";
my $h = $sth->fetchrow_hashref;
undef $sql;
undef $vals;
undef $fields;
$sth->finish;
return $h->{id};
} # sub data_Save
sub data_Update {
my($record, $table, $id) = @_;
my $dbh = &db_connect;
my $sql = qq|UPDATE $table set |;
foreach (keys %{$record}) {
$vals = $dbh->quote($record->{$_});
$sql .= $_ ."=" . $vals . ",";
} # foreach
$sql =~ s/,$//;
$sql .= qq| WHERE id=$id|;
$sql =~s/¬¬//g;
$sth = $dbh->prepare($sql) or die "Can't prepare $sql: $dbh->errstr\n";
$sth->execute or die "Unable to Execute $sql";
undef $sql;
undef $vals;
$sth->finish;
return $id;
} # sub data_Update
sub data_Delete {
my($table, $id) = @_;
my $dbh = &db_connect;
my $sql = qq|DELETE FROM $table WHERE id=$id|;
$sth = $dbh->prepare($sql) or die "Can't prepare $sql";
$result = $sth->execute or die "Can't execute $sql";
$sth->finish;
return $id;
} # sub data_Delete
sub data_New {
my $table = shift;
my($result);
if ($table eq $db_table_AUT) {
$result->{"unsubscribe_link"} = 1;
$result->{"affiliate_link"} = 0;
$result->{"email_control"} = 1;
$result->{"form_control"} = 1;
$result->{"subscribe_notify"} = 1;
$result->{"unsubscribe_notify"} = 1;
$result->{"allow_duplicates"} = 0;
$result->{"immediate_message_id"} = 0;
$result->{"use_double"} = 0;
$result->{"edit_format"} = 0;
$result->{"listens_on"} = "";
$result->{"unsubscribeaid"} = "";
$result->{"description"} = "";
$result->{"reply_name"} = "";
$result->{"reply_email"} = "";
$result->{"header_text"} = "";
$result->{"html_header_text"} = "";
$result->{"footer_text"} = "";
$result->{"html_footer_text"} = "";
$result->{"status"} = "A";
$result->{"completion"} = "F";
$result->{"loop_ar"} = "";
$result->{"loop_message"} = "1";
$result->{"loop_interval"} = "1";
$result->{"message_order"} = "";
$result->{"optin_success"} = "";
$result->{"optin_failure"} = "";
$result->{"v2_id"} = "";
$result->{"use_habeas"} = 0;
$result->{"double_text"} = $g_settings{double_text};
$result->{"double_subject"} = $g_settings{double_subject};
} # AUT
elsif ($table eq $db_table_OWN) {
$result->{"name"} = "";
$result->{"password"} = "";
$result->{"email"} = "";
$result->{"affiliate_nickname"} = "";
$result->{"last_login"} = "";
$result->{"my_title"} = "";
$result->{"my_firstname"} = "";
$result->{"my_lastname"} = "";
$result->{"my_fullname"} = "";
$result->{"my_position"} = "";
$result->{"my_company"} = "";
$result->{"my_address1"} = "";
$result->{"my_address2"} = "";
$result->{"my_address3"} = "";
$result->{"my_address4"} = "";
$result->{"my_address5"} = "";
$result->{"my_phone1"} = "";
$result->{"my_phone2"} = "";
$result->{"my_fax1"} = "";
$result->{"my_fax2"} = "";
$result->{"my_email1"} = "";
$result->{"my_email2"} = "";
$result->{"my_email3"} = "";
$result->{"my_email4"} = "";
$result->{"my_email5"} = "";
$result->{"my_web1"} = "";
$result->{"my_web2"} = "";
$result->{"my_web3"} = "";
$result->{"my_web4"} = "";
$result->{"my_web5"} = "";
$result->{"my_misc1"} = "";
$result->{"my_misc2"} = "";
$result->{"my_misc3"} = "";
$result->{"my_misc4"} = "";
$result->{"my_misc5"} = "";
} # OWN
elsif ($table eq $db_table_TRA) {
$result->{"tag"} = "";
$result->{"description"} = "";
$result->{"subscribe_success"} = "";
$result->{"subscribe_failure"} = "";
$result->{"unsubscribe_success"} = "";
$result->{"unsubscribe_failure"} = "";
} # TRA
elsif ($table eq $db_table_TAG) {
$result->{"tag"} = "TAG_";
$result->{"content"} = "";
} # TAG
elsif ($table eq $db_table_FIL) {
$result->{"aid"} = "";
$result->{"last_message"} = "";
$result->{"failures"} = "";
$result->{"tt"} = "";
$result->{"status"} = "";
$result->{"double_status"} = "";
$result->{"link_hit_codes"} = "";
$result->{"from_day"} = "";
$result->{"from_month"} = "";
$result->{"from_year"} = "";
$result->{"to_day"} = "";
$result->{"to_month"} = "";
$result->{"to_year"} = "";
$result->{"use_from"} = "";
$result->{"use_to"} = "";
$result->{"criteria"} = "";
$result->{"email"} = "";
$result->{"name"} = "";
$result->{"custom"} = "";
$result->{"order_1"} = "";
$result->{"negate"} = 0;
} # FIL
elsif ($table eq $db_table_SETTINGS) {
$result->{"cookie_life"} = "365";
$result->{"time_difference"} = "0";
$result->{"mail_failures"} = "3";
$result->{"your_domain"} = "";
$result->{"cgi_arplus_url"} = "";
$result->{"support_email"} = "";
$result->{"optin_success"} = "";
$result->{"optin_failure"} = "";
$result->{"subscribe_success"} = "";
$result->{"subscribe_failure"} = "";
$result->{"unsubscribe_success"} = "";
$result->{"unsubscribe_failure"} = "";
$result->{"affiliate_text"} = "";
$result->{"affiliate_ad"} = "";
$result->{"message_page_header"} = "";
$result->{"message_page_footer"} = "";
$result->{"double_text"} = "";
$result->{"double_subject"} = "";
$result->{"send_unconf"} = "";
$result->{"subscribe_notify"} = "";
$result->{"unsubscribe_notify"} = "";
$result->{"remote_notify"} = "";
$result->{"remote_login"} = "";
$result->{"remote_password"} = "";
$result->{"daily_report"} = "";
$result->{"unsubscribe_text"} = "";
$result->{"edit_text"} = "";
$result->{"next_text"} = "";
$result->{"unsubscribe_email"} = "";
$result->{"force_default_personalisation"} = "";
$result->{"cron_backup"} = "";
$result->{"backup_interval"} = 24;
$result->{"cron_dedup"} = "";
$result->{"message_limit"} = $_default_message_limit;
$result->{"system_name"} = "";
$result->{"system_email"} = "";
$result->{"max_list"} = 50;
$result->{"preview_font"} = '';
$result->{"mailer"} = "";
$result->{"sendmail"} = "";
$result->{"smtp_server"} = "";
$result->{"show_news"} = "1";
$result->{"show_banner"} = "1";
$result->{"count_subscribers"} = "1";
$result->{"show_extra"} = "1";
$result->{"spam_message"} = "";
$result->{"privacy_statement"} = "";
$result->{"attachments_path"} = "";
$result->{"sig_1"} = "";
$result->{"sig_2"} = "";
$result->{"sig_3"} = "";
$result->{"ad1"} = "";
$result->{"ad2"} = "";
$result->{"ad3"} = "";
$result->{"ad4"} = "";
$result->{"ad5"} = "";
$result->{"ad6"} = "";
$result->{"ad7"} = "";
$result->{"ad8"} = "";
$result->{"ad9"} = "";
$result->{"ad10"} = "";
$result->{"reserved_email"} = "";
$result->{"banned_email"} = "";
$result->{"show_habeas"} = 1;
$result->{"email_links"} = 0;
} # SETTINGS
elsif ($table eq $db_table_MES) {
$result->{"subject"} = "Put the subject here";
$result->{"time_interval"} = "1";
$result->{"plain_text"} = "";
$result->{"html_text"} = "";
$result->{"use_header"} = "";
$result->{"use_footer"} = "";
$result->{"default_format"} = "";
$result->{"attachments"} = "";
$result->{"cc"} = "";
$result->{"bcc"} = "";
$result->{"schedule"} = "INT";
} # MES
elsif ($table eq $db_table_CAM) {
use Time::HiRes;
my $microtime = Time::HiRes::time;
my $usec = sprintf("%.6f", $microtime);
$result->{"aid"} = "";
$result->{"v2_aid"} = "";
$result->{"last_message"} = "0";
$result->{"message_id"} = &db_message_id;
$result->{'pin'} = $usec;
$result->{"title"} = "";
$result->{"first_name"} = "";
$result->{"last_name"} = "";
$result->{"middle_name"} = "";
$result->{"last_date"} = "";
$result->{"status"} = "A";
$result->{"source"} = "";
$result->{"email"} = "";
$result->{"full_name"} = "";
$result->{"company"} = "";
$result->{"department"} = "";
$result->{"street"} = "";
$result->{"city"} = "";
$result->{"state"} = "";
$result->{"zip"} = "";
$result->{"country"} = "";
$result->{"phone1"} = "";
$result->{"phone2"} = "";
$result->{"phone3"} = "";
$result->{"fax1"} = "";
$result->{"fax2"} = "";
$result->{"format_preference"} = "D";
$result->{"notes"} = "";
$result->{"custom_fields"} = "";
$result->{"tracking_tag"} = "";
$result->{"failures"} = 0;
$result->{"ip_address"} = "";
$result->{"subscribe_date"} = time;
$result->{"unsubscribe_date"} = "";
$result->{"double_status"} = "";
$result->{"double_requested"} = "";
$result->{"double_confirmed"} = "";
$result->{"send_log"} = "";
$result->{"batch_codes"} = "";
$result->{"link_hit_codes"} = "";
$result->{"cron_send"} = 0;
$result->{"import_id"} = "";
$result->{"import_timestamp"} = "";
$result->{"send_confirmation"} = 0;
} # CAM
elsif ($table eq $db_table_BAT) {
$result->{"create_date"} = time;
$result->{"from_email"} = "";
$result->{"from_name"} = "";
$result->{"subject"} = "";
$result->{"attachments"} = "";
$result->{"schedule"} = "";
$result->{"plain_text"} = "";
$result->{"html_text"} = "";
$result->{"format_preference"} = "";
$result->{"header"} = 0;
$result->{"footer"} = 0;
$result->{"unsubscribe_link"} = 0;
$result->{"affiliate_link"} = 0;
$result->{"send_date"} = "";
$result->{"omit_duplicates"} = 1;
$result->{"omit_unsubscribers"} = 1;
$result->{"omit_unconfdouble"} = 1;
$result->{"email_conf"} = "";
$result->{"status"} = "W";
$result->{"subscribers"} = 0;
$result->{"progress"} = 0;
$result->{"num_sent"} = 0;
$result->{"duplicates_removed"} = 0;
$result->{"sessions"} = 0;
$result->{"runtime"} = 0;
$result->{"use_habeas"} = 0;
} #BAT
elsif ($table eq $db_table_LOCKS) {
$result->{lock_type} = '';
$result->{create_date} = time;
} #LOCKS
elsif ($table eq $db_table_DUP) {
$result->{"batch_id"} = 0;
$result->{"email"} = '';
} #DUP
elsif ($table eq $db_table_LINKS) {
$result->{link} = '';
$result->{description} = '';
$result->{impressions} = 0;
$result->{clicks} = 0;
} #LINKS
return $result;
} # sub data_New
sub data_Drop {
my($data_type) = shift;
my $dbh = &db_connect;
my $table = $data_type;
if ($table eq $db_table_AUT) {
$sql = qq|DROP TABLE IF EXISTS $db_table_AUT|;
$dbh->do($sql);
} # AUT
elsif ($table eq $db_table_OWN) {
$sql = qq|DROP TABLE IF EXISTS $db_table_OWN|;
$dbh->do($sql);
} # OWN
elsif ($table eq $db_table_TRA) {
$sql = qq|DROP TABLE IF EXISTS $db_table_TRA|;
$dbh->do($sql);
} # TRA
elsif ($table eq $db_table_TAG) {
$sql = qq|DROP TABLE IF EXISTS $db_table_TAG|;
$dbh->do($sql);
} # TAG
elsif ($table eq $db_table_FIL) {
$sql = qq|DROP TABLE IF EXISTS $db_table_FIL|;
$dbh->do($sql);
} # FIL
elsif ($table eq $db_table_MES) {
$sql = qq|DROP TABLE IF EXISTS $db_table_MES|;
$dbh->do($sql);
} # MES
elsif ($table eq $db_table_CAM) {
$sql = qq|DROP TABLE IF EXISTS $db_table_CAM|;
$dbh->do($sql);
} # CAM
elsif ($table eq $db_table_BAT) {
$sql = qq|DROP TABLE IF EXISTS $db_table_BAT|;
$dbh->do($sql);
} # BAT
elsif ($table eq $db_table_LOCKS) {
$sql = qq|DROP TABLE IF EXISTS $db_table_LOCKS|;
$dbh->do($sql);
} # LOCKS
elsif ($table eq $db_table_LINKS) {
$sql = qq|DROP TABLE IF EXISTS $db_table_LINKS|;
$dbh->do($sql);
} # LINKS
elsif ($table eq $db_table_SETTINGS) {
$sql = qq|DROP TABLE IF EXISTS $db_table_SETTINGS|;
$dbh->do($sql);
} # SETTINGS
} # sub data_Drop
sub data_Build {
my($data_type) = shift;
my $dbh = &db_connect;
my $table = $data_type;
if ($table eq $db_table_AUT) {
#Create AUT db
$sql = qq|CREATE TABLE $db_table_AUT (
id int auto_increment not null,
unsubscribe_link int,
affiliate_link int,
email_control int,
form_control int,
subscribe_notify int,
unsubscribe_notify int,
allow_duplicates int,
immediate_message_id int,
use_double int,
edit_format int,
completion varchar(1),
loop_ar int,
loop_message int,
loop_interval int,
use_habeas int(1),
status varchar(1),
listens_on varchar(255),
unsubscribeaid varchar(255),
description varchar(255),
reply_name varchar(255),
reply_email varchar(255),
optin_success varchar(255),
optin_failure varchar(255),
v2_id varchar(255),
double_subject varchar(255),
header_text text,
html_header_text text,
footer_text text,
html_footer_text text,
message_order text,
double_text text,
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # AUT
elsif ($table eq $db_table_OWN) {
#Create OWN db
$sql = qq|CREATE TABLE $db_table_OWN (
id int auto_increment not null,
name varchar(255),
password varchar(255),
email varchar(255),
affiliate_nickname varchar(255),
last_login timestamp,
my_title varchar(255),
my_firstname varchar(255),
my_lastname varchar(255),
my_fullname varchar(255),
my_position varchar(255),
my_company varchar(255),
my_address1 varchar(255),
my_address2 varchar(255),
my_address3 varchar(255),
my_address4 varchar(255),
my_address5 varchar(255),
my_phone1 varchar(255),
my_phone2 varchar(255),
my_fax1 varchar(255),
my_fax2 varchar(255),
my_email1 varchar(255),
my_email2 varchar(255),
my_email3 varchar(255),
my_email4 varchar(255),
my_email5 varchar(255),
my_web1 varchar(255),
my_web2 varchar(255),
my_web3 varchar(255),
my_web4 varchar(255),
my_web5 varchar(255),
my_misc1 varchar(255),
my_misc2 varchar(255),
my_misc3 varchar(255),
my_misc4 varchar(255),
my_misc5 varchar(255),
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # OWN
elsif ($table eq $db_table_TRA) {
#Create TRA db
$sql = qq|CREATE TABLE $db_table_TRA (
id int auto_increment not null,
tag varchar(255),
description varchar(255),
subscribe_success varchar(255),
subscribe_failure varchar(255),
unsubscribe_success varchar(255),
unsubscribe_failure varchar(255),
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # TRA
elsif ($table eq $db_table_TAG) {
#Create TAG db
$sql = qq|CREATE TABLE $db_table_TAG (
id int auto_increment not null,
tag varchar(255),
content varchar(255),
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # TAG
elsif ($table eq $db_table_FIL) {
#Create FIL db
$sql = qq|CREATE TABLE $db_table_FIL (
id int auto_increment not null,
use_from int,
use_to int,
negate int(1),
order_1 char(1),
status varchar(255),
double_status varchar(255),
link_hit_codes varchar(255),
name varchar(255),
last_message varchar(255),
failures varchar(255),
email varchar(255),
custom varchar(255),
from_day varchar(255),
from_month varchar(255),
from_year varchar(255),
to_day varchar(255),
to_month varchar(255),
to_year varchar(255),
criteria text,
aid text,
tt text,
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # FIL
elsif ($table eq $db_table_SETTINGS) {
#Create SETTINGS db
$sql = qq|CREATE TABLE $db_table_SETTINGS (
id int auto_increment not null,
cookie_life int,
time_difference int,
mail_failures int,
send_unconf int,
subscribe_notify int,
unsubscribe_notify int,
remote_notify int,
daily_report int,
force_default_personalisation int,
cron_backup int,
backup_interval int,
cron_dedup int,
message_limit int,
max_list int,
show_news int,
show_banner int,
count_subscribers int,
show_extra int,
show_habeas int(1),
email_links int(1),
your_domain varchar(255),
cgi_arplus_url varchar(255),
support_email varchar(255),
optin_success varchar(255),
optin_failure varchar(255),
subscribe_success varchar(255),
subscribe_failure varchar(255),
unsubscribe_success varchar(255),
unsubscribe_failure varchar(255),
system_email varchar(255),
system_name varchar(255),
mailer varchar(10),
sendmail varchar(255),
smtp_server varchar(255),
attachments_path varchar(255),
preview_font varchar(255),
double_subject varchar(255),
remote_login varchar(255),
remote_password varchar(255),
affiliate_text text,
affiliate_ad text,
message_page_header text,
message_page_footer text,
double_text text,
unsubscribe_text text,
edit_text text,
next_text text,
unsubscribe_email text,
sig_1 text,
sig_2 text,
sig_3 text,
ad1 text,
ad2 text,
ad3 text,
ad4 text,
ad5 text,
ad6 text,
ad7 text,
ad8 text,
ad9 text,
ad10 text,
reserved_email text,
banned_email text,
spam_message text,
privacy_statement text,
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # SET
elsif ($table eq $db_table_MES) {
#Create MES db
$sql = qq|CREATE TABLE $db_table_MES (
id int auto_increment not null,
aid int,
use_header int,
use_footer int,
subject varchar(255),
time_interval varchar(255),
default_format varchar(255),
attachments varchar(255),
cc varchar(255),
bcc varchar(255),
schedule varchar(255),
plain_text text,
html_text text,
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # MES
elsif ($table eq $db_table_CAM) {
#Create CAM db
$sql = qq|CREATE TABLE $db_table_CAM (
id int auto_increment not null,
aid int not null,
last_message int,
last_date int,
subscribe_date int,
unsubscribe_date int,
failures int,
double_status int,
double_requested int,
double_confirmed int,
cron_send int(1),
send_confirmation int(1),
source char(1) NOT NULL,
format_preference char(1) NOT NULL,
message_id varchar(255) NOT NULL,
full_name varchar(255) NOT NULL,
v2_aid varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
middle_name varchar(255) NOT NULL,
title varchar(255) NOT NULL,
first_name varchar(255) NOT NULL,
import_id varchar(255) NOT NULL,
import_timestamp varchar(255) NOT NULL,
status char(1) NOT NULL,
tracking_tag varchar(255) NOT NULL,
ip_address varchar(255) NOT NULL,
email varchar(200) NOT NULL,
pin varchar(255) NOT NULL,
company varchar(255) NOT NULL,
department varchar(255) NOT NULL,
street varchar(255) NOT NULL,
city varchar(255) NOT NULL,
state varchar(255) NOT NULL,
zip varchar(255) NOT NULL,
country varchar(255) NOT NULL,
phone1 varchar(255) NOT NULL,
phone2 varchar(255) NOT NULL,
phone3 varchar(255) NOT NULL,
fax1 varchar(255) NOT NULL,
fax2 varchar(255) NOT NULL,
notes text NOT NULL,
custom_fields text NOT NULL,
send_log text NOT NULL,
batch_codes text NOT NULL,
link_hit_codes text NOT NULL,
primary key (id),
unique id (id),
index aid_status (aid,status),
index aid_email (aid,email),
index message_id (message_id),
index(id)
)|;
$dbh->do($sql);
} # CAM
elsif ($table eq $db_table_BAT) {
$sql = qq|CREATE TABLE $db_table_BAT (
id int auto_increment not null,
create_date int,
header int,
footer int,
omit_duplicates int,
omit_unsubscribers int,
omit_unconfdouble int,
email_conf int,
subscribers int,
num_sent int,
progress int,
duplicates_removed int,
unsubscribe_link int,
affiliate_link int,
sessions int,
runtime int,
use_habeas int(1),
format_preference char(1),
send_date varchar(255),
status varchar(255),
from_email varchar(255),
from_name varchar(255),
subject varchar(255),
attachments varchar(255),
schedule varchar(255),
plain_text text,
html_text text,
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # BAT
elsif ($table eq $db_table_LOCKS) {
$sql = qq|CREATE TABLE $db_table_LOCKS (
id int auto_increment not null,
create_date int,
lock_type varchar(255) NOT NULL,
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # LOCKS
elsif ($table eq $db_table_LINKS) {
$sql = qq|CREATE TABLE $db_table_LINKS (
id int auto_increment not null,
create_date int,
link varchar(255),
description varchar(255),
impressions int,
clicks int,
primary key (id),
unique id (id)
)|;
$dbh->do($sql);
} # LINKS
return 1;
} # sub data_New
sub db_message_id {
my $result = int(rand() * 1000000);
$result .= "_" . int(rand() * 1000000);
$result .= "_" . int(rand() * 1000000);
$result .= "_" . int(rand() * 1000000);
return $result;
} # sub db_message_id
sub db_field_exists {
my ($table, $field) = @_;
$table = &Trim(lc($table));
$field = &Trim(lc($field));
if (($table eq "") or ($field eq "")) {return $result}
my $query = qq|SELECT * FROM $table WHERE id=1|;
my $sth = $global_dbh->prepare($query);
$sth->execute or die;
my @field_names = @{$sth->{NAME}};
my %field_exists = map {$_=>1} @field_names;
$result = $field_exists{$field};
$sth->finish;
return $result;
} # sub db_field_exists
sub db_table_exists {
my $table = shift;
my $result = 0;
$table = &Trim(lc($table));
my @tables = $global_dbh->tables();
my %exists = map {$_=>1} @tables;
foreach my $tab (@tables) {
$tab =~s/'//g;
$tab =~s/"//g;
$tab =~s/`//g;
if (lc($tab) eq lc($table)) {
$result = 1;
last;
} # if
} # foreach
return $result;
} # sub db_table_exists;
sub db_index_exists {
my $table = shift;
my $index = shift;
my $result = 0;
$index = &Trim(lc($index));
my $db_idx = &db_execute("SHOW INDEX FROM $table");
if ($db_idx->rows > 1) {
while (my $idx = $db_idx->fetchrow_hashref) {
my $thisindex = &Trim(lc($idx->{Key_name}));
$thisindex =~s/'//g;
$thisindex =~s/"//g;
$thisindex =~s/`//g;
if ($thisindex eq $index) {
$result = 1;
last;
} # if
} # while
} # if
$db_idx->finish;
return $result;
} # sub db_index_exists
sub db_disconnect {
$global_dbh->disconnect;
} # sub db_disconnect
return 1;
I'd really appreciate it if anyone can help!
Kind Regards,
Abul
