Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k & phranque

Message Too Old, No Replies

It Works, but.

mysql form processing



3:35 am on Aug 22, 2011 (gmt 0)

5+ Year Member

Hi, The following script works but it pretty much comes down to a case of "there is a method to my madness". I say this because I know there has to be a better way but I haven't been able to figure it out after much searching, thought, blood, sweat, tears......okay, enough of that.

What I'm doing is retrieving info from a table I'll call tmp and it needs to be approved before being posted on a public viewing page or added to the text file. Everything goes into an archive file will include the approve or not approved decision. The only way I could figure out how to do this was to have the script write a file on the fly in such a way that the column "number" is used to identify the data--if an input is skipped it still works. The problem is it has to write a new file each time it is used. As you can see in the form part I used for the radio buttons
<input type="radio"name="approve$ref->{'number'}"value="1"> or valuse ="2" for the other button.

The radio buttons could be called with a name of "approve21 approve 24 approve25, etc., which works okay in case an entry was not approved and admin has to go back to it at a later time.

What are your thoughts on this? This is the best I could come up with. Granted, timtowtdi but are they all sensible?

use strict;
use CGI;
use DBI;

use CGI::Carp qw(fatalsToBrowser);

sub readform { #this subroutine is taken from YaBB
my(@pairs, $pair, $name, $value);
sub split_string
my ($string, $hash, $altdelim) = @_;
if($altdelim && $$string =~ m~;~) { @pairs = split(/;/, $$string); }
else { @pairs = split(/&/, $$string); }
foreach $pair (@pairs) {
($name,$value) = split(/=/, $pair);
$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
if (exists($hash->{$name})) {
$hash->{$name} .= ", $value";
} else {
$hash->{$name} = $value;
split_string(\$ENV{QUERY_STRING}, \%INFO, 1);
read(STDIN, my $input, $ENV{CONTENT_LENGTH});
split_string(\$input, \%FORM)

use DBI;
my $dbusername="root";
my $dbpwd="secret";
my $dbname="topsecret";
my $dbhostname="localhost";
my $dbtablename="tmp";
my $database = "dbi:mysql:".$dbname.":".$dbhostname.":3306";
#$database = "dbi:mysql:".$dbname.":".$dbhostname;
my $dbh = DBI->connect($database,$dbusername,$dbpwd) or die "Can?t connect to the DB: $DBI::errstr\n";


#$db = DBI->connect('dbi:mysql:cwtest:examplepass.ipowermysql.com:3306','examplename','00934100') or die('Couldnt connect');


my $entriespp=10;
my $epp = 10;
my $start = 0;

$sth=$dbh->prepare("select * from tmp order by number limit $start,$epp");
$num_rows= $dbh->selectrow_array('select COUNT(*) FROM tmp');
$num = $info;# total number or entries(based on counter
$start = $INFO{'start'} + 0;# number of the entry currently on top(0- first page,50 on the second, depends on $epp
if ($start > $info) { $start = $info - 1 }
elsif($start < 0) { $start = 0; }
$test=int(($start/$epp)+1);#number of currently shown page
$output='page: ';
$pagenum = int(($k/$epp)+1);
if($test == $pagenum) {
$output .=" $pagenum"; #this is the page we're currently on
} else {
$newstart = ($pagenum - 1)*$epp; #this will be the start variable for the page we link to;
$output .=qq~ <a href="adpropry127.pl?start=$newstart">$pagenum</a>~;

print" <head>\n";
print" <link rel=\"stylesheet\"type=\"text/css\"href=\"\">\n";
print" <title>Read Requests</title>\n";
print" </head>\n";
print" <body>\n";
print" <table align=\"center\"width=\"90%\"cellpadding=\"4px\"cellspacing=\"4px\"border=\"4px\"bordercolor=\"gold\"valign=\"top\">\n";
print" <tr bgcolor=\"lavender\">\n";
print" <td width=\"100%\"colspan=\"2\">\n";
print" <h2 class=\"c\">Process Prayer Requests<br /><!--$start--></h2>\n";
print" </td>\n";
print" <form method=\"post\"action=\"adpro2.pl\">\n";

$i = 0;#alt color line one
$sth=$dbh->prepare("select * from tmp order by number limit $start,$epp");

$bgcolor = ($i++ & 1) ? 'lavender' : 'lightblue'; #alt color line two#fofofo

print" </tr><tr bgcolor='$bgcolor'>\n";
print" <td width=\"10%\"><p class=\"c\">Ok &nbsp; NA<br /><input type=\"radio\"name=\"approve_$ref->{'number'}\"value=\"1\">&nbsp;&nbsp;<input type=\"radio\"name=\"approve_$ref->{'number'}\"value=\"2\"></p></td>\n";
print" <td width=\"90%\"border=\"6px\"bordercolor=\"gold\">\n";
print" <p>First Name: $ref->{'name'} <br />\n";
print" Location: $ref->{'area'} <br />\n";
print" Request: $ref->{'input'}</p>\n";
print" </td>\n";
print" </tr><tr>\n";
print" <td width=\"100%\"colspan=\"2\">\n";
print" <p class=\"c\"><input type=\"submit\"value=\"Process\">\n";
print" <br /><span class=\"c\">$output</span></p>\n";
print" </td>\n";
print" </tr>\n";
print" </form>\n";
print" </table><br /><br />\n";
print" </body>\n";
#########Begin Page Two###################
print PTL"\#!\"C:\\xampp\\perl\\bin\\perl.exe\" -w -t\n";
print PTL"use CGI\;\n";
print PTL"use strict\;\n";
print PTL"use CGI::Carp qw(fatalsToBrowser)\; \n";
print PTL"use DBI\;\n";
print PTL"print\"Content-Type:text/html\\n\\n\"\;\n";
print PTL"my \$pryarc=\"pryarc\"\;\n";
print PTL"my \$prydis=\"prydis\"\;\n";
print PTL"my(\$start,\$ref)\;\n";
print PTL"my \$query = new CGI;\n";
print PTL"my \$epp = \$query->param(\"epp\")\;\n";

print PTL"my \$dbusername=\"root\"\;\n";
print PTL"my \$dbpwd=\"secret\"\;\n";
print PTL"my \$dbname=\"topsecret\"\;\n";
print PTL"my \$dbhostname=\"localhost\"\;\n";
print PTL"my \$dbtablename=\"tmp\"\;\n";
print PTL"my \$database = \"dbi:mysql:\".\$dbname.\":\".\$dbhostname.\":3306\"\;\n";
print PTL"my \$dbh = DBI->connect(\$database,\$dbusername,\$dbpwd) or die \"Cant connect to the DB: \$DBI::errstr\\n\"\;\n";

print PTL"my \$approve\;\n";
print PTL"my \$query = new CGI\;\n";
print PTL"print\"<html>\\n\"\;\n";
print PTL"print\"<body>\\n\"\;\n";
print PTL"print\"<p align=\\\"center\\\">\\n\"\;\n";

$sth=$dbh->prepare("select * from tmp order by number limit $start,$epp");

print PTL"if(\$approve_$ref->{'number'} == 1 || \$approve_$ref->{'number'} == 2) {\n";
print PTL"my \$state=\"INSERT INTO \".\$archivetable.\" (name,area,input) VALUES ('$ref->{'name'}\',\'$ref->{'area'}\',\'$ref->{'input'}\')\"\;\n";
print PTL"my \$sth=\$dbh->prepare(\$state)\;\n";
print PTL"\$sth->execute()\;\n";

print PTL"if(\$approve_$ref->{'number'} == 1) {\n";
print PTL"if($ref->{'decision'} == 2 || $ref->{'decision'} == 3) {\n";

print PTL"my \$state=\"INSERT INTO \".\$displaytable.\" (post_date,nametag,area,input) VALUES ('$ref->{'name'}\',\'$ref->{'area'}\',\'$ref->{'input'}\')\"\;\n";

print PTL"my \$sth=\$dbh->prepare(\$state)\;\n";

print PTL"\$sth->execute()\;\n";
print PTL"}\n";
print PTL"}\n";

print PTL"if(\$approve_$ref->{'number'} eq \"1\"){\n";
print PTL"open(PNP,\">>prayerrequests.txt\")\;\n";
print PTL"print PNP\" Name: $ref->{'name'}\\n\"\;\n";
print PTL"print PNP\" Location: $ref->{'area'}\\n\"\;\n";
print PTL"print PNP\" Prayer Request: $ref->{'input'}\\n\"\;\n";
print PTL"print PNP\" --------------------------------------\\n --------------------------------------\\n\"\;\n";
print PTL"close(PNP)\;\n";
print PTL"}\n";

print PTL"}\n";

[edited by: engine at 8:26 am (utc) on Aug 22, 2011]
[edit reason] obfuscated username and password [/edit]


7:56 pm on Aug 22, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member

General advice:
use CGI.pm!
yes it's fat and does alot more than simple input parsing, but it's also activly maintained and it does it well, it'll replace your readform-sub.

Also, you might want to look into using some kind of database, just to make it easy. I assume you cannot create and update tables on the remote database?
Maybe DBD::CSV is installed. It'd let you use plain csv-files as database tables and run simple SQL-Queries against them, which will make your code much simpler.

Personally, I don't like to have all that HTML in my code. Think about using templates. Yes, your script is pretty small and does not require templates, but it's never too early.
I'd also split the functionality:
one script to query the remote database and transfer data into the local database ( whatever format that may have )
one script to list and search the local database
one script to edit a single row in the local database

that'd make your scripts much shorter and thus easier to maintain.

Software error:

Can't locate /home/deploy/webmasterworld/code_format-v6.lib in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.18.2 /usr/local/share/perl/5.18.2 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.18 /usr/share/perl/5.18 /usr/local/lib/site_perl .) at decode-post-v6.lib line 27, <THREADDAT> line 4.

For help, please send mail to the webmaster (it@imninjas.com), giving this error message and the time and date of the error.