Keyword reach URLs in 5 minutes

This article is an introduction as well as a detailed guide to new mod_seo module being an integral part of Helicon Ape 3 which is destined to build good-looking URLs based on the application’s DB data or maps without necessity to change anything in its code.

Search Engine Optimization usually means a set of measures to improve web site performance for search engines, and one popular instrument for large electronic sites like forums, blogs, e-shops is Search Engine Friendly (SEF) URLs or Keyword Reach URLs. Such URL usually implies that you will see something like http://www.mysite.com/my_cool_product_name.html in the address bar instead of http://www.mysite.com/products.asp?pid=5568290. Search engines use such keywords in links to better rank pages with these keywords for search requests. Nowadays many web applications support Keyword Reach URLs either internally or by means of mod_rewrite but anyway application level support is required. Many other applications like, for example, version of WebWiz forum we are using on our web site, do not support keywords in links, so we have invented mod_seo to fill in this gap.

The idea is quite easy. Web applications like forums or blogs usually use database to store topics, posts and articles, identify posts by some IDs and use these IDs in links. This database already exists and we can easily connect to it and bind these IDs to the topic titles, where all these valuable keywords are stored.

mod_seo introduces only one directive – SeoRule – with the syntax very close to that of RewriteRule directive in mod_rewrite, but SeoRule rewrites links on pages instead of rewriting requested URLs. Together with RewriteRule and RewriteMap directives and database support (already built into Helicon Ape engine) we can make a configuration to retract keywords from database, put these keywords in the links on pages and then navigate user around the site without changing a single line of code in the web application (forum in our case).

Now we will show you how to turn this http://www.helicontech.com/forum/forum_posts.asp?TID=15116 into this http://www.helicontech.com/forum/15116-RewriteMap_and_RewriteRule_and_querystring.html step by step using our live forum as example.

What we do:

1. Make sure mod_rewrite, mod_dbd and mod_seo modules are enabled in httpd.conf
   
2. Put the following lines into .htaccess in the root of our site:

   DBDriver mssql
   # include DBDParams
   DBDParams "Data Source=(local)\SQLEXPRESS;Initial Catalog=WebWizForum;User ID=sa;Password=*****"
   DBDPrepareSQL "SELECT [Subject]     FROM [WebWizForum].[dbo].[tblTopic]  WHERE [Topic_ID] =@KEY" \
                 getSeoTopic
   DBDPrepareSQL "SELECT [Forum_name]  FROM [WebWizForum].[dbo].[tblForum]  WHERE [Forum_ID] =@KEY" \
                 getSeoForum
   
   RewriteMap SeoTopic dbd:getSeoTopic
   RewriteMap SeoForum dbd:getSeoForum
   RewriteMap slug int:slug
   
   #SEO-ing
   SeoRule forum_topics\.asp\?FID=(\d+)\&title=([^\s"'$&]*) \
           forum$1-$2.html [Redirect,Scope=A]
   SeoRule forum_topics.asp\?FID=(\d+)&PN=(\d+)$ \
           forum$1-${slug:${SeoForum:$1|not-found}}-$2.html [Redirect,Scope=A]
   SeoRule forum_topics.asp\?FID=(\d+)$ \
           forum$1-${slug:${SeoForum:$1|not-found}}.html [Redirect,Scope=A]
   
   SeoRule forum_posts.asp\?TID=(\d+)\&title=([^\s"'$&]*)(?:&(get=last#\d+))? \
           $1-$2.html?4\?$4 [Redirect,Scope=A]
   SeoRule forum_posts.asp\?TID=(\d+)(?:\&PN=(\d+)\&TPN=(\d+))?(?:&(get=last#\d+))?$ \
           $1-${slug:${SeoTopic:$1|not-found}}(?2-$3).html?4\?$4 [Redirect,Scope=A]
   
   #rewrite to the original ones
   RewriteRule forum(\d+)-.*(?:-(\d+))\.html$ /forum/forum_topics.asp\?FID=$1&PN=$2 [NC,QSA,L]
   RewriteRule forum(\d+)-.*\.html$ /forum/forum_topics.asp\?FID=$1 [NC,QSA,L]
   
   RewriteRule (\d+)-.*(?:-(\d+))\.html$ /forum/forum_posts.asp\?TID=$1&TPN=$2 [NC,QSA]
   RewriteRule (\d+)-.*\.html$ /forum/forum_posts.asp\?TID=$1 [NC,QSA]

Now with explanations:

First we need to declare database maps (RewriteMap with database connection) to use in link replacing rule.

• Declare Microsoft SQL Server driver:

  DBDriver mssql

• And include DBDParams string for correct SQL connection

  DBDParams "Data Source=(local)\SQLEXPRESS;Initial Catalog=WebWizForum;User ID=sa;Password=*****"

• Prepare SQL queries to select proper fields (topic/forum name) from corresponding tables of the forum database. I was using Microsoft SQL Server Management Studio to do this. Though any database management tool will do. A ‘@KEY’ in the query has a special meaning and will be substituted by the key value when map call is executed. Declaration is needed to assign names for the queries to refer to them in RewriteMap declaration. We have choosen getSeoTopic and getSeoForum as names for them.

  DBDPrepareSQL "SELECT [Subject]     FROM [WebWizForum].[dbo].[tblTopic]  WHERE [Topic_ID] =@KEY" \
                getSeoTopic
  DBDPrepareSQL "SELECT [Forum_name]  FROM [WebWizForum].[dbo].[tblForum]  WHERE [Forum_ID] =@KEY" \
                getSeoForum

  
  Topics table and corresponding SELECT query

  
  Forums table and corresponding SELECT query

• Now declare database-driven maps using previously declared queries. These maps will be referred to in subsequent SeoRule directives by their names – SeoTopic and SeoForum.

  RewriteMap SeoTopic dbd:getSeoTopic
  RewriteMap SeoForum dbd:getSeoForum

• Define slug map that is used to remove special characters from topic names to make our links more friendly for browsers and user eyes.

  RewriteMap slug int:slug

• Now it is time for SeoRule directives.
  New URL format for main forum pages includes the forum number FID and the forum name taken from the database: http://www.helicontech.com/forum/forum11-Helicon_Ape.html
  [Redirect] flag forces requests to old-style links to be redirected to new analogues (for the above link it’s http://www.helicontech.com/forum/forum_topics.asp?FID=11)
  [Scope=A] says mod_seo to search and replace URLs in tags only

  SeoRule forum_topics.asp\?FID=(\d+)&PN=(\d+)$ \
          forum$1-${slug:${SeoForum:$1|not-found}}-$2.html   [Redirect,Scope=A]
  SeoRule forum_topics.asp\?FID=(\d+)$ \
          forum$1-${slug:${SeoForum:$1|not-found}}.html   [Redirect,Scope=A]

• New URL format for topic pages includes the topic number TID (as the topic name is not unique) and the topic name taken from the db: http://www.helicontech.com/forum/10000-ISAPI_and_ZEND_Framework.html
  [Redirect] flag will deal with its ancestor: http://www.helicontech.com/forum/forum_posts.asp?TID=10000)

  SeoRule forum_posts.asp\?TID=(\d+)(?:\&PN=(\d+)\&TPN=(\d+))?(?:&(get=last#\d+))?$ \
          $1-${slug:${SeoTopic:$1|not-found}}(?2-$3).html?4\?$4   [Redirect,Scope=A]

• As the forum engine doesn’t understand the new URL format, we use rewriting to convert it to habitual one, e.g. http://www.helicontech.com/forum/forum_posts.asp?TID=10000 for forum topics

  RewriteRule ^forum/forum(\d+)-.*(?:-(\d+))\.html$ /forum/forum_topics.asp\?FID=$1&PN=$2 [NC,QSA,L]
  RewriteRule ^forum/forum(\d+)-.*\.html$ /forum/forum_topics.asp\?FID=$1 [NC,QSA,L]

  and for forum posts

  RewriteRule ^forum/(\d+)-.*(?:-(\d+))\.html$ /forum/forum_posts.asp\?TID=$1&TPN=$2 [NC,QSA]
  RewriteRule ^forum/(\d+)-.*\.html$ /forum/forum_posts.asp\?TID=$1 [NC,QSA]

• The result is a page like this:
  

The pleasant thing is you don’t need to change anything in your application — mod_seo and mod_rewrite take care of all cycle of transformations and you just enjoy the SEF picture.

Same technique can be used for many other database driven applications like forums, blogs or e-shops. Existing database may be used to transform links or new maps can be created.

If you need assistance with doing same thing for your application, you can get a Premium Support Plan from us and our support team will be happy to tailor a custom solution for you!

Best regards,
Yaroslav, Anton — HeliconTech Team