MySQL Cookbook (2007)

---

^[20^] If you use the CGI.pm object-oriented interface, there is no ambiguity. In that case, you invoke the tr() method through a CGI object and it is unnecessary to invoke it as Tr():

$cgi->tr ( ... );

Displaying Query Results as Hyperlinks

Problem

You want to use database content to generate clickable hyperlinks.

Solution

Add the proper tags to the content to generate anchor elements.

Discussion

The examples in the preceding sections generate static text, but database content also is useful for creating hyperlinks. If you store web-site URLs or email addresses in a table, you can easily convert them to active links in web pages. All you need to do is encode the information properly and add the appropriate HTML tags.

Suppose that you have a table that contains company names and web sites, such as the following book_vendor table that lists booksellers and publishers:

mysql>SELECT * FROM book_vendor ORDER BY name;

+----------------+------------------+

| name | website |

+----------------+------------------+

| Amazon.com | www.amazon.com |

| Barnes & Noble | www.bn.com |

| Bookpool | www.bookpool.com |

| O'Reilly Media | www.oreilly.com |

+----------------+------------------+

This table has content that readily lends itself to the creation of hyperlinked text. To produce a hyperlink from a row, add the http:// protocol designator to the website value, use the result as the href attribute for an <a> anchor tag, and use the name value in the body of the tag to serve as the link label. For example, the row for Barnes & Noble can be written like this:

<a href="http://www.bn.com">Barnes & Noble</a>

JSP code to produce a bulleted (unordered) list of hyperlinks from the table contents looks like this:

<sql:query dataSource="${conn}" var="rs">

SELECT name, website FROM book_vendor ORDER BY name

</sql:query>

<ul>

<c:forEach items="${rs.rows}" var="row">

<li>

<a href="http://<c:out value="${row.website}"/>">

<c:out value="${row.name}"/></a>

</li>

</c:forEach>

</ul>

When displayed in a web page, each vendor name in the list becomes an active link that may be selected to visit the vendor’s web site. In Python, the equivalent operation looks like this:

stmt = "SELECT name, website FROM book_vendor ORDER BY name"

cursor = conn.cursor ()

cursor.execute (stmt)

items = []

for (name, website) in cursor.fetchall ():

items.append ("<a href=\"http://%s\">%s</a>" \

% (urllib.quote (website), cgi.escape (name, 1)))

cursor.close ()

# print items, but don't encode them; they're already encoded

print make_unordered_list (items, False)

CGI.pm-based Perl scripts produce hyperlinks by invoking the a() function as follows:

a ({-href => "url-value"}, "link label")

The function can be used to produce the vendor link list like this:

my $stmt = "SELECT name, website FROM book_vendor ORDER BY name";

my $sth = $dbh->prepare ($stmt);

$sth->execute ();

my @items = ();

while (my ($name, $website) = $sth->fetchrow_array ())

{

push (@items, a ({-href => "http://$website"}, escapeHTML ($name)));

}

print ul (li (\@items));

Ruby scripts use the cgi module a method to produce hyperlinks:

stmt = "SELECT name, website FROM book_vendor ORDER BY name"

list = ""

dbh.execute(stmt) do |sth|

sth.fetch do |row|

list << cgi.li {

cgi.a("href" => "http://#{row[1]}") {

CGI.escapeHTML(row[0].to_s)

}

}

end

end

list = cgi.ul { list }

Generating links using email addresses is another common web programming task. Assume that you have a table newsstaff that lists the department, name, and (if known) email address for the news anchors and reporters employed by a television station, WRRR:

mysql>SELECT * FROM newsstaff;

+------------------+----------------+-------------------------+

| department | name | email |

+------------------+----------------+-------------------------+

| Sports | Mike Byerson | mbyerson@wrrr-news.com |

| Sports | Becky Winthrop | bwinthrop@wrrr-news.com |

| Weather | Bill Hagburg | bhagburg@wrrr-news.com |

| Local News | Frieda Stevens | NULL |

| Local Government | Rex Conex | rconex@wrrr-news.com |

| Current Events | Xavier Ng | xng@wrrr-news.com |

| Consumer News | Trish White | twhite@wrrr-news.com |

+------------------+----------------+-------------------------+

From this you want to produce an online directory containing email links to all personnel, so that site visitors can easily send mail to any staff member. For example, a row for a sports reporter named Mike Byerson with an email address of mbyerson@wrrr-news.com will become an entry in the listing that looks like this:

Sports: <a href="mailto:mbyerson@wrrr-news.com">Mike Byerson</a>

It’s easy to use the table’s contents to produce such a directory. First, let’s put the code to generate an email link into a helper function, because it’s the kind of operation that’s likely to be useful in several scripts. In Perl, the function might look like this:

sub make_email_link

{

my ($name, $addr) = @_;

$name = escapeHTML ($name);

# return name as static text if address is undef or empty

return $name if !defined ($addr) || $addr eq "";

# return a hyperlink otherwise

return a ({-href => "mailto:$addr"}, $name);

}

The function handles instances where the person has no email address by returning just the name as static text. To use the function, write a loop that pulls out names and addresses and displays each email link preceded by the staff member’s department:

my $stmt = "SELECT department, name, email FROM newsstaff

ORDER BY department, name";

my $sth = $dbh->prepare ($stmt);

$sth->execute ();

my @items = ();

while (my ($dept, $name, $email) = $sth->fetchrow_array ())

{

push (@items,

escapeHTML ($dept) . ": " . make_email_link ($name, $email));

}

print ul (li (\@items));

Equivalent email link generator functions for Ruby, PHP, and Python look like this:

def make_email_link(name, addr = nil)

name = CGI.escapeHTML(name.to_s)

# return name as static text if address is nil or empty

return name if addr.nil? or addr == ""

# return a hyperlink otherwise

return "<a href=\"mailto:#{addr}\">#{name}</a>"

end

function make_email_link ($name, $addr = NULL)

{

$name = htmlspecialchars ($name);

# return name as static text if address is NULL or empty

if ($addr === NULL || $addr == "")

return ($name);

# return a hyperlink otherwise

return (sprintf ("<a href=\"mailto:%s\">%s</a>", $addr, $name));

}

def make_email_link (name, addr = None):

name = cgi.escape (name, 1)

# return name as static text if address is None or empty

if addr is None or addr == "":

return name

# return a hyperlink otherwise

return "<a href=\"mailto:%s\">%s</a>" % (addr, name)

For a JSP page, you can produce the newsstaff listing as follows:

<sql:query dataSource="${conn}" var="rs">

SELECT department, name, email

FROM newsstaff

ORDER BY department, name

</sql:query>

<ul>

<c:forEach items="${rs.rows}" var="row">

<li>

<c:out value="${row.department}"/>:

<c:set var="name" value="${row.name}"/>

<c:set var="email" value="${row.email}"/>

<c:choose>

<%-- null or empty value test --%>

<c:when test="${empty email}">

<c:out value="${name}"/>

</c:when>

<c:otherwise>

<a href="mailto:<c:out value="${email}"/>">

<c:out value="${name}"/></a>

</c:otherwise>

</c:choose>

</li>

</c:forEach>

</ul>

Creating a Navigation Index from Database Content

Problem

A list of items in a web page is long. You want to make it easier for users to move around in the page.

Solution

Create a navigation index containing links to different sections of the list.

Discussion

It’s easy to display lists in web pages (Displaying Query Results as Lists). But if a list contains a lot of items, the page containing it may become quite long. In such cases, it’s often useful to break up the list into sections and provide a navigation index, in the form of hyperlinks that enable users to reach sections of the list quickly without scrolling the page manually. For example, if you retrieve rows from a table and display them grouped into sections, you can include an index that lets the user jump directly to any section. The same idea can be applied to multiple-page displays as well, by providing a navigation index in each page so that users can reach any other page easily.

This recipe provides two examples to illustrate these techniques, both of which are based on the kjv table that was introduced in Using FULLTEXT Searches. The examples implement two kinds of display, using the verses from the book of Esther stored in the kjv table:

§ A single-page display that lists all verses in all chapters of Esther. The list is broken into 10 sections (one per chapter), with a navigation index that has links pointing to the beginning of each section.

§ A multiple-page display consisting of pages that each show the verses from a single chapter of Esther, and a main page that instructs the user to choose a chapter. Each of these pages also displays a list of chapters as hyperlinks to the pages that display the corresponding chapter verses. These links enable any page to be reached easily from any other.

Creating a single-page navigation index

This example displays all verses in Esther in a single page, with verses grouped into sections by chapter. To display the page so that each section contains a navigation marker, place an <a name> anchor element before each chapter’s verses:

<a name="1">Chapter 1</a>

...list of verses in chapter 1...

<a name="2">Chapter 2</a>

... list of verses in chapter 2...

<a name="3">Chapter 3</a>

... list of verses in chapter 3...

...

That generates a list that includes a set of markers named 1, 2, 3, and so forth. To construct the navigation index, build a set of hyperlinks, each of which points to one of the name markers:

<a href="#1">Chapter 1</a>

<a href="#2">Chapter 2</a>

<a href="#3">Chapter 3</a>

...

The # in each href attribute signifies that the link points to a location within the same page. For example, href= "#3" points to the anchor with the name= "3" attribute.

To implement this kind of navigation index, you can use a couple of approaches:

§ Retrieve the verse rows into memory and determine from them which entries are needed in the navigation index. Then print both the index and verse list.

§ Figure out all the applicable anchors in advance and construct the index first. The list of chapter numbers can be determined by this statement:

SELECT DISTINCT cnum FROM kjv WHERE bname = 'Esther' ORDER BY cnum;

You can use the query result to build the navigation index, and then fetch the verses for the chapters later to create the page sections that the index entries point to.

Here’s a script, esther1.pl, that uses the first approach. It’s an adaptation of one of the nested-list examples shown in Displaying Query Results as Lists.

#!/usr/bin/perl

# esther1.pl - display book of Esther in a single page,

# with navigation index

use strict;

use warnings;

use CGI qw(:standard escape escapeHTML);

use Cookbook;

my $title = "The Book of Esther";

my $page = header ()

. start_html (-title => $title, -bgcolor => "white")

. h3 ($title);

my $dbh = Cookbook::connect ();

# Retrieve verses from the book of Esther and associate each one with the

# list of verses for the chapter it belongs to.

my $sth = $dbh->prepare ("SELECT cnum, vnum, vtext FROM kjv

WHERE bname = 'Esther'

ORDER BY cnum, vnum");

$sth->execute ();

my %verses = ();

while (my ($cnum, $vnum, $vtext) = $sth->fetchrow_array ())

{

# Initialize chapter's verse list to empty array if this is

# first verse for it, and then add verse number/text to array.

$verses{$cnum} = [] unless exists ($verses{$cnum});

push (@{$verses{$cnum}}, p (escapeHTML ("$vnum. $vtext")));

}

# Determine all chapter numbers and use them to construct a navigation

# index. These are links of the form <a href="#num>Chapter num</a>, where

# num is a chapter number a'#' signifies a within-page link. No URL- or

# HTML-encoding is done here (the text that is displayed here doesn't need

# it). Make sure to sort chapter numbers numerically (use { a <=> b }).

# Separate links by nonbreaking spaces.

my $nav_index;

foreach my $cnum (sort { $a <=> $b } keys (%verses))

{

$nav_index .= " " if $nav_index;

$nav_index .= a ({-href => "#$cnum"}, "Chapter $cnum");

}

# Now display list of verses for each chapter. Precede each section by

# a label that shows the chapter number and a copy of the navigation index.

foreach my $cnum (sort { $a <=> $b } keys (%verses))

{

# add an <a name> anchor for this section of the state display

$page .= p (a ({-name => $cnum}, font ({-size => "+2"}, "Chapter $cnum"))

. br ()

. $nav_index);

$page .= join ("", @{$verses{$cnum}}); # add array of verses for chapter

}

$dbh->disconnect ();

$page .= end_html ();

print $page;

Creating a multiple-page navigation index

This example shows a Perl script, esther2.pl, that is capable of generating any of several pages, all based on the verses in the book of Esther stored in the kjv table. The initial page displays a list of the chapters in the book, along with instructions to select a chapter. Each chapter in the list is a hyperlink that reinvokes the script to display the list of verses in the corresponding chapter. Because the script is responsible for generating multiple pages, it must be able to determine which page to display each time it runs. To make that possible, the script examines its own URL for achapter parameter that indicates the number of the chapter to display. If no chapter parameter is present, or its value is not an integer, the script displays its initial page.

The URL to request the initial page looks like this:

http://localhost/cgi-bin/esther2.pl

The links to individual chapter pages have the following form, where cnum is a chapter number:

http://localhost/cgi-bin/esther2.pl?chapter=cnum

esther2.pl uses the CGI.pm param() function to obtain the chapter parameter value like so:

my $cnum = param ("chapter");

if (!defined ($cnum) || $cnum !~ /^\d+$/)

{

# No chapter number was present or it was malformed

}

else

{

# A chapter number was present

}

If no chapter parameter is present in the URL, $cnum will be undef. Otherwise, $cnum is set to the parameter value, which we check to make sure that it’s an integer. (That covers the case where a garbage value may have been specified by someone trying to crash the script.)

Here is the entire esther2.pl script:

#!/usr/bin/perl

# esther2.pl - display book of Esther over multiple pages, one page per

# chapter, with navigation index

use strict;

use warnings;

use CGI qw(:standard escape escapeHTML);

use Cookbook;

# Construct navigation index as a list of links to the pages for each

# chapter in the the book of Esther. Labels are of the form "Chapter

# n"; the chapter numbers are incorporated into the links as chapter=num

# parameters

# $dbh is the database handle, $cnum is the number of the chapter for

# which information is currently being displayed. The label in the

# chapter list corresponding to this number is displayed as static

# text; the others are displayed as hyperlinks to the other chapter

# pages. Pass 0 to make all entries hyperlinks (no valid chapter has

# a number 0).

# No encoding is done because the chapter numbers are digits and don't

# need it.

sub get_chapter_list

{

my ($dbh, $cnum) = @_;

my $nav_index;

my $ref = $dbh->selectcol_arrayref (

"SELECT DISTINCT cnum FROM kjv

WHERE bname = 'Esther' ORDER BY cnum"

);

foreach my $cur_cnum (@{$ref})

{

my $link = url () . "?chapter=$cur_cnum";

my $label = "Chapter $cur_cnum";

$nav_index .= br () if $nav_index; # separate entries by <br>

# use static bold text if entry is for current chapter,

# use a hyperlink otherwise

$nav_index .= ($cur_cnum == $cnum

? strong ($label)

: a ({-href => $link}, $label));

}

return ($nav_index);

}

# Get the list of verses for a given chapter. If there are none, the

# chapter number was invalid, but handle that case sensibly.

sub get_verses

{

my ($dbh, $cnum) = @_;

my $ref = $dbh->selectall_arrayref (

"SELECT vnum, vtext FROM kjv

WHERE bname = 'Esther' AND cnum = ?",

undef, $cnum);

my $verses = "";

foreach my $row_ref (@{$ref})

{

$verses .= p (escapeHTML ("$row_ref->[0]. $row_ref->[1]"));

}

return ($verses eq "" # no verses?

? p ("No verses in chapter $cnum were found.")

: p ("Chapter $cnum:") . $verses);

}

# ----------------------------------------------------------------------

my $title = "The Book of Esther";

my $page = header () . start_html (-title => $title, -bgcolor => "white");

my ($left_panel, $right_panel);

my $dbh = Cookbook::connect ();

my $cnum = param ("chapter");

if (!defined ($cnum) || $cnum !~ /^\d+$/)

{

# Missing or malformed chapter; display main page with a left panel

# that lists all chapters as hyperlinks and a right panel that provides

# instructions.

$left_panel = get_chapter_list ($dbh, 0);

$right_panel = p (strong ($title))

. p ("Select a chapter from the list at left.");

}

else

{

# Chapter number was given; display a left panel that lists chapters

# chapters as hyperlinks (except for current chapter as bold text)

# and a right panel that lists the current chapter's verses.

$left_panel = get_chapter_list ($dbh, $cnum);

$right_panel = p (strong ($title))

. get_verses ($dbh, $cnum);

}

$dbh->disconnect ();

# Arrange the page as a one-row, three-cell table (middle cell is a spacer)

$page .= table (Tr (

td ({-valign => "top", -width => "15%"}, $left_panel),

td ({-valign => "top", -width => "5%"}, " "),

td ({-valign => "top", -width => "80%"}, $right_panel)

));

$page .= end_html ();

print $page;

See Also

esther2.pl examines its execution environment using the param() function. Collecting Web Input further discusses web script parameter processing.

Generating Previous-Page and Next-Page Links discusses another navigation problem: how to split display of a result set across multiple pages and create previous-page and next-page links.

Storing Images or Other Binary Data

Problem

You want to store images in MySQL.

Solution

That’s not difficult, provided that you follow the proper precautions for encoding the image data.

Discussion

Web sites are not limited to displaying text. They can also serve various forms of binary data such as images, music files, PDF documents, and so forth. Images are a common kind of binary data, and because image storage is a natural application for a database, a very common question is“How do I store images in MySQL?” Many people will answer this question by saying, “Don’t do it!” and some of the reasons are discussed in the sidebar “Should You Store Images in Your Database?” Because it’s important to know how to work with binary data, this section does show how to store images in MySQL. Nevertheless, in recognition that that may not always be the best thing to do, the section also shows how to store images in the filesystem.

Although the discussion here is phrased in terms of working with images, the principles apply to any kind of binary data, such as PDF files or compressed text. In fact, they apply to any kind of data at all, including text. People tend to think of images as special somehow, but they’re not.

One reason that image storage confuses people more often than does storing other types of information like text strings or numbers is that it’s difficult to type in an image value manually. For example, you can easily use mysql to enter an INSERT statement to store a number like 3.48 or a string like Je voudrais une bicyclette rouge, but images contain binary data and it’s not easy to refer to them by value. So you need to do something else. Your options are:

§ Use the LOAD_FILE() function.

§ Write a program that reads in the image file and constructs the proper INSERT statement for you.

SHOULD YOU STORE IMAGES IN YOUR DATABASE?

Deciding where to store images is a matter of trade-offs. There are advantages and disadvantages regardless of whether you store images in the database or in the filesystem:

§ Storing images in a database table bloats the table. With a lot of images, you’re more likely to approach any limits your operating system places on table size. On the other hand, if you store images in the filesystem, directory lookups may become slow. To avoid this, you may be able to implement some kind of hierarchical storage or use a filesystem that has good lookup performance for large directories (such as the Reiser filesystem that is available on Linux).

§ Using a database centralizes storage for images that are used across multiple web servers on different hosts. Images stored in the filesystem must be stored locally on the web server host. In a multiple-host situation, that means you must replicate the set of images to the filesystem of each host. If you store the images in MySQL, only one copy of the images is required because each web server can get the images from the same database server.

§ When images are stored in the filesystem, they constitute in essence a foreign key. Image manipulation requires two operations: one in the database and one in the filesystem. This in turn means that if you require transactional behavior, it’s more difficult to implement—not only do you have two operations, but they take place in different domains. Storing images in the database is simpler because adding, updating, or removing an image requires only a single row operation. It becomes unnecessary to make sure the image table and the filesystem remain in synchrony.

§ It can be faster to serve images over the Web from the filesystem than from the database, because the web server itself opens the file, reads it, and writes it to the client. Images stored in the database must be read and written twice. First, the MySQL server reads the image from the database and writes it to your web script. Then the script reads the image and writes it to the client.

§ Images stored in the filesystem can be referred to directly in web pages by means of <img> tag links that point to the image files. Images stored in MySQL must be served by a script that retrieves an image and sends it to the client. However, even if images are stored in the filesystem and accessible to the web server, you might still want to serve them through a script. This would be appropriate if you need to account for the number of times you serve each image (such as for banner ad displays where you charge customers by the number of ad impressions) or if you want to select an image at request time (such as when you pick an ad at random).

§ If you store images in the database, you need to use a data type such as a BLOB. This is a variable length type, so the table itself will have variable-length rows. For the MyISAM storage engine, operations on fixed-length rows are often quicker, so you may gain some table lookup speed by storing images in the filesystem and using fixed-length types for the columns in the image table.

Storing images with LOAD_FILE()

The LOAD_FILE() function takes an argument indicating a file to be read and stored in the database. For example, an image stored in /tmp/myimage.png might be loaded into a table like this:

INSERT INTO mytbl (image_data) VALUES(LOAD_FILE('/tmp/myimage.png'));

To load images into MySQL with LOAD_FILE(), certain requirements must be satisfied:

§ The image file must be located on the MySQL server host.

§ The file must be readable by the server.

§ You must have the FILE privilege.

These constraints mean that LOAD_FILE() is available only to some MySQL users.

Storing images using a script

If LOAD_FILE() is not an option, or you don’t want to use it, you can write a short program to load your images. The program should either read the contents of an image file and create a row that contains the image data, or create a row that indicates where in the filesystem the image file is located. If you elect to store the image in MySQL, include the image data in the row-creation statement the same way as any other kind of data. That is, you either use a placeholder and bind the data value to it, or else encode the data and put it directly into the statement string.

The script shown in this recipe, store_image.pl, runs from the command line and stores an image file for later use. The script takes no side in the debate over whether to store images in the database or the filesystem. Instead, it demonstrates how to implement both approaches! Of course, this requires twice the storage space. To adapt this script for your own use, you’ll want to retain only the parts that are appropriate for whichever storage method you want to implement. The necessary modifications are discussed at the end of this section.

The store_image.pl script uses an image table that includes columns for the image ID, name, and MIME type, and a column in which to store the image data:

CREATE TABLE image

(

id INT UNSIGNED NOT NULL AUTO_INCREMENT, # image ID number

name VARCHAR(30) NOT NULL, # image name

type VARCHAR(20) NOT NULL, # image MIME type

data MEDIUMBLOB NOT NULL, # image data

PRIMARY KEY (id), # id and name are unique

UNIQUE (name)

);

The name column indicates the name of the image file in the directory where images are stored in the filesystem. The data column is a MEDIUMBLOB, which is good for images smaller than 16 MB. If you need larger images, use a LONGBLOB column.

It is possible to use the name column to store full pathnames to images in the database, but if you put them all under the same directory, you can store names that are relative to that directory, and name values will take less space. That’s what store_image.pl does. It needs to know the pathname of the image storage directory, which is what its $image_dir variable is for. You should check this variable’s value and modify it as necessary before running the script. The default value reflects where I like to store images, but you’ll need to change it according to your own preferences. Make sure to create the directory if it doesn’t exist before you run the script, and set its access permissions so that the web server can read and write files there. You’ll also need to check and possibly change the image directory pathname in the display_image.pl script discussed later in this chapter.

NOTE

The image storage directory should be outside the web server document tree. Otherwise, a user who knows or can guess the location may be able to upload executable code and cause it to run by requesting it with a web browser.

store_image.pl looks like this:

#!/usr/bin/perl

# store_image.pl - read an image file, store in the image table and

# in the filesystem. (Normally, you'd store images only in one

# place or another; this script demonstrates how to do both.)

use strict;

use warnings;

use Fcntl; # for O_RDONLY, O_WRONLY, O_CREAT

use FileHandle;

use Cookbook;

# Default image storage directory and pathname separator

# (CHANGE THESE AS NECESSARY)

# The location should NOT be within the web server document tree

my $image_dir = "/usr/local/lib/mcb/images";

my $path_sep = "/";

# Reset directory and pathname separator for Windows/DOS

if ($^O =~ /^MSWin/i || $^O =~ /^dos/)

{

$image_dir = "C:\\mcb\\images";

$path_sep = "\\";

}

-d $image_dir or die "$0: image directory ($image_dir)\ndoes not exist\n";

# Print help message if script was not invoked properly

(@ARGV == 2 || @ARGV == 3) or die <<USAGE_MESSAGE;

Usage: $0 image_file mime_type [image_name]

image_file = name of the image file to store

mime_time = the image MIME type (e.g., image/jpeg or image/png)

image_name = alternate name to give the image

image_name is optional; if not specified, the default is the

image file basename.

USAGE_MESSAGE

my $file_name = shift (@ARGV); # image filename

my $mime_type = shift (@ARGV); # image MIME type

my $image_name = shift (@ARGV); # image name (optional)

# if image name was not specified, use filename basename

# (allow either / or \ as separator)

($image_name = $file_name) =~ s|.*[/\\]|| unless defined $image_name;

my $fh = new FileHandle;

my ($size, $data);

sysopen ($fh, $file_name, O_RDONLY)

or die "Cannot read $file_name: $!\n";

binmode ($fh); # helpful for binary data

$size = (stat ($fh))[7];

sysread ($fh, $data, $size) == $size

or die "Failed to read entire file $file_name: $!\n";

$fh->close ();

# Save image file in filesystem under $image_dir. (Overwrite file

# if an old version exists.)

my $image_path = $image_dir . $path_sep . $image_name;

sysopen ($fh, $image_path, O_WRONLY|O_CREAT)

or die "Cannot open $image_path: $!\n";

binmode ($fh); # helpful for binary data

syswrite ($fh, $data, $size) == $size

or die "Failed to write entire image file $image_path: $!\n";

$fh->close ();

# Save image in database table. (Use REPLACE to kick out any old image

# that has the same name.)

my $dbh = Cookbook::connect ();

$dbh->do ("REPLACE INTO image (name,type,data) VALUES(?,?,?)",

undef,

$image_name, $mime_type, $data);

$dbh->disconnect ();

If you invoke the script with no arguments, it displays a short help message. Otherwise, it requires two arguments that specify the name of the image file and the MIME type of the image. By default, the file’s basename (final component) is also used as the name of the image stored in the database and in the image directory. To use a different name, provide it using an optional third argument.

The script is fairly straightforward. It performs the following actions:

1. Check that the proper number of arguments was given and initialize some variables from them.

2. Make sure the image directory exists. If it does not, the script cannot continue.

3. Open and read the contents of the image file.

4. Store the image as a file in the image directory.

5. Store a row containing identifying information and the image data in the image table.

store_image.pl uses REPLACE rather than INSERT so that you can replace an old image with a new version having the same name simply by loading the new one. The statement specifies no id column value; id is an AUTO_INCREMENT column, so MySQL assigns it a unique sequence number automatically. Note that if you replace an image by loading a new one with the same name as an existing image, the REPLACE statement will generate a new id value. If you want to keep the old value, you should use INSERT ... ONDUPLICATEKEYUPDATE instead (Using Sequence Generators as Counters). This will insert the row if the name doesn’t already exist, or update the image value if it does.

The REPLACE statement that stores the image information into MySQL is relatively mundane:

$dbh->do ("REPLACE INTO image (name,type,data) VALUES(?,?,?)",

undef,

$image_name, $mime_type, $data);

If you examine that statement looking for some special technique for handling binary data, you’ll be disappointed, because the $data variable that contains the image isn’t treated as special in any way. The statement refers to all column values uniformly using ? placeholder characters and the values are passed at the end of the do() call. Another way to accomplish the same result is to perform escape processing on the column values explicitly and then insert them directly into the statement string:

$image_name = $dbh->quote ($image_name);

$mime_type = $dbh->quote ($mime_type);

$data = $dbh->quote ($data);

$dbh->do ("REPLACE INTO image (name,type,data)

VALUES($image_name,$mime_type,$data)");

Many people think image-handling is a lot more troublesome than it really is. If you properly handle image data in a statement by using placeholders or by encoding it, you’ll have no problems. If you don’t, you’ll get errors. It’s as simple as that. This is no different from how you should handle other kinds of data, even text. After all, if you insert into a statement a piece of text that contains quotes or other special characters without escaping them, the statement will blow up in your face. So the need for placeholders or encoding is not some special thing that’s necessary only for images—it’s necessary for all data. Say it with me: “I will always use placeholders or encode my column values. Always. Always, always, always.” (Having said that, I feel obliged to point out that if you know enough about a given value—for example, if you’re absolutely certain that it’s an integer—there are times you can get away with breaking this rule. Nevertheless, it’s never wrong to follow the rule.)

To try the script, change location into the apache/images directory of the recipes distribution. That directory contains the store_image.pl script, and some sample images are in its flags subdirectory (they’re pictures of national flags for several countries). To store one of these images, run the script like this under Unix:

%./store_image.pl flags/iceland.jpg image/jpeg

Or like this under Windows:

C:\>store_image.pl flags\iceland.jpg image/jpeg

store_image.pl takes care of image storage, and the next section discusses how to retrieve images to serve them over the Web. What about deleting images? I’ll leave it to you to write a utility to remove images that you no longer want. If you are storing images in the filesystem, remember to delete both the database row and the image file that the row points to.

store_image.pl stores each image both in the database and in the filesystem for illustrative purposes, but of course that makes it inefficient. Earlier, I mentioned that if you use this script as a basis for your own applications, you should modify it to store images only in one place—either in the database or in the filesystem—not in both places. The modifications are as follows:

§ To adapt the script to store images only in MySQL, there is no need to create an image directory, and you can delete the code that checks for that directory’s existence and that writes image files there.

§ To adapt the script for storage only in the filesystem, drop the data column from the image table, and modify the REPLACE statement so it doesn’t refer to that column.

These modifications also apply to the display_image.pl image processing script shown in Retrieving Images or Other Binary Data.

See Also

Retrieving Images or Other Binary Data shows how to retrieve images for display over the Web. Processing File Uploads discusses how to upload images from a web page for storage into MySQL.

Retrieving Images or Other Binary Data

Problem

You can store images or other binary data values in your database, using the techniques discussed in Storing Images or Other Binary Data. But how do you get them back out?

Solution

You need nothing more than a SELECT statement. Of course, what you do with the information after you retrieve it might be a little more involved.

Discussion

As described in Storing Images or Other Binary Data, it’s difficult to issue a statement manually that stores a literal image value into a database, so normally you use LOAD_FILE() or write a script that encodes the image data for insertion. However, there is no problem at all entering a statement that retrieves an image:

SELECT * FROM image WHERE id = 1;

But binary information tends not to show up well on text-display devices, so you probably don’t want to do this interactively from the mysql program unless you want your terminal window to turn into a horrible mess of gibberish, or possibly even to lock up. It’s more common to use the information for display in a web page. Or you might send it to the client for downloading, although that is more common for nonimage binary data such as PDF files. (Serving Query Results for Download discusses downloading.)

To display an image in a web page, include an <img> tag in the page that tells the client’s web browser where to get the image. If you’ve stored images as files in a directory that the web server has access to, you can refer to an image directly. For example, if the image file iceland.jpg is located in the /usr/local/lib/mcb/images directory, you can reference it like this:

<img src="/usr/local/lib/mcb/images/iceland.jpg" />

If you use this approach, make sure that each image filename has an extension (such as .gif or .png) that enables the web server to determine what kind of Content-Type: header to generate when it sends the file to the client.

If the images are stored in a database table instead, or in a directory that is not accessible to the web server, the <img> tag can refer to a script that knows how to fetch images and send them to clients. To do this, the script should respond by sending a Content-Type: header that indicates the image format, a Content-Length: header that indicates the number of bytes of image data, a blank line, and finally the image itself as the body of the response.

The following script, display_image.pl, demonstrates how to serve images over the Web. It requires a name parameter that indicates which image to display, and allows an optional location parameter that specifies whether to retrieve the image from the image table or from the filesystem. The default is to retrieve image data from the image table. For example, the following URLs display an image from the database and from the filesystem, respectively:

http://localhost/cgi-bin/display_image.pl?name=iceland.jpg

http://localhost/cgi-bin/display_image.pl?name=iceland.jpg;location=fs

The script looks like this:

#!/usr/bin/perl

# display_image.pl - display image over the Web

use strict;

use warnings;

use CGI qw(:standard escapeHTML);

use FileHandle;

use Cookbook;

sub error

{

my $msg = escapeHTML ($_[0]);

print header (), start_html ("Error"), p ($msg), end_html ();

exit (0);

}

# ----------------------------------------------------------------------

# Default image storage directory and pathname separator

# (CHANGE THESE AS NECESSARY)

my $image_dir = "/usr/local/lib/mcb/images";

# The location should NOT be within the web server document tree

my $path_sep = "/";

# Reset directory and pathname separator for Windows/DOS

if ($^O =~ /^MSWin/i || $^O =~ /^dos/)

{

$image_dir = "C:\\mcb\\images";

$path_sep = "\\";

}

my $name = param ("name");

my $location = param ("location");

# make sure image name was specified

defined ($name) or error ("image name is missing");

# use default of "db" if the location is not specified or is

# not "db" or "fs"

(defined ($location) && $location eq "fs") or $location = "db";

my $dbh = Cookbook::connect ();

my ($type, $data);

# If location is "db", get image data and MIME type from image table.

# If location is "fs", get MIME type from image table and read the image

# data from the filesystem.

if ($location eq "db")

{

($type, $data) = $dbh->selectrow_array (

"SELECT type, data FROM image WHERE name = ?",

undef,

$name)

or error ("Cannot find image with name $name");

}

else

{

$type = $dbh->selectrow_array (

"SELECT type FROM image WHERE name = ?",

undef,

$name)

or error ("Cannot find image with name $name");

my $fh = new FileHandle;

my $image_path = $image_dir . $path_sep . $name;

open ($fh, $image_path)

or error ("Cannot read $image_path: $!");

binmode ($fh); # helpful for binary data

my $size = (stat ($fh))[7];

read ($fh, $data, $size) == $size

or error ("Failed to read entire file $image_path: $!");

$fh->close ();

}

$dbh->disconnect ();

# Send image to client, preceded by Content-Type: and Content-Length:

# headers.

print header (-type => $type, -Content_Length => length ($data));

print $data;

Serving Banner Ads

Problem

You want to display banner ads by choosing images on the fly from a set of images.

Solution

Use a script that selects a random row from an image table and sends the image to the client.

Discussion

The display_image.pl script shown in Retrieving Images or Other Binary Data assumes that the URL contains a parameter that names the image to be sent to the client. Another application might determine which image to display for itself. One popular image-related use for web programming is to serve banner advertisements for display in web pages. A simple way to do this is by means of a script that picks an image at random each time it is invoked. The following Python script, banner.py, shows how to do this, where the “ads” are the flag images in the image table:

#!/usr/bin/python

# banner.py - serve randomly chosen banner ad from image table

# (sends no response if no image can be found)

import MySQLdb

import Cookbook

conn = Cookbook.connect ()

stmt = "SELECT type, data FROM image ORDER BY RAND() LIMIT 1"

cursor = conn.cursor ()

cursor.execute (stmt)

row = cursor.fetchone ()

cursor.close ()

if row is not None:

(type, data) = row

# Send image to client, preceded by Content-Type: and

# Content-Length: headers. The Expires:, Cache-Control:, and

# Pragma: headers help keep browsers from caching the image

# and reusing it for successive requests for this script.

print "Content-Type: %s" % type

print "Content-Length: %s" % len (data)

print "Expires: Sat, 01 Jan 2000 00:00:00 GMT"

print "Cache-Control: no-cache"

print "Pragma: no-cache"

print ""

print data

conn.close ()

banner.py sends a few headers in addition to the usual Content-Type: and Content-Length: headers. The extra headers help keep browsers from caching the image. Expires: specifies a date in the past to tell the browser that the image is out of date. The Cache-Control: andPragma: headers tell the browser not to cache the image. The script sends both headers because some browsers understand one, and some the other.

Why suppress caching? Because if you don’t, the browser will send a request for banner.py only the first time it sees it in a link. On subsequent requests for the script, the browser will reuse the image, which rather defeats the intent of having each such link resolve to a randomly chosen image.

Install the banner.py script in your cgi-bin directory. Then, to place a banner in a web page, use an <img> tag that invokes the script. For example, if the script is installed as /cgi-bin/banner.py, the following page references it to include an image below the introductory paragraph:

<!-- bannertest1.html - page with single link to banner-ad script -->

<html>

<head>

<title>Banner Ad Test Page 1</title>

</head>

<body bgcolor="white">

<p>You should see an image below this paragraph.</p>

<img src="/cgi-bin/banner.py" />

</body>

</html>

If you request this page, it should display an image, and you should see a succession of randomly chosen images each time you reload the page. (I am assuming here that you have loaded several images into the image table by now using the store_image.pl script discussed in Storing Images or Other Binary Data. Otherwise you won’t see any images at all!) If you modify banner.py not to send the cache-related headers, you likely will see the same image each time you reload the page.

The cache-control headers suppress caching for links to banner.py that occur over the course of successive page requests. Another complication occurs if multiple links to the script occur within the same page. The following page illustrates what happens:

<!-- bannertest2.html - page with multiple links to banner-ad script -->

<html>

<head>

<title>Banner Ad Test Page 2</title>

</head>

<body bgcolor="white">

<p>You should see two images below this paragraph,

and they probably will be the same.</p>

<img src="/cgi-bin/banner.py" />

<img src="/cgi-bin/banner.py" />

<p>You should see two images below this paragraph,

and they probably will be different.</p>

<img src="/cgi-bin/banner.py?image1" />

<img src="/cgi-bin/banner.py?image2" />

</body>

</html>

The first pair of links to banner.py are identical. What you’ll probably find when you request this page is that your browser will notice that fact, send only a single request to the web server, and use the image that is returned where both links appear in the page. As a result, the first pair of images displayed in the page will be identical. The second pair of links to banner.py show how to solve this problem. The links include some extra fluff at the end of the URLs that make them look different. banner.py doesn’t use that information at all, but making the links look different fools the browser into sending two image requests. The result is that the second pair of images will differ from each other—unless banner.py happens to randomly select the same image both times, of course.

Serving Query Results for Download

Problem

You want to send database information to a browser for downloading rather than for display.

Solution

Unfortunately, there’s no good way to force a download. A browser will process information sent to it according to the Content-Type: header value, and if it has a handler for that value, it will treat the information accordingly. However, you may be able to trick the browser by using a“generic” content type for which it’s unlikely to have a handler.

Discussion

Earlier sections of this chapter discuss how to incorporate the results of database queries into web pages, to display them as paragraphs, lists, tables, or images. But what if you want to produce a query result that the user can download to a file instead? It’s not difficult to generate the response itself: send a Content-Type: header preceding the information, such as text/plain for plain text, image/jpeg for a JPEG image, or application/pdf or application/msexcel for a PDF or Excel document. Then send a blank line and the content of the query result. The problem is that there’s no way to force the browser to download the information. If it knows what to do with the response based on the content type, it will try to handle the information as it sees fit. If it knows how to display text or images, it will. If it thinks it’s supposed to give a PDF or Excel document to a PDF viewer or to Excel, it will. Most browsers enable the user to select a download explicitly (for example, by right-clicking or Ctrl-clicking on a link), but that’s a client-side mechanism. You have no access to it on the server end.

About the only thing you can do is try to fool the browser by faking the content type. The most generic type is application/octet-stream. Most users are unlikely to have any content handler specified for it, so if you send a response using that type, it’s likely to trigger a download by the browser. The disadvantage of this, of course, is that the response contains a false indicator about the type of information it contains. You can try to alleviate this problem by suggesting a default filename for the browser to use when it saves the file. If the filename has a suffix indicative of the file type, such as .txt, .jpg, .pdf, or .xls, that may help the client (or the operating system on the client host) determine how to process the file. To suggest a name, include a Content-Disposition: header in the response that looks like this:

Content-disposition: attachment; filename="suggested_name"

The following PHP script, download.php, demonstrates one way to produce downloadable content. When first invoked, it presents a page containing a link that can be selected to initiate the download. The link points back to download.php but includes a download parameter. When you select the link, it reinvokes the script, which sees the parameter and responds by issuing a query, retrieving a result set, and sending it to the browser for downloading. The Content-Type: and Content-Disposition: headers in the response are set by invoking the header() function. (This must be done before the script produces any other output, or header() will have no effect.)

<?php

# download.php - retrieve result set and send it to user as a download

# rather than for display in a web page

require_once "Cookbook.php";

require_once "Cookbook_Webutils.php";

$title = "Result Set Downloading Example";

# If no download parameter is present, display instruction page

if (!get_param_val ("download"))

{

# construct self-referential URL that includes download parameter

$url = get_self_path () . "?download=1";

?>

<html>

<head>

<title><?php print ($title); ?></title>

</head>

<body bgcolor="white">

<p>

Select the following link to commence downloading:

<a href="<?php print ($url); ?>">download</a>

</p>

</body>

</html>

<?php

exit ();

} # end of "if"

# The download parameter was present; retrieve a result set and send

# it to the client as a tab-delimited, newline-terminated document.

# Use a content type of application/octet-stream in an attempt to

# trigger a download response by the browser, and suggest a default

# filename of "result.txt".

$conn =& Cookbook::connect ();

if (PEAR::isError ($conn))

die ("Cannot connect to server: "

. htmlspecialchars ($conn->getMessage ()));

$stmt = "SELECT * FROM profile";

$result =& $conn->query ($stmt);

if (PEAR::isError ($result))

die ("Cannot execute query: "

. htmlspecialchars ($result->getMessage ()));

header ("Content-Type: application/octet-stream");

header ("Content-Disposition: attachment; filename=\"result.txt\"");

while ($row =& $result->fetchRow ())

print (join ("\t", $row) . "\n");

$result->free ();

$conn->disconnect ();

?>

download.php uses a couple of functions that we haven’t covered yet:

§ get_self_path() returns the script’s own pathname. This is used to construct a URL that points back to the script and that includes a download parameter.

§ get_param_val() determines whether that parameter is present.

These functions are included in the Cookbook_Webutils.php file and are discussed further in Recipes and .

Another way to produce downloadable content is to generate the query result, write it to a file on the server side, compress it, and send the result to the browser. The browser likely will run some kind of uncompress utility to recover the original file.

Using a Template System to Generate Web Pages

Problem

Your scripts mix the code for retrieving information from your database with the code for generating HTML. You want to decouple these activities so that they can be performed separately.

Solution

Use a template system that enables you to design the general layout of the page but plug in specific data on a request-specific basis. Then you can obtain the data separately and pass it to the template system for output generation.

Discussion

In this chapter, we’ve been using an approach that retrieves data for a web page and generates the HTML for the page all in the same script. That has the advantage of simplicity, but there are disadvantages as well, and there are alternatives.

The primary disadvantage of mixing everything together is that there is little functional decoupling (separation of function) between the logic involved in obtaining the data needed for the page (the business or application logic) and the logic that formats the data for display (the presentation logic).

An alternative approach to web page generation is to use some kind of template system that enables functional decomposition of phases of the page-generation process. A template system does this by separating business logic from the presentation logic. If you’re partial to the Model-View-Controller (MVC) architecture, templates help you implement MVC logic by taking care of the View part.

There are many template packages to choose from, as a few minutes searching the Internet quickly reveals. Here, we’ll briefly explore two (PageTemplate for Ruby and Smarty for PHP), each of which uses the following approach:

1. The web server invokes a script in response to a client request for a web page.

2. The script determines what must be displayed and retrieves any data necessary to make that happen.

3. The script calls the template engine, passing a page template to it along with the data to insert into the template.

4. The template engine looks for special markers in the template that indicate where to insert data values, replaces them with the appropriate values, and produces the result as its output. The output is the web page that is sent to the client as the response to the client’s request.

Typically, template engines are used in web contexts to produce HTML, but depending on your requirements, you could produce other formats such as plain text or XML.

A template system provides the benefits of functional decoupling in the following way:

§ A programmer can change the implementation of how to obtain the information to be displayed without caring about what it will look like when displayed. For example, if you want to change database systems, you can do that in the application logic without requiring changes to any templates (the presentation logic).

§ A page designer can change the way information is displayed by changing the templates, without knowing anything about how to obtain the information. It could come from a file, a database, or whatever. The designer simply assumes that the information is available and doesn’t care where it comes from. In particular, the designer doesn’t need to know how to write programs.

That last sentence might be considered subject to debate. Although it’s true that page designers do not, for the most part, need to know a programming language, it’s also true that templates are embedded with special markers that indicate where to substitute data values. Depending on the complexity of the data to be displayed and the markup syntax of the template package, the markup can range from very simple to looking like a program itself! Nevertheless, it’s easier to understand template markers than a general-purpose programming language.

In this section, we’ll revisit some of the topics that were addressed earlier in the chapter: Generating paragraphs (Displaying Query Results as Paragraph Text), lists (Displaying Query Results as Lists), and tables (Displaying Query Results as Tables). But here we’ll consider how to create these structures in web pages by designing page templates for PageTemplate and Smarty. Before doing so, the discussion begins for each template package with a short tutorial that shows how to use markup for the following concepts:

Value substitution

How to indicate where to substitute values into a template. A given value can be substituted as is, or it can be subjected to HTML-encoding or URL-encoding first.

Conditional testing

How to select or ignore part of a template based on the result of a test.

Iteration (looping)

How to process part of a template repeatedly, once per element of a data structure such as an array or hash. This is useful for generating lists and tables.

Appendix A indicates where to get PageTemplate and Smarty. I assume in the remainder of this recipe that you have already installed them. The example templates and scripts discussed here can be found under the apache directory of the recipes distribution, in the pagetemplate and smartysubdirectories.

The examples use .tmpl as the extension for template files, but there is no requirement for either template package that you use that particular extension. (For example, Smarty applications often use .tpl.)

Using page template for web page generation in Ruby

A PageTemplate web application consists of an HTML template file for the output page, and a Ruby script that gathers the data needed by the template and calls the template engine to process the template. In PageTemplate, the markup indicators are [% and %]. The content between the markers tells PageTemplate what action to perform.

Value substitution. To indicate where a value goes in a PageTemplate template file, use a [%varvar_name%] directive:

[%var myvar%]

PageTemplate replaces the directive with the value of the var_name variable. By default, the value is substituted as is. To perform HTML-encoding or URL-encoding of the value, add the appropriate preprocessor name to the directive:

[%var myvar :escapeHTML%]

[%var myvar :escapeURI%]

:escapeHTML has the same effect as invokingCGI.escapeHTML() to make sure that special characters such as < or & are converted to the corresponding < and & entities. :escapeURI has the same effect as CGI.escape().

Any value referred to by a [%var%] directive must be a string, or able to produce a string if the to_s method is applied to it.

Conditional testing. A conditional test begins with an [%ifvar_name%] directive, ends with [%endif%], and optionally contains an [%else%] directive:

[%if myvar%]

myvar is true

[%else%]

myvar is false

[%endif%]

If myvar evaluates to true, PageTemplate processes the content following the [%if%] directive. Otherwise, it processes the content following [%else%]. For a simple conditional with no “else” part, omit the [%else%] part.

Iteration. The [%invar_name%]construct provides iteration over the members of the list named by the given variable. The template content between the [%in%] and [%endin%] directives is processed once per member. If the list members are hashes, you can refer to the hash elements by name. Suppose that mylist is an array consisting of items, each of which is a hash with this structure:

{ "first_name" =>value, "last_name" => value }

The hash members are first_name and last_name, so an iterator can process the list and refer to its item members as follows:

[%in mylist%]

Name: [%var first_name%] [%var last_name%]

[%endin%]

If the list is a simple indexed list, its elements don’t have names. In that case, you can provide a name in the [%in%] directive by which to refer to the elements:

[%in mylist: myitem%]

Item: [%var myitem%]

[%endin%]

The following template file, pt_demo.tmpl, demonstrates the concepts just discussed:

<!-- pt_demo.tmpl -->

<html>

<head>

<title>PageTemplate Demonstration</title>

</head>

<body>

<p>Value substitution:</p>

<p>

My name is [%var character_name%],

and I am [%var character_role%].

</p>

<p>Value substitution with encoding:</p>

<p>

HTML-encoding: [%var str_to_encode :escapeHTML%];

URL-encoding: [%var str_to_encode :escapeURI%]

</p>

<p>Conditional testing:</p>

<p>

[%if id%]

You requested information for item number [%var id%].

[%else%]

You didn't choose any item!

[%endif%]

</p>

<p>Iteration:</p>

<p>

Colors of the rainbow:

[%in colors: color%]

[%var color%]

[%endin%]

</p>

</body>

</html>

We also need a script to go with the template. The basic skeleton of a Ruby script that uses PageTemplate looks like this:

#!/usr/bin/ruby -w

# Access required modules

require "PageTemplate"

require "cgi"...obtain data to be displayed in the output page...

# Create template object

pt = PageTemplate.new

# Load template file

pt.load("template_file_name")

# Assign values to template variables

pt["var_name1"] = value1

pt["var_name2"] = value2

...

# Generate output (cgi.out adds headers)

cgi = CGI.new("html4")

cgi.out { pt.output }

The script begins by including the required Ruby library files: PageTemplate to generate the page, and cgi because its out method provides an easy way to send the page to the client complete with any required headers.

The next steps are to create a template object, load the template file, and associate values with the variables named in the template. The filename should be an absolute variable or a pathname relative to the script’s current working directory at the point when the template object is created. The syntax for assigning a value to a template variable is similar to hash-value assignment:

pt["var_name"] = value

Finally, the script generates output. This is done with the template object’s output method. As shown, the script prints output from the template using the cgi object’s out method, which takes care of adding the page headers.

We can adapt that skeleton to write a pt_demo.rb script that accompanies the pt_demo.tmpl template file:

#!/usr/bin/ruby -w

# pt_demo.rb - PageTemplate demonstration script

require "PageTemplate"

require "cgi"

pt = PageTemplate.new

pt.load("pt_demo.tmpl")

pt["character_name"] = "Peregrine Pickle"

pt["character_role"] = "a young country gentleman"

pt["str_to_encode"] = "encoded text: (<>&'\" =;)"

pt["id"] = 47846

pt["colors"] = ["red","orange","yellow","green","blue","indigo","violet"]

cgi = CGI.new("html4")

cgi.out { pt.output }

To try the application, copy the pt_demo.rb and pt_demo.tmpl files to your Ruby web script directory and then request the script with your web browser. For example, if you copy the files to your usual cgi-bin directory, use this URL to request the script:

http://localhost/cgi-bin/pt_demo.rb

You might prefer to install the .tmpl file somewhere other than the cgi-bin directory because it isn’t an executable file. If you do that, adjust the pathname in the pt.load call.

The preceding tutorial is sufficient background for developing templates to produce paragraphs, lists, and tables that display information retrieved from MySQL.

Paragraph generation. Our first PageTemplate-based MySQL application requires only the production of simple paragraphs. The application is patterned after the discussion in Displaying Query Results as Paragraph Text. The scripts developed there connect to the MySQL server, issue a query to retrieve some information, and write out a few paragraphs to report the query result: the current time, the server version, the MySQL username, and the current database. For an equivalent PageTemplate application, we need a template file and a Ruby script, here namedpt_paragraphs.tmpl and pt_paragraphs.rb.

Begin by designing the page template. For this application, the template is quite simple. It requires only simple value substitution, not conditionals or iterators. Use the [%var var_name %] construct to make a template for a page containing simple paragraphs:

<!-- pt_paragraphs.tmpl -->

<html>

<head>

<title>[%var title :escapeHTML%]</title>

</head>

<body bgcolor="white">

<p>Local time on the MySQL server is [%var now :escapeHTML%].</p>

<p>The server version is [%var version :escapeHTML%].</p>

<p>The current user is [%var user :escapeHTML%].</p>

<p>The default database is [%var db :escapeHTML%].</p>

</body>

</html>

The pt_paragraphs.tmpl template uses :escapeHTML in the [%var%] directives based on the assumption that we don’t necessarily have much knowledge about whether the data values contain special characters. That completes the page design.

Next, write the Ruby script that retrieves the data and invokes the template engine. The directives embedded within the template indicate that the accompanying script will need to provide values for template variables named title, now, version, user, and db:

#!/usr/bin/ruby -w

# pt_paragraphs.rb - generate HTML paragraphs

require "PageTemplate"

require "cgi"

require "Cookbook"

title = "Query Output Display - Paragraphs"

dbh = Cookbook.connect

# Retrieve data required for template

(now, version, user, db) =

dbh.select_one("SELECT NOW(), VERSION(), USER(), DATABASE()")

db = "NONE" if db.nil?

dbh.disconnect

pt = PageTemplate.new

pt.load("pt_paragraphs.tmpl")

pt["title"] = title

pt["now"] = now

pt["version"] = version

pt["user"] = user

pt["db"] = db

cgi = CGI.new("html4")

cgi.out { pt.output }

This script is a lot like the pt_demo.rb script discussed earlier. The main differences are that it uses the Cookbook module (for its connect method) and it obtains the data required for the page template by connecting to MySQL and issuing queries.

List generation. Lists contain repeating elements, so they are produced with the PageTemplate [%in%] directive that iterates through a list. The following template generates an ordered list, an unordered list, and a definition list:

<!-- pt_lists.tmpl -->

<html>

<head>

<title>[%var title :escapeHTML%]</title>

</head>

<body bgcolor="white">

<p>Ordered list:</p>

<ol>

[%in list: item %]

<li>[%var item :escapeHTML%]</li>

[%endin%]

</ol>

<p>Unordered list:</p>

<ul>

[%in list: item %]

<li>[%var item :escapeHTML%]</li>

[%endin%]

</ul>

<p>Definition list:</p>

<dl>

[%in defn_list%]

<dt>[%var note :escapeHTML%]</dt>

<dd>[%var mnemonic :escapeHTML%]</dd>

[%endin%]

</dl>

</body>

</html>

The first two lists differ only in the surrounding tags (<ol> versus <ul>), and they use the same data for the list items. The values come from a simple array, so we provide another argument to the [%in%] directive that associates a name (item) with values in the list and gives us a way to refer to them.

For the definition list, each item requires both a term and a definition. We’ll assume that the script can provide a list of structured values that have members named note and mnemonic.

The script that retrieves the list data from MySQL and processes the template looks like this:

#!/usr/bin/ruby -w

# pt_lists.rb - generate HTML lists

require "PageTemplate"

require "cgi"

require "Cookbook"

title = "Query Output Display - Lists"

dbh = Cookbook.connect

# Fetch items for ordered, unordered lists

# (create an array of "scalar" values; the list actually consists of

# DBI::Row objects, but for single-column rows, applying the to_s

# method to each object results in the column value)

stmt = "SELECT item FROM ingredient ORDER BY id"

list = dbh.select_all(stmt)

# Fetch terms and definitions for a definition list

# (create a list of hash values, one hash per row)

defn_list = []

stmt = "SELECT note, mnemonic FROM doremi ORDER BY id"

dbh.execute(stmt) do |sth|

sth.fetch_hash do |row|

defn_list << row

end

end

dbh.disconnect

pt = PageTemplate.new

pt.load("pt_lists.tmpl")

pt["title"] = title

pt["list"] = list

pt["defn_list"] = defn_list

cgi = CGI.new("html4")

cgi.out { pt.output }

Recall that for the definition list, the template expects that the list items are structured values containing note and mnemonic members. The script should satisfy this requirement by creating an array of hashes, in which each hash is of this form:

{ "note" =>val1, "mnemonic" => val2 }

This is easily accomplished by selecting the note and mnemonic columns from the table that contains the data, and using the fetch_hash function to retrieve the rows as hashes.

Table generation. Designing a template for an HTML table is similar to designing one for a list because they both have repeating elements and thus use iterators. For a table, the repeating element is the row (using the <tr> element). Within each row, you write the values for the cells using<td> elements. The natural data structure for this is an array of hashes, where each hash has elements keyed by the column names.

The example shown here displays the contents of the cd table, which has three columns: year, artist, and title. Assuming that we use a template variable named rows to hold the table contents, the following template generates the table. Actually, the template generates the table twice(once with all rows the same color, and once with alternating row colors):

<!-- pt_tables.tmpl -->

<html>

<head>

<title>[%var title :escapeHTML%]</title>

</head>

<body bgcolor="white">

<p>HTML table:</p>

<table border="1">

<tr>

<th>Year</th>

<th>Artist</th>

<th>Title</th>

</tr>

[%in rows%]

<tr>

<td>[%var year :escapeHTML%]</td>

<td>[%var artist :escapeHTML%]</td>

<td>[%var title :escapeHTML%]</td>

</tr>

[%endin%]

</table>

<p>HTML table with rows in alternating colors:</p>

<table border="1">

<tr>

<th bgcolor="silver">Year</th>

<th bgcolor="silver">Artist</th>

<th bgcolor="silver">Title</th>

</tr>

[%in rows%]

[%if __ODD__ %]

<tr bgcolor="white">

[%else%]

<tr bgcolor="silver">

[%endif%]

<td>[%var year :escapeHTML%]</td>

<td>[%var artist :escapeHTML%]</td>

<td>[%var title :escapeHTML%]</td>

</tr>

[%endin%]

</table>

</body>

</html>

The first table template generates a “plain” table. The second template generates a table that has alternating row colors. Switching between colors is easy to do by using a conditional directive that tests the value of the built-in __ODD__ variable that is true for every odd-numbered row.

To fetch the table data and process the template, use this script:

#!/usr/bin/ruby -w

# pt_tables.rb - generate HTML tables

require "PageTemplate"

require "cgi"

require "Cookbook"

title = "Query Output Display - Tables"

dbh = Cookbook.connect

# Fetch table rows

# (create a list of hash values, one hash per row)

rows = []

stmt = "SELECT year, artist, title FROM cd ORDER BY artist, year"

dbh.execute(stmt) do |sth|

sth.fetch_hash do |row|

rows << row

end

end

dbh.disconnect

pt = PageTemplate.new

pt.load("pt_tables.tmpl")

pt["title"] = title

pt["rows"] = rows

cgi = CGI.new("html4")

cgi.out { pt.output }

Using Smarty for web page generation in PHP

A Smarty web application consists of an HTML template file for the output page, and a PHP script that gathers the data needed by the template and calls the template engine to process the template. In Smarty templates, the markup indicators are { and }, which surround the commands that tell Smarty what actions you want taken.

Value substitution. To indicate where to substitute a value in a Smarty template, use {$var_name} notation. The substitution uses the value with no preprocessing. If you want to HTML-encode or URL-encode the value, add a modifier. The following three lines substitute a value without modification, with HTML-encoding, and with URL-encoding, respectively:

{$myvar}

{$myvar|escape}

{$myvar|escape:"url"}

Conditional testing. A conditional test begins with {ifexpr}, ends with {/if}, and optionally contains one or more {elseifexpr} clauses and an {else} clause. Each expr can be a variable or a more complex expression (unlike PageTemplate, which allows only a variable name). Here is a simple if-then-else test:

{if $myvar}

myvar is true

{else}

myvar is false

{/if}

Iteration. Smarty has multiple iterator constructs. The {foreach} command names the variable containing the list of values to iterate over and indicates the name by which the body of the loop will refer to each value. The following construct specifies $mylist as the name of the list and uses myitem for the list item name:

{foreach from=$mylist item=myitem}

{$myitem}

{/foreach}

The {section} command names the variable containing the list of values and the name to use for subscripting the list variable within the loop:

{section loop=$mylist name=myitem}

{$mylist[myitem]}

{/section}

Note that the syntax for referring to list items is different for {section} than for {foreach}. The $mylist[myitem] syntax shown is useful for iterating through a list of scalar values. If you need to iterate through structured values such as associative arrays, refer to structure members as$mylist[myitem]. member_name. The list and table applications discussed later illustrate this syntax further.

The following template file, sm_demo.tmpl, demonstrates the preceding concepts:

<!-- sm_demo.tmpl -->

<html>

<head>

<title>Smarty Demonstration</title>

</head>

<body>

<p>Value substitution:</p>

<p>

My name is {$character_name},

and I am {$character_role}.

</p>

<p>Value substitution with encoding:</p>

<p>

HTML-encoding: {$str_to_encode|escape};

URL-encoding: {$str_to_encode|escape:"url"}

</p>

<p>Conditional testing:</p>

<p>

{if $id}

You requested information for item number {$id}.

{else}

You didn't choose any item!

{/if}

</p>

<p>Iteration:</p>

<p>

Colors of the rainbow (using foreach):

{foreach from=$colors item=color}

{$color}

{/foreach}

</p>

<p>

Colors of the rainbow (using section):

{section loop=$colors name=color}

{$colors[color]}

{/section}

</p>

</body>

</html>

We also need a script to accompany the template. An outline for a PHP script that uses Smarty looks like this:

<?php

require_once "Smarty.class.php";...obtain data to be displayed in the output page...

# Create template object

$smarty = new Smarty ();

# Assign values to template variables

$smarty->assign ("var_name1", value1);

$smarty->assign ("var_name2", value2);

...

# Process the template to produce output

$smarty->display("template_file_name");

?>

The Smarty.class.php file gives you access to Smarty’s capabilities. As mentioned earlier, I assume that you have Smarty installed already. To make it easy for your PHP scripts to access the Smarty.class.php file without having to specify its full pathname, you should add the directory where that file is located to the value of the include_path configuration variable in your php.ini file. For example, if Smarty.class.php is installed in /usr/local/lib/php/smarty, add that directory to the value of include_path.

The rest of the skeleton shows the essential steps for using Smarty: create a new Smarty template object, specify values for template variables with assign(), and then pass the template filename to the display() object to generate the output.

The skeleton is easily adapted to produce the following script, sm_demo.php, to go along with the sm_demo.tmpl template:

<?php

# sm_demo.php - Smarty demonstration script

require_once "Smarty.class.php";

$smarty = new Smarty ();

$smarty->assign ("character_name", "Peregrine Pickle");

$smarty->assign ("character_role", "a young country gentleman");

$smarty->assign ("str_to_encode", "encoded text: (<>&'\" =;)");

$smarty->assign ("id", 47846);

$colors = array ("red","orange","yellow","green","blue","indigo","violet");

$smarty->assign ("colors", $colors);

$smarty->display ("sm_demo.tmpl");

?>

We now have a simple Smarty application (the template file and the PHP script that uses it), but a bit of additional setup is required before we can deploy the application. Smarty uses a set of directories to do its work, so you’ll need to create them first. By default, Smarty assumes that these directories are located in the same directory where the PHP script is installed. The following instructions assume that this directory is mcb under your Apache document root (the same PHP directory that has been used throughout this chapter).

Change location to the mcb directory and create these four directories:

%mkdir cache

% mkdir configs

% mkdir templates

% mkdir templates_c

The directories you just created must be readable by the web server, and two of them must also be writable. To do this on Unix, set the group permissions appropriately, and then change their group to be that used by the web server. Set the group permissions with these commands:

%chmod g+rx cache configs templates templates_c

% chmod g+w cache templates_c

Next, determine the correct group name to use for the directories. To do this, look in the Apache httpd.conf file for a Group line, which might look like this:

Group www

That line indicates that Apache runs using the www group ID. Other common values are nobody or apache. Use the group name in the following command, which should be executed as root:

#chgrp www cache configs templates templates_c

That completes the Smarty directory setup. You can deploy the Smarty demonstration application by copying sm_demo.php to the mcb directory and sm_demo.tmpl to the mcb/templates directory. Then request the script with your web browser:

http://localhost/mcb/sm_demo.php

For each of the following applications, follow the same principle that the PHP script goes in the mcb directory and the Smarty template goes in mcb/templates.

If you want to keep your application subdirectories outside of your document tree, create them somewhere other than in the mcb directory. In this case, you need to let your PHP scripts know where they are by setting several members of your Smarty object after you create it. For example, if you create the Smarty directories under /usr/local/lib/mcb/smarty, you modify your scripts to tell Smarty about their location as follows:

$smarty = new Smarty ();

$smarty->cache_dir = "/usr/local/lib/mcb/smarty/cache";

$smarty->config_dir = "/usr/local/lib/mcb/smarty/configs";

$smarty->template_dir = "/usr/local/lib/mcb/smarty/templates";

$smarty->compile_dir = "/usr/local/lib/mcb/smarty/templates_c";

Paragraph generation. The Smarty equivalent to the PageTemplate file shown earlier for generating paragraphs is as follows:

<!-- sm_paragraphs.tmpl -->

<html>

<head>

<title>{$title|escape}</title>

</head>

<body bgcolor="white">

<p>Local time on the MySQL server is {$now|escape}.</p>

<p>The server version is {$version|escape}.</p>

<p>The current user is {$user|escape}.</p>

<p>The default database is {$db|escape}.</p>

</body>

</html>

The template uses nothing more than {$ var_name } value substitution, plus the escape modifier to tell Smarty to perform HTML-escaping on the values.

To retrieve the data referred to in the template and then invoke Smarty, use this script:

<?php

# sm_paragraphs.php - generate HTML paragraphs

require_once "Smarty.class.php";

require_once "Cookbook.php";

$title = "Query Output Display - Paragraphs";

$conn =& Cookbook::connect ();

if (PEAR::isError ($conn))

die ("Cannot connect to server: "

. htmlspecialchars ($conn->getMessage ()));

$result =& $conn->query ("SELECT NOW(), VERSION(), USER(), DATABASE()");

if (PEAR::isError ($result))

die (htmlspecialchars ($result->getMessage ()));

list ($now, $version, $user, $db) = $result->fetchRow ();

$result->free ();

if (!isset ($db))

$db = "NONE";

$conn->disconnect ();

$smarty = new Smarty ();

$smarty->assign ("title", $title);

$smarty->assign ("now", $now);

$smarty->assign ("version", $version);

$smarty->assign ("user", $user);

$smarty->assign ("db", $db);

$smarty->display ("sm_paragraphs.tmpl");

?>

List generation. The following template generates three lists. The first two are ordered and unordered lists that use the same set of scalar item values. The third list is a definition list that requires two values per iteration through a set of items:

<!-- sm_lists.tmpl -->

<html>

<head>

<title>{$title|escape}</title>

</head>

<body bgcolor="white">

<p>Ordered list:</p>

<ol>

{foreach from=$list item=cur_item}

<li>{$cur_item|escape}</li>

{/foreach}

</ol>

<p>Unordered list:</p>

<ul>

{foreach from=$list item=cur_item}

<li>{$cur_item|escape}</li>

{/foreach}

</ul>

<p>Definition list:</p>

<dl>

{section loop=$defn_list name=cur_item}

<dt>{$defn_list[cur_item].note|escape}</dt>

<dd>{$defn_list[cur_item].mnemonic|escape}</dd>

{/section}

</dl>

</body>

</html>

The definition list uses the {section} command and the $ list [ item ]. member notation mentioned earlier for referring to members of structured values.

The script that processes the template needs to fetch the values for the ordered and unordered lists as an array of scalar values. For the definition list, the script should create an array of associative arrays that have members named note and mnemonic:

<?php

# sm_lists.php - generate HTML lists

require_once "Smarty.class.php";

require_once "Cookbook.php";

$title = "Query Output Display - Lists";

$conn =& Cookbook::connect ();

if (PEAR::isError ($conn))

die ("Cannot connect to server: "

. htmlspecialchars ($conn->getMessage ()));

# Fetch items for ordered, unordered lists

# (create an array of scalar values)

$stmt = "SELECT item FROM ingredient ORDER BY id";

$result =& $conn->query ($stmt);

if (PEAR::isError ($result))

die (htmlspecialchars ($result->getMessage ()));

$list = array ();

while (list ($item) = $result->fetchRow ())

$list[] = $item;

$result->free ();

# Fetch terms and definitions for a definition list

# (create an array of associative arrays)

$stmt = "SELECT note, mnemonic FROM doremi ORDER BY id";

$defn_list =& $conn->getAll ($stmt, array (), DB_FETCHMODE_ASSOC);

if (PEAR::isError ($defn_list))

die (htmlspecialchars ($result->getMessage ()));

$conn->disconnect ();

$smarty = new Smarty ();

$smarty->assign ("title", $title);

$smarty->assign ("list", $list);

$smarty->assign ("defn_list", $defn_list);

$smarty->display ("sm_lists.tmpl");

?>

The script uses getAll() to fetch the values for the definition list. This is a connection object method that executes a query and retrieves the result set in a single call. By setting the fetch mode to DB_FETCHMODE_ASSOC, you get an array of associative arrays, each of which has members named note and mnemonic—perfect for creating an HTML definition list in Smarty.

Table generation. To generate an HTML table, we need an iterator for looping through the list of rows to produce a <tr> element per row. As with the definition list in the previous application, this is easily done by using a {section} command and referring to the values for cells within the row by using $list[item].member syntax. The following script uses that syntax to generate two tables (one “plain” table, and one that has alternating row colors):

<!-- sm_tables.tmpl -->

<html>

<head>

<title>{$title|escape}</title>

</head>

<body bgcolor="white">

<p>HTML table:</p>

<table border="1">

<tr>

<th>Year</th>

<th>Artist</th>

<th>Title</th>

</tr>

{section loop=$rows name=row}

<tr>

<td>{$rows[row].year|escape}</td>

<td>{$rows[row].artist|escape}</td>

<td>{$rows[row].title|escape}</td>

</tr>

{/section}

</table>

<p>HTML table with rows in alternating colors:</p>

<table border="1">

<tr>

<th bgcolor="silver">Year</th>

<th bgcolor="silver">Artist</th>

<th bgcolor="silver">Title</th>

</tr>

{section loop=$rows name=row}

<tr bgcolor="{cycle values="white,silver"}">

<td>{$rows[row].year|escape}</td>

<td>{$rows[row].artist|escape}</td>

<td>{$rows[row].title|escape}</td>

</tr>

{/section}

</table>

</body>

</html>

To produce alternating row colors for the second table, the template uses a {cycle} command, which alternately selects white and silver for successive iterations through the row list. {cycle} provides a nice convenience here for a task that you would otherwise handle using a conditional and the $smarty.section. list .iteration value that returns the current iteration number (beginning with 1):

{if $smarty.section.row.iteration % 2 == 1}

<tr bgcolor="white">

{else}

<tr bgcolor="silver">

{/if}

The following script fetches the table rows and processes the template:

<?php

# smtables.php - generate HTML tables

require_once "Smarty.class.php";

require_once "Cookbook.php";

$title = "Query Output Display - Tables";

$conn =& Cookbook::connect ();

if (PEAR::isError ($conn))

die ("Cannot connect to server: "

. htmlspecialchars ($conn->getMessage ()));

# Fetch items for table

$stmt = "SELECT year, artist, title FROM cd ORDER BY artist, year";

$rows =& $conn->getAll ($stmt, array (), DB_FETCHMODE_ASSOC);

if (PEAR::isError ($rows))

die (htmlspecialchars ($result->getMessage ()));

$conn->disconnect ();

$smarty = new Smarty ();

$smarty->assign ("title", $title);

$smarty->assign ("rows", $rows);

$smarty->display ("sm_tables.tmpl");

?>