Form-created database with perl and MySQL

Hello. I'm not a strong programmer, but I'm trying to figure out the basics of filling a database via form as well as accessing information after it's created. I would like to use MySQL and perl as the tools.

I've been researching this for about a week, and to this point, I haven't found real specific information on how to create the form(s) for the project. I've seen the webmonkey tutorial, but it refers to eperl, which I don't want to use unless it comes with the standard perl distribution. I have the perl dbi book by oreilly, but unfortunately, it doesn't go thru the form process in much detail.

I guess I'm looking for some good examples or a tutorial that explain these processes well. The database will only have one table and about ten or so fields which will be filled by users, e.g. name, address, phone... And the same users will be able to access it, maybe with a password. Any help will be appreciated. Please feel free to email if you need more information. Thnx.

Bob

The Web Monkey tutorial at http://hotwired.lycos.com/webmonkey/98/47/index2a.html?tw=programming is pretty good if rather long winded. This one is also not bad: http://webdeveloper.com/cgi-perl/cgi_beginners_perl.html

Basically, you include a form in a Web page with HTML code like the following:

<form action="/cgi-bin/my_program.pl" method="post">
<input type="text" name="firstname"><br>
<input type="submit" value="Send!">
</form>

The opening and closing <form> tags create the form and tell the Web browser which program to ask the Web server execute (in this case a PERL script called my_program.pl that resides in the cgi-bin directory on the Web server). The method by which the data is sent to the server is also specified in the <form> tag (in this case "post"). When the user clicks on the submit button the data they have entered in the form is sent to the Web server as name/value pairs. The PERL script needs to first decifer these pairs to do anything useful with them.

In the form the <input type="text" name="firstname"> creates an input text box and gives it the name "firstname". This name is used as the name part of the name/value pair that is sent to the Web server when the form is submitted. The value part of the name/value pair is whatever the user has entered in the input text box. There are a whole bunch of input types to create everything from radio buttons to drop down lists in your forms (see http://wdvl.com/Authoring/HTML/Forms/ for a list).

Sounds like you're on the right track with the DBI connection to the MySQL database.

Regards

Geoff

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by frogman:
Hello. I'm not a strong programmer, but I'm trying to figure out the basics of filling a database via form as well as accessing information after it's created. I would like to use MySQL and perl as the tools.

I've been researching this for about a week, and to this point, I haven't found real specific information on how to create the form(s) for the project. I've seen the webmonkey tutorial, but it refers to eperl, which I don't want to use unless it comes with the standard perl distribution. I have the perl dbi book by oreilly, but unfortunately, it doesn't go thru the form process in much detail.

I guess I'm looking for some good examples or a tutorial that explain these processes well. The database will only have one table and about ten or so fields which will be filled by users, e.g. name, address, phone... And the same users will be able to access it, maybe with a password. Any help will be appreciated. Please feel free to email if you need more information. Thnx.

Bob[/quote]

Thanks alot Geoff,

I think my biggest source of confusion is how to tie in the form and the database. Are the form and script in the same page, or are they separate? In other words, do I reference the cgi script from a separate html form? If not, what path do I put in the <form action="whatgoeshere?" method="post">

I've done a form to email before, but never any database work. Sorry, there's alot to this that I don't yet understand. Thanks again for your help.

Bob

frogman,
Interacting the database with perl is very easy.I'll show you an example how you can do it.I hope you have both mysql and perl installed in your server.

First you should create a table in your mysql.
give table name "emp".then create two fields 'ename' and 'email' in emp table.

Now You can create a html page with a form.

I am giving the html name "test.html".

test.html:

<html>
<head><title>Database test</title></head>
<body>
<h1>Just a Database test</h1>
<form method="post" action="connectivity.cgi">
Ename :<input type="text" name="ename">
Email :<input type="text" name="email">
</form>
</body>
</html>


In "connectivity.cgi" you should write the following

#!/usr/bin/perl

use CGI;
use DBI;

$q=new CGI;

$ename=$q->param('ename');
#get the parameter from your html form.
$email=$q->param('email');

print $q->header;

$dbh=DBI->connect('dbi:mysql:databasename','username','pwd');

#connect to the database ..
#enter your database name ,username and password to the connection string..

$sql="INSERT INTO emp(ename,email) values('$ename','$email')";


$sth = $dbh->prepare($sql)
or die "Can't prepare $sql: $dbh->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;
}

I hope this will help you to understand the database connectivity.

You can get some good tutorial for database connectivity with perl at:
www.perl.com/pub/1999/10/DBI.html

------------------
SR -
shiju.dreamcenter.net

"The fear of the LORD is the beginning of knowledge..."



[This message has been edited by Shiju Rajan (edited June 20, 2000).]

if you are using the mysql database, you can actuallyuse a mysql module in your script.

use Mysql;
use CGI;

$database="mydb";
$user="myuser";
$pass="mypass";
$host="localhost";
$db=Mysql->connect($host,$database,$user,$pass);
$db->selectdb($database);

you can use this to connect to db