Here's the Perl I wrote (I called the file empnew.cgi). I made sure the program was executable, and placed the file in a directory on my web server (make sure your path points to this directory):
#!/usr/bin/perl
use CGI;
use HTML::Template;
use DBI;
use strict;
my $dsn = 'dbi:mysql:one:localhost:3306:mysql_socket=/opt/lampp/var/mysql/mysqld.sock';
# the database here is called 'one'
my $CGI = CGI->new();
my $user = 'root';
my $pass = 'passwordx';
my $dbh = DBI->connect($dsn, $user, $pass)
or die "Can’t connect to the DB: $DBI::errstr\n";
my $sth = $dbh->prepare("select empno, ename, job, sal from emp order by ename");
$sth->execute;
# Prepare a data structure for HTML::Template
my $rows;
push @{$rows}, $_ while $_ = $sth->fetchrow_hashref();
#
# initiate the template and substitute the values:
my $template = HTML::Template->new(filename => 'empnew.tmpl',
die_on_bad_params => 0, );
# call param to fill in the loop with the loop data by reference.
$template->param(ROWS => $rows);
print $CGI->header();
# print the template
print $template->output();
$dbh->disconnect();
The module CGI deals quickly and efficiently with http headers.
The module HTML::Template that is called, allows you to state the Html layout in a separate file. In our example this calls empnew.tmpl
<!-- empnew.tmpl -->
<html>
<head>
<title>Employee Listing</title>
</head>
<body>
<h1>Employees</h1>
<table border=1>
<tr>
<td bgcolor="#ffff00"><font COLOR="#800500"><b>Emp Number</b></font></td>
<td bgcolor="#ffff00"><font COLOR="#800500"><b>Emp Name</b></font></td>
<td bgcolor="#ffff00"><font COLOR="#800500"><b>Job</b></font></td>
<td bgcolor="#ffff00"><font COLOR="#800500"><b>Salary</b></font></td>
</tr>
<!-- TMPL_LOOP NAME=ROWS -->
<tr>
<td><!-- TMPL_VAR NAME=EMPNO --></td>
<td><!-- TMPL_VAR NAME=ENAME --></td>
<td><!-- TMPL_VAR NAME=JOB --></td>
<td><!-- TMPL_VAR NAME=SAL --></td>
</tr>
<!-- /TMPL_LOOP -->
</table>
</body>
</html>
There are basically 3 new kinds of tags to be aware of when making a .tmpl file for access by HTML::Template. These are
# * VARIABLES defined by <tmp_var>
# * LOOPS defined by <tmpl_loop>
# * CONDITIONALS you can use <tmpl_if> <tmpl_unless> or <tmpl_else>
Variable tags such as <tmpl_var name="FOO"> will be replaced by whatever value is assigned to parameters when calling the HTMP::Template to the parameter FOO
The other tags can be standard html formatting stuff. Here's a good introduction to using the HTML::Template
Finally, in order to access data from the Mysql database we need to use the DBI module. You can see from the Perl script that we connect to the database using parameters given (not a good idea from the security pov to have the password in this script in real life - better to bring in as a parameter from a file that can't be seen by public eyes), prepare the sql script, and then execute. For more information on starting with DBI see this tutorial
Once this is setup, it's fairly simple to change the layout by editing the tmpl file directly without needing to change the program code.
Lastly - want to introduce some way of bringing back different results according to a parameter you pass over? Simply modify the previous Perl code as follows ($sal here to be parameter you can change according to your program, the ? serves as a placeholder for this argument passed to the sql execution - this helps stop sql injection as quote characters etc are escaped):
my $sal = 2900;
my $dbh = DBI->connect($dsn, $user, $pass)
or die "Can’t connect to the DB: $DBI::errstr\n";
my $sth = $dbh->prepare("select empno, ename, job, sal from emp where sal > ? order by empno");
my $rc = $sth->execute($sal);
# Prepare a data structure for HTML::Template
I hope this has been of interest! Good luck with your web programming!
NB. Whilst reproducing this entry I had a little difficulty in getting Blogger to print the HTML source code. There's a conversion utility on this site that allows you to do this.
No comments:
Post a Comment