Using MySQL to Stop Editing Web Pages

Using MySQL to Stop Editing Web Pages
Pages: 1, 2

Margin Links

The next table we'll need to create will keep track of the hyperlinks displayed in the left margin of each page. For this table, we'll have SQL statements in our CGI scripts that will retrieve certain rows based on which section of the site the user is viewing.

Therefore, the table links will not only need columns related to the hyperlink, but also a column for the section number:

CREATE TABLE links (
    rec_id      INT          AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(75),
    description VARCHAR(50),
    type        ENUM('section', 'sub-section', 'all', 'offsite'),
    address     VARCHAR(100),
    target      ENUM('_blank', '_self'),
    section     INT,
    sequence    INT,
    status      ENUM('ac','in'));

The column called name contains the text displayed to the user in the margin of the web page. The description column provides alternate or title text for the hyperlink. The column labeled type determines the type of link. This can allow us to organize links in more meaningful ways. The address column contains the site address of the link. This could be an internal or an external address.

The column target provides the target property for the link: _blank will open the link in a new browser window. We'll use this for offsite links so that users don't forget from where they came. The section column, of course, sets the pages on which the links will display; sequence is for their ordering. Finally, status will allow the site administrator to disable a link temporarily.

Once we load our links table with the data for linking each section together and for allowing the user to reach each page within a section, we can set up our CGI scripts to retrieve the data:

my $main_links = "SELECT name, address, target
                  FROM links
                  WHERE type='section' AND status='ac'
                  ORDER BY sequence";

my $section_links = "SELECT name, address, target
                     FROM links
                     WHERE type='sub-section' AND section=?
                     AND status='ac'
                     OR type='all' AND status='ac'
                     ORDER BY sequence";

my $offsite_links = "SELECT name, address, target
                     FROM links
                     WHERE type='offsite' AND section='$section'
                     AND status='ac'
                     ORDER BY sequence";

The first SQL statement retrieves the main links for all active sections of the site. The second SQL statement will retrieve active sub-section links only for the section of the site the user happens to be viewing, based on the value of the variable $section. It will also retrieve links for all pages of the site, regardless of the section. The third SQL statement retrieves offsite links for the section. All three of these SQL statements order the rows based on sequence number as set by the site administrator.

Of course, beware that interpolating $section directly into a SQL statement can leave you vulnerable to SQL injection attacks. Be sure that you use DBI's quote() method to escape hazardous characters or, better yet, use DBI placeholders.

In a CGI script, we would perhaps print a "Sections" heading in the left margin and then use the first SQL statement above to retrieve the links to other sections and print them. To do this in Perl, we might have a function that looks like this:

sub margin {
    my ($q, $link_kind) = @_;
    my $sql_stmnt;

    if ($link_kind eq 'main') {
        $sql_stmnt = $main_links;
    }
    elsif ($link_kind eq 'section') {
        $sql_stmnt = $section_links;
    }
    else {
        $sql_stmnt = $offsite_links;
    }

    my $dbh = DBI->connect("DBI:mysql:db1:localhost", $user, $password)
              || die "Could not connect: " . DBI->errstr;

    my $sth = $dbh->prepare($sql_stmnt);
    $sth->execute();

    my $links = $sth->fetchall_arrayref();

    $sth->finish();
    $dbh->disconnect();

    foreach my $row (@$links) {
        my ($name, $address, $target) = @$row;

        print $q->a({ -href => $address, -target=> $target }, $name), 
            $q->br, "\n";
    }
}

This Perl function requires the use of the CGI Perl module. Call it like this:

...

print "Sections", $q->br, "\n";
margin($q, 'main');

print "Links", $q->br, "\n";
margin($q, 'section');
margin($q, 'offsite');

...

Each line that calls the function margin passes the CGI object ($q) and instructions as to which SQL statement to use. The function will pick up the instruction, running it through the if/elsif/else statement to determine the SQL statement to use, and storing it in $sql_stmnt for its own use. Next, the function connects to the MySQL server, queries the appropriate database with the SQL statement, stores the data in memory (referenced by $links), and then disconnects. Finally, it loops through the complex data structure with a foreach statement and prints out a hyperlink for each row.

Administrative Forms

We need to develop some online forms so that our non-technical, site-content administrators can modify and add data on their own. I usually set up a separate, password-protected web page with links to, and brief descriptions for, each administrative form. There are several forms that can be created. Since content administrators will require training and learning time, you may want to develop just a few basic forms initially and add more as they learn. Start with a page to list the basic data in the table postings and link each record to another form to edit, add, and delete postings.

... # Initial set up

my %types = (
    top => 'Page Heading & Paragraph',
    sub => 'Sub-Heading & Paragraphs',
);

my %sections = (
    1000 => 'Main',
    1001 => 'Products',
    1002 => 'Service',
    1003 => 'Shipping',
);

my $sql_stmnt = "SELECT rec_id, post_date, 
                 type, heading, section
                 FROM postings 
                 ORDER BY section, type, post_date";

my $dbh = DBI->connect("DBI:mysql:db1:localhost", $user, $password)
          || die "Could not connect: " . DBI->errstr;

my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();

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

... # Start HTML page

foreach my $row (@$results) {
    my ($rec_id,$post_date,$type,$heading,$section) = @$row;
    print 
      "$sections{$section} - $types{$type} ", 
      $q->a({ href=> "post-edit.cgi?rec_id=$rec_id" }, $heading),
      " ($post_date)", $q->br, "\n";
}

... # End HTML and exit

Here again, we're using the CGI.pm, which precedes this excerpt. We set up a hash listing our sections of the web site (%sections) and another hash for the types of posts (%types). Next, we put together our SQL statement to retrieve some key columns from our table. We then connect to MySQL, run the SQL statement, store a reference to the results in the variable $results, and then disconnect from MySQL. We loop through the array of rows of data and print each row within a hyperlink that will connect the user to the CGI script post-edit.cgi with the rec_id. That script will allow the user to edit the data.

This brings us to our form for editing and adding postings.

... # Initial set up

my $script = 'post-save.cgi';

print
   $q->header(-type => 'text/html'), "\n",
   $q->start_html, "\n",
   $q->start_form(-method => 'post', -action => $script), "\n",
   $q->h3("WebPage Posting Form"), "\n",

   "Select Section:  ",
   $q->popup_menu(-name => 'section', -values =>[sort keys %sections],
                  -labels =>\%sections), 
   $q->br, "\n",

   "Post Type:  ",
   $q->radio_group(-name => 'type', -values =>[sort keys %types],
                    -default => 'sub', -labels =>\%types), 
   $q->br, "\n",

   $q->b("Heading:  "), 
   $q->textfield(-name => 'heading', -value => $heading, -size => '10'),
   $q->br, "\n",

   $q->b("Body:  "), $q->br,
   $q->textarea(-name => 'post', -value => $post,
                -rows => '25', -columns => '60'),
   $q->br, "\n",

   $q->submit('Save'), "\n",
   $q->end_form,
   $q->end_html;

...

If you're not familiar with CGI.pm notation, this excerpt especially may look a little cryptic. Basically, we're starting an HTML page and form that will post the data that the user enters into the form here to the proper Perl script (post-save.cgi). On the next line, we display the page heading (h3). We then instruct the user to select a section of the site from a pull-down list (a pop-up menu) to display the posting that is about to be entered. This list comes from the hash %sections, which is not shown in this excerpt. After that, the user is allowed to choose the type of posting. Next, the script provides a text box to enter a heading for the posting and a large multi-line box to enter paragraphs of text to be posted. Incidentally, we'll need to convert double hard-returns entered by the user to HTML paragraph tags. The module HTML::FromText can help with this task. Finally, we end with a submit button and end the form and the web page.

There are several other forms to develop. We will need a form for the links table as well as forms for other tables that will hold a list of sections, and possibly text, that may appear at the bottom of each page. However, this one example gives you the idea of how to proceed on your own.

Conclusion

Once you embrace the concept that a site's content does not belong in flat HTML pages or buried in CGI scripts, but in accessible MySQL tables, then the process of handing over content administration to other people can begin. Once you finish the conversion of a site so that non-technical administrators can manage it, then the content of the site will improve greatly and quickly, and your involvement will be strictly as a developer, not just a typist. The result will be better web sites, more satisfied co-workers or clients, and higher job satisfaction for you.

Russell Dyer has worked full-time for several years as a free-lance writer of computer articles, primarily on MySQL.

Return to ONLamp.com.