Home > Perl > Database > Accessing SQL databases in HTML::Mason
Accessing SQL databases in HTML::Mason
Written by Philip L Yuson   
Who is this for

This article is for those who would like to know more of the features of HTML::Mason

What you need to know
Perl programming, HTML::Mason basics

Introduction
In the past 3 articles, we discussed about the different components of HTML::Mason. Most web sites require database access so when you create your HTML::Mason pages, you should also consider this.

Data base access in HTML::Mason
Accessing database in HTML::Mason is similar to accessing it in an ordinary Perl script. Let's say you have a database called article and you want to list all the rows in the article table. Assuming that your database is MySQL, you would do this in an ordinary Perl script:

use DBI;
my $db = DBI->connect('DBI:mysql:database=article',
'userid', 'password');
my $sql = qq { SELECT DATEPUBLISHED, TITLE FROM ARTICLE };
$sth = $db->prepare($sql);
$sht->execute();
so on

In HTML::Mason, you also need to connect to your database and issue SQL statements. If you have only one page that will require database access, you can hard code the connection statement. However, if you have 10 pages that require database access, you can still hard code the connection statement. But if you need to change the userid or password, you will need to change them in all these pages.

A simpler way is to separate the connection script and call it, say, connect.html

<%perl>
use DBI;
return DBI->connect('DBI;mysql;database=article',
'userid', 'password');
Then you can call this component from all webpages that need database access:

<%init>
use DBI;
my $db = $m->comp('connect.html');
my $sql = qq { SELECT DATEPUBLISHED, TITLE FROM ARTICLE };
my $sth = $db->prepare($sql);
$sth->execute();
so on
If you did it this way, any changes to the database connection statement will not require that you change all 100 scripts. Just go in and change the connect.html statement and you are all set!

Displaying all Rows
Now that you have selected a set of rows, you need to display them:
To do this, you can display them in a table like this:

<HTML>
<HEAD>
</HEAD>
<body>
<TABLE BORDER=1>
<TR>
<th>Date</th><th>Title</th>
</TR>
% while (my $rec = $sth->fetchrow_hashref() ) {
<tr>
<td><% $rec->{DATEPUBLISHED} %></td>
<td>
<A HREF=view.html?date=<% $rec->{DATEPUBLISHED} %> >
<% $rec->{TITLE} %>
</A>
</td>
</tr>
% }
</TABLE>
</BODY>
</HTML>
The result will look something like this:
 DateTime
2001-04-24


2001-05-24


2001-07-24


2001-08-24





 
Copyright: © 2017 Philip Yuson