newtoperl101 has asked for the wisdom of the Perl Monks concerning the following question:

Hi,(NEW UPDATED QUESTION) I wrote a PERL .cgi script to connect to database. I also created a html form for user input. The idea is to pass parameters from HTML form to CGI. However, when I run the html file from the browser it simply publishes the script instead of passing parameters. Yes, I have configured Apache for CGI and gave write permissions to my files. The cgi file is in cgi-bin and the html file in htdocs. Please help why this isn't working. Here is my html file contactsform.html:

<html>
<head><title>ADDRESS BOOK</title></head>
<body bgcolor="#FFFFFF" link="#0000FF" alink="#FF0000" vlink="#C000FF"
+>
<h1>ADDRESS BOOK CONTACTS</h1>

<table>
<form method="post" action="C:/Apache/Apache2/cgi-bin/sqlconfig.cgi">

  <tr>
  <td align="right">Last Name:</td>
  <td align="left"><input type="text" name="lname" size="15" maxlength
+="50"></td>
  <td align="right">First Name:</td>
  <td align="left"><input type="text" name="fname" size="15" maxlength
+="50"></td>
  </tr>
  <tr>
  <td align="right">Phone:</td>
  <td align="left"><input type="text" name="phone" size="15" maxlength
+="50"></td>
  <td align="right">Email:</td>
  <td align="left"><input type="text" name="email" size="15" maxlength
+="50"></td>
  </tr>
  <tr>
  <td align="right">Address:</td>
  <td align="left"><input type="text" name="address" size="15" maxleng
+th="50"></td>
  <td align="right">Zip Code:</td>
  <td align="left"><input type="text" name="zip" size="15" maxlength="
+50"></td>
  </tr>
  </table>

<input type="submit" value="Submit">
</form>
</body>
</html>

[download]

And this is the perl script sqlconfig.cgi:


#!c:/Dwimperl/perl/bin/perl.exe
# PERL MODULES WE WILL BE USING
use CGI;
use DBI;
use DBD::mysql;

# Config DB variables
our $platform = "mysql";
our $database = "test";
our $host = "localhost";
our $port = "3306";
our $tablename = "addressbook";
our $user = "root";
our $pw = "password";
our $q = new CGI;

# DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306";

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);

#Get the parameter from your html form.
$lname=$q->param('lname');
$fname=$q->param('fname');
$phone=$q->param('phone');
$email=$q->param('email');
$address=$q->param('address');
$zip=$q->param('zip');

print $q->header; 

$sql="INSERT INTO test.addressbook(last_name,first_name) values('$lnam
+e','$fname')";
$sth = $connect->prepare($sql)
or die "Can't prepare $sql: $connect->errstrn";
#pass sql query to database handle..

$rv = $sth->execute
or die "can't execute the query: $sth->errstrn";
#execute your query

if ($rv==1){
print "Record has been successfully updated !!!n";
}else{
print "Error!!while inserting recordn";
exit;
}

[download]
  • Comment on How to bind html page with PERL script to accept user input to Mysql database (eg. Phonebook)
  • Select or Download Code

Replies are listed 'Best First'.
Re: How to bind html page with PERL script to accept user input to Mysql database (eg. Phonebook)
by ww (Archbishop) on Jul 04, 2013 at 00:44 UTC
    Lacking some pertinent information (and knowing zippola about Padre), I'm taking a WAG that you have a server running somewhere. If it's Apache, the path in your form action doesn't look right.

    And, depending on your Apache config, you may have to rename it as sqlconfig.cgi.

    If Padre provides the server, ignore this....


    If you didn't program your executable by toggling in binary, it wasn't really programming!

[reply]

      Thanks. I renamed it to .cgi and also have both the files in a subfolder under cgi-bin. Where (what path)should my form action point to?

[reply]

        "Where...?" That's one of the (implicit) questions I posed for you. Your server's administrator should know. If you're the admin, you need to find out... and to learn the answers to the other questions/ambiguities above. I don't have any way of offered more than alternatives... and you need to learn those alts yourself.

        Update: Missed your ref to putting the script in a sub-folder of cgi-bin. That's probably OK, but you'll have to account for that sub-dir in your form action element. Otherwise, Apache will go looking for your script in (some?) cgi-bin and not find it. /Update

        But I'm pretty confident that there's NO NEED NOR REASON to put the .html in cgi-bin. In fact, it's sometimes regarded as very bad practice.

        UPDATE: NB OP's text between my markup, ***1 and /***1.

        OP has updated the node text & the html (form action path) in the top node of this thread. The original content went like this:

        Hi, I am new to PERL. I am using Padre and wrote a PERL script to connect to database. I also created a html form for user input. How to bind html page with PERL script to accept user input to Mysql database (eg. Phonebook). ***1 The idea is to open browser on running the script for user input to add/delete/update entries from the database.
        When I run this script, it adds a blank row to database, but doesn't open the html page where I am supposed to add values. /***1
        I have Apache running but my files are in a perl project folder. Do I need to shift my project to cgi-bin? Here is my html file contactsform.html:

        <html>
<head><title>ADDRESS BOOK</title></head>
<body bgcolor="#FFFFFF" link="#0000FF" alink="#FF0000" vlink="#C000FF"
+>
<h1>ADDRESS BOOK CONTACTS</h1>

<table>
<form method="post" action="sqlconfig.pl">

  <tr>
  <td align="right">Last Name:</td>
  <td align="left"><input type="text" name="lname" size="15" maxlength
+="50"></td>
  <td align="right">First Name:</td>
  <td align="left"><input type="text" name="fname" size="15" maxlength
+="50"></td>
  </tr>
  <tr>
  <td align="right">Phone:</td>
  <td align="left"><input type="text" name="phone" size="15" maxlength
+="50"></td>
  <td align="right">Email:</td>
  <td align="left"><input type="text" name="email" size="15" maxlength
+="50"></td>
  </tr>
  <tr>
  <td align="right">Address:</td>
  <td align="left"><input type="text" name="address" size="15" maxleng
+th="50"></td>
  <td align="right">Zip Code:</td>
  <td align="left"><input type="text" name="zip" size="15" maxlength="
+50"></td>
  </tr>
  </table>

<input type="submit" value="Submit" onclick="addcontact">
</form>
</body>
</html>

        [download]

        And this is the perl script sqlconfig.pl:

        
#!c:/Dwimperl/perl/bin/perl.exe
# PERL MODULES WE WILL BE USING
use CGI;
use DBI;
use DBD::mysql;
use HTML::TEMPLATE;

# Config DB variables
our $platform = "mysql";
our $database = "test";
our $host = "localhost";
our $port = "3306";
our $tablename = "addressbook";
our $user = "root";
our $pw = "password";
our $q = new CGI;

# DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306";

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);

#Get the parameter from your html form.
$lname=$q->param('lname');
$fname=$q->param('fname');
$phone=$q->param('phone');
$email=$q->param('email');
$address=$q->param('address');
$zip=$q->param('zip');

print $q->header; 

$sql="INSERT INTO test.addressbook(last_name,first_name) values('$lnam
+e','$fname')";
$sth = $connect->prepare($sql)
or die "Can't prepare $sql: $connect->errstrn";
#pass sql query to database handle..

$rv = $sth->execute
or die "can't execute the query: $sth->errstrn";
#execute your query

if ($rv==1){
print "Record has been successfully updated !!!n";
}else{
print "Error!!while inserting recordn";
exit;
}

        [download]

        The announced, but non-specific edit may have occasioned considerable confusion below.

        I was certainly confused (and may still be) BUT -- upon trying to explain why the original raised the bad path suspicion (eg, that Apache doesn't expect to find executables in a "perl project folder" -- I discovered the language now marked by ***1.

        Does anyone else read that, in paraphrase, as I am calling the script by some unspecified means -- perhaps even from the C:> prompt -- and expect it to somehow cause my browser to start and read the html form -- or have I just been looking at this too long?

        meh!


        If you didn't program your executable by toggling in binary, it wasn't really programming!

[reply]
[d/l]
[select]

        Adjust your form tag to location of cgi script like this <form method="post" action="/cgi-bin/subfolder/sqlconfig.cgi">. Put the html page in the htdocs folder.

        In your cgi script I suggest to keep the database/sql processing separate and put all the cgi at the end of the script like this

        my $message;
if ($rv == 1){
  $message =  q(Record has been successfully updated !!!);
}else{
  $message =  q(<b style="color:red">Error!!while inserting records</b
+>);
##  exit;
}

# return to html page
my $URL = '/contactsform.html';
my $refresh = 5;
print $q->header( 
 -type=>"text/html", -expires=>"-1m" , -refresh=>"$refresh ; URL=$URL"
+);

print $q->start_html,
$q->p($message),
$q->p( qq!This page returns to $URL in $refresh seconds,
Click <a href="$URL">here</a> to return now! ),
$q->end_html;

        [download]

        It would be advisable to add use strict but you will need declare all your variables with my. I can't see any reason to our

        Also, whilst testing you might find it useful to add use CGI::Carp 'fatalsToBrowser' but remove it when the script is working.


        poj
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom