DBD::mysql::st execute failed: Table [Archive]

View Full Version : DBD::mysql::st execute failed: Table

genx_000

07-05-2008, 12:56 AM

Hi,

I just started learning PERL and CGI scripting and am a complete newbie. So I sincerely apologize if my problem seems too simple for the guru's.

its been atleast two weeks that I've been trying to install a custom script on my server. The script named install_mysql.cgi - installs a set of mysql tables into the mysql server for the domain name.

The install_msql.cgi -> calls an admin-list.inc file which in turns calls the config file containing the info for mysql, such as dbname, user, password, and host and the iSubs.pm (module) file that aides in the building of the mysql tables.

The problem that I'm facing is rather strange: When I run the install_mysql.cgi from the browser, I get a message saying "The MySQL Database has been setup properly".

But when i refer to my server error log I get the following error messages:

[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::st execute failed: Table 'myfunlotto.lotto_pages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 188.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 220.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::st execute failed: Table 'myfunlotto.lotto_pages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 188.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 220.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Invalid default value for 'id' at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_advertisers' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Invalid default value for 'id' at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_books' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Invalid default value for 'id' at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Invalid default value for 'id' at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_members' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Invalid default value for 'id' at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_messages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_messages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_messages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Invalid default value for 'id' at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Invalid default value for 'id' at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_pages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_pages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_pages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_pages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_pages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Table 'myfunlotto.lotto_pages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Invalid default value for 'id' at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::db do failed: Invalid default value for 'id' at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 182.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::st execute failed: Table 'myfunlotto.lotto_pages' doesn't exist at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 188.
[Fri Jul 04 13:19:27 2008] [error] [client 68.84.40.48] [Fri Jul 4 13:19:27 2008] install_mysql.cgi: DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files/iSubs.pm line 220.

---------------------------------------

iSubs.pm file:

sub OpenMySQL {
use DBI;
$dbh = DBI->connect("DBI:mysql:$mysql_db:$mysql_host",$mysql_u,$mysql_p) or die $DBI::errstr;
$mysqlopen = 1;
return; }

sub CloseMySQL {
$dbh->disconnect();
$mysqlclosed = 1;
return; }

sub DoMySQL {
if (!$mysqlopen) { &OpenMySQL; }
$dbh->do("$_[0]");
return; }

sub PrepareMySQL {
if (!$mysqlopen) { &OpenMySQL; }
$result = $dbh->prepare("$_[0]");
$result->execute();
return; }

I spent a lot of time researching on the web and the books but I just could not find a solution.

I would really appreciate your expert advice and help on how to resolve this error.

Thanks a lot guys!

Best Wishes,
genX_000!

netroact

07-05-2008, 04:54 AM

Sounds like they simply used the message "The MySQL Database has been setup properly" as the output to the browser after the script has been run. The script did not test for success.

It would be very helpful if you could provide the code for install_msql.cgi.

genx_000

07-05-2008, 05:02 AM

Hi Netroact,

Thanks a million for your most valuable response. Thanks a lot.

Here is the install_mysql.cgi code:

--------------------------------------

#!/usr/bin/perl

use CGI::Carp qw(fatalsToBrowser);
require "admin-list.inc";

$section = "Setting Up the MySQL Database ...";
&PrintHead;
&DoMySQL("CREATE TABLE $advtable (id int(10) DEFAULT '' NOT NULL auto_increment, userid varchar(35) DEFAULT '' NOT NULL , password varchar(35) DEFAULT '' NOT NULL , company varchar(75) , url varchar(255) , email varchar(75) DEFAULT '' NOT NULL , fullname varchar(75) DEFAULT '' NOT NULL , orders int(10) DEFAULT '0' NOT NULL , totalspent float DEFAULT '0' NOT NULL , extra1 varchar(75) , extra2 varchar(75) , extra3 varchar(75) , PRIMARY KEY (id), KEY custid (id))");
&DoMySQL("INSERT INTO $advtable (id,userid,password,company,url,email,fullname,orders,totalspent,extra1,extra2,extra3) VALUES('1','admin','$adminpassword','$site_name','$clickthru_url','$admin_email','$admin_name','0',' 0','','','')");
&DoMySQL("CREATE TABLE $bookstable (id int(24) DEFAULT '' NOT NULL auto_increment, userid varchar(35) DEFAULT '' NOT NULL , refclicks int(24) DEFAULT '0' NOT NULL , referrals int(24) DEFAULT '0' NOT NULL , tickets_played int(24) DEFAULT '0' NOT NULL , tickets_avail int(24) DEFAULT '0' NOT NULL , tickets_won int(24) DEFAULT '0' NOT NULL , PRIMARY KEY (id), UNIQUE userid (userid))");
&DoMySQL("INSERT INTO $bookstable (id,userid,refclicks,referrals,tickets_played,tickets_avail,tickets_won) VALUES('1','admin','0','0','0','100','0')");
&DoMySQL("CREATE TABLE $drawtable (id int(10) DEFAULT '' NOT NULL auto_increment, win_number int(3) DEFAULT '0' NOT NULL , win_date varchar(50) DEFAULT '' NOT NULL , win_time varchar(50) DEFAULT '' NOT NULL , win_ip varchar(50) DEFAULT '' NOT NULL , winners int(10) DEFAULT '0' NOT NULL , PRIMARY KEY (id))");
&DoMySQL("CREATE TABLE $memtable (id int(9) DEFAULT '' NOT NULL auto_increment, userid varchar(35) DEFAULT '' NOT NULL , password varchar(35) DEFAULT '' NOT NULL , first varchar(35) DEFAULT '' NOT NULL , last varchar(35) DEFAULT '' NOT NULL , email varchar(125) DEFAULT '' NOT NULL , city varchar(125) , state char(2) , zipcode varchar(125) , notes text , status varchar(75) DEFAULT '' NOT NULL , estatus varchar(75) DEFAULT '' NOT NULL , vcode varchar(128) , ipadd varchar(50) DEFAULT '' NOT NULL , referrer varchar(35) , PRIMARY KEY (id), UNIQUE userid (userid), KEY id (id))");
&DoMySQL("INSERT INTO $memtable (id,userid,password,first,last,email,city,state,zipcode,notes,status,estatus,vcode,ipadd,referrer) VALUES('1','admin','$admin_password','$admin_name','($site_name)','$admin_email','Your City','FL','33713',NULL,'ACTIVE','VERIFIED','','$ipadd','')");
&DoMySQL("CREATE TABLE $msgtable (id int(10) DEFAULT '' NOT NULL auto_increment, messageid varchar(50) DEFAULT '' NOT NULL , contents longtext DEFAULT '' NOT NULL , lastedited varchar(255) DEFAULT '' NOT NULL , description longtext , PRIMARY KEY (id), UNIQUE messageid (messageid), KEY id (id))");
&DoMySQL("INSERT INTO $msgtable (id,messageid,contents,lastedited,description) VALUES('1','Verify Email','Hi ~firstname~,\n\nIn order for you to start using your account you must\nclick on the email verification link included below.\n\nEmail Verification Link:\n~cgibin~/verify.cgi?~vcode~\n\n\nAdmin,\n~sitename~','','Email Verification Link Message')");
&DoMySQL("INSERT INTO $msgtable (id,messageid,contents,lastedited,description) VALUES('2','Password Resend','Hi ~firstname~,\n\nHere is your login information as requested by you or some\none at ~ipaddress~.\n\nUser ID: ~userid~\nPassword: ~password~\n\n\nAdmin,\n~sitename~','','Password Resend Email Message')");
&DoMySQL("INSERT INTO $msgtable (id,messageid,contents,lastedited,description) VALUES('3','Winner Notification','Congratulations ~firstname~, You have WON!!\r\n\r\nDetails and Prize Redeeming Instructions go here.\r\n','','This is the member winning ticket notification email. Here add the instructions on how to redeem the prize you are offering for winning.')");
&DoMySQL("CREATE TABLE $orderstable (id int(10) DEFAULT '' NOT NULL auto_increment, userid varchar(35) DEFAULT '' NOT NULL , linkid varchar(25) , linkurl text DEFAULT '' NOT NULL , linktype varchar(128) , linkxtra text , maxclicks int(10) DEFAULT '0' NOT NULL , status varchar(128) DEFAULT '' NOT NULL , datedone varchar(12) , clicks int(11) DEFAULT '0' NOT NULL , PRIMARY KEY (id))");
&DoMySQL("CREATE TABLE $htmtable (id int(10) DEFAULT '' NOT NULL auto_increment, pageid varchar(255) DEFAULT '' NOT NULL , contents longtext DEFAULT '' NOT NULL , lastedited varchar(255) DEFAULT '' NOT NULL , description varchar(255) , PRIMARY KEY (id), KEY id (id))");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('1','Referral Banners Page','Banner Option #1:\r\nBanner #1 HTML Code:<p align=\"center\"><a href=\"~cgibin~/p.cgi?~userid~/index.html\" target=\"_blank\"><img src=\"/imgs/banner_001.gif\" border=\"0\"></a></p>','','Banners you want your members to use for promting your Lotto Site. Use (~cgibin~/p.cgi?~userid~/index.html) where you want the referral link displayed.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('2','Banner Code', 'Add banner code here for the banner you want displayed when advertisers are not shown.', '', 'Default Banner Code for ALL your pages')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('3','Template Header', '<TABLE WIDTH=750 BORDER=0 CELLPADDING=0 CELLSPACING=0 align="center">\r\n <TR> \r\n <TD COLSPAN=2> <IMG SRC="http://www.myfunlotto.com/images/lotto_01.jpg" WIDTH=141 HEIGHT=80 ALT=""></TD>\r\n <TD COLSPAN=11 background="http://www.myfunlotto.com/images/lotto_02.jpg"> \r\n <table width="90%" border="0" cellspacing="0" cellpadding="10" align="center">\r\n <tr> \r\n <td><font face="Arial, Helvetica, sans-serif" size="4"><b><font color="#FFFFFF">www.myfunlotto.com<br>\r\n <font size="3">Just Play & Win!</font></font></b></font></td>\r\n </tr>\r\n </table>\r\n </TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=1 HEIGHT=80 ALT=""></TD>\r\n </TR>\r\n <TR> \r\n <TD COLSPAN=4> <a href="http://www.myfunlotto.com"><IMG SRC="http://www.myfunlotto.com/images/lotto_03.jpg" WIDTH=170 HEIGHT=29 ALT="" border="0"></a></TD>\r\n <TD COLSPAN=3> <a href="http://www.myfunlotto.com/cgi-bin/signup.cgi"><IMG SRC="http://www.myfunlotto.com/images/lotto_04.jpg" WIDTH=131 HEIGHT=29 ALT="" border="0"></a></TD>\r\n <TD> <a href="http://www.myfunlotto.com/login.html"><IMG SRC="http://www.myfunlotto.com/images/lotto_05.jpg" WIDTH=103 HEIGHT=29 ALT="" border="0"></a></TD>\r\n <TD COLSPAN=3> <a href="http://www.myfunlotto.com/cgi-bin/advert/signup.cgi"><IMG SRC="http://www.myfunlotto.com/images/lotto_06.jpg" WIDTH=155 HEIGHT=29 ALT="" border="0"></a></TD>\r\n <TD COLSPAN=2> <a href="http://www.myfunlotto.com/advert/login.html"><IMG SRC="http://www.myfunlotto.com/images/lotto_07.jpg" WIDTH=191 HEIGHT=29 ALT="" border="0"></a></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=1 HEIGHT=29 ALT=""></TD>\r\n </TR>\r\n <TR> \r\n <TD COLSPAN=13 align="left" valign="top">', '', 'Top part of the HTML Coding for EVERY page made thru the script.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('4','Template Footer', ' </TD><HTML>\r\n <HEAD>\r\n <TITLE>www.myfunlotto.com FREE LOTTO</TITLE>\r\n </HEAD>\r\n <BODY BGCOLOR=#FFFFFF LEFTMARGIN=0 TOPMARGIN=0 MARGINWIDTH=0 MARGINHEIGHT=0>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=1 HEIGHT=8 ALT=""></TD>\r\n </TR>\r\n <TR> \r\n <TD colspan="13" bgcolor="#000000">\r\n <div align="center"><font face="Arial, Helvetica, sans-serif" size="2" color="#FFFFFF">Copyright \r\n www.myfunlotto.com 2004</font></div>\r\n </TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=1 HEIGHT=37 ALT=""></TD>\r\n </TR>\r\n <TR> \r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=87 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=54 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=8 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=21 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=5 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=73 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=53 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=103 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=44 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=11 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=100 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=20 HEIGHT=1 ALT=""></TD>\r\n <TD> <IMG SRC="http://www.myfunlotto.com/images/spacer.gif" WIDTH=171 HEIGHT=1 ALT=""></TD>\r\n <TD></TD>\r\n </TR>\r\n</TABLE></body></html>', '', 'Bottom part of the HTML Coding for EVERY page made thru the script.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('5','Members Main Page','Welcome to the ~sitename~ Members Section! Put information about the prizes you are offering here.','','HTML Coding you want displayed on the members section main page.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('6','Advertisers Main Page','Welcome to the ~sitename~ Advertisers Section!','','HTML Coding you want displayed on the advertisers section main page.')");
&DoMySQL("CREATE TABLE $ticketstable (id int(10) DEFAULT '' NOT NULL auto_increment, userid varchar(35) DEFAULT '' NOT NULL , a int(1) DEFAULT '0' NOT NULL , b int(1) DEFAULT '0' NOT NULL , c int(1) DEFAULT '0' NOT NULL , status varchar(28) DEFAULT '' NOT NULL , ipadd varchar(50) DEFAULT '' NOT NULL , dateplayed varchar(50) DEFAULT '' NOT NULL , extra varchar(255) , PRIMARY KEY (id))");
&DoMySQL("CREATE TABLE $tracktable (id int(10) DEFAULT '' NOT NULL auto_increment, userid varchar(35) DEFAULT '' NOT NULL , ipadd varchar(50) DEFAULT '' NOT NULL , linkid varchar(25) DEFAULT '' NOT NULL , date varchar(12) DEFAULT '' NOT NULL , time varchar(15) DEFAULT '' NOT NULL , PRIMARY KEY (id))");
print "<p>    </p>\n";
print "<p><b>The MySQL Database has been setup properly</b></p>\n";
print "<p>    </p>\n";
&PrintFoot;
exit;

The table's has been defines as follows in iSub.pm:

$memtable = "$prefix"."_members";
$msgtable = "$prefix"."_messages";
$htmtable = "$prefix"."_pages";
$ticketstable = "$prefix"."_tickets";
$bookstable = "$prefix"."_books";
$advtable = "$prefix"."_advertisers";
$tracktable = "$prefix"."_tracking";
$orderstable = "$prefix"."_orders";
$ticketstable = "$prefix"."_tickets";
$drawtable = "$prefix"."_drawings";

netroact

07-05-2008, 05:43 AM

First, we know you are set up to run perl scripts on the website, or you would have received an error instead of the output from the script.

Secondly, we know that you have a database and you are connecting to it, or the following line would have spit an error at you:

$dbh = DBI->connect("DBI:mysql:$mysql_db:$mysql_host",$mysql_u,$mysql_p) or die $DBI::errstr;

So, the problem is in creating tables in the database as the error logs suggest.

Do you have a graphical interface or control panel with myphpadmin?

FishMonger

07-05-2008, 06:47 AM

Can you post the code for admin-list.inc

genx_000

07-05-2008, 02:27 PM

Hi FishMonger,

Thank you for your reply.

here is the code for admin-list.inc

====================

#
################################################################################
$datadir = "/var/www/vhosts/myfunlotto.com/httpdocs/lotto/data_files";
require "$datadir/iConfig.pm";
require "$datadir/iSubs.pm";

1;

During the initial setup of the script there were 3 .inc files where the default value for $datadir had to be changed to where the folder data_files exists on my server.

FishMonger

07-05-2008, 07:57 PM

Hmm, I was expecting that script to be more involved. There is absolutely no justifiable reason for that script. Those 3 lines could and probably should be in the install_mysql.cgi script. I have to conclude that the other files follow a similar structure as these 2 scripts.

From what I see, you have 2 options (realistically only 1).

Contact the people that provided those scripts and have them fix it.
Throw them away and start over.

Without a doubt, that (install_mysql.cgi) is the worst Perl script I have ever seen and it's not worth your or our time to troubleshoot and fix it.

genx_000

07-05-2008, 08:03 PM

oesxyl

07-05-2008, 08:10 PM

Hi FishMonger,

Thanks again for your reply. Have tried contacting the people responsible for the scripts but no response. I'm beginning to think if this was a rip of or something. Anyways, I'll give it a shot one more time.

Many thanks,
Jerry!
try to extend a little what FishMonger said. If you finaly succed to install this application you get into trouble because the application can't be better written then the instalation script in my opinion.

PS: I assume that both are wtitten by same people.

regards

genx_000

07-05-2008, 08:17 PM

Hi oesxyl,

Thank you so much for your quick reply.

So you are saying that I have a crappy script at hand? .... boy if this is the case then damn I'm so pissed off. I spent thousands of hours configuring my server and studying perl and cgi just to understand what the script does.

Well atleast I can say I know a lot more than what I started with and thanks to you guys for helping me out.

Although, since I put in such huge amount of effort and time into this, I'll try to continue to work on it. And then maybe one I improve my skills I can modify the script further.

Thanks a lot guys.
Jerry

FishMonger

07-05-2008, 08:18 PM

If you paid for it, then yes it was a complete rip off.

FishMonger

07-05-2008, 08:24 PM

If you really want to troubleshoot it, then the first step would be to reformat the lines to have a reasonable length so that you can follow what it's doing. A line length of over 2400 characters it totally absurd.

genx_000

07-05-2008, 08:52 PM

oesxyl

07-05-2008, 09:05 PM

Since I was able to successfully connect to mysql database and create a table via a cgi script of my own (based upon guidelines obtained from Sam's Tech Yourself CGI in 24 hrs), I was thinking maybe I could divide the install_mysql into two parts.

Modify part one and run it to create just the tables and then modify the second part to connect to the tables and insert values to it.

The only problem is I hope I wont have to change the other scripts related to the website for its proper functioning.

What are your thoughts, guys?

~ Jerry!
the script run the query, :)
- replace "&DoMySQL(" with print and remove the final ')'. This will expand variables and produce the query as strings
- run into a terminal and redirect output to a text file
- remove manualy garbage from text file and load into mysql

I really don't know if this is not a bigger complication but could be a solution.

regards

genx_000

07-05-2008, 10:26 PM

I was thinking much along the same lines too.

Here is an interesting fact. I decided to write a script of my own to test the creation of a sample table, and insert values into via a perl script.

and wholla!! it worked just fine. The only thing is I'm not sure how to incorporate multiple insert statements into a string. Here is the code I wrote to generate the table:

#!/usr/bin/perl

use strict;

use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

my $database = "DBI:mysql:myfunlotto";
my $user = "mfladmin";
my $password = "passwrd";

my $query = new CGI;

print $query->header;

my $dbh = DBI -> connect($database,$user,$password) || die "\n\t Error on DBI->connect. \n\tMake sure the server is running!\n\t";

####### Subroutine PrintPersonsTable()

sub PrintPersonsTable{

my $sql = "Select personid, pname, city from persons; ";
my $sth = $dbh->prepare($sql);
$sth->execute() || warn "****** SELECT ERROR ****** $!";

print (" personid\t pname \t city\n");
print (" --------\t--------------\t-------------\n");

my $dat; # Create a local variable to hold returned raw data
while ($dat = $sth->fetchrow_hashref ) # fetch each row
{

print ("\t$dat->{personid}\t ");
print ("$dat->{pname}\t ");
print ("$dat->{city}\n");
}
print ("\n");

} ####### End subroutine PrintPersonsTable

################################## DROP TABLE PERSONS - IF EXISTS

my $sql = "DROP TABLE persons;";
my $sth = $dbh->prepare($sql); # sth = statement handle
$sth->execute() || warn "****** DROP TABLE ERROR ******* $!";

################################### CREATE TABLE PERSONS

my $sql = "CREATE TABLE persons (
personid INTEGER PRIMARY KEY,
pname VARCHAR(15) NOT NULL,
city VARCHAR(15)
);";

my $sth = $dbh->prepare($sql);
$sth->execute() || warn "*********** CREATE TABLE ERROR ************ $!";

################################### INSERT 2 ROWS

my $sql = "INSERT INTO persons VALUES (1, 'Carol Smith', 'Boston'); ";
my $sql2 = "INSERT INTO persons VALUES (2, 'Sam Jones', 'Detroit'); ";

my $sth = $dbh->prepare($sql);
my $sth2 = $dbh->prepare($sql2);

$sth->execute() || warn "***** INSERT ERROR ***** $!";
$sth2->execute() || warn "***** INSERT ERROR - 2nd RECORD ***** $!";

PrintPersonsTable();

#########################################

$sth->finish;
$sth2->finish;
$dbh->disconnect;

Under INSERT 2 ROWS section i tried doing the following:
==========================================

my $sql = " INSERT INTO persons VALUES (1, 'Carol Smith', 'Boston');
INSERT INTO persons VALUES (2, 'Sam Jones', 'Detroit'); ";

... and it failed giving me an error saying:

dbiquery_create.pl: INSERT INTO persons VALUES (2, 'Sam Jones', 'Detroit')' at line 1 at dbiquery_create.pl line 66.
dbiquery_create.pl: ***** INSERT ERROR ***** at dbiquery_create.pl line 66.

would you be able to tell me why this happened? And if possible how can you input multiple values into a table using just one statement declaration.

Thanks for your help guys.

~ genx

oesxyl

07-05-2008, 10:39 PM

try this way:

INSERT INTO persons VALUES (1, 'Carol Smith', 'Boston'),
(2, 'Sam Jones', 'Detroit')

regards

genx_000

07-05-2008, 10:52 PM

hi oesxyl,

Thanks a million, you r an absolute genius. It worked!

So now I'm absolutely sure the problem is not with my 'mysql' server or connection. .... hmmm so now I'll start analyzing the mysql script line by line.

God I hate that! ;)

By the way oesxyl, is there a personal email address for you. and would it be ok if i could get in touch with you via email?

oesxyl

07-05-2008, 11:12 PM

By the way oesxyl, is there a personal email address for you. and would it be ok if i could get in touch with you via email?
I'm sorry but this is not a good idea for two reason:
1. are many member of this forum with a lot of knowledge and could help you better then I do. Of course that don't mean I don't help when I can.
2. your problems posted here and solutions can help other users to solve their problems when they search the forum.

regards

FishMonger

07-05-2008, 11:14 PM

I've made a few modifications for you to try, but it can use more.
#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

my $database = "DBI:mysql:myfunlotto";
my $user = "mfladmin";
my $password = "passwrd";

my $query = new CGI;

print $query->header;
warningsToBrowser(1);

my $dbh = DBI->connect($database, $user, $password, { RaiseError => 1 })
or die "Error on DB connection $DBI->errstr";

####### Subroutine PrintPersonsTable()

sub PrintPersonsTable{

my $sql = "Select personid, pname, city from persons";
my $sth = $dbh->prepare($sql);

# the RaiseError in the connect statement takes care of the error reporting
$sth->execute();

# since it appears that you want this in a cgi setting, using the spaces and tabs don't make sense.
print (" personid\t pname \t city\n");
print (" --------\t--------------\t-------------\n");

my $dat; # Create a local variable to hold returned raw data
while ($dat = $sth->fetchrow_hashref ) # fetch each row
{

print ("\t$dat->{personid}\t ");
print ("$dat->{pname}\t ");
print ("$dat->{city}\n");
}
print ("\n");

} ####### End subroutine PrintPersonsTable

################################## DROP TABLE PERSONS - IF EXISTS

my $sth = $dbh->prepare("DROP TABLE persons"); # sth = statement handle
$sth->execute();

################################### CREATE TABLE PERSONS

my $sql = "CREATE TABLE persons (
personid INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
pname VARCHAR(15) NOT NULL,
city VARCHAR(15)
);";

$sth = $dbh->prepare($sql);
$sth->execute();

################################### INSERT 2 ROWS

$sth = $dbh->prepare("INSERT INTO persons (pname, city) VALUES (?,?)");

# I'd need to verify this, but we may need to add undef as the 1st parameter
$sth->execute('Carol Smith', 'Boston');
$sth->execute('Sam Jones', 'Detroit');

PrintPersonsTable();

#########################################

$dbh->disconnect;

genx_000

07-05-2008, 11:34 PM

FishMonger,

thanks a million for your help.

I modified the resulting output into html format which is more neat and easy to read.

Ofcourse, It Works like a charm!

#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

my $database = "DBI:mysql:myfunlotto";
my $user = "mfladmin";
my $password = "passwrd";

my $query = new CGI;

print $query->header;
warningsToBrowser(1);

my $dbh = DBI->connect($database, $user, $password, { RaiseError => 1 })
or die "Error on DB connection DBI->errstr";

####### Subroutine PrintPersonsTable()

sub PrintPersonsTable{

my $sql = "Select personid, pname, city from persons";
my $sth = $dbh->prepare($sql);

# the RaiseError in the connect statement takes care of the error reporting
$sth->execute();

######### MODIFIED THE OUTPUT TO HTML FORMAT ##########################

print "<html>\n<head>\n<title>Persons Data</title>\n</head>\n<body>\n";

print "<h1>Persons Data</h1>\n";

print "<table border=\"1\">\n";
print "<tr>\n<th>PERSON ID</th>\n<th>NAME</th>\n<th>CITY</th>\n";

# my $dat Create a local variable to hold returned raw data

while (my @dat = $sth->fetchrow_array ) # fetch each row
{
my ($personid, $pname, $city) = @dat;

print "<tr>\n";
print "<td>$personid</td>\n";
print "<td>$pname</td>\n";
print "<td>$city</td>\n";
print "</tr>\n";

}

print "</table>\n";
print "</body>\n</html>\n";

} ####### End subroutine PrintPersonsTable

################################## DROP TABLE PERSONS - IF EXISTS

my $sth = $dbh->prepare("DROP TABLE persons"); # sth = statement handle
$sth->execute();

################################### CREATE TABLE PERSONS

my $sql = "CREATE TABLE persons (
personid INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
pname VARCHAR(15) NOT NULL,
city VARCHAR(15)
);";

$sth = $dbh->prepare($sql);
$sth->execute();

################################### INSERT 2 ROWS

$sth = $dbh->prepare("INSERT INTO persons (pname, city) VALUES (?,?)");

# I'd need to verify this, but we may need to add undef as the 1st parameter
$sth->execute('Carol Smith', 'Boston');
$sth->execute('Sam Jones', 'Detroit');
$sth->execute('Joe Schmo', 'Texas');
$sth->execute('Jerry Alex', 'Philadelphia');
$sth->execute('Don Monch', 'San Fransisco');

PrintPersonsTable();

#########################################

$dbh->disconnect;

genx_000

07-05-2008, 11:38 PM

I'm sorry but this is not a good idea for two reason:
1. are many member of this forum with a lot of knowledge and could help you better then I do. Of course that don't mean I don't help when I can.
2. your problems posted here and solutions can help other users to solve their problems when they search the forum.

regards

Ofcourse I'd love to and am more than happy to share my knowledge with other members.

the reason why i asked is I working on a project and have couple of projects lined up. I thought with your programing expertise or knowledge maybe we could work something out.

Besides right now I'm my own army and I could use some expert team members to whip out few web projects.

genx_000

07-05-2008, 11:41 PM

would you like me to provide the link to script to view the html output?

genx_000

07-08-2008, 04:10 AM

Hi,

I just wanted to thank all of you who helped me out with the problem. Thanks a million.

Although it took me some grueling hours of debugging I was finally able to create and insert values into the table using the same script. It worked.

So thanks again, guys!

regards,
Jerry!

martinrab

01-03-2009, 04:16 AM

Hey Jerry, I have the exact same problem with probably the exact same script. I am debugging this for thousands of hours already and wish I would have just trashed it, learned how to program perl myself and be done with it. unfortunately I made the bad decision to go on with it. Can you help me out here?

Thanks, Martin :D

KevinADC

01-03-2009, 05:08 AM

martinrab

01-03-2009, 11:20 AM

what a shame. This script is driving me nuts.

martinrab

01-03-2009, 12:19 PM

OK, fixed the script so I will post the solution here. I hate when people ask for help and find the solution and don't share it. So maybe this helps someone now. The code is still ugly but at least it works:

#!/usr/bin/perl
################################################################################
# Program : iFantasy3 Lotto Written by : Ivan Santiago #
# Plataforms: Unix or Windows Writtan Date: October 3rd, 2002 #
################################################################################
# Iphat.net / IphatScripts.com Copyright © 2002 #
################################################################################
# Read the following notice carefully before using this software. #
# #
# The following are terms and conditions you must agree with in order to #
# use/run this software. #
# #
# If you DO NOT agree to the following you may not use this script, in whole #
# or any part of it. #
# #
# By using this code you agree to indemnify it's creator and all members, #
# employees and affiliates of Iphat.net including but not limited to, it's #
# creator Ivan (from Iphat.net). You may modify this script as you find #
# necessary as long as this entire header remains intact. #
# #
# Anyone using this script without paying any and all necessary fees is in #
# violation of United States and International copyright laws. Selling or #
# redistributing any of this code without prior written consent is expressly #
# forbidden. #
# #
# This program should be tested with non-real data before it is publicly used. #
# The user of this scripts assumes the entire risk of using this program. #
# This agreement shall be governed by the laws of the State of Florida. #
################################################################################
# THIS SOFTWARE INCLUDING FILES THAT ACCOMPANY IT ARE SOLD AND DISTRIBUTED #
# "AS IS" AND WITHOUT ANY WARRANTIES EXPRESSED OR IMPLIED, INCLUDING, BUT NOT #
# LIMITED TO, PERFORMANCE AND MERCHANTABILITY. NO WARRANTY OF FITNESS FOR ANY #
# PARTICULAR REASON OR PURPOSE IS OFFERED. ANY AND ALL LIABILITY OF THE SELLER #
# IS LIMITED EXCLUSIVELY TO PRODUCT REPLACEMENT OR REPAIR - NO REFUNDS ARE #
# GIVEN OR OFFERED FOR ANY OF OUR CURRENT OR FUTURE SCRIPTS #
################################################################################
use CGI::Carp qw(fatalsToBrowser);
require "admin-list.inc";

$section = "Setting Up the MySQL Database ...";
&PrintHead;
&DoMySQL("CREATE TABLE $advtable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , password varchar(35) NOT NULL , company varchar(75) , url varchar(255) , email varchar(75) NOT NULL , fullname varchar(75) NOT NULL , orders int NOT NULL , totalspent float NOT NULL , extra1 varchar(75) , extra2 varchar(75) , extra3 varchar(75) , PRIMARY KEY (id), KEY custid (id))");
&DoMySQL("INSERT INTO $advtable (id,userid,password,company,url,email,fullname,orders,totalspent,extra1,extra2,extra3) VALUES('1','admin','$adminpassword','$site_name','$clickthru_url','$admin_email','$admin_name','0',' 0','','','')");
&DoMySQL("CREATE TABLE $bookstable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , refclicks int NOT NULL , referrals int NOT NULL , tickets_played int NOT NULL , tickets_avail int NOT NULL , tickets_won int NOT NULL , PRIMARY KEY (id), UNIQUE userid (userid))");
&DoMySQL("INSERT INTO $bookstable (id,userid,refclicks,referrals,tickets_played,tickets_avail,tickets_won) VALUES('1','admin','0','0','0','100','0')");
&DoMySQL("CREATE TABLE $drawtable (id int NOT NULL auto_increment, win_number int NOT NULL , win_date varchar(50) NOT NULL , win_time varchar(50) NOT NULL , win_ip varchar(50) NOT NULL , winners int NOT NULL , PRIMARY KEY (id))");
&DoMySQL("CREATE TABLE $memtable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , password varchar(35) NOT NULL , first varchar(35) NOT NULL , last varchar(35) NOT NULL , email varchar(125) NOT NULL , city varchar(125) , state char(2) , zipcode varchar(125) , notes text , status varchar(75) NOT NULL , estatus varchar(75) NOT NULL , vcode varchar(128) , ipadd varchar(50) NOT NULL , referrer varchar(35) , PRIMARY KEY (id), UNIQUE userid (userid), KEY id (id))");
&DoMySQL("INSERT INTO $memtable (id,userid,password,first,last,email,city,state,zipcode,notes,status,estatus,vcode,ipadd,referrer) VALUES('1','admin','$admin_password','$admin_name','($site_name)','$admin_email','Your City','FL','33713',NULL,'ACTIVE','VERIFIED','','$ipadd','')");
&DoMySQL("CREATE TABLE $msgtable (id int NOT NULL auto_increment, messageid varchar(50) NOT NULL , contents longtext NOT NULL , lastedited varchar(255) NOT NULL , description longtext , PRIMARY KEY (id), UNIQUE messageid (messageid), KEY id (id))");
&DoMySQL("INSERT INTO $msgtable (id,messageid,contents,lastedited,description) VALUES('1','Verify Email','Hi ~firstname~,\n\nIn order for you to start using your account you must\nclick on the email verification link included below.\n\nEmail Verification Link:\n~cgibin~/verify.cgi?~vcode~\n\n\nAdmin,\n~sitename~','','Email Verification Link Message')");
&DoMySQL("INSERT INTO $msgtable (id,messageid,contents,lastedited,description) VALUES('2','Password Resend','Hi ~firstname~,\n\nHere is your login information as requested by you or some\none at ~ipaddress~.\n\nUser ID: ~userid~\nPassword: ~password~\n\n\nAdmin,\n~sitename~','','Password Resend Email Message')");
&DoMySQL("INSERT INTO $msgtable (id,messageid,contents,lastedited,description) VALUES('3','Winner Notification','Congratulations ~firstname~, You have WON!!\r\n\r\nDetails and Prize Redeeming Instructions go here.\r\n','','This is the member winning ticket notification email. Here add the instructions on how to redeem the prize you are offering for winning.')");
&DoMySQL("CREATE TABLE $orderstable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , linkid varchar(25) , linkurl text NOT NULL , linktype varchar(128) , linkxtra text , maxclicks int NOT NULL , status varchar(128) NOT NULL , datedone varchar(12) , clicks int(11) NOT NULL , PRIMARY KEY (id))");
&DoMySQL("INSERT INTO $orderstable (id,userid,linkid,linkurl,linktype,linkxtra,maxclicks,status,datedone,clicks) VALUES('1','admin','Admin-001','http://iphatscripts.com/','TICKET CLICKS','http://iphatscripts.com/imgs/banner_001.gif','100','OPEN ORDER','','7')");
&DoMySQL("INSERT INTO $orderstable (id,userid,linkid,linkurl,linktype,linkxtra,maxclicks,status,datedone,clicks) VALUES('2','admin','Admin-002','http://iphatscripts.com/','FREE TICKET IMG','http://iphatscripts.com/imgs/banner_001.gif','100','OPEN ORDER','','12')");
&DoMySQL("INSERT INTO $orderstable (id,userid,linkid,linkurl,linktype,linkxtra,maxclicks,status,datedone,clicks) VALUES('3','admin','Admin-003','http://iphatscripts.com/','FREE TICKET LNK','IphatScripts.com - Home of the 100% Profit Kits!','100','OPEN ORDER','','11')");
&DoMySQL("CREATE TABLE $htmtable (id int NOT NULL auto_increment, pageid varchar(255) NOT NULL , contents longtext NOT NULL , lastedited varchar(255) NOT NULL , description varchar(255) , PRIMARY KEY (id), KEY id (id))");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('1','Referral Banners Page','Banner Option #1:\r\nBanner #1 HTML Code:<p align=\"center\"><a href=\"~cgibin~/p.cgi?~userid~/index.html\" target=\"_blank\"><img src=\"/imgs/banner_001.gif\" border=\"0\"></a></p>','','Banners you want your members to use for promting your Lotto Site. Use (~cgibin~/p.cgi?~userid~/index.html) where you want the referral link displayed.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('2','Purchase Tickets Page','100 Tickets for ONLY $3.00!!!\n\nGet 100 Playable Lotto Tickets for your account at the incredibly low rate of $3.00!!!\n\n---------| Payment Button HTML Code Here |----------','','Filling information about the tickets packages you offer for purchase, and the HTML code of the payment buttons for members to order them.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('3','Banner Code','','','Default Banner Code for ALL your pages')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('4','Template Header','<html>\n<head>\n<title>~pagetitle~</title>\n<style>\n\n</style>\n</head>\n<body>\n','','Top part of the HTML Coding for EVERY page made thru the iPaidEmail scripts package.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('5','Template Footer','</body>\n</html>\n','','Bottom part of the HTML Coding for EVERY page made thru the iPaidEmail scripts package.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('6','Members Main Page','Welcome to the ~sitename~ Members Section!','','HTML Coding you want displayed on the members section main page.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('7','Advertisers Main Page','Welcome to the ~sitename~ Advertisers Section!','','HTML Coding you want displayed on the advertisers section main page.')");
&DoMySQL("CREATE TABLE $ticketstable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , a int(1) NOT NULL , b int(1) NOT NULL , c int(1) NOT NULL , status varchar(28) NOT NULL , ipadd varchar(50) NOT NULL , dateplayed varchar(50) NOT NULL , extra varchar(255) , PRIMARY KEY (id))");
&DoMySQL("CREATE TABLE $tracktable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , ipadd varchar(50) NOT NULL , linkid varchar(25) NOT NULL , date varchar(12) NOT NULL , time varchar(15) NOT NULL , PRIMARY KEY (id))");
print "<p>    </p>\n";
print "<p><b>The MySQL Database has been setup properly, you can now use your Lotto Site!</b></p>\n";
print "<p>    </p>\n";
&PrintFoot;
exit;

malan70

06-02-2010, 02:01 PM

Did you get the whole script to work?
I have the same script and anything that points to cgi-bin/*****.cgi/html doesnt work..

OK, fixed the script so I will post the solution here. I hate when people ask for help and find the solution and don't share it. So maybe this helps someone now. The code is still ugly but at least it works:

#!/usr/bin/perl
################################################################################
# Program : iFantasy3 Lotto Written by : Ivan Santiago #
# Plataforms: Unix or Windows Writtan Date: October 3rd, 2002 #
################################################################################
# Iphat.net / IphatScripts.com Copyright © 2002 #
################################################################################
# Read the following notice carefully before using this software. #
# #
# The following are terms and conditions you must agree with in order to #
# use/run this software. #
# #
# If you DO NOT agree to the following you may not use this script, in whole #
# or any part of it. #
# #
# By using this code you agree to indemnify it's creator and all members, #
# employees and affiliates of Iphat.net including but not limited to, it's #
# creator Ivan (from Iphat.net). You may modify this script as you find #
# necessary as long as this entire header remains intact. #
# #
# Anyone using this script without paying any and all necessary fees is in #
# violation of United States and International copyright laws. Selling or #
# redistributing any of this code without prior written consent is expressly #
# forbidden. #
# #
# This program should be tested with non-real data before it is publicly used. #
# The user of this scripts assumes the entire risk of using this program. #
# This agreement shall be governed by the laws of the State of Florida. #
################################################################################
# THIS SOFTWARE INCLUDING FILES THAT ACCOMPANY IT ARE SOLD AND DISTRIBUTED #
# "AS IS" AND WITHOUT ANY WARRANTIES EXPRESSED OR IMPLIED, INCLUDING, BUT NOT #
# LIMITED TO, PERFORMANCE AND MERCHANTABILITY. NO WARRANTY OF FITNESS FOR ANY #
# PARTICULAR REASON OR PURPOSE IS OFFERED. ANY AND ALL LIABILITY OF THE SELLER #
# IS LIMITED EXCLUSIVELY TO PRODUCT REPLACEMENT OR REPAIR - NO REFUNDS ARE #
# GIVEN OR OFFERED FOR ANY OF OUR CURRENT OR FUTURE SCRIPTS #
################################################################################
use CGI::Carp qw(fatalsToBrowser);
require "admin-list.inc";

$section = "Setting Up the MySQL Database ...";
&PrintHead;
&DoMySQL("CREATE TABLE $advtable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , password varchar(35) NOT NULL , company varchar(75) , url varchar(255) , email varchar(75) NOT NULL , fullname varchar(75) NOT NULL , orders int NOT NULL , totalspent float NOT NULL , extra1 varchar(75) , extra2 varchar(75) , extra3 varchar(75) , PRIMARY KEY (id), KEY custid (id))");
&DoMySQL("INSERT INTO $advtable (id,userid,password,company,url,email,fullname,orders,totalspent,extra1,extra2,extra3) VALUES('1','admin','$adminpassword','$site_name','$clickthru_url','$admin_email','$admin_name','0',' 0','','','')");
&DoMySQL("CREATE TABLE $bookstable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , refclicks int NOT NULL , referrals int NOT NULL , tickets_played int NOT NULL , tickets_avail int NOT NULL , tickets_won int NOT NULL , PRIMARY KEY (id), UNIQUE userid (userid))");
&DoMySQL("INSERT INTO $bookstable (id,userid,refclicks,referrals,tickets_played,tickets_avail,tickets_won) VALUES('1','admin','0','0','0','100','0')");
&DoMySQL("CREATE TABLE $drawtable (id int NOT NULL auto_increment, win_number int NOT NULL , win_date varchar(50) NOT NULL , win_time varchar(50) NOT NULL , win_ip varchar(50) NOT NULL , winners int NOT NULL , PRIMARY KEY (id))");
&DoMySQL("CREATE TABLE $memtable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , password varchar(35) NOT NULL , first varchar(35) NOT NULL , last varchar(35) NOT NULL , email varchar(125) NOT NULL , city varchar(125) , state char(2) , zipcode varchar(125) , notes text , status varchar(75) NOT NULL , estatus varchar(75) NOT NULL , vcode varchar(128) , ipadd varchar(50) NOT NULL , referrer varchar(35) , PRIMARY KEY (id), UNIQUE userid (userid), KEY id (id))");
&DoMySQL("INSERT INTO $memtable (id,userid,password,first,last,email,city,state,zipcode,notes,status,estatus,vcode,ipadd,referrer) VALUES('1','admin','$admin_password','$admin_name','($site_name)','$admin_email','Your City','FL','33713',NULL,'ACTIVE','VERIFIED','','$ipadd','')");
&DoMySQL("CREATE TABLE $msgtable (id int NOT NULL auto_increment, messageid varchar(50) NOT NULL , contents longtext NOT NULL , lastedited varchar(255) NOT NULL , description longtext , PRIMARY KEY (id), UNIQUE messageid (messageid), KEY id (id))");
&DoMySQL("INSERT INTO $msgtable (id,messageid,contents,lastedited,description) VALUES('1','Verify Email','Hi ~firstname~,\n\nIn order for you to start using your account you must\nclick on the email verification link included below.\n\nEmail Verification Link:\n~cgibin~/verify.cgi?~vcode~\n\n\nAdmin,\n~sitename~','','Email Verification Link Message')");
&DoMySQL("INSERT INTO $msgtable (id,messageid,contents,lastedited,description) VALUES('2','Password Resend','Hi ~firstname~,\n\nHere is your login information as requested by you or some\none at ~ipaddress~.\n\nUser ID: ~userid~\nPassword: ~password~\n\n\nAdmin,\n~sitename~','','Password Resend Email Message')");
&DoMySQL("INSERT INTO $msgtable (id,messageid,contents,lastedited,description) VALUES('3','Winner Notification','Congratulations ~firstname~, You have WON!!\r\n\r\nDetails and Prize Redeeming Instructions go here.\r\n','','This is the member winning ticket notification email. Here add the instructions on how to redeem the prize you are offering for winning.')");
&DoMySQL("CREATE TABLE $orderstable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , linkid varchar(25) , linkurl text NOT NULL , linktype varchar(128) , linkxtra text , maxclicks int NOT NULL , status varchar(128) NOT NULL , datedone varchar(12) , clicks int(11) NOT NULL , PRIMARY KEY (id))");
&DoMySQL("INSERT INTO $orderstable (id,userid,linkid,linkurl,linktype,linkxtra,maxclicks,status,datedone,clicks) VALUES('1','admin','Admin-001','http://iphatscripts.com/','TICKET CLICKS','http://iphatscripts.com/imgs/banner_001.gif','100','OPEN ORDER','','7')");
&DoMySQL("INSERT INTO $orderstable (id,userid,linkid,linkurl,linktype,linkxtra,maxclicks,status,datedone,clicks) VALUES('2','admin','Admin-002','http://iphatscripts.com/','FREE TICKET IMG','http://iphatscripts.com/imgs/banner_001.gif','100','OPEN ORDER','','12')");
&DoMySQL("INSERT INTO $orderstable (id,userid,linkid,linkurl,linktype,linkxtra,maxclicks,status,datedone,clicks) VALUES('3','admin','Admin-003','http://iphatscripts.com/','FREE TICKET LNK','IphatScripts.com - Home of the 100% Profit Kits!','100','OPEN ORDER','','11')");
&DoMySQL("CREATE TABLE $htmtable (id int NOT NULL auto_increment, pageid varchar(255) NOT NULL , contents longtext NOT NULL , lastedited varchar(255) NOT NULL , description varchar(255) , PRIMARY KEY (id), KEY id (id))");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('1','Referral Banners Page','Banner Option #1:\r\nBanner #1 HTML Code:<p align=\"center\"><a href=\"~cgibin~/p.cgi?~userid~/index.html\" target=\"_blank\"><img src=\"/imgs/banner_001.gif\" border=\"0\"></a></p>','','Banners you want your members to use for promting your Lotto Site. Use (~cgibin~/p.cgi?~userid~/index.html) where you want the referral link displayed.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('2','Purchase Tickets Page','100 Tickets for ONLY $3.00!!!\n\nGet 100 Playable Lotto Tickets for your account at the incredibly low rate of $3.00!!!\n\n---------| Payment Button HTML Code Here |----------','','Filling information about the tickets packages you offer for purchase, and the HTML code of the payment buttons for members to order them.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('3','Banner Code','','','Default Banner Code for ALL your pages')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('4','Template Header','<html>\n<head>\n<title>~pagetitle~</title>\n<style>\n\n</style>\n</head>\n<body>\n','','Top part of the HTML Coding for EVERY page made thru the iPaidEmail scripts package.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('5','Template Footer','</body>\n</html>\n','','Bottom part of the HTML Coding for EVERY page made thru the iPaidEmail scripts package.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('6','Members Main Page','Welcome to the ~sitename~ Members Section!','','HTML Coding you want displayed on the members section main page.')");
&DoMySQL("INSERT INTO $htmtable (id,pageid,contents,lastedited,description) VALUES('7','Advertisers Main Page','Welcome to the ~sitename~ Advertisers Section!','','HTML Coding you want displayed on the advertisers section main page.')");
&DoMySQL("CREATE TABLE $ticketstable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , a int(1) NOT NULL , b int(1) NOT NULL , c int(1) NOT NULL , status varchar(28) NOT NULL , ipadd varchar(50) NOT NULL , dateplayed varchar(50) NOT NULL , extra varchar(255) , PRIMARY KEY (id))");
&DoMySQL("CREATE TABLE $tracktable (id int NOT NULL auto_increment, userid varchar(35) NOT NULL , ipadd varchar(50) NOT NULL , linkid varchar(25) NOT NULL , date varchar(12) NOT NULL , time varchar(15) NOT NULL , PRIMARY KEY (id))");
print "<p>    </p>\n";
print "<p><b>The MySQL Database has been setup properly, you can now use your Lotto Site!</b></p>\n";
print "<p>    </p>\n";
&PrintFoot;
exit;