My first cgi/mysql

Anonymous posted this at 21:46 — 7th December 2001.

Joined: Jan 1970

Hello to all board members

Ok I will try to keep this short but sql is making me nuts for days know! I got it running but still have some probs that I can't figure out so all help (also links to good tutorials on cgi/sql) is very welcome!

Her you got the code:

#!/usr/bin/perl

use strict;
$| = 1;

use CGI::Carp "fatalsToBrowser";
use CGI ":all";
use DBI;

my $serverName = "***";
my $serverPort = "***";
my $serverUser = "***";
my $serverPass = "***";
my $serverDb = "***";
my $serverTabl = "***";

print 
     header,
     start_html("SQL Guestbook"),
     h1("Add and Read Guestbook Entries");
if(my $error = check_form()) {
     show_form($error);
     print end_html;
} else {
     if(my $error = insert_entry()) {
          show_form($error);
     } else {
          show_entries();
     }
     print end_html;
}

sub show_form {
     my $error = shift;
     print hr;
     if($error) { print $error, hr; }
     print 
          start_form,
     table(map
          Tr(td($_->[0]), td(textfield($_->[1],"",undef,60))),
          ["Name", "name"],
          ["E-Mail Address", "email"],
          ["Web Site Address", "website"],
          ["Comments", "comments"],
          ),
     submit,
     end_form,
     hr;
}

sub check_form() {
     return "You didn't enter anything..." unless param();
     return "Please enter a name" unless param("name"); 
     return "Please enter your e-mail address" unless param("email");
     return;
}

sub insert_entry {
     my ($dbh, $success, $name, $email, $website, $comments,$time);
     
     $dbh = DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass);
     $name = param("name");
     $email = param("email");
     $website = param("website");
     $comments = param("comments");
     $time = time;
     $success = $dbh->do("INSERT INTO 
               $serverTabl(name,email,website,comments,time) 
               VALUES(?,?,?,?,?)", undef, $name, $email, $website, $comments, $time);
     $dbh->disconnect;
     if($success != 1) {
          return "Sorry, the database was unable to add your entry.
               Please try again later.";
     } else {
          return;
     }
}

sub show_entries {
     my ($dbh, $sth, @row);

     $dbh = DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass);
     $sth = $dbh->prepare("SELECT name,email,website,comments,time 
               FROM $serverTabl ORDER BY time");
     $sth->execute;
     print "Existing Entries",hr;
     while(@row = $sth->fetchrow_array) {
          $row[5] = scalar(localtime($row[4]));
          
          print "Web Site Address: ", $row[2], br;
          print "Comments: ", $row[3], br;
          print "Added on ", $row[4], hr;
     }
     $sth->finish;
     $dbh->disconnect;
}

The problems:

1. I would like to insert an if statement that would allow me to show
the form or the database when I want.
e.g. ...guestb.pl?action=form => print the form
...guestb.pl?action=members => print the data

2. The time isn't correct I get an output like: Added on 1007754684

If you want to see the script in action check here.

Thx a lot in advance for all help because I find it very difficult to find info on the net about cgi/sql. so if somoene can give me links to good tutorials it would be very appreciated also.

cheers
[email protected] :batman:

Peter J. Boettcher posted this at 17:37 — 10th December 2001.

They have: 812 posts

Joined: Feb 2000

I'm not going to be able to help much, Perl/MySql is not my area of expertise, but maybe you can get some ideas from this link: http://cgi.resourceindex.com/Programs_and_Scripts/Perl/Database_Manipulation/

PJ | Are we there yet?
pjboettcher.com