homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

It Works, but.
mysql form processing

Msg#: 4354104 posted 3:35 am on Aug 22, 2011 (gmt 0)

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]



WebmasterWorld Senior Member 5+ Year Member

Msg#: 4354104 posted 7:56 pm on Aug 22, 2011 (gmt 0)

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.


WebmasterWorld Administrator phranque us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

Msg#: 4354104 posted 9:29 am on Aug 23, 2011 (gmt 0)

you can simplify your html by using print statements with "here document" syntax:

print <<END_OF_HTML;

this way you can avoid the necessity for newlines (\n) or escaping double quotes and it is a type of quoting syntax that supports variable interpolation.


Msg#: 4354104 posted 10:39 am on Aug 23, 2011 (gmt 0)

You mean there is a difference between "use CGI" and "use CGI.pm ? Will definitely be into that one before I shut this machine off. As far as templates are concerned I've been contemplating that pretty heavy. I would probably be best to get used to it on small scripts like this. On CVS files, well that's kind of a new word to me but I'll assuredly check it out. My problem was getting the right table rows to do the right thing as the right time which was the reason for the "while($ref=$sth->fetchrow_hashref()){" statement running from the part that displays the entries to the part that writes the file that processes them. I've so much to learn it isn't even funny. At my age, if I ever reach the point where I could be called a programmer my eyes and ears will probably be gone by then...lol. Usually for the html I use print qq~ for opening and ~; for closing. Probably should've gave that technique some thought to make the part that writes the new file a little easier. Can't thank yall' enough.


WebmasterWorld Administrator phranque us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

Msg#: 4354104 posted 12:38 pm on Aug 23, 2011 (gmt 0)

You mean there is a difference between "use CGI" and "use CGI.pm ?

as far as i can tell, you aren't using anything from the CGI module in that script.
On CVS files, well that's kind of a new word to me

CSV = comma separated values


Msg#: 4354104 posted 12:57 pm on Aug 23, 2011 (gmt 0)

Soon as I get my current batch of spaghetti sorted and on the plate that will be my next undergoing. Thanx


Msg#: 4354104 posted 12:06 pm on Sep 15, 2011 (gmt 0)

If you have a database, why don't you store your form data there?

Like phranque, I use an alternate quote method. Not sure what you call it, but it looks like this:

print qq[here is some stuff for my $customer<br />
new lines are "OK" and so are "Quotes"];

if you do a print q[$customer]; one q - your output will be $customer, and not the value of $customer.

I think janharders is recommending CGI.pm for your form data, and some basic HTML output.

your readform subroutine could be replaced with:

my $query = CGI::new();

my $number = $query->param('number');
my $letter = $query->param('letter');

and then your opening few lines of HTML can be

print $query->start_html();

You can go crazy with it if you want, but it's Perl, so there is 20 ways to solve any one problem.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved