Perl Refreshments ⌘

Web Development - DB Management

Now that you have some idea how to create HTML with Perl, we can move on to something a bit more useful - how to access a database and manage the results.

It's one thing to access a database from Terminal or the command line, but a web environment requires a bit more.

First we have to connect to a database. Whichever one you have available will show you how to achieve this in Perl. I usually put the appropriate code in a subroutine (DBconnect), and call that near the beginning of my scripts.

I use SQLite as my database, but it follows most of the standard SQL rules. Most, if not all, of what follows should work for you.

Here is a typical block of code. Remember to put this code OUTSIDE of your here documemt code, and after you've defined and/or assigned the variables.

$sth is a String handler
$dbh is a Database handler

DBconnect();

$sth = $dbh->prepare( qq{select count(*) from books});
$sth->execute();
while (@records=$sth->fetchrow_array())
{
	$NumBooks=$records[0];
}
$sth->finish();

This gets the number of elements in the table 'books', and places that value in the variable '$NumBooks'.

Most DB commands you will use are coded as a 3-step process:

1. prepare
2. execute
3. finish

The first 2 lines of the code do that. Since most queries have a result, we need somewhere to put those results. A good choice is an array, although a hash also works.

while (@records=$sth->fetchrow_array())

That's what this line does. The array @records will hold the result/s.

In the while loop you can also do other things with the array elements.

Here is a more complicated query:
Get the title, author, etc. for all books that have a subject / keyword of (fill in the blank):

$sth=$dbh->prepare(qq{select books.titleid,title,pubyear,notes,filename,author,books.authorid from books,keywords,authors
where keyword like ?
and books.titleid=keywords.titleid
and authors.authorid=books.authorid
and books.authorid=keywords.authorid
order by title collate nocase});

$sth->execute("$Keyword");

while (@records=$sth->fetchrow_array()) {
	$numK++;
	$TitleID=$records[0];
	$Title=$records[1];
	$PubYear=$records[2];
	$Notes=$records[3];
	$FileName=$records[4];
	$Author=$records[5];
	$AuthorID=$records[6];
	print qq{<li><a href="$DATAPATH/$FileName">$Title</a> [$TitleID]<br/>};
	print qq{<span style="background-color:#06FF0B;border-radius:5px"> PDF: Click title to read online </span><br/>} if ($FileName =~ m/\.pdf/);
	print qq{<span style="background-color:#abcdef;border-radius:5px"> EPUB: Click title to download </span><br/>} if ($FileName =~ m/\.epub/);
	print qq{<span style="background-color:#FFAE05;border-radius:5px"> MOBI: Click title to download </span><br/>} if ($FileName =~ m/\.mobi/);
	print qq{Author/s: <span class="field">$Author</span><br/>};
	print qq{Pub. Year: <span class="field">$PubYear</span><br/>};
	GetKeywords($AuthorID,$TitleID);
}

For step 1, we prepare the select statement. We include the table name for some of the column names to clarify which one we want (titleid and authorid is in 2 of the tables).

select books.titleid, ... books.authorid

where keyword like ?

Then we ensure that titleid and authorid match in the relevant tables.

and books.titleid=keywords.titleid
and authors.authorid=books.authorid
and books.authorid=keywords.authorid

And last we sort the results by title, disregarding upper or lower case letters.

order by title collate nocase});

Now the execute command (step 2). This is where we tell the query what the placeholder contains. In this case it's the keyword we previously acquired in the $Keyword variable.

Finally (step 3) we get the results in the @records array, but first, we increment a counter variable ($numK).

This is also where we can assign variables to the specific elements of the @records array - note they have to follow the same order as the select statement.

And where we can now print the results or our query.

A call to the sub-routine (GetKeywords($AuthorID,$TitleID) finishes off the while loop. This was defined previously:

sub GetKeywords {
	@mykeys=();
	($AuthorID,$TitleID)=@_;
	$sth2=$dbh->prepare(qq{select keyword from keywords
	where authorid=$AuthorID
	and titleid=$TitleID
	order by keyword});
	$sth2->execute();
	while(@records2=$sth2->fetchrow_array()) {
		push(@mykeys,$records2[0]);
	}
	$sth2->finish();
} # GetKeywords

Now that the associated keywords are in an array (@mykeys), we can also print them as part of the data associated with a particular book.