Almost every major site on the internet today uses some form of database for content. I will discuss how to implement database content using Embperl. I have chosen MySQL as a database for its speed. I will be using DBI/DBD, so this solution is easily ported to other databases if you have any preference.
Database connect.
These examples will assume you have a database up and running. I typically create a user of the database that is called 'browser', and give this user privileges to see all data. If you plan to do inserts I would suggest creating a special user for each site just for easy accounting.
Here is a simple and efficient way to connect to a MySQL database with Embperl.
[- use DBI ;
$dbh = DBI->connect("DBI:mysql:agristarts",browser,webuser);
-]
[- $cursor = $dbh->prepare("select * FROM
AgristartsTechData where ID=$id");
$cursor->execute;
-]
[- @field = $cursor->fetchrow -]
[- $cursor->finish; -]
[- $cusor = $dbh->disconnect; -]
This is purely a connect, a select, and a disconnect. Additionally, for
portability you can indicate the host you wish to connect to by placing
the hostname in the DBI connect statement. To connect to a different
type of database it usually is as simple as substituting a different DBD
in place of mysql in the above example.
Let's say you have a database of items and a given column indicates what category each item is in. You would like to make a menu of sorts that is dynamic based on the items in the database. The following example does just that, and using Embperl built in table generator to make the code a little cleaner.
[- $cursor=$dbh->prepare("select distinct category
FROM AgristartsTechData ORDER BY category");
$cursor->execute;-]
[- while (@tfield = $cursor->fetchrow)
{$field[$i] = $tfield[0]; ++$i}
-]
[-$maxcol=4-]
<table bgcolor="#000000" cellpadding=5 cellspacing=2
bordercolor="#FFFFFF">
<tr>
<td width=100 bgcolor="#000066" align="center">
<a href="smallpics.htm?gen=[+ $field[$cnt] +]">
<b>[- $field[$cnt] =~ s/\W.*// -]
<font size="2" face="Arial" color="#FFFFCC">
[+ $field[$cnt] +]
</font>
</b>
</a>
</td>
</tr>
</table>
[- $cursor->finish -]
A little explanation of the above example. The variable $maxcol is a
special Embperl variable for telling it how many columns you want in
your dynamic table and the variable $cnt is the counter that it uses for
looping purposes. The hyperlinks created simply pass a variable to the
smallpics page to tell it what category to display.
Let's say you were doing s realtors site and wanted to display a property listing on a page. You would pass a variable into the page denoting which listing to display, do a database pull, and display the information in a pleasing style.
Example url: listing.htm&ID=111
[- $id=$fdat{ID} -]
[-
$dbh=DBI->connect("DBI:mysql:house_database",
'browser','webuser',
{
RaiseError => 1
}
) or die $dbh->err;
-]
[- $cursor=$dbh->prepare("select
MLS,Leadline,Adtext,Bedrooms,Baths,Squarefeet,Pricecurrent,
Propertytype from house_database where ID=$id");
$cursor->execute;
-]
[- @field = $cursor->fetchrow -]
[- sub commify {
local $_ = shift;
1 while s/^(-?\d+)(\d{3})/$1,$2/;
return $_;
}
-]
<TABLE WIDTH="490" BORDER="0" ALIGN="CENTER">
<TR>
<TD WIDTH=220><FONT SIZE=2 FACE="VERANDA">
<B>ID:</B> [+ $field[0] +]<BR>
<B>Bedrooms:</B> [+ $field[3] +]<BR>
<B>Bath Rooms:</B> [+ $field[4] +]<BR>
</TD>
</TR>
</TABLE>
[- $cursor->finish -]
In the above example, I get a variable off the url and into perl,
connect to a database, select the given record, put the information into
an array, and display the information in a table. I also provided myself
with a subroutine for comma-fying any dollar amounts which I can call
later on.
As a final example, you can also include the databased information in javascript. This example shows a database pull that is used to make javascript to display some DHTML navigation. Don't worry, I will explain everything after the example.
<HTML>
<HEAD>
<SCRIPT LANGUAGE="JavaScript">
<!--
NS4 = (document.layers);
IE4 = (document.all);
ver4 = (NS4 || IE4);
isMac = (navigator.appVersion.indexOf("Mac") != -1);
isMenu = (NS4 || (IE4 && !isMac));
function popUp(){return};
function popDown(){return};
function startIt(){return};
if (!ver4) event = null;
//-->
</SCRIPT>
<SCRIPT LANGUAGE="JavaScript1.2">
<!--
if (isMenu) {
menuVersion = 3;
menuWidth = 120;
childOverlap = 75;
childOffset = 0;
perCentOver = null;
secondsVisible = 1.5;
fntCol = "white";
fntSiz = "10";
fntBold = false;
fntItal = false;
fntFam = "helvetica,arial,sans-serif";
backCol = "#DDDDDD";
overCol = "#FFCCCC";
overFnt = "purple";
borWid = 1;
borCol = "black";
borSty = "solid";
itemPad = 1;
imgSrc = "images/arrow.gif";
imgSiz = 10;
separator = 1;
separatorCol = "red";
isFrames = false;
navFrLoc = "left";
mainFrName = "main";
clickStart = true;
clickKill = false;
keepHilite = true;
NSfontOver = true;
showVisited = "green";
}
//-->
</SCRIPT>
<SCRIPT LANGUAGE="JavaScript1.2">
<!--
if (isMenu)
{
arMenu1 = new Array(
150,
100,30,
"","",
"#3399FF","#55BBFF",
"#0000FF","#000088",
"A through E","",1,
"F through J","",1,
"K through O","",1,
"P through T","",1,
"U through Z","",1
)
arMenu1_1 = new Array(
[- use DBI; -]
[-
$dbh=DBI->connect("DBI:mysql:agristarts",browser,password,
{
RaiseError => 1
}
) or die $dbh->err;
$athroughe = $dbh->selectall_arrayref(
"SELECT DISTINCT genus
FROM AgristartsTechData
WHERE genus >= 'a' and genus < 'f'
ORDER BY genus"
) or die $dbh->err;
$dbh->disconnect;
-]
[$ foreach $genus (0..(scalar(@{$athroughe}))-1) $]
"[+ $athroughe->[$genus][0] +]",
"main_frame.htm?gen=
[- $athroughe->[$genus][0] =~ s/ /\plus/g -]
[+ $athroughe->[$genus][0] +]",0
[$ if $genus < ((scalar(@{$athroughe}))-1) $],
[$ endif $]
[$ endforeach $]
)
arMenu1_2 = new Array(
[-
$dbh=DBI->connect("DBI:mysql:agristarts",browser,password,
{
RaiseError => 1
}
) or die $dbh->err;
$fthroughj = $dbh->selectall_arrayref(
"SELECT DISTINCT genus
FROM AgristartsTechData
WHERE genus >= 'f' and genus < 'k'
ORDER BY genus"
) or die $dbh->err;
$dbh->disconnect;
-]
[$ foreach $genus (0..(scalar(@{$fthroughj}))-1) $]
"[+ $fthroughj->[$genus][0] +]",
"main_frame.htm?gen=
[- $fthroughj->[$genus][0] =~ s/ /\plus/g -]
[+ $fthroughj->[$genus][0] +]",0
[$ if $genus < ((scalar(@{$fthroughj}))-1) $],
[$ endif $]
[$ endforeach $]
)
arMenu1_3 = new Array(
[-
$dbh=DBI->connect("DBI:mysql:agristarts",browser,password,
{
RaiseError => 1
}
) or die $dbh->err;
$kthrougho = $dbh->selectall_arrayref(
"SELECT DISTINCT genus
FROM AgristartsTechData
WHERE genus >= 'k' and genus < 'p'
ORDER BY genus"
) or die $dbh->err;
$dbh->disconnect;
-]
[$ foreach $genus (0..(scalar(@{$kthrougho}))-1) $]
"[+ $kthrougho->[$genus][0] +]",
"main_frame.htm?gen=
[- $kthrougho->[$genus][0] =~ s/ /\plus/g -]
[+ $kthrougho->[$genus][0] +]",0
[$ if $genus < ((scalar(@{$kthrougho}))-1) $],
[$ endif $]
[$ endforeach $]
)
arMenu1_4 = new Array(
[-
$dbh=DBI->connect("DBI:mysql:agristarts",browser,password,
{
RaiseError => 1
}
) or die $dbh->err;
$fthroughj = $dbh->selectall_arrayref(
"SELECT DISTINCT genus
FROM AgristartsTechData
WHERE genus >= 'p' and genus < 'u'
ORDER BY genus"
) or die $dbh->err;
$dbh->disconnect;
-]
[$ foreach $genus (0..(scalar(@{$pthrought}))-1) $]
"[+ $pthrought->[$genus][0] +]",
"main_frame.htm?gen=
[- $pthrought->[$genus][0] =~ s/ /\plus/g -]
[+ $pthrought->[$genus][0] +]",0
[$ if $genus < ((scalar(@{$pthrought}))-1) $],
[$ endif $]
[$ endforeach $]
)
arMenu1_5 = new Array(
[-
$dbh=DBI->connect("DBI:mysql:agristarts",browser,password,
{
RaiseError => 1
}
) or die $dbh->err;
$uthroughz = $dbh->selectall_arrayref(
"SELECT DISTINCT genus
FROM AgristartsTechData
WHERE genus >= 'u'
ORDER BY genus"
) or die $dbh->err;
$dbh->disconnect;
-]
[$ foreach $genus (0..(scalar(@{$uthroughz}))-1) $]
"[+ $uthroughz->[$genus][0] +]",
"main_frame.htm?gen=
[- $uthroughz->[$genus][0] =~ s/ /\plus/g -]
[+ $uthroughz->[$genus][0] +]",0
[$ if $genus < ((scalar(@{$uthroughz}))-1) $],
[$ endif $]
[$ endforeach $]
)
;
document.write("<SCRIPT LANGUAGE='JavaScript1.2'
SRC='javascript/hierMenus.js'><\/SCRIPT>");
}
// -->
</SCRIPT>
<SCRIPT LANGUAGE="JavaScript">
<!--
if (document.images) {
menu1 = new Image
menu1out = new Image
menu2 = new Image
menu2out = new Image
menu3 = new Image
menu3out = new Image
menu4 = new Image
menu4out = new Image
menu5 = new Image
menu5out = new Image
menu1.src = "images/button_agristarts_1_alternative.gif"
menu1out.src = "images/disc_agristarts_1.gif"
menu2.src = "images/button_agristarts_2_alternative.gif"
menu2out.src = "images/disc_agristarts_2.gif"
menu3.src = "images/button_agristarts_3_alternative.gif"
menu3out.src = "images/disc_agristarts_3.gif"
menu4.src = "images/button_agristarts_4_alternative.gif"
menu4out.src = "images/disc_agristarts_4.gif"
menu5.src = "images/button_agristarts_5_alternative.gif"
menu5out.src = "images/disc_agristarts_5.gif"
}
else {
menu1 = ""
menu1out = ""
menu2 = ""
menu2out = ""
menu3 = ""
menu3out = ""
menu4 = ""
menu4out = ""
menu5 = ""
menu5out = ""
}
// -->
</SCRIPT>
</HEAD>
<BODY BGCOLOR="#FFFFFF" BACKGROUND="images/background_left.gif"
TEXT="#000066" LINK="#000066" VLINK="#000066" ALINK="#000066">
<TABLE WIDTH="104" BORDER="0" CELLSPACING="0" CELLPADDING="0"
ALIGN="LEFT">
<TR>
<TD HEIGHT="20" ALIGN="CENTER" VALIGN="TOP">
<FONT FACE="Arial" SIZE="-2">
<DIV ALIGN="center">Navigation</DIV>
</FONT>
</TD>
</TR>
<TR>
<TD ALIGN="CENTER">
<A HREF="#" STYLE="text-decoration:none;"
ONMOUSEOVER="document.images[0].src=menu1.src;
self.status='Click to Browse Our Plants';
popUp('elMenu1',event);
return true"
ONMOUSEOUT="document.images[0].src=menu1out.src;
self.status='';
popDown('elMenu1');
return true"
NAME="menu1">
<IMG SRC="images/disc_agristarts_1.gif" WIDTH=100
HEIGHT=60 ALT="Plants" BORDER="0">
</A>
</TD>
</TR>
<TR>
<TD ALIGN="CENTER">
<A HREF="second_frame.htm?middle=price_sheet_header.htm
&bottom=price_availability.htm"
OnMouseOver="document.images[1].src=menu2.src;
self.status='Click to Check Pricing and Availability';
return true"
OnMouseOut="document.images[1].src=menu2out.src;
self.status='';
return true"
NAME="menu2">
<IMG SRC="images/disc_agristarts_2.gif" WIDTH=100
HEIGHT=60 ALT="Pricing & Availability" BORDER="0">
</A>
</TD>
</TR>
<TR>
<TD ALIGN="CENTER">
< A HREF="second_frame.htm?middle=blank.htm
&bottom=links_legal.htm"
OnMouseOver="document.images[2].src=menu3.src;
self.status='Click for Related Sites';
return true"
OnMouseOut="document.images[2].src=menu3out.src;
self.status='';
return true"
NAME="menu3">
<IMG SRC="images/disc_agristarts_3.gif" WIDTH=100
HEIGHT=60 ALT="Links" BORDER="0">
</A>
</TD>
</TR>
<TR>
<TD ALIGN="CENTER">
<A HREF="second_frame.htm?middle=blank.htm
&bottom=contact_us.htm"
OnMouseOver="document.images[3].src=menu4.src;
self.status='Click to Contact uS';
return true"
OnMouseOut="document.images[3].src=menu4out.src;
self.status='';
return true"
NAME="menu4">
<IMG SRC="images/disc_agristarts_4.gif" WIDTH=100
HEIGHT=60 ALT="Contact Us" BORDER="0">
;</A>
</TD>
</TR>
<TR>
<TD ALIGN="CENTER">
<A HREF="second_frame.htm?middle=blank.htm
&bottom=featured_plants.htm"
OnMouseOver="document.images[4].src=menu5.src;
self.status='Click for Legal Information and Links';
return true"
OnMouseOut="document.images[4].src=menu5out.src;
self.status='';
return true"
NAME="menu5">
<IMG SRC="images/disc_agristarts_5.gif" WIDTH=100
HEIGHT=60 ALT="New Plants" BORDER="0">
</A>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
The example above uses a DHTML menu script I saw at
http://webreference.com/dhtml/.
DHTML is beyond the scope for this article; take a look at their site which
will explain the DHTML part more in depth. I put
databse information in their script with the proper formatting. From the
top down, a small script to do basic browser detection, a script to
setup the dynamic menus, a script of the actual menus, a script for the
mouse-overs, and finally the actual html. The submenus are database
pulls of the plants of a given genus put in alphabetical order. In the
dynamic menus are urls in which the spaces are substituted with the word
plus this because spaces don't get translated very well
(perl->browser->perl) so it is easier to replace them and then replace
them again on the other page. This code is used at
www.agristarts.com
Wrapup
These examples are just the tip of the iceberg in using databases in your web pages. You can use a wide variety of types of pulls from a database (fetching rows, getting an array referenced pull, or getting a hash referenced pull). For further information I would recommend reading the documentaion on DBI and whichever DBD you choose to use. The main thing to do is be creative in how you use databases. Next month I will demonstrate how to create dynamic images for the web using Perl.
If you have any questions about the examples, or would like something discussed in a future column, feel free to email me.