Using MySQL to Stop Editing Web Pages

by Russell Dyer
02/19/2004

Although there's much that can be done with web design, sometimes I find it to be extremely boring. When I'm deep into a Perl project, the last thing I want is to meet with other department managers to discuss changes in the text on the corporate web site. It's not a good (or interesting) use of my time. As a result, over the last few years I've developed CGI scripts for sites in Perl and databases in MySQL so that non-technical staff can manage and update site content with little help from me.

Concepts and Layout

When most technical people think of web pages, I suspect that they think of static pages. They know that web pages may be dynamically generated, but the CGI code that I've seen usually has static HTML buried in it. It's true that at the core there will always be text. Nevertheless, non-technical people cannot edit content text contained in a flat HTML page or in a CGI script. A technical person must make any changes for them.

The goal of developing dynamic web pages is not only to reduce code to a minimum, but also to reduce the involvement of technical staff in maintaining the data, especially data that belongs to other departments. With this concept in mind, you should eliminate all flat HTML pages and lift out of CGI scripts all text that is displayed to the user. This should be your goal initially, and your rule eventually, if you want to get out of the text-management business and instead focus on what you enjoy and what you were hired to do: develop software.

If it's to become accessible to non-technical staff, content text belongs in a database like MySQL. All content should be stored in tables for easy retrieval and editing. On a simple web page there, is typically a page heading and a short paragraph introducing the page. Below that it's common to have sub-headings with paragraphs of related text. There are often hyperlinks to other sections and pages in the left margin. Each of these components should be separated and stored in MySQL. This may seem tedious and unnecessary at first, but it can be very useful and save you enormous amounts of time. It also will allow those who are concerned with site content to make changes on their own.

Content Text

We'll start by creating a database and a table in MySQL that will store the page headings and the introductory text for every page of a fictitious web site. It's good form to include a record number and the record creation date. We also need a column for section, so that our CGI scripts can select the correct record for the section of the site to be displayed. Finally, we will need a field for the headings and another for text to be posted. Here's what we'll enter in the mysql client:

CREATE DATABASE db1;

    USE db1;

    CREATE TABLE postings (
        rec_id    INT AUTO_INCREMENT  PRIMARY KEY,
        post_date DATE,
        section   INT,
        heading   VARCHAR(50),
        post      TEXT
    );

The first two lines create the database called db1 and then switch the MySQL session to it. The next set of lines create our first table, postings. The record identification column is automatically incremented so that we're assured of unique numbers for each row. The column section is an integer data type so that it can be linked to another table that we'll create to house data on each section — that table will have a rec_id column like this table. We've limited the heading to fifty characters; that should be enough. Finally, the post column will store the page's introductory text. We've made this column's data type TEXT, which will hold up to 64K of text, which is plenty.

That takes care of our page headings and opening paragraphs. We could create a separate table in MySQL to store the sub-text of each page. Instead, we'll just use the postings table with some modifications. We're already pushing our new policies, I know.

ALTER TABLE postings
    ADD COLUMN type     ENUM('top', 'sub') AFTER section,
    ADD COLUMN sequence INT                AFTER section,
    ADD COLUMN status   ENUM('ac', 'in');

We've added an enumerated column called type with choices to designate the difference between postings for the top of web pages and for sub-postings. Before that, we've added a column to allow a non-technical administrator to dictate the sequence in which pages will be displayed. The last column added will allow postings to be created in advance with a setting of inactive (in) so that they're not displayed until they're activated (ac). Of course, they could also be deactivated later, if desired. This all makes it possible to use a SELECT statement like the following to retrieve and display the headings and related text:

sub postings {
    my ($q, $section, $type) = @_;
    my $sql_stmnt = "SELECT heading, post
                     FROM   postings
                     WHERE  section=?
                     AND    type=?
                     AND    status='ac'
                     ORDER BY sequence";
    my $dbh = DBI->connect("DBI:mysql:db1:localhost", "user", "password")
              || die "Could not connect:" . DBI->errstr;
    my $sth = $dbh->prepare($sql_stmnt);
    $sth->execute( $section, $type );

    my $postings = $sth->fetchall_arrayref();
    $sth->finish();
    $dbh->disconnect();

    foreach my $row (@$postings) {
        my($heading, $text) = @$row;
        print $q->h3("$heading"), "\n",
        $q->p("$text"), "\n";
    }
}

Before explaining this function, let's look at how we might call it from a Perl CGI script:

#!/usr/bin/perl -w

use strict;

require 'library.pl';

use DBI;
use CGI;

my $q       = CGI->new();
my $section = $q->param("section");

...  # start HTML
postings($q,$section,'top');
postings($q,$section,'sub');

...

In the script excerpt just above, we're requiring or merging library.pl into our script — that's the script that contains the function postings. Next we're calling CGI.pm and setting up a new CGI object ($q). We then capture the parameter labeled section that's sent to the script by the user (e.g., index.cgi?section=1001). After our initial HTML items, we'll call the function postings, passing it the CGI object and the section number. Going back to the function, we parse out the section number and store it in $section and the posting type and store it in $type for our local use. We next insert these values into our SQL statement. We then connect to MySQL and fetch the matching rows from the table all at once and store the results in memory. Finally, we loop through the array of rows of data and print each heading and paragraph.