I know DBMS,
Currently I am not being able to insert data using Perl CGI.
it’s covered in the module documentation:
usually your perl scripts go in the cgi-bin if this is a CGI application. The public cgi-bin should be in the public_html or www folder of your website. But some sites are setup to run perl (or cgi) scripts from any directory in the public_html or www folders.
M experience with mysql is limited so I can’t just post the code for you.
I have created perl database and subscribers table.
I don’t think you can insert and display the values in the same execution of the script. So I would make two files.
Here is what I’d do:
#!/usr/bin/perl
use DBI;
Start insert.pl
$dbh = DBI->connect(“DBI:mysql:dbname:host”,“uname”,“upass”) or die "Error: $DBI::errstr
";
$sql = “INSERT INTO subscribers VALUES(\“chetan\”, \“chetan\@sitepoint.com\”)”;
When you insert data into a MySQL table you don’t need to state the rows you are inserting into. You just need to insert a value for each row in the same order that they are listed in your DB.
$dbh->do($sql);
$dbh->disconnect;
print "Location: display.pl
";
End insert.pl
If the values you are inserting are from a form (meaning they’re scalars) then this is what you need to do:
$sql = “INSERT INTO subscribers VALUES(?, ?)”;
$sth = $dbh->prepare($sql);
$sth->execute($userename, $emailaddr);
The values in execute() replace the ?'s. This is a much simpler way to insert scalars as values.
#!/usr/bin/perl
use DBI;
Start display.pl
$dbh = DBI->connect(“DBI:mysql:dbname:host”,“uname”,“upass”) or die "Error: $DBI::errstr
";
$sql = “SELECT username, emailaddr FROM subscribers”;
$sth = $dbh->prepare($sql);
$sth->execute;
$sth->bind_columns(undef, \($username, $email));
while ($sth->fetch()) {
print “$username: $email<br>”}
$dbh->disconnect;
End display.pl
Hope this helped
Here is all to all I did
Created database perl and subscribers table with username & email fields
Created insert.pl
#!/usr/bin/perl
use DBI;
use CGI;
$dbh = DBI->connect(“DBI:mysql: perl:localhost”,“root”,“pass”);
$sql = “INSERT INTO subscribers VALUES(‘chetan’, ‘chetan@sitepoint.com’)”;
$dbh->do($sql);
$dbh->disconnect;
print "Location: display.pl
";
Created display.cgi
#!/usr/bin/perl
use DBI;
use CGI;
$dbh = DBI->connect(“DBI:mysql: perl:localhost”,“root”,“pass”) or die "Error: $DBI::errstr
";
$sql = “SELECT username, email FROM subscribers”;
$sth = $dbh->prepare($sql);
$sth->execute;
$sth->bind_columns(undef, \($username, $email));
while ($sth->fetch()) {
print “$username: $email<br>”}
$dbh->disconnect;
Now still it shows Internal server Error
Any Comment ?
Sorry my bad.
In insert.pl change the last line to:
print "Location: display.cgi
";
It needs to be a .cgi in order to display HTML.
You really don’t need that file any more though. It should have done it’s job.
The internal server error is because of display.pl. I foregot to add some important code. Change to this:
#!/usr/bin/perl
use DBI;
print "Content-type: text/html
";
print “<html><head><title>Display Subscribers</title></head><body>”;
$dbh = DBI->connect(“DBI:mysql: perl:localhost”,“root”,“password”) or die "Error: $DBI::errstr
";
$sql = “SELECT username, email FROM subscribers”;
$sth = $dbh->prepare($sql);
$sth->execute;
$sth->bind_columns(undef, \($username, $email));
while ($sth->fetch()) {
print “$username: $email<br>”;
}
$dbh->disconnect;
print “</body></html>”;
Resave it as display.cgi and don’t forget to CHMOD 755 your files.
This time you only need to go directly to display.cgi on your server.
If you would like me to explain anything used PM me.
Thanks Fren,
I get no error at display.cgi
Finally can you help me at insert.pl
I want to insert data using simple form
Try this.
#!/usr/bin/perl
use DBI;
use CGI;
This reads and parses the data from the form
read(STDIN, $buffer, $ENV{‘CONTENT_LENGTH’});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-zA-Z0-9][a-zA-Z0-9])/pack(“C”, hex($1))/eg;
$value =~ s/
/<br \/>/g;
$FORM{$name} = $value;
}
$dbh = DBI->connect(“DBI:mysql: perl:localhost”,“root”,“pass”);
$sql = “INSERT INTO subscribers VALUES(?, ?)”;
$sth = $dbh->prepare($sql);
$sth->execute($FORM{‘username’}, $FORM{‘email’});
$dbh->do($sql);
$dbh->disconnect;
Change display.cgi in the line below to a thank you page (ie: thanks.html)
print "Location: display.cgi
";
Then in the form on your html page set the action to insert.pl or path/to/insert.pl. For the 2 text fields one should be named username and the other should be email.
Example:
<form name=“subscribers” method=“post” action=“insert.pl”>
<p>Name: <input type=“text” name=“username”><br>
Emal: <input type=“text” name=“email”><br>
<br>
<input type=“submit” name=“submit” value=“submit”>
</form>
That should do it.
Thanks for help woochoochinchill,
Will you please clear me on the previous post
Do u mean that I need to create insert.pl display.cgi and thanks.html (3) pages ?
Also what is CHMOD 755 ?
No. The thanks.html is of course up to you. It is just to say thanks to the people who subscribe. You don’t need it.
It is always a good idea to send your users to a real web page instead of an empty page like insert.pl is currently.
If you don’t want to make another page you can change the last line so it redirects to a page of your choise.
print "Location: path/to/page
";
Or you can also redirect them to the page they were on previously.
print "Location: $ENV{‘HTTP_REFERER’}
";
None of that is needed but it is user friendly.
As for the example HTML code in my previous post that is just to show you what the code would look like to submit the users name and email to the script. Since you said you wanted insert.pl to add the values submitted by a form to your database you will need some HTML code like the example code I provided.
Also to view all of your subscribers you just need to go to display.cgi and it will list them all.
Is this Right ?
Insert.pl
#!/usr/bin/perl
use DBI;
use CGI;
read(STDIN, $buffer, $ENV{‘CONTENT_LENGTH’});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-zA-Z0-9][a-zA-Z0-9])/pack(“C”, hex($1))/eg;
$value =~ s/
/<br \/>/g;
$FORM{$name} = $value;
}
$dbh = DBI->connect(“DBI:mysql: perl:localhost”,“root”,“pass”) or die "Error: $DBI::errstr
";
$sql = “INSERT INTO subscribers (username, email) VALUES(‘chetan’,‘chetan@site.com’)”;
$sth = $dbh->prepare($sql);
$sth->execute($FORM{‘username’}, $FORM{‘email’});
$dbh->do($sql);
$dbh->disconnect;
<form name=“subscribers” method=“post” action=“insert.pl”>
<p> Name: <input type=“text” name=“username”><br>
Email: <input type=“text” name=“email”><br>
<input type=“submit” name=“submit” value=“submit”>
</form>
print "Location: display.cgi
";
Created Display.cgi (No Problem)
woochoochinchill,
You are using the CGI module but doing nothing with it. Better written as:
#!/usr/bin/perl
#nearly all perl scripts should have the next two lines
use strict;
use warnings;
use DBI;
use CGI;
my $q = CGI->new;
my $name = $q->param('username');
my $email = $q->param('email');
my $dbh = DBI->connect("DBI:mysql: perl:localhost","root","pass");
my $sql = "INSERT INTO subscribers VALUES(?, ?)";
my $sth = $dbh->prepare($sql);
$sth->execute($name, $email);
$dbh->do($sql);
$dbh->disconnect;
print "Location: display.cgi\
\
";
Thanks. Didn’t even reilize that was in there.